I think that the diagrams on http://www.sqlite.org/lang_createtable.html are the generic parsing and construction rules for "expr" everywhere an "expr" can be used. The text description further down the page describes restrictions and usage particular to the CREATE TABLE statement itself.
Under COLUMN DEFINITIONS you have: The DEFAULT clause specifies a default value to use for the column if no value is explicitly provided by the user when doing an INSERT. If there is no explicit DEFAULT clause attached to a column definition, then the default value of the column is NULL. An explicit DEFAULT clause may specify that the default value is NULL, a string constant, a blob constant, a signed-number, or any constant expression enclosed in parentheses. An explicit default value may also be one of the special case-independent keywords CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP. For the purposes of the DEFAULT clause, an expression is considered constant provided that it does not contain any sub-queries, column or table references, or string literals enclosed in double-quotes instead of single-quotes. note the "constant expression". The type of "constant" is stated, and that it may also be a constant expression (meaning an expression of constants). 45.7 given literally is a constant. sin(45.7) is a constant expression. Since ? is not a constant, then an expression containing ? is not a constant expression. Perhaps it could be more explicit by saying that bound values are not permitted. Under SQL Data Constraints you will also see that: A CHECK constraint may be attached to a column definition or specified as a table constraint. In practice it makes no difference. Each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated and cast to a NUMERIC value in the same way as a CAST expression. If the result is zero (integer value 0 or real value 0.0), then a constraint violation has occurred. If the CHECK expression evaluates to NULL, or any other non-zero value, it is not a constraint violation. The expression of a CHECK constraint may not contain a subquery. Nothing in the CREATE TABLE (or any CREATE) can contain a bound value (well, I suppose the parser could render it into text and insert that value literally, but at the moment it does not). This is because the schema is stored as the text of the create statements, so the value of that variable must be available each time the constraint parsed when DML is parsed against the definition. >-----Original Message----- >I'm trying to set a default value for a column in my "CREATE TABLE" >statement. I really, really want to bind the default value (using >sqlite3_bind_*) rather than expressing it in SQL text to avoid the >following problems: > > * SQL injection attacks; > * floating point value rounding in conversion to/from text; > * how else can I set a default value for a binary blob column? > > >The SQL syntax tree for the "CREATE TABLE" statement at >http://www.sqlite.org/lang_createtable.html suggests binding a default >value is permitted. In particular"create-table-stmt:column-def:column- >constraint:expr:" can be a bind parameter. > > >The example code, below, highlights the problem I'm having. The code >creates a table with a bound default value for one of the two columns. >The statement is prepared (no syntax error warning), and the value bound >successfully. But upon inserting a row that requires using the default >parameter, it crashes in function sqlite3ExprCodeTarget(). (Inserting a >row with both column values explicitly set works fine.) > > >Tech details: using sqlite-amalgamation-3080600 on Centos 7, compiling >with GCC4.8.2. > > >Am I making a silly mistake? Or, contrary to the documentation, is >binding a default column value in a "CREATE TABLE" statement not >possible? If it's not possible, can anyone suggest another way to achieve >the goal of avoiding conversion ofdefault column values to SQL text? > > >Many thanks, > >Nathaniel > > >Example code: > > > >#include <stdio.h> >#include <stdlib.h> >#include <sqlite3.h> > >int main(int argc, char* argv[]) >{ > sqlite3 *db = NULL; > sqlite3_stmt *pStmt = NULL; > int c; > > /* Open database */ > c = sqlite3_open_v2( > "test.db", &db, (SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE), 0); > if (c) { > fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); > exit(0); > } > > /* Table creation SQL */ > const char *sql = > "CREATE TABLE test_table (" > "name TEXT DEFAULT ('joe'), " > "interest TEXT DEFAULT (?1))"; > > /* Prepare SQL statement */ > c = sqlite3_prepare(db, sql, -1, &pStmt, NULL); > if (c != SQLITE_OK) { > fprintf(stderr, "Can't prepare statement: %s\n", sqlite3_errmsg(db)); > exit(0); > } > > /* Bind SQL statement value */ > const char *golf = "golf"; > c = sqlite3_bind_text(pStmt, 1, golf, -1, SQLITE_STATIC); > if (c != SQLITE_OK) { > fprintf(stderr, "Can't bind statement: %s\n", sqlite3_errmsg(db)); > exit(0); > } > > /* Evaluate SQL statement */ > c = sqlite3_step(pStmt); > if (c != SQLITE_DONE) { > fprintf(stderr, "Can't evaluate statement: %s\n", >sqlite3_errmsg(db)); > exit(0); > } > > /* Destroy prepared SQL statement */ > c = sqlite3_finalize(pStmt); > if (c != SQLITE_OK) { > fprintf(stderr, "Can't destroy statement: %s\n", sqlite3_errmsg(db)); > exit(0); > } > > /* Insert row */ > const char *sql2 = "INSERT INTO test_table (name) VALUES ('jack');"; > char *errmsg = NULL; > c = sqlite3_exec(db, sql2, NULL, NULL, &errmsg); > if (c != SQLITE_OK) { > fprintf(stderr, "Can't insert row: %s\n", errmsg); > exit(0); > } > sqlite3_free(errmsg); > > /* Close database */ > c = sqlite3_close(db); > if (c != SQLITE_OK) { > fprintf(stderr, "Can't close database: %s\n", sqlite3_errmsg(db)); > exit(0); > } > > return 0; >} >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
