Skip Menu |
 

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 4860
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: kwilliams [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
Subject: Placeholders not working for UPDATE statements
X-RT-Original-Encoding: iso-8859-1
Content-Length: 1061
Hi, The following demonstrates a bug in SQLite or DBD::SQLite 0.29: ------------------------- [sol2:~/bin/test] % perl -nle 'print "$.\t$_"' sqlite-test.pl 1 use strict; 2 use DBI; 3 4 my $dbh = DBI->connect('dbi:SQLite:dbname=test', undef, undef, 5 {RaiseError => 1}); 6 7 $dbh->do("CREATE TABLE foo (a, b)"); 8 $dbh->do("INSERT INTO foo VALUES (1, 2)"); 9 $dbh->do("INSERT INTO foo VALUES (2, 4)"); 10 11 my $sth = $dbh->prepare("SELECT oid FROM foo"); 12 $sth->execute; 13 while (my ($oid) = $sth->fetchrow_array) { 14 $dbh->do("UPDATE foo SET a=7 WHERE oid = ?", undef, $oid); 15 } [sol2:~/bin/test] % rm -f test ; perl sqlite-test.pl DBD::SQLite::db do failed: at sqlite-test.pl line 14. DBD::SQLite::db do failed: at sqlite-test.pl line 14. ------------------------- If I do the UPDATE query without being nested inside the SELECT query, it seems to work fine. So maybe some routine isn't re-entrant or things are otherwise stomping on each other. -Ken
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 954
Download (untitled) / with headers
text/plain 954b
On closer inspection this doesn't seem related to placeholders at all: --------------------------- [sol2:~/bin/test] % perl -nle 'print "$.\t$_"' sqlite-test.pl 1 use strict; 2 use DBI; 3 4 my $dbh = DBI->connect('dbi:SQLite:dbname=test', undef, undef, 5 {RaiseError => 1}); 6 7 $dbh->do("CREATE TABLE foo (a, b)"); 8 $dbh->do("INSERT INTO foo VALUES (1, 2)"); 9 $dbh->do("INSERT INTO foo VALUES (2, 4)"); 10 11 my $sth = $dbh->prepare("SELECT b FROM foo"); 12 $sth->execute; 13 while (my ($b) = $sth->fetchrow_array) { 14 $dbh->do("UPDATE foo SET a=7"); 15 } [sol2:~/bin/test] % rm -f test ; perl sqlite-test.pl DBD::SQLite::db do failed: at sqlite-test.pl line 14. DBD::SQLite::db do failed: at sqlite-test.pl line 14. --------------------------- It looks like this happens whenever I try to UPDATE a table and there's already an active statement. -Ken
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 435
Download (untitled) / with headers
text/plain 435b
I discovered that, according to http://www.mail-archive.com/sqlite- users@sqlite.org/msg00878.html , SQLite can't make any updates while another statement is reading through results. Drat. This is a pretty huge compatibility kludge, since most other databases (at least MySQL, Oracle, and Postgres) can do this. Might want to mention this in the docs though, as this is an SQLite limitation and not a DBD::SQLite problem. -Ken


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.