Darn it! Sorry! I forgot to reset my prepared select statement. My whole example
was screwed up. The actual behavior is at least sensible-ish, but still not
quite right.
If you use sqlite3_bind_blob on insert and select, it will successfully find the
test row. Only mixing sqlite3_bind_blob, and sqlite3_bind_text causes these
mysterious failures. Does sqlite3_bind_text... encode it to UTF-16 or something?
This is still a (moderately) huge problem for troubleshooting, because for
instance inserting a row with a text field using the "sqlite3" command line
utility won't work if the separate program you wrote to use the database uses
sqlite3_bind_blob. I tried inserting a blob with a trailing null, but that
didn't help either. Am I misunderstanding something about that sqlite3_bind_text
function?
Here's the not stupidly buggy example:
-----------------------------------------------------------------
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h> // NULL
#include <assert.h> //
#define LITLEN(lit) lit, sizeof(lit)-1
int main(int argc, char *argv[])
{
sqlite3* db;
sqlite3_open(":memory:",&db);
sqlite3_exec(db,"CREATE TABLE foo (id INTEGER PRIMARY KEY, bar
TEXT)",NULL,NULL,NULL);
sqlite3_stmt *ins,*sel;
sqlite3_prepare(db,LITLEN("INSERT INTO foo (bar) VALUES (?)"),&ins,NULL);
sqlite3_prepare(db,LITLEN("SELECT id FROM foo WHERE bar = ?"),&sel,NULL);
puts("This is fine.");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
assert(SQLITE_ROW == sqlite3_step(sel));
printf("Got ID %d\n",sqlite3_column_int(sel,0));
sqlite3_reset(sel);
sqlite3_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),&clear,NULL);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is fine too.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is NOT fine.");
sqlite3_bind_blob(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_text(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
sqlite3_step(clear);
sqlite3_reset(clear);
puts("This is also NOT fine");
sqlite3_bind_text(ins,1,"test",4,NULL);
sqlite3_step(ins);
sqlite3_reset(ins);
sqlite3_bind_blob(sel,1,"test",4,NULL);
if(SQLITE_ROW != sqlite3_step(sel)) {
printf("no results? %s\n",sqlite3_errmsg(db));
} else {
printf("Got ID %d\n",sqlite3_column_int(sel,0));
}
sqlite3_reset(sel);
return 0;
}
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
