Re: prepared statements and DBD::Pg

Tim Bunce wrote:

> The example that started this thread was that this valid statement
> worked:
>
> prepare("CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);")
>
> but this valid statement didn't:
>
> prepare(" INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);")
>
> My argument is that both calls should return statement handles.

I think they do, and the original report is somehow flawed. Here's a
test that demonstrates this with the SQL pasted from the initial
example.

print "version is $DBD::Pg::VERSION\n";
$dbh->{pg_server_prepare} = 1;
my $prepare_sql =<<SQL;
CREATE TEMP TABLE foo( id int, user_id int,);

INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth1=$dbh->prepare($prepare_sql);
print "1st statement handle=$sth1\n";
$prepare_sql=<<SQL;
INSERT INTO foo(1, 1);

INSERT INTO foo(2, 2);
SQL
my $sth2=$dbh->prepare($prepare_sql);
print "2nd statement handle=$sth2\n";

And here's the output I get:
version is 2.8.2
1st statement handle=DBI::st=HASH(0x8d40908)
2nd statement handle=DBI::st=HASH(0x8c73660)

> If a server-side prepare is attempted and fails because it's a kind
of
> statement that can't be server-side prepared then DBD::pg should
> fallback to a client-side prepare.

Unfortunately with PG, an error in server-side prepare aborts the
current transaction, so that any subsequent command will fail until a
rollback is issued. Falling back to client-side prepare once in this
state would probably not help much.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org