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

In reply to Re^2: How to store NUL characters into SQLite BLOB ? by Anonymous Monk
in thread How to store NUL characters into SQLite BLOB ? by dont_you

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.