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

Reply via email to