Yii beginTransaction在Sybase上设置链模式

Question

我遇到了一个使用Yii通过odbc连接到sybase的存储过程和多语句事务的问题。

要隔离我在事务中运行存储过程时遇到的以下错误…

CDbCommand failed to execute the SQL statement: SQLSTATE[ZZZZZ]: <>: 7713 [Sybase][ODBC Driver][Adaptive Server Enterprise]Stored procedure 'myStoredProcedure' may be run only in unchained transaction mode. The 'SET CHAINED OFF' command will cause the current session to use unchained transaction mode. (SQLExecute[7713] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254). The SQL statement executed was: exec myStoredProcedure 

我尝试了下面这个非常简单的测试:

$connection=Yii::app()->db;
$transaction = Yii::app()->db->beginTransaction();
$sql="select 'Tranchained'=@@tranchained";  //0=off, 1=on
$command=$connection->createCommand($sql);
error_log('Chained Mode On or Off ='.$command->queryScalar());
$transaction->commit();

错误日志显示连锁模式On或Off = 1，这意味着事务正在打开连锁模式。这和我想的正好相反。我还确认了链模式在此事务之外是关闭的，因此我确定是事务将其打开的。

简单的解决方案是添加一条sql语句，通过在事务内部(在顶部)插入以下代码来关闭事务中的链式模式:

$sql='set chained off';
$command=$connection->createCommand($sql);
$command->execute();

这适用于我的简单示例。但是，当我在实际应用程序中使用它时，它不起作用，因为我使用的是多语句事务，所以我得到以下错误:

CDbCommand failed to execute the SQL statement: SQLSTATE[ZZZZZ]: <>: 226 [Sybase][ODBC Driver][Adaptive Server Enterprise]SET CHAINED command not allowed within multi-statement transaction. (SQLExecute[226] at /builddir/build/BUILD/php-5.3.3/ext/pdo_odbc/odbc_stmt.c:254). The SQL statement executed was: set chained off 

我也试过了:

$connection->setAutoCommit(false);

无效

Answer 1

解决方案很简单。不要使用

Yii::app()->db->beginTransaction();

应该这样做，它实际工作并按照预期运行

$sql='begin transaction'; //or commit transaction, or rollback transaction
$command=$connection->createCommand($sql);
$command->execute();

可以如下方式创建一个make-shift类(可能是一种更优雅的方法):

class SybaseDb {
    public function beginTransaction() {
        $connection=Yii::app()->db;
        $sql='
            begin transaction 
        ';
        $command=$connection->createCommand($sql);
        $command->execute();        
    }
    public function rollBack() {
        $connection=Yii::app()->db;     
        $sql='
            rollback transaction 
        ';
        $command=$connection->createCommand($sql);
        $command->execute();            
    }
    public function commit() {
        $connection=Yii::app()->db; 
        $sql='
            commit transaction 
        ';
        $command=$connection->createCommand($sql);
        $command->execute();        
    }   
}