SQLite Forum

Clarification re parameters needed.
Login

Clarification re parameters needed.

(1) By anonymous on 2021-04-19 23:08:18 [link] [source]

I am very new to SQLite3 in terms of sqlite3.c amalgamation, started this project late march, I if have created a working non visible component for c++ builder, so far it is functioning very well with the functions implemented.

How does this work? 

@AAAA	An "at" sign works exactly like a colon, except that the name of the parameter created is @AAAA.

What I mean by my question is, does the sqlite engine give a name to the parameter like @some_name_1 or, if using the @ symbol, should the programmer provide the name 'some_name' and the engine assigns a number in the case of the ':AAA'

Need to understand this to use in a function I have as per example 

if (dbPropertyResult.empty())
	throw Exception("Field types have not yet been obtained for parameter bindings");

	std::auto_ptr<TSQLiteParameters> param(new TSQLiteParameters);

	param->ColumnName = ColumnName;

	if (FParamFromColName)
		param->ParamName = ":" + param->ColumnName; 
	else
		param->ParamName = ":" + IntToStr((int)Params.size());

	param->ParamValue = Value;
	param->ColumnType = FieldType;

	pName = param->ParamName;

	String type;

	//get the column type from the dbPropertyResult vector using column name
	if (FindType(ColumnName, type))
		param->ColumnType = type;

	Params.push_back(param.get());

	param.release();

TIA

(2.1) By Keith Medcalf (kmedcalf) on 2021-04-20 00:08:32 edited from 2.0 in reply to 1 [source]

The name given is exactly the name specified. That is, if the parameter used in the SQL statement is @boogaloo then the recorded name string is exactly '@boogaloo'. The same applies for all parameters that are not purely anonymous positional parameters (a bare ?).

The assignment of paramters works as follows.

Firstly, the "number of parameters" is set to 0.
The SQL statement is scanned from left to right.
For each purely anonymous positional parameter marker found, increment "number of parameters".
For each "numbered positional found" (?nnn) set the "number of parameters" to nnn if nnn is greater than the current "number of parameters".
If a parameter is encountered which commences with :, @, $ that has not previously been seen (determined by searching the name:position mapping table) in this statement then increment "number of parameters" and record that the name (:xxx, @xxx, or $xxx as used in the statement) maps to the new "number of parameters" position.
Stop when the end of the statement is reached.

Now, allocate an array long enough to store all of the parameters from 1 to the "number of parameters" (even if there are some that are not referenced).

sqlite3_bind_parameter_count returns the upper extent of the array.

sqlite3_bind_parameter_index looks up the given string (exactly) in the mapping table of "names to locations" and returns the parameter number stored for that name.

sqlite3_bind_parameter_name looks up the parameter number given in the table of "names to locations" and returns the name associated with that location.

In some cases if a positional name is used, that positional name might be stored against the location number (that is, if the parameter marker ?437 is stored, then there may be a mapping created between the name ?437 and the location 437).

Edited to exchange the work greater for the word less

(3) By anonymous on 2021-04-20 06:14:24 in reply to 2.1 [link] [source]

Thanks Keith, 

that is what I thought, just needed to be absolutely sure about it before going further as all INSERT and UPDATE's are auto generated by a worker component that looks at visible custom edit controls on a form matched to the column names and the properties in a table.

The project is going very well atm.

Cheers