Subject: | INSERT After PK Failure Also Fails Using Prepared |
Using prepared statements/bind params, an INSERT with a duplicate pk
value in the database fails do to a PK violation with the following error:
DBD::SQLite::st execute failed: column id is not unique(19) at dbdimp.c
line 403
This is of course, expected. However, the very next INSERT statement
using a unique pk value executed on the same statement handle also fails
with the following, slightly different error:
DBD::SQLite::st execute failed: column id is not unique(21) at dbdimp.c
line 376
This does not happen if one uses regular sql statements using $dbh->do
instead of the prepared statement. On win32, this only happens in
1.13/1.14 and worked fine in 1.12. One some other platforms, rather than
an error, a core dump might happen instead.
Attached is the test script containing a bare bones example of the issue.
Subject: | test.pl |
#!/usr/bin/perl -wT
use strict;
use warnings;
use DBI;
unlink 'test.db';
my $dbh = DBI->connect('dbi:SQLite:test.db', undef, undef, {
AutoCommit => 1,
PrintError => 0,
PrintWarn => 0,
RaiseError => 1
});
$dbh->do('CREATE TABLE test (id VARCHAR(10) NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY(id));');
$dbh->do('INSERT INTO test (id, name) VALUES (\'1\', \'a\');');
my $sth = $dbh->prepare('INSERT INTO test (id, name) VALUES (?, ?)');
eval {
$sth->execute('1', 'a');
};
warn $@;
## the following line barfs on win32 1.13/1.14 with:
## DBD::SQLite::st execute failed: column id is not unique(21) at dbdimp.c line 376 at test.pl line 26
$sth->execute('2', 'b');