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@ca.afilias.info