D. Richard Hipp wrote:
> A design proposal for SQLite version 3.0 can be found at:
>
> http://www.sqlite.org/prop2.html
>
Richard,
I read your proposal and it all look very promising to me.
I would like to propose some additions to the API to support named
parameters in the SQL statement. These features will make it easier to use
parameters in complex SQL statements, and also allow generalized handling of
SQL statements in cases where the statement to be executed is not know
before it is executed (this occurs with manually entered SQL in database
"explorer" applications for example). It also makes the use of pre-prepared
SQL more straight forward.
First I would propose that SQLite recognize named parameters in the SQL
code. A named parameter is a colon, ':', followed by the parameter name. The
name itself must be a valid SQL identifier, like table and column names.
Some example names are; :foo, :bar, :part_number, and :zip_code. SQLite
would recognize the named parameters when the statement is parsed by the
prepare API function. SQLite will maintain a list of parameters found in the
statement. As each parameter name recognized, SQLite will scan the list of
parameters to see if this name has appeared earlier in the statement. If so
it will use the previously assigned index for that parameter. If not, it
will add the new parameter name to the end of the list (effectively
assigning it the next index value).
The API should provide functions that allow the application to inspect the
number, type, and names of the parameters that were discovered while parsing
the SQL. These functions could be called any time after the statement is
prepared.
int sqlite3_param_count(sqlite3_stmt* stmt);
int sqlite3_param_type(sqlite3_stmt* stmt, int iParm);
const char* sqlite3_param_name(sqlite3_stmt* stmt, int iParm);
The first function returns the number of parameters in the statement. The
second returns the type code of the indexed parameter. This function would
use the same type codes as the sqlite3_column_type function. The third would
return the name (without the colon prefix character) of the parameter. The
parameter names may be used to build a menu of parameters for a user to fill
in before the statement is executed.
The application could use the currently proposed bind functions to bind a
value to the parameter.
int sqlite3_bind_int32(sqlite3_stmt*, int iParm, int value);
int sqlite3_bind_int64(sqlite3_stmt*, int iParm, long long int value);
int sqlite3_bind_double(sqlite3_stmt*, int iParm, double value);
int sqlite3_bind_null(sqlite3_stmt*, int iParm);
int sqlite3_bind_text(sqlite3_stmt*, int iParm, const char* value, int
length, int eCopy);
int sqlite3_bind_text16(sqlite3_stmt*, int iParm, const char* value, int
length, int eCopy);
int sqlite3_bind_blob(sqlite3_stmt*, int iParm, const void* value, int
length, int eCopy);
I noticed a error in the prototypes for the bind text and blob API functions
in the proposal. You have the value data pointers declared as static char
and void pointers. I believe they should be const pointers.
I would propose adding a second set of parallel API functions that would
allow the application to bind the parameters by name for those cases (likely
far more common) where the application knows the names of the parameters
beforehand. This way the application can bind to the parameters by name
without being forced to use the parameter inspection functions to determine
the parameter indexes. This would also be resistant to errors introduced by
reordering the parameters while editing the SQL statement without reordering
the bind calls.
int sqlite3_bind_name_int32(sqlite3_stmt*, const char* name, int value);
int sqlite3_bind_name_int64(sqlite3_stmt*, const char* name, long long
int value);
int sqlite3_bind_name_double(sqlite3_stmt*, const char* name, double
value);
int sqlite3_bind_name_null(sqlite3_stmt*, const char* name);
int sqlite3_bind_name_text(sqlite3_stmt*, const char* name, const char*
value, int length, int eCopy);
int sqlite3_bind_name_text16(sqlite3_stmt*, const char* name, const char*
value, int length, int eCopy);
int sqlite3_bind_name_blob(sqlite3_stmt*, const char* name, const void*
value, int length, int eCopy);
This second set of these bind functions could be eliminated by the use of a
single new API function that would return the index for a parameter given
its name.
int sqlite3_param_index(sqlite3_stmt* stmt, const char* name);
Using this function and one of the index based bind functions together would
accomplish the same thing, but it may introduce slightly more overhead due
to repeated checking of the statement pointer etc. This function could
return an invalid index of zero if the name does not match any of the
parameters for the statement. I think the invalid, zero, index approach
should be quite safe since the bind functions will need to check and report
the invalid index through its error return code anyway.
On the other hand, it might be better to return an explicit error code, and
pass the index back through a int pointer variable. Returning an error code
will make this function slightly more dificult to use, since it will require
declaring a seperate int variable, and two statements (one to get the index,
and one to bind the parameter value) to bind a parameter by name.
In addition, I think it would be useful for some applications to be able to
extract the values of a statement's parameters in much the same way as it
can extract the values of its result columns. This would mean adding a set
of parameter inspection functions (similar to those proposed for getting the
column data) as below.
const unsigned char *sqlite3_param_data(sqlite3_stmt*, int iParm);
const unsigned char *sqlite3_param_data16(sqlite3_stmt*, int iParm);
int sqlite3_param_bytes(sqlite3_stmt*, int iParm);
long long int sqlite3_param_int(sqlite3_stmt*, int iParm);
double sqlite3_param_float(sqlite3_stmt*, int iParm);
A statement's parameter values would be reset to null values when the
statement is reset.
It seems to me that these features can be added in a manner that is
completely compatible with existing applications that use ? based positional
parameters. These parameters would be handled in the same manner except
their name would be null. Parameters with null names would always add a new
parameter to the parameter list (as they do now). The user can get a count
of the positional parameters, and their type and value information using an
index, but the name would be returned as a null pointer and name based
binding and lookup would all fail when passed a null name pointer. It might
even be possible to mix the two types of parameters in a single SQL
statement, but I'm not sure why anyone would want to do that.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
