DBI begin_work does not work with stored procedure calls
I am trying to make a call to a stored procedure from a transaction in a simplified form:
my $dbh= DBI->connect(............ );
my $sth = $dbh->prepare("call sp_get_workitems (1,1)");
$dbh->begin_work or die $dbh->errstr;
$sth->execute();
my ($result)= $sth->fetchrow_array();
$dbh->commit;
this gives the following error:
DBD driver has not implemented the AutoCommit attribute
If I replace the begin_work statement with $dbh->{'AutoCommit'} = 0;
(before or after preparation) I get this error:
DBD::mysql::db commit failed: Commands out of sync; you can't run this command now
If I replace the stored procedure call with a simple select statement everything works fine.
The stored procedure includes a series of updates and ends with a select statement. Of course, it would be easier if I could process the transaction as part of the procedure I need to execute some Perl code if a rollback occurs.
I am using ActivePerl on Windows 7 and amazon cloud instance running Centos with DBI 1.616 installed, this happens on both.
Should this work or is there a way around this?
thank
source to share
Be sure to include finish()
each executed prepared CALL procedure before the explicit commit()
transaction. For example.
$sth->finish; $sth->commit();
This seems like a mistake to me given the typical semantics finish()
. Many result sets, invocation more_results
, etc. Irrelevant.
DBD 1.616, DBD :: mysql 4.020 and MySQL 5.5.19.
source to share
If you are using AutoCommit => 0 you don't need begin_work (). Everything happens in a transaction until you commit () or rollbacks (). Then a new transaction starts.
You should actually hook into RaiseError => 1 because you should get a begin_work () error when AutoCommit is 0. From thin docs:
If AutoCommit is already off when begin_work is called, then it will return nothing but an error. If the driver does not support transactions then begin_work tries to set the Disable driver autonegotiation fatal error.
Also which version of DBD :: mysql are you using? I think the latest version implements AutoCommit.
source to share