FAQ
Hi Mr. Sergeant! (and the members of the DBI-dev mailing list).

I encountered a problem in DBD::SQLite: (version 1.09)

http://search.cpan.org/dist/DBD-SQLite/

It is demonstrated by the following two scripts:

<<<
# Statement-Reuse-Prepare.pl - run it once.
use DBI;

$db_name = "foo.sqlite";
unlink($db_name);
my $test_dsn = "DBI:SQLite:dbname=$db_name";
my $test_user = '';
my $test_password = '';

my $dbh = DBI->connect($test_dsn, $test_user,
$test_password);

foreach my $stmt (
qq{CREATE TABLE testaa (id INTEGER, name CHAR (64));},
qq{INSERT INTO "testaa" VALUES(1, 'Alligator Descartes');},
qq{INSERT INTO "testaa" VALUES(NULL, 'NULL-valued id');},
qq{INSERT INTO "testaa" VALUES(2, 'Gary Shea');},
)
{
$dbh->do($stmt);
}
>>>

And:
<<<<<<<
# Statement-Reuse.pl - run it after the -Prepare script to demonstrate
# the problem
use strict;
use warnings;
use DBI;
my $test_dsn = "DBI:SQLite:dbname=foo.sqlite";
my $test_user = '';
my $test_password = '';
my $test_table = "testaa";

sub Test
{
return @_;
}
my $dbh = DBI->connect($test_dsn, $test_user,
$test_password);
{
my (@ret);
my $test_query = "SELECT name FROM $test_table WHERE id = ?";
my $sth = $dbh->prepare_cached($test_query);
print "DEBUG \$sth == $sth\n";
print "DEBUG \\\$sth == " . (\$sth) . "\n";
@ret = $dbh->selectrow_array($sth, undef, 1);
Test ($ret[0] eq "Alligator Descartes")
or printf("selectrow_array returned a wrong result: %s\n", $ret[0]);
$sth = $dbh->prepare_cached($test_query);
@ret = $dbh->selectrow_array($sth, undef, 1);
Test ($ret[0] eq "Alligator Descartes")
or printf("selectrow_array returned a wrong result: %s\n", $ret[0]);
@ret = $dbh->selectrow_array($sth, undef, 2);
Test ($ret[0] eq "Gary Shea")
or printf("selectrow_array returned a wrong result: %s\n", $ret[0]);
$sth = $dbh->prepare_cached($test_query);
@ret = $dbh->selectrow_array($sth, undef, 2);
Test ($ret[0] eq "Gary Shea")
or printf("selectrow_array returned a wrong result: %s\n", $ret[0]);
}
>>>>>>>

The bug is also reported here:

http://www.issociate.de/board/post/242705/DBD::SQLite_1.09_dies_if_select_returns_0_rows.html

(Short URL - http://xrl.us/hdor )

After delving into the code and trying to solve it, I reached the following
conclusions:

1. The following lines (319 and on) in dbdimp.c in the DBD-SQLite distro, are
doing the wrong thing:

psv = hv_fetch((HV*)SvRV(sth), "Statement", 9, 0);
statement = (psv && SvOK(*psv)) ? SvPV_nolen(*psv) : "";

This is because there isn't a "Statement" key in the Hashref of the statement
handle (sth). As a result the variable "statement" is assigned the empty
string, and it is compiled as such.

2. There seems to be some assignment to the "Statement" key in the core
DBI module but it doesn't take effect there.

I tried to correct everything using this patch:

<<<<<<<<<<<<<<<
Index: dbdimp.c
===================================================================
--- dbdimp.c (revision 18)
+++ dbdimp.c (revision 19)
@@ -259,6 +259,10 @@
imp_sth->retval = SQLITE_OK;
imp_sth->params = newAV();

+ /* Put the "Statement" key manually so it can later be referred to by
+ * sqlite_st_execute */
+ hv_store((HV*)SvRV(sth), "Statement", 9, newSVpv(statement, 0), 0);
+
if ((retval = sqlite3_prepare(imp_dbh->db, statement, 0,
&(imp_sth->stmt), &extra))
!= SQLITE_OK)
{
@@ -313,6 +317,8 @@
if (imp_sth->nrow >= 0) {
SV **psv;
char *statement;
+ psv = hv_fetch((HV*)SvRV(sth), "Statement", 9, 0);
+ statement = (psv && SvOK(*psv)) ? SvPV_nolen(*psv) : "";
if (!sqlite_st_finish(sth, imp_sth)) {
return -1;
}
>>>>>>>>>>>>>>>

But it doesn't work from some reason. Note that I'm not very familiar with
the Perl 5 C API and the perl 5 internals so I can know exactly what to
do. I read "Extending and Embedding Perl" once upon a time, but don't recall
too much from it, or have it handy.

Can anyone help me fix this problem? I spent an entire day trying to fix it
myself. I need it because I'm using DBD::SQLite to test a module I wrote (for
work), and I'd rather be working on the core production code.

Regards,

Shlomi Fish


---------------------------------------------------------------------
Shlomi Fish shlomif@iglu.org.il
Homepage: http://www.shlomifish.org/

Tcl is LISP on drugs. Using strings instead of S-expressions for closures
is Evil with one of those gigantic E's you can find at the beginning of
paragraphs.

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 1 | next ›
Discussion Overview
groupdbi-dev @
categoriesperl
postedSep 1, '05 at 7:13a
activeSep 1, '05 at 7:13a
posts1
users1
websitedbi.perl.org

1 user in discussion

Shlomi Fish: 1 post

People

Translate

site design / logo © 2021 Grokbase