| Subject: | prepare_cached brokenness |
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);
}
Show quoted text
>>>
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]);
}
Show quoted text>>>>>>>
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;
}
Show quoted text>>>>>>>>>>>>>>>
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