Subject: | DB Statement Handle Confusion |
The attached script dies with the following error:
DBD::SQLite::st fetchrow_arrayref failed: bug.file may not be NULL(1) at dbdimp.c line 402 at /home/jhedden/bug.pl line 49.
Issuing rollback() for database handle being DESTROY'd without explicit disconnect().
This show that the 2nd time the select statement is to be executed, the insert statement is being executed instead.
#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
# Create DB
my $dbh;
if (! ref($dbh = DBI->connect("dbi:SQLite:dbname=bug.db", '', '',
{ PrintError => 0,
RaiseError => 1,
AutoCommit => 0 })))
{
print("Failure connecting to DB: ", $DBI::errstr, "\n");
exit(1);
}
# Create table
$dbh->do('CREATE TABLE bug (
cksum INTEGER NOT NULL,
size INTEGER NOT NULL,
file VARCHAR(100) NOT NULL,
PRIMARY KEY ( cksum, size ) )');
$dbh->commit();
# Prepare select and insert statements
my $sel = $dbh->prepare('SELECT COUNT(1)
FROM bug
WHERE cksum = ?
AND size = ?');
my $ins = $dbh->prepare('INSERT INTO bug
(cksum, size, file)
VALUES
(?, ?, ?)');
# Fetch data
$sel->execute(99, 9);
$sel->fetchrow_arrayref();
# Fetch 2nd set of data - it will fail
$sel->execute(123, 45);
$sel->fetchrow_arrayref();
# Done
$sel->finish();
$dbh->commit();
$dbh->disconnect();
unlink('bug.db');
# EOF