[sqlite] (BUG?) sqlite3_bind_blob oops, not exactly a bug

[s***@verge.info.tm]

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;
}

[Dominique Devienne]

Post by s***@verge.info.tm
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?

Consider the example below. This demonstrates in pure SQL using the
official CLI
what you are doing in C I think. SQLite is "weakly" typed, i.e. you say the
column
bar stores text, but if you insert a blob, that value stays as a blob. Here
x'74657374'
and 'test' are bit-wise identical, but one is typed as 'blob' (x'hexa' is a
blob literal),
and the other as 'text', which I kinda prove by using length() or cast()
below. There
is no conversion or type coercion taking place when inserting blobs,
although there
are some with numbers. Same thing on the select. A blob will never equal
text,
even if bitwise equal. You need to read up on
https://www.sqlite.org/datatype3.html IMHO.
I'm no expert with these rules, just aware of the few things that I need to
remember
to remain sane with SQLite:
1) type affinity will bite you eventually, so watch out for it, and
2) SQLite is "weakly" typed, each value can have its own type despite the
column's declared type (if any)

Hope this helps. --DD

C:\Users\ddevienne>sqlite3
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo (id INTEGER PRIMARY KEY, bar TEXT);
sqlite> insert into foo values
...> (1, 'test'), -- as text
...> (2, x'74657374'); -- as blob
sqlite> .header on
sqlite> .mode column
sqlite> select id, length(bar), typeof(bar) from foo;
id length(bar) typeof(bar)
---------- ----------- -----------
1 4 text
2 4 blob
sqlite> select id, length(bar2), typeof(bar2) from (
...> select id, cast(bar as text) as bar2 from foo
...> );
id length(bar2) typeof(bar2)
---------- ------------ ------------
1 4 text
2 4 text
sqlite> select id from foo where bar = 'test';
id
----------
1
sqlite> select id from foo where bar = x'74657374';
id
----------
2