Yii Framework Forum: Transaction And Multiple Queries In One String With Pdo Mysql [Solved] - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Transaction And Multiple Queries In One String With Pdo Mysql [Solved] Rate Topic: -----

#1 User is offline   Galiego710 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 31-January 13

Posted 31 January 2013 - 05:10 PM

When second query in multple-queries-string have an error, the Exception is not thrown!
For example, next string has error in second query:
'insert into t1 (val) value (1); insert into t1 (_ERROR_) value (3); insert into t1 (val) value (2);'

But the transaction will be fully executed and committed!

I think this is not right behavior relative to common sense.

There are any way to get the thrown Exception where occurs an sql error in not-first query of multiple-queries-string?
Or there are any around way to safely execute multiple queries in one string?

At now I have next conclusion:
    PDOStatement can execute multiple queries in one string,
    but if second and next queries has an error execution, 
    no any Exception will be thrown.

    That is, if string contains three sql-query, first and third sql is valid, 
    but the second is not, then first sql will be done, 
    but second and third sql are NOT, but transaction will be committed!


My environment: PHP 5.4.7; Yii 1.1.13; MySQL 5.5.27

I wrote simple unit test for testing it:

/**
 * Testing transaction in MySQL.
 * The instruction of DROP TABLE, CREATE TABLE is excluded,
 * because they doing implicit COMMIT in MySQL.
 *
 * NOTE: Your application must have "db" component.
 */
class TransactionTest extends CTestCase
{
    /**
     * echo additional information
     */
    protected function echoInfo()
    {
        echo 'PDO::ATTR_EMULATE_PREPARES='.PDO::ATTR_EMULATE_PREPARES."\n"; // =20
        echo 'PDO::ATTR_AUTOCOMMIT='.PDO::ATTR_AUTOCOMMIT."\n";             // =0

        $row = Yii::app()->db->createCommand('select @@global.init_connect, '
            .'@@global.autocommit, @@session.autocommit;')->queryRow();
        echo var_export($row,true)."\n\n";
    }

    /**
     * recreate a table
     */
    public function setUp()
    {
        $sql = 'drop table if exists t1; '
            .'create table t1 (id int(11) not null auto_increment, val varchar(45) default null, '
            .'primary key (id)) engine=innodb default charset=utf8;';
        Yii::app()->db->createCommand($sql)->execute();
        echo 'Table recreated.'."\n\n";
    }

    public function testForEchoInfo()
    {
        $this->echoInfo();
    }

    /**
     * a few queries in one string
     */
    public function testMultipleQueries()
    {
        echo __METHOD__."\n";

        $sql = 'insert into t1 (val) value (1);'
            .'insert into t1 (_ERROR_) value (3);'   // (re-)comment to test different variant
            .'insert into t1 (val) value (2);'
        ;

        /** @var CDbConnection $conn  */
        $conn = Yii::app()->db;
        $transaction = $conn->beginTransaction();
        try {
            $conn->createCommand($sql)->execute();

            $transaction->commit();
            echo 'commit'."\n";
        }
        catch(Exception $e)
        {
            $transaction->rollback();
            echo 'rollback'."\n";
        }
    }


    /**
     * one query per string
     */
    public function testSqlByOrder()
    {
        echo __METHOD__."\n";

        $sql1 = 'insert into t1 (val) value (1);';
        $sql2 = 'insert into t1 (_ERROR_) value (3);'; // invalid SQL
        $sql3 = 'insert into t1 (val) value (2);';

        /** @var CDbConnection $conn  */
        $conn = Yii::app()->db;
        $transaction = $conn->beginTransaction();
        try {
            $conn->createCommand($sql1)->execute();
            $conn->createCommand($sql2)->execute();  // invalid SQL
            $conn->createCommand($sql3)->execute();

            $transaction->commit();
            echo 'commit'."\n";
        }
        catch(Exception $e)
        {
            $transaction->rollback();
            echo 'rollback'."\n";
        }
    }

    /**
     * a few queries in one string with PDO
     */
    public function testPDO_MultipleQueries()
    {
        echo __METHOD__."\n";

        $db = new PDO(Yii::app()->db->connectionString,
            Yii::app()->db->username, Yii::app()->db->password);

        $sql = 'insert into t1 (val) value (1);'
            .'insert into t1 (_ERROR_) value (3);'
            .'insert into t1 (val) value (2);';

        $this->assertTrue( $db->beginTransaction() );
        try {
            $success = $db->prepare($sql)->execute();

            if (!$success)
                throw new Exception('error!');

            $db->commit();
            echo 'commit'."\n";
        }
        catch(Exception $e)
        {
            $db->rollBack();
            echo 'rollback'."\n";
        }
    }

