Re: prepared statements and DBD::Pg

Daniel Verite wrote:
> 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.
In my attempt to obfuscate the actual code, I actually included invalid
SQL , but I can assure you that the failure occurred as I described it,
though only with the version 2.11.8. Other versions > 1.4 worked fine,
despite the explanation in the DBD::Pg docs which implied that they
might not.

> 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,

--
JP Fletcher
Database Administrator
Afilias Canada
voice: 416.646.3304 ext. 4123
fax: 416.646.3305
mobile: 416.561.4763
jpfletch(at)ca(dot)afilias(dot)info