Re: [sqlite] problem with blobs (perl code)
On Mon, Dec 05, 2005 at 08:23:19AM -0500, [EMAIL PROTECTED] wrote:
> > OK, so 1.11 is on CPAN which fixes this. However I have another bug
> > report about this not working for user defined functions, where I do
> > this:
> >
> > s = SvPV(result, len);
> > sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
I'm the original reporter of that bug, and I can confirm that while it
did not work in DBD::SQLite 1.09, it works without problem in 1.11.
sqlite3_result_text() appears to deal fine with embedded NUL's.
Attached is a tiny C program I just used to confirm this to myself.
Large thanks to Matt and Richard for so quickly ending up with a great
solution that just transparently works!
--nate
#include
#include
/* gcc test_blob_as_text.c -lsqlite3 -o test_blob_as_text */
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i=0; i
Re: [sqlite] problem with blobs (perl code)
On 5 Dec 2005, at 13:23, [EMAIL PROTECTED] wrote: I added a test case (check-in [2798]) that checks to make sure that sqlite3_result_text is able to deal with embedded '\000' characters in a string. I appears to work fine. I cannot reproduce the problem Can you suggest other ways of producing the problem? Not yet. Let me write a test case and see if there's really a bug or not. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
Matt Sergeant <[EMAIL PROTECTED]> wrote:
>
> OK, so 1.11 is on CPAN which fixes this. However I have another bug
> report about this not working for user defined functions, where I do
> this:
>
> s = SvPV(result, len);
> sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
>
> (SvPV is a macro that retrieves a char* from result, and as a side
> effect sets len to the length of the string in bytes, even if it
> contains nuls).
>
> Is this maybe a bug in sqlite3_result_text()? I could patch it to do:
>
> 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 );
> }
>
> But that seems a waste of resources if it's a bug in
> sqlite3_result_text().
>
I added a test case (check-in [2798]) that checks to make sure
that sqlite3_result_text is able to deal with embedded '\000'
characters in a string. I appears to work fine. I cannot
reproduce the problem
Can you suggest other ways of producing the problem?
--
D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On 2 Dec 2005, at 13:07, [EMAIL PROTECTED] wrote:
Right. So it's retreival that's the issue when this occurs, because I
do:
int col_type = sqlite3_column_type(stmt, i);
and it returns SQLITE_TEXT, so I then do:
val = (char*)sqlite3_column_text(stmt, i);
which doesn't return a length for me.
Would sqlite3_column_bytes() return the right length there rather than
me doing strlen() on the resulting data?
yes it will.
OK, so 1.11 is on CPAN which fixes this. However I have another bug
report about this not working for user defined functions, where I do
this:
s = SvPV(result, len);
sqlite3_result_text( context, s, len, SQLITE_TRANSIENT );
(SvPV is a macro that retrieves a char* from result, and as a side
effect sets len to the length of the string in bytes, even if it
contains nuls).
Is this maybe a bug in sqlite3_result_text()? I could patch it to do:
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 );
}
But that seems a waste of resources if it's a bug in
sqlite3_result_text().
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] problem with blobs (perl code)
On 2 Dec 2005, at 08:07, [EMAIL PROTECTED] wrote: Would sqlite3_column_bytes() return the right length there rather than me doing strlen() on the resulting data? yes it will. Doh! In that case then 1.11 will head to CPAN with blobs working transparently. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
Matt Sergeant <[EMAIL PROTECTED]> wrote: > On 1 Dec 2005, at 21:52, [EMAIL PROTECTED] wrote: > > > SQLite does has a separate BLOB type. But for TEXT types, SQLite > > still works like Perl and carries around a length so that the string > > can have embedded '\000' characters. I just added a test to the > > test suite to verify that this works. > > > > Suppose you do this: > > > >sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); > > > > If this is part of an INSERT, say, then you will insert a 10-character > > string that happens to contain a couple of extra \000 characters. > > Right. So it's retreival that's the issue when this occurs, because I > do: > >int col_type = sqlite3_column_type(stmt, i); > > and it returns SQLITE_TEXT, so I then do: > >val = (char*)sqlite3_column_text(stmt, i); > > which doesn't return a length for me. > > Would sqlite3_column_bytes() return the right length there rather than > me doing strlen() on the resulting data? > yes it will. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On 1 Dec 2005, at 21:52, [EMAIL PROTECTED] wrote: SQLite does has a separate BLOB type. But for TEXT types, SQLite still works like Perl and carries around a length so that the string can have embedded '\000' characters. I just added a test to the test suite to verify that this works. Suppose you do this: sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); If this is part of an INSERT, say, then you will insert a 10-character string that happens to contain a couple of extra \000 characters. Right. So it's retreival that's the issue when this occurs, because I do: int col_type = sqlite3_column_type(stmt, i); and it returns SQLITE_TEXT, so I then do: val = (char*)sqlite3_column_text(stmt, i); which doesn't return a length for me. Would sqlite3_column_bytes() return the right length there rather than me doing strlen() on the resulting data? Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
Nathan Kurz <[EMAIL PROTECTED]> wrote: > > > So as far as I can tell, both SQLite and Perl are doing exactly what > > they ought to be. > > That certainly could be argued, although it does seem to trap the > unwary (like me) with fair regularity. One option for 'improving' it > might be to make 'BLOB' become a bona fide column affinity, so that a > TEXT type inserted into a BLOB column would be stored as a blob. > > Would there be a downside to do this? > You mean besides breaking backwards compatibility? ;-) For one, it is not clear to me what to do when you insert an integer into a BLOB column. Do I convert the integer to text first then store the text as a blob. Or do I store the twos complement representation of the integer as a blob. And how many bytes of the integer - internally SQLite has 1, 2, 3, 4, 6, and 8 byte integers. All of this gets very merky, so I figure the best approach is to do nothing. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On Thu, Dec 01, 2005 at 09:52:25PM -0500, [EMAIL PROTECTED] wrote: > Suppose you do this: > >sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); > > If this is part of an INSERT, say, then you will insert a 10-character > string that happens to contain a couple of extra \000 characters. > The statement: This is indeed exactly how DBD::SQLite handles an execute statement with a blob argument: it's bound as text but with the length set according to the blob length. So the retrievals happen exactly as you describe (although I had not thought to try 'cast as blob'). > So as far as I can tell, both SQLite and Perl are doing exactly what > they ought to be. That certainly could be argued, although it does seem to trap the unwary (like me) with fair regularity. One option for 'improving' it might be to make 'BLOB' become a bona fide column affinity, so that a TEXT type inserted into a BLOB column would be stored as a blob. Would there be a downside to do this? --nate
Re: [sqlite] problem with blobs (perl code)
A very interesting discussion thread! Thanks to everyone who posted for adding to my knowledge. Bob Cochran [EMAIL PROTECTED] wrote: Matt Sergeant <[EMAIL PROTECTED]> wrote: Perl has no concept of blobs. A scalar variable can be one of: IV (integer) UV (unsigned integer) NV (double) PV (string) so a blob is just a string - but perl carries a length around with it so you can have binary data in there. SQLite does has a separate BLOB type. But for TEXT types, SQLite still works like Perl and carries around a length so that the string can have embedded '\000' characters. I just added a test to the test suite to verify that this works. Suppose you do this: sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); If this is part of an INSERT, say, then you will insert a 10-character string that happens to contain a couple of extra \000 characters. The statement: SELECT length(x) FROM table; will return 3 because the length() function is counting UTF-8 characters, not bytes, and it will stop at the first '\000'. But if you say this: SELECT length(cast(x AS blob)) FROM table; you will get 10, because length() returns the number of bytes in a blob. Similarly, if you say: SELECT quote(x) FROM table; you will get 'abc' as a reply. But if you say: SELECT quote(cast(x AS blob)) FROM table; then you will get X'6162630078797A007071' as the result. So you see, all 10 bytes of the original string are still there. So as far as I can tell, both SQLite and Perl are doing exactly what they ought to be. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
Matt Sergeant <[EMAIL PROTECTED]> wrote: > Perl has no concept of blobs. A scalar variable can be one of: > > IV (integer) > UV (unsigned integer) > NV (double) > PV (string) > > so a blob is just a string - but perl carries a length around with it > so you can have binary data in there. > SQLite does has a separate BLOB type. But for TEXT types, SQLite still works like Perl and carries around a length so that the string can have embedded '\000' characters. I just added a test to the test suite to verify that this works. Suppose you do this: sqlite3_bind_text(pVm, 1, "abc\000xyz\000pq", 10, SQLITE_STATIC); If this is part of an INSERT, say, then you will insert a 10-character string that happens to contain a couple of extra \000 characters. The statement: SELECT length(x) FROM table; will return 3 because the length() function is counting UTF-8 characters, not bytes, and it will stop at the first '\000'. But if you say this: SELECT length(cast(x AS blob)) FROM table; you will get 10, because length() returns the number of bytes in a blob. Similarly, if you say: SELECT quote(x) FROM table; you will get 'abc' as a reply. But if you say: SELECT quote(cast(x AS blob)) FROM table; then you will get X'6162630078797A007071' as the result. So you see, all 10 bytes of the original string are still there. So as far as I can tell, both SQLite and Perl are doing exactly what they ought to be. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] problem with blobs (perl code)
On 1 Dec 2005, at 15:10, [EMAIL PROTECTED] wrote: So in the example of $sth->execute($blob), if $blob contains an integer, use sqlite3_bind_int64(), or if $blob contains a string use sqlite3_bind_text(), or if $blob contains a blob, then use sqlite3_bind_blob(), and so forth. Is there something about perl internals that prevents the above from working? Yes. Perl has no concept of blobs. A scalar variable can be one of: IV (integer) UV (unsigned integer) NV (double) PV (string) so a blob is just a string - but perl carries a length around with it so you can have binary data in there. You'd have to check something like strlen(data) != len to determine if it contained NULs (or just scan for the NUL - same difference). Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
Jim Dodgen <[EMAIL PROTECTED]> wrote: > Perl is mostly typeless, or more correctly has late dynamic binding. No way > to > tell between a scalar used as a string, or a blob. I see no down side in > having > to specify the SQL_BLOB type when access a BLOB field, I just would help to > have know that. > > JIm Dodgen > > I think you have misunderstood my suggestion. I'm talking about having the DBD::SQLite code check the type of $blob at runtime, then invoking the appropriate sqlite3_bind api **at runtime** when the type of $blob is well known. The code that does this is inside the DBD::SQLite and is never seen by the person using DBD::SQLite. > > Quoting [EMAIL PROTECTED]: > > > So in the example of $sth->execute($blob), if $blob contains an > > integer, use sqlite3_bind_int64(), or if $blob contains a string > > use sqlite3_bind_text(), or if $blob contains a blob, then use > > sqlite3_bind_blob(), and so forth. > > > > Is there something about perl internals that prevents the above > > from working? > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > > > > .
Re: [sqlite] problem with blobs (perl code)
Perl is mostly typeless, or more correctly has late dynamic binding. No way to tell between a scalar used as a string, or a blob. I see no down side in having to specify the SQL_BLOB type when access a BLOB field, I just would help to have know that. JIm Dodgen Quoting [EMAIL PROTECTED]: > So in the example of $sth->execute($blob), if $blob contains an > integer, use sqlite3_bind_int64(), or if $blob contains a string > use sqlite3_bind_text(), or if $blob contains a blob, then use > sqlite3_bind_blob(), and so forth. > > Is there something about perl internals that prevents the above > from working? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > >
Re: [sqlite] problem with blobs (perl code)
On Thu, 1 Dec 2005, Matt Sergeant wrote: > > 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'. > > It's pointless though to do that, because then when they try and get data > out the same way they won't understand why it's truncated. At least this > way they have to bind properly on both in and out. Oh, I forgot to answer the issue - if I did a scan for NUL bytes on every string going into the system it would really hurt those doing performance sensitive apps. Unfortunately for you that includes me :-) Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] problem with blobs (perl code)
On Wed, Nov 30, 2005 at 05:10:19PM -0600, Jim Dodgen wrote: > > What do you get back (using the command-line client) when you > > ask for LENGTH(x) or QUOTE(x) instead of just the column x? > > sqlite> select length(val) from foo; > 3 > sqlite> select quote(val) from foo; > 'MZP' > > strange, reports a length of 3 but the database is > the size of the > file i put into the blob. other than the record in foo the database > is fresh and empty. Although the problem is definitely with Perl here, SQLite's response _is_ pretty odd. The whole value of the BLOB gets inserted (since the length is set correctly by Perl), but since it is inserted as type TEXT, the retrieval (and the length) depend on the first NUL even though the entire blob is actually entered in the database. Probably not quite a bug, but maybe worth a spare thought. > I tried one ot the workarounds noted in > http://rt.cpan.org/NoAuth/Bug.html?id=14595 which had you force the > data type to SQL_BLOB this makes things work!! horray!! I'm glad something worked for you! In some ways that is probably a better solution than my patch, since it is more explicit. D. Richard Hipp <[EMAIL PROTECTED]> writes: > Matt Sergeant monitors this mailing list and should see your post. Thanks! --nate
Re: [sqlite] problem with blobs (perl code) workaround
thanks for the help,
I tried one ot the workarounds noted in
http://rt.cpan.org/NoAuth/Bug.html?id=14595
which had you force the data type to SQL_BLOB
this makes things work!! horray!!
I included the complete test program for reference to others
code snippit
use DBI qw(:sql_types);
print "original slurped file size $fn=".length($data)."\n";;
{
my $sth = $dbh->prepare("INSERT or replace INTO foo (nm, val) VALUES (?, ?)");
$sth->bind_param(1, $fn);
$sth->bind_param(2, $data, {TYPE => SQL_BLOB});
$sth->execute();
$sth->finish;
}
--- complete test program ---
#!/usr/bin/perl -w
#
# jim dodgen 2005
use Carp;
use DBI;
use DBI qw(:sql_types);
use strict;
my %attr = (PrintError => 1,
RaiseError => 0,
AutoCommit => 0);
my $dbh = DBI->connect("dbi:SQLite:slurp.db","","",\%attr);
if (!defined($dbh))
{
croak("could not connect to db");
}
{
my $sth = $dbh->prepare("drop table foo");
if (defined $sth)
{
$sth->execute();
$sth->finish;
}
}
{
my $sth = $dbh->prepare("create table foo (nm, val, primary key (nm))");
$sth->execute();
$sth->finish;
}
undef $/;
my ($fn) = @ARGV; # name of big file
open (IN, $fn);
my $data = ; # slurp complete file into variable
close IN;
print "original slurped file size $fn=".length($data)."\n";;
{
my $sth = $dbh->prepare("INSERT or replace INTO foo (nm, val) VALUES (?, ?)");
$sth->bind_param(1, $fn);
$sth->bind_param(2, $data, {TYPE => SQL_BLOB});
$sth->execute();
$sth->finish;
}
undef $data; # JUST TO FREE SOME SPACE
my $max = $dbh->selectrow_array("SELECT MAX(LENGTH(val)) FROM foo");
$dbh->{LongReadLen} = $max+10;
print "largest BLOB $max\n";
{
my $sth = $dbh->prepare("select nm, val from foo where nm = ?");
my $stat = $sth->execute($fn);
my ($nm, $out) = $sth->fetchrow_array;
$sth->finish;
print "size of $nm BLOB returned from query ".length($out)."\n";
open (NEW1, ">out_".$fn);
binmode NEW1;
print NEW1 $out;
close NEW1;
}
$dbh->commit;
$dbh->disconnect;
Re: [sqlite] problem with blobs (perl code)
Nathan Kurz <[EMAIL PROTECTED]> wrote: > On Wed, Nov 30, 2005 at 04:36:30PM -0600, Jim Dodgen 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. > > I've submitted a patch (http://rt.cpan.org/NoAuth/Bug.html?id=14595), > but never heard back about it. > > DRH -- perhaps if you have contact with the maintainer of the Perl > interface you could pass them on? > Matt Sergeant monitors this mailing list and should see your post. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] problem with blobs (perl code)
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?
thanks
Jim
I'm using sqlite 3.2.7, 1.09 and of the perl module also code and test results
are below.
also when I use the command line sqlite3 I also only get 3 characters back.
-- test perl code
#!/usr/bin/perl -w
#
use Carp;
use DBI;
use strict;
my %attr = (PrintError => 1,
RaiseError => 0,
AutoCommit => 0);
my $dbh = DBI->connect("dbi:SQLite:slurp.db","","",\%attr);
if (!defined($dbh))
{
croak("could not connect to db");
}
{
my $sth = $dbh->prepare("drop table foo");
if (defined $sth)
{
$sth->execute();
$sth->finish;
}
}
{
my $sth = $dbh->prepare("create table foo (nm, val, primary key (nm))");
$sth->execute();
$sth->finish;
}
undef $/;
my ($fn) = @ARGV; # name of big file
open (IN, $fn);
my $data = ; # slurp complete file into variable
close IN;
print "original slurped file size $fn=".length($data)."\n";;
{
my $sth = $dbh->prepare(qq{
INSERT or replace INTO foo (nm, val) VALUES (?, ?)
});
$sth->execute($fn, $data);
$sth->finish;
}
undef $data; # JUST TO FREE SOME SPACE
$dbh->{LongReadLen} = $dbh->selectrow_array(qq{
SELECT MAX(LENGTH(val))
FROM foo
});
{
my $sth = $dbh->prepare("select nm, val from foo where nm = ?");
my $stat = $sth->execute($fn);
my ($nm, $out) = $sth->fetchrow_array;
$sth->finish;
print "size of $nm BLOB returned from query ".length($out)."\n";
open (NEW1, ">out_".$fn);
binmode NEW1;
print NEW1 $out;
close NEW1;
}
$dbh->commit;
$dbh->disconnect;
--- results of running slurp_test.pl
[EMAIL PROTECTED] dev]# time ./slurp_test.pl gorp.foo; ls -l *.foo slurp.db
original slurped file size gorp.foo=4166070
size of gorp.foo BLOB returned from query 3
real 0m0.317s
user 0m0.070s
sys 0m0.150s
-rw-r--r--1 root root 4166070 Nov 30 10:33 gorp.foo
-rw-r--r--1 root root3 Nov 30 14:22 out_gorp.foo
-rw-r--r--1 root root 4185088 Nov 30 14:22 slurp.db
