In my endless obsession with premature optimization, I've been using
sqlite3_bind_blob, whenever I know the length of what I'm inserting, even if
it's text or whatnot. It exhibits some very strange properties though, which I
can't imagine is anything other than a bug. Here's my test case:
-----------------------------------------------------------------------mimesucks-----------------
#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_stmt* clear;
sqlite3_prepare(db,LITLEN("DELETE FROM foo"),&clear,NULL);
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_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_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_step(clear);
sqlite3_reset(clear);
puts("This is fine though?");
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));
}
return 0;
}
-----------------------------------------------------------------------mimesucks-----------------
When I insert anything via sqlite3_bind_blob, sqlite then loses the record, uh,
forever. Searching for the exact same text I just inserted, I cannot get any
results, if sqlite3_bind_blob is used. Even if I use sqlite3_bind_blob on both
the search text and the insert text, sqlite still comes up with no results.
Interestingly, if I use sqlite_bind_text on insert, then sqlite_bind_blob on
select, the database DOES find a result. Only when I use sqlite3_bind_blob, on
an insert statement, does the resulting field become entirely unsearchable. If I
get the row by some other criteria, the data inserted with sqlite3_bind_blob and
sqlite3_bind_text are byte-equivalent, and when I look at them using
sqlite3_column_blob/bytes, neither one has an embedded null terminator.
I'm not... familiar with sqlite's innards, but my best guess is that there must
be a (hidden) null terminator included on disk for stuff inserted with
sqlite3_bind_text, that isn't reported, but when sqlite3_bind_blob is used, that
extra byte for the null doesn't go on disk. When sqlite encounters an "a = b"
expression, it assumes that null byte exists, and adds the null terminator to
the criteria, which then fails to find the row, since the actual data was
inserted with sqlite3_bind_blob and lacks a null terminator.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
