DBI begin_work不适用于存储过程调用 [英] DBI begin_work doesn't work with stored procedure calls

问题描述

我正在尝试以简化形式在事务中从中调用存储过程:

I am trying to make a call to a stored procedure from with in a transaction in its 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;

这将导致以下错误:

DBD driver has not implemented the AutoCommit attribute

如果我用$dbh->{'AutoCommit'} = 0;替换begin_work语句(在编写之前或之后)，则会出现此错误:

If I replace the begin_work statement with $dbh->{'AutoCommit'} = 0; (before or after the prepare), I get this error:

DBD::mysql::db commit failed: Commands out of sync; you can't run this command now

如果我用简单的select语句替换存储过程调用，则一切正常.

If I replace the stored procedure call with a simple select statement it all works fine.

存储过程包括许多更新，并以select语句结束. 当然，如果我可以在发生回滚的情况下在需要处理一些perl代码的过程中处理事务，则会更容易.

The stored procedure includes a number of updates and finishes with a select statement. Of course it would be easier if I could handle the transaction within the procedure put I need to action some perl code if rollback occurs.

我在Windows 7上使用ActivePerl，并在运行Centos且安装了DBI 1.616的亚马逊云实例上使用这两种方法.

I'm using ActivePerl on windows 7 and an amazon cloud instance running Centos with DBI 1.616 installed, this occurs on both.

这应该可行还是可以解决?

Should this work or is there a way around it?

谢谢

推荐答案

在显式commit()事务之前，请确保显式finish()每个已执行的准备好的过程CALL.例如，

Make sure you explicitly finish() every executed prepared procedure CALL before you explicitly commit() the transaction. E.g.,

$sth->finish;
$sth->commit();

考虑到finish()的典型语义，这对我来说似乎是个错误.多个结果集，调用more_results等都没关系.

This appears to be a bug to me, given the typical semantics of finish(). Multiple result sets, calling more_results, etc. did not matter.

DBD 1.616，DBD :: mysql 4.020和MySQL 5.5.19.

DBD 1.616, DBD::mysql 4.020 and MySQL 5.5.19.

这篇关于DBI begin_work不适用于存储过程调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！