My guess would be that finalizing the create table statement makes the bound value go out of scope and thus be unavailable to the insert statement.
Bound values reside somewhere in the internal prepared statement structure and do not get copied into the database file, even if they happen to be default column values. -----Ursprüngliche Nachricht----- Von: Nathaniel Trellice [mailto:napt...@yahoo.co.uk] Gesendet: Mittwoch, 24. September 2014 13:36 An: sqlite-users@sqlite.org Betreff: [sqlite] Crash when binding default column value Dear list members, 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
