Do you plan on adding support for binary data in the future to overcome some of the issues you mentioned?
--eric On Thu, Feb 19, 2004 at 03:23:17PM -0800, D. Richard Hipp wrote: > Eric Kolve wrote: > > > > 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] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
