Re: How to store NUL characters into SQLite BLOB ?

DBD::SQLite usually does not come with the latest version of SQLite library. DBD::SQLite has a private variable sqlite_version that tells you the version of SQLite library it uses.

Because of this, what you read from the documentation of the latest SQLite library, when you try it with DBD::SQLite, it does not neccessary to work. We cannot determine whether this is a bug of DBD::SQLite, only base on your code. Several possibilities:

The version of SQLite came with DBD::SQLite does not support BLOB with \x00. In this case, it is not a bug at all;
The version of SQLite came with DBD::SQLite claims to support BLOB with \x00, but there was a bug. In this case, the bug is with SQLite, not DBD::SQLite;
There is really a bug in DBD::SQLite. (The version of SQLite library it used supports \x00 with BLOB, but somehow DBD::SQLite messed that up)

Comment on Re: How to store NUL characters into SQLite BLOB ?

Replies are listed 'Best First'.
Re^2: How to store NUL characters into SQLite BLOB ? by Anonymous Monk on Nov 11, 2005 at 02:55 UTC
I've tracked down the problem a bit deeper, and determined that it indeed is a problem with DBD::SQLite. Contrary to the other comments on this page, it is not a problem with retrieval but with storage. Blobs set by outside means are retrieved correctly. Currently, the full NUL containing BLOB is being stored, but since it be being bound with 'sqlite3_bind_text()' the SQLite internal type is being set to text, and then the subsequent retrieve fails. The solution is use sqlite3_bind_blob() to bind the variable. There is is currently code in dbdimp.c that appears to do this, but it never is called. I'm not sure if this is due to a bug or something not implemented at a higher level. I've come up with a a workaround patch, but I don't understand the internals well enough to know whether it is a good idea. --- dbdimp.c.orig 2005-11-10 19:27:55.000000000 -0700 +++ dbdimp.c 2005-11-10 19:27:32.000000000 -0700 @@ -356,11 +356,17 @@ STRLEN len; char * data = SvPV(value, len); retval = sqlite3_bind_blob(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); - } + } else { STRLEN len; char * data = SvPV(value, len); - retval = sqlite3_bind_text(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + if (memchr(data, 0, len)) { + sqlite_trace(4, "binding NUL containing data as a blob"); + retval = sqlite3_bind_blob(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + } + else { + retval = sqlite3_bind_text(imp_sth->stmt, i+1, data, len, SQLITE_TRANSIENT); + } }	[reply]
Re^2: How to store NUL characters into SQLite BLOB ? by Anonymous Monk on Sep 22, 2005 at 17:58 UTC
Well, I see the bug with SQLite 3.2.5 and DBD::SQLite 1.09. SQLite doesn't mention any bug fixes related to this (there is only one more recent version, 3.2.6), and as far as I canl tell, 1.09 is the latest SQLite version.	[reply]