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

[Nathan Kurz]

On Thu, Dec 01, 2005 at 10:13:24AM -0500, Matt Sergeant wrote:
> On 30 Nov 2005, at 17:59, Nathan Kurz wrote:
> 
> >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.
> 
> This is nonsense. The perl interface is just fine with regard to blobs 
> - there's even a test suite for it.

I apologize if I've offended you, but I think your response is a bit
harsh.  I'll try to be more specific as to how I might consider it to
be broken, even if this isn't the word you would choose.  While it
certainly works in some cases (including those in the test suite) I
don't think it works in all the cases it could or should.

Inserting blobs works fine if one uses "$sth->bind_param(1, $blob,
SQL_BLOB)" as in the test.  It does not work if one merely uses
"$sth->execute($blob)".  While I can see that this might be behaviour
as designed, it temporarily stumped me when I first starting using it,
and searching the web I found several others stumped by it as well.
The poster to the list presumably was having the same problem.

Looking now at the DBI documentation, I see that values bound using
execute are 'usually treated as "SQL_VARCHAR" types unless the driver
can determine the correct type (which is rare)'.  Because it is simple
to scan the string for NUL's, I guess I consider this one of those
rare cases where the driver can just 'do the right thing'. 

For the second patch I offered, I think I'm on firmer ground.  I don't
think it is currently possible to return NUL containing data from a
user defined function.  Attached is a patch to 08create_function.t to
test for this.  It fails with the released version, but works with my
patched version. While I think my patch is 'safe', I would happily
agree that there might be a more elegant solution.

Thanks for your work in creating this interface!  I don't mean to
sound ungrateful for it.

--nate




--- t/08create_function.t.orig  2005-12-01 10:09:04.000000000 -0700
+++ t/08create_function.t       2005-12-01 10:28:38.000000000 -0700
@@ -1,5 +1,5 @@
 use Test;
-BEGIN { plan tests => 18 }
+BEGIN { plan tests => 19 }
 use DBI;
 
 sub now {
@@ -44,6 +44,10 @@
         return $_[0];
 }
 
+sub return_blob {
+    return "bl" . "\x00" . "ob";
+}
+
 my $dbh = DBI->connect("dbi:SQLite:dbname=foo", "", "", { PrintError => 0 } );
 ok($dbh);
 
@@ -111,4 +115,8 @@
 $result = $dbh->selectrow_arrayref( "SELECT noop(1.1)" );
 ok( $result &&  $result->[0] == 1.1 );
 
+$dbh->func( "return_blob", 0, \&return_blob, "create_function" );
+$result = $dbh->selectrow_arrayref( "SELECT return_blob()" );
+ok( $result &&  $result->[0] eq return_blob() );
+
 $dbh->disconnect;