[Dbi-interbase-devel] DBD and Interbase functionality

[Adam C. <Ada...@St...>]

> From: Chris Winters <ch...@cw...>
> Subject: [Dbi-interbase-devel] DBD and Interbase functionality

> I have all that more or less done and things work great. However, I ran
> into a problem trying to run multiple statements simultaneously on a
> single database handle? (Test script and error message attached.) Is
> this supported with DBD::InterBase? If so, do I need to do anything
> special?

Chris, I think that you are being bitten by an issue that is described in
the FAQ for the driver. See http://dbi.interbase.or.id/faq.html. In summary
Interbase keeps track of transaction context in the database handle. With
AutoCommit on (the default) the DBI behaviour is to call an implicit commit
upon certain events, one of those being a final fetch from a statement
handle. So your St1 hits it's final fetch and AutoCommit does it's thing but
unfortunately for your example (because the transaction context is pegged o
the dbh) this also commits St2 which hasn't done anything yet!

I believe that my rejigged test code (not tested) will solve this problem in
this simple case, however for other situations (like nested select
statements) the best solution is to manage your own transactions or
investigate the new ib_softcommit driver specific option.

> #!/usr/bin/perl
>
> use strict;
> use Data::Dumper qw( Dumper );
> use DBI;
>
> $Data::Dumper::Indent = 0;
>
> my $DSN    = 'DBI:InterBase:dbname=3Dcwtest.gdb';
> my $USER   = 'sysdba';
> my $PASS   = 'password';
> my $TABLE1 = 'foo';
> my $TABLE2 = 'spops_user';
>
> {
>     my $dbh =3D DBI->connect( $DSN, $USER, $PASS )
>                     || die "Cannot connect: $DBI::errstr";
>     $dbh->{RaiseError} =3D 1;
>     my $sth =3D $dbh->prepare( "select * from $TABLE1" );
>     $sth->execute;
>
>     while ( my $row =3D $sth->fetchrow_arrayref ) {
>         print "St1: ", Dumper( $row ), "\n";
>     }
>
>     my $sth2 =3D $dbh->prepare( "select * from $TABLE2" );
>     $sth2->execute;
>
>     while ( my $row =3D $sth2->fetchrow_arrayref ) {
>         print "St2: ", Dumper( $row ), "\n";
>     }
> }