On Mon, Mar 28, 2011 at 7:44 PM, Klaus Keppler <k...@keppler-it.de> wrote:
> 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 ) > Thanks for your hard work in tracking this down. You are correct, this is a documentation bug. What is happening is that sometimes SQLite will automatically re-prepare a statement on the first call to sqlite3_step(). When that happens, it really creates a new prepared statement, finalizes the old one, then copies the new one over top of the old one. But that finalization of the old prepared statement is the same as calling sqlite3_finalize() in that it cases the old column names to be invalidated. I have updated the documentation here: http://www.sqlite.org/src/fdiff?v1=2ab8766c32afed16&v2=e047f69a61d604d4 > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