    /**
     * one query per string with PDO
     *
     * $db->prepare($sql2)->execute(); // return false if a sql error occurs
     * see http_//www.php.net/manual/en/pdostatement.execute.php
     */
    public function testPDO_SqlByOrder()
    {
        echo __METHOD__."\n";

        $db = new PDO(Yii::app()->db->connectionString,
            Yii::app()->db->username, Yii::app()->db->password);

        $sql1 = 'insert into t1 (val) value (1);';
        $sql2 = 'insert into t1 (_ERROR_) value (3);';
        $sql3 = 'insert into t1 (val) value (2);';

        $this->assertTrue( $db->beginTransaction() );
        try {
            $success = true;
            $success = $success && $db->prepare($sql1)->execute();
            $success = $success && $db->prepare($sql2)->execute();
            $success = $success && $db->prepare($sql3)->execute();

            if (!$success)
                throw new Exception('error!');

            $db->commit();
            echo 'commit'."\n";
        }
        catch(Exception $e)
        {
            $db->rollBack();
            echo 'rollback'."\n";
        }
    }
}


The execution log is:
Table recreated.

PDO::ATTR_EMULATE_PREPARES=20
PDO::ATTR_AUTOCOMMIT=0
array (
  '@@global.init_connect' => '',
  '@@global.autocommit' => '1',
  '@@session.autocommit' => '1',
)



Table recreated.

TransactionTest::testMultipleQueries
commit


Table recreated.

TransactionTest::testSqlByOrder
rollback


Table recreated.

TransactionTest::testPDO_MultipleQueries
commit


Table recreated.

TransactionTest::testPDO_SqlByOrder
rollback

Thanks.
0

#2 User is offline   Galiego710 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 31-January 13

Posted 03 February 2013 - 12:28 PM

I have the simple sql code, therefore I desire to use this algorithm for solving this problem:

public function executeSql($multiQuery)
    {
        // split multi-query-string by "semicolon and newline with or not comment"
        $arrQuery = mb_split(';\s*?(-- )?.*?\n',$multiQuery);

        $conn = Yii::app()->db;
        $transaction = $conn->beginTransaction();
        try
        {
            foreach($arrQuery as $query) {
                if (strlen(trim($query)) > 0) {
                    $conn->createCommand($query)->execute();
                }
            }
            $transaction->commit();
        }
        catch (Exception $e)
        {
            $transaction->rollback();
            throw $e;
        }
        return true;
    }


This works fine! And there is not problem of silent syntax error.

The next sql code will successfully throw exception by this method:

insert into t1 (val)
value (1); -- any comment
insert into t1 (__ERROR__) value (2); -- any comment
insert into t1 (val) value (3);


But this sql code will be successfully executed:

insert into t1 (val)
value (1); -- any comment
insert into t1 (val) value (2); -- any comment
insert into t1 (val) value (3);


The test class.

/**
 * Test for executeSql($multiQuery)
 */
class ExecuteSqlTest extends CDbTestCase
{

    public function executeSql($multiQuery)
    {
        // split multi-query-string by "semicolon and newline with or not comment"
        $arrQuery = mb_split(';\s*?(-- )?.*?\n',$multiQuery);

        $conn = Yii::app()->db;
        $transaction = $conn->beginTransaction();
        try
        {
            foreach($arrQuery as $query) {
                if (strlen(trim($query)) > 0) {
                    $conn->createCommand($query)->execute();
                }
            }
            $transaction->commit();
        }
        catch (Exception $e)
        {
            $transaction->rollback();
            throw $e;
        }
        return true;
    }

    /**
     * recreate a table
     */
    public function setUp()
    {
        $sql = 'drop table if exists t1; '
            .'create table t1 (id int(11) not null auto_increment, val varchar(45) default null, '
            .'primary key (id)) engine=innodb default charset=utf8;';
        Yii::app()->db->createCommand($sql)->execute();
        echo 'Table recreated.'."\n\n";
    }

    public function testExecuteSql1()
    {
        $sql = 'insert into t1 (val)'."\n"              // "\n" in middle of query
            .'value (1);'." -- any comment\n"           // attention to "\n" at end of query
            .'insert into t1 (_ERROR_) value (3);'." --any comment\n"
            .'insert into t1 (val) value (2);'
        ;

        try {
            $this->executeSql($sql);
            $this->fail();
        } catch(CDbException $e) {
            echo $e->getMessage();
        }
    }

    public function testExecuteSql2()
    {
        $sql = 'insert into t1 (val)'."\n"              // "\n" in middle of query
            .'value (1);'." -- any comment \n"          // attention to "\n" at end of query
            .'insert into t1 (val) value (3);'." --any comment \n"
            .'insert into t1 (val) value (2);'
        ;

        try {
            $this->assertTrue( $this->executeSql($sql) );
            $this->assertTrue( Yii::app()->db->createCommand(
                'select count(*) from t1')->queryScalar() == 3 );
        } catch(CDbException $e) {
            echo $e->getMessage();
            $this->fail();
        }
    }
}

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users