I guess what I am asking for is transparent handling of binary data. You shouldn't have to escape anything, other clients shouldn't have to know you escaped anything and you shouldn't have to encode the data.
The current version of SQLite has some support for binary data. But SQLite is not 100% ready to handle binary data just yet. To be safe, you should consider encoding your data into a form that does not contain \000 characters. The sqlite_encode_binary() and sqlite_decode_binary() functions work well for this.
You cannot insert binary data using an INSERT statement in SQL. Why not? Because any \000 character in the binary data will appear to the parser to be the end of the INSERT statement and you will end up with a syntax error. So you cannot insert binary data using sqlite_exec(). But you can insert binary data using sqlite_compile(). Using sqlite_compile() your SQL statement can contain a '?' character any place it is legal to put a string constant. So you can say things like this:
INSERT INTO t1(x) VALUES(?);
Then you can use the sqlite_bind() API to bind the data you want the ? to represent. sqlite_bind() allows you to bind arbitrary length binary data.
This works, mostly. As currently implemented SQLite cannot accept binary data in an indexed column or primary key. (It's going to take a file format change to fix that problem.) But how often do you want an index on binary data anyhow?
There is also a little bit of a problem getting the binary data back out. Using either the sqlite_exec() callback or the sqlite_step() APIs, all you get is a pointer to the beginning of the binary data - you are not told its length. SQLite knows the length internally, it just isn't telling you. We hope to fix that deficiency soon. In the meantime, just store the length in a separate column.
You should also avoid trying to transform binary data using built-in operators or SQL functions. They will not, for the most part, work. With binary data, about all you can do is store and retrieve it.
Here is the biggest show-stopper: If you VACUUM a database that contains binary data, it will truncate the binary data at the first \000 character. The same thing happens if you try to ".dump" the database into an ascii text file. These are really the same problem because VACUUM works internally by doing a ".dump" and piping the results into a new database. At some point, I'll probably work around this by implementing optional mysql-style backslash escapes so that the \000 characters in binary data can be represented by the 4-character sequence "\000". This would be OFF by default (for SQL compatibility) but be selectable ON using a pragma.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
