SQLite problem with some parameterized queries

Question

I am having some trouble using SQLite and parameterized queries with a few tables. I have noticed some queries using the SELECT * FROM Table WHERE row=? are returning 1 row when there should be more rows returned. If I change the parameterized query to SELECT * FROM Table WHERE row='row' then the correct number of rows is returned.

Does anyone know why sqlite3_step would return only 1 row when using a parameterized query vs. using the same query in a traditional non-parameterized way?

I am using a very thin C++ wrapper around SQLite3. I suspect there could be a problem with the wrapper, but this problem only exists on a few tables. It makes me wonder if there is something wrong with the way those tables are setup. Any advice is appreciated.

EDIT: Here is the schema of the simplest table showing the problem: CREATE TABLE RefNums (Key TEXT PRIMARY KEY, TripNumber TEXT, RefDesc TEXT, RefNum TEXT);

I am using the query:

SELECT * FROM RefNums WHERE TripNumber=? and using sqlite3_bind_text. That returns SQLITE_ROW on the first call to sqlite3_step and returns SQLITE_DONE on the second call to sqlite3_step. There should be 2 rows. I have verified the rows exist using SQLDataBrowser. If I change the query to SELECT * FROM RefNumbers WHERE TripNumber='012345'; then it works as expected.

Answer 1

I have solved the problem. This code is running on Windows CE/Windows Mobile platform where virtually all system API are Unicode-only. I am using the ATL 7.0 conversion utility CT2A to convert between T (Unicode) to A (Ascii) before passing the variable to SQLite via the sqlite3_bind_xxx functions. This appears to be the source of the problem because the same program works correctly if I hard-code the query instead of using parameterized queries.

I modified the sqlite3_bind_text wrapper to use SQLITE_TRANSIENT (SQL makes a copy of the data) instead of SQLITE_STATIC (SQLite assumes the pointer is fixed). The program now works correctly. There appears to be some problems with the lifetime of the temporary CT2A object and the lifetime of the sqlite3_stmt object.