> > 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
