| Subject: | DBD-Pg "do" does not handle multiple prepare statements even with pg_server_prepare set to 0 |
| Date: | Wed, 20 Aug 2008 13:12:46 -0700 |
| To: | bug-DBD-Pg [...] rt.cpan.org |
| From: | Stephen Tu <stephen_tu [...] berkeley.edu> |
Hello DBD-Pg team:
I have encountered a slight issue with the DBD-Pg driver when trying to
do multiple prepare statements.
I am not exactly sure if this is exactly a bug per se, or a result of a
design decision, but I'd like to be able
to see this functionality. Anyways, let me provide you with some
background information first of all:
$ uname -a
FreeBSD [domain-name] 6.2-RELEASE-p10 FreeBSD 6.2-RELEASE-p10 #2: Thu
Jan 17 04:34:12 PST 2008
$ pkg_info | grep -i db [only showing relevant ones]
p5-DBD-Pg-2.9.0 Provides access to PostgreSQL databases through the DBI
p5-DBI-1.60.4 The perl5 Database Interface. Required for DBD::*
modules
$ pkg_info | grep postgres
postgresql-client-8.2.5_1 PostgreSQL database (client)
postgresql-server-8.2.5_2 The most advanced open-source database
available anywhere
$ perl -v
This is perl, v5.8.8 built for i386-freebsd-64int
-----------
Here is a very simple, trivial script that i put together that
demonstrates the issue:
dbi_test.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect(
'dbi:Pg:dbname=databasename',
'user',
'password',
{ AutoCommit => 0 }
);
$dbh->{pg_server_prepare} = 0;
#according to DBD::Pg docs, this is how you get multi-prepared
statements to work
my $sql = <<SQL;
UPDATE test SET description = 'this is my trivial test'
WHERE id = ?;
SELECT * FROM test WHERE id = ?;
SQL
my @values = (1,1);
# this segment will work
my $sth = $dbh->prepare($sql);
$sth->execute(@values);
my $rows = $sth->rows;
print "number of rows = $rows\n";
# these two segments will not work
$dbh->do($sql,undef,@values);
$dbh->do($sql,{ pg_server_prepare => 0 },@values);
# i even tried to pass in the explicit attribute
$dbh->commit;
and here is the output:
$ ./dbi_test.pl
number of rows = 1
DBD::Pg::db do failed: ERROR: cannot insert multiple commands into a
prepared statement at ./dbi_test.pl line 27.
DBD::Pg::db do failed: ERROR: cannot insert multiple commands into a
prepared statement at ./dbi_test.pl line 28.
like i said before, i'm not sure if this is really a "bug", but it'd be
a feature that would be fairly useful.
i'm actually not sure why this doesn't work, since according to DBI.pm,
the implementation of "do" is "logically" similar
to what i did the first time around w/ "prepare" and "execute", but i
guess the actual implementation does some
optimization that makes it not work? i hope that this is clear and
concise enough to you, and at the same time descriptive.
please let me know if there are any further questions
Regards,
Stephen Tu