Hi, I'd like to submit the following bug report. However, before suspecting the problem in SQLite, we've debugged and traced our own code for two days, and at last got it reproducible within SQLite.
DESCRIPTION: It's an absolute strange behaviour: if you do queries with the LIKE operator using the new sqlite_prepare_v2() interface *and* using host parameters, the column names returned by sqlite3_column_name() get invalid after the first call to sqlite3_step() According to the documentation, the string pointer returned by sqlite3_column_name() "is valid until either the prepared statement is destroyed by sqlite3_finalize() or until the next call to sqlite3_column_name() [...] on the same column" (see http://www.sqlite.org/capi3ref.html#sqlite3_column_name ) We've prepared a short sample code to illustrate the problem. Code was tested with SQLite 3.7.5 using SQLITE_ENABLE_UPDATE_DELETE_LIMIT; platform is AMD64 (with Debian 5) When running this test either without host parameters, or with the "old" sqlite_prepare() interface, everything works fine. We don't have the resources to investigate deeper into the SQLite source code, but I'm sure that the sample code makes the problem easily visible. The issue is propably not critical, but within our application we cache(d) the column names after preparing the statements once, so this behaviour at least caused some serious trouble when trying to access the row data by "our" column names. The most straightforward "bug fix" would be an documentation update, stating that the returned string pointers maybe are invalid after sqlite3_step(). ;-) For any questions don't hezitate to contact me. Best regards Klaus Keppler Sample code (please adjust to any test table; schema doesn't matter, we tried many different variants) ---cut--- /* Test-Code; Public Domain */ #include <stdio.h> #include <stdlib.h> #include <string.h> #define SQLITE_API #define SQLITE_ENABLE_UPDATE_DELETE_LIMIT #include "sqlite3.h" int main(void) { sqlite3 *dbh = NULL; sqlite3_stmt *stmt = NULL; int done; const char *search = "%u%"; const char *sql = "SELECT LOG_MESSAGE FROM LOG WHERE LOG_MESSAGE LIKE :1"; const char *name = NULL; if (sqlite3_open("sqlite.db", &dbh) != SQLITE_OK) { printf("sqlite3_open() failed\n"); exit(-1); } if (sqlite3_prepare_v2( dbh, sql, strlen(sql)+1, &stmt, NULL) != SQLITE_OK) { printf("sqlite3_prepare() failed\n"); exit(-1); } /* get column name */ name = sqlite3_column_name(stmt, 0); printf("Column name (1): '%s'\n", name == NULL ? "(NULL)" : name); /* bind parameter */ if (sqlite3_bind_text( stmt, 1, search, search == NULL ? 0 : strlen(search), SQLITE_STATIC) != SQLITE_OK) { printf("sqlite3_bind() failed\n"); exit(-1); } printf("Column name (2): '%s'\n", name == NULL ? "(NULL)" : name); done = 0; while (!done) { int r = sqlite3_step(stmt); printf("Column name (3): '%s'\n", name == NULL ? "(NULL)" : name); const unsigned char *ch; switch (r) { case SQLITE_ROW: /* we have a result row */ ch = sqlite3_column_text(stmt, 0); printf(" result: '%s'\n", ch == NULL ? "(NULL)" : (char*)ch); break; case SQLITE_DONE: /* we're done */ printf(" (done)\n"); done=1; break; default: printf(" RESULT: %i\n", r); exit(-1); } } /* free statement */ sqlite3_finalize(stmt); stmt = NULL; /* close connection */ sqlite3_close(dbh); dbh = NULL; printf("Done.\n"); return(0); } ---/cut--- Output: (the SELECT returned two rows) ---cut--- Column name (1): 'LOG_MESSAGE' Column name (2): 'LOG_MESSAGE' Column name (3): 'Ø$Z' result: '[...]' Column name (3): 'Ø$Z' result: '[...]' Column name (3): 'Ø$Z' (done) Done. ---/cut--- -- ______________________________________________________________________ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus Keppler Tel. (09131) 691-480 Geschäftsführer Fax: (09131) 691-489 Am Weichselgarten 7 UStID.-Nr. DE259788698 91058 Erlangen Amtsgericht Fürth, HRB 11477 www.keppler-it.de Sitz d. Gesellschaft: Erlangen ______________________________________________________________________ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
