Re: [sqlite] problem with blobs (perl code)

[Nathan Kurz]

On Wed, Nov 30, 2005 at 04:36:30PM -0600, Jim Dodgen wrote:
> I am having a problem with blobs, I seem to insert ok but only get three (3) 
> bytes when I query it back. yes I am setting LongReadLen. any ideas?

The Perl interface through DBD-SQLite-1.09 is broken with regard to
blobs.  It binds the result as text, thus doesn't handle NUL's.  

I've submitted a patch (http://rt.cpan.org/NoAuth/Bug.html?id=14595),
but never heard back about it.  Presumably it'll be incorporated
eventually, but until then here it is.  As mentioned in the bug
report, I'm not sure this is the best approach, although it seems to
work well.  Also included is a patch for a parallel bug
(http://rt.cpan.org/NoAuth/Bug.html?id=15711) regarding blobs being
returned by user defined functions.

DRH -- perhaps if you have contact with the maintainer of the Perl
interface you could pass them on?

--nate



--- 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);
+           }
         }
         
         if (value) {

--- dbdimp.c.orig       2005-11-10 19:27:55.000000000 -0700
+++ dbdimp.c    2005-11-10 20:03:14.000000000 -0700
@@ -707,7 +713,13 @@
         sqlite3_result_double( context, SvNV(result));
     } else {
         s = SvPV(result, len);
-        sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
+       if (memchr(s, 0, len)) {
+           /* if the result contains NUL(s) treat it as a blob */
+           sqlite3_result_blob(context, s, len, SQLITE_TRANSIENT );
+       }
+       else {
+           sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
+       }
     }
 }