Cdbcommand Failed To Execute The Sql Statement: Sqlstate[Hy000]: General Error.

Yii 1.1 General Discussion
1

Hello all, I’m new to Yii and while developing my first project on a Windows 8 PC, using Yii ver. 1.1.14 on a XAMPP ver 1.8.3 installation (php 5.5.3 - Mysql 5.6) I get the above error.

Specifically I’m attempting to run a DAO sql statement from my yii code that does an insert in a table (as_members) and then subsequently in the same statement selects the auto incremented ID value of the record inserted:




        $q = "INSERT INTO `as_members`(`MB_TYPE`, `MB_EMAIL`, `MB_COUNTRY`, `MB_STATE`, `MB_BIRTHDAY`) "

             . "VALUES($gender, '$email', $country, $area_city, '$sdt'); SELECT LAST_INSERT_ID(); ";

        $cmd = Yii::app()->db->createCommand($q);

        $mb_id = $cmd->queryScalar();

I get the following run-time error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error. The SQL statement executed was: INSERT INTO as_members(MB_TYPE, MB_EMAIL, MB_COUNTRY, MB_STATE, MB_BIRTHDAY) VALUES(1, ‘aaaaa@yahoo.com’, 2, 58, ‘1968-08-11’); SELECT LAST_INSERT_ID();

The statement executes OK from a Windows SQL client (HeidiSQL) I am using. Furthermore when I break the sql statement in a insert statement and a select statement and execute them as 2 steps from Yii the code works!

What am I missing here? Why does my initial code produces an error?

Thanks in advance

2

As you already found out, you cannot execute multiple statements in one command.

3

do it so:




$q = "INSERT INTO `as_members`(`MB_TYPE`, `MB_EMAIL`, `MB_COUNTRY`, `MB_STATE`, `MB_BIRTHDAY`) "

             . "VALUES($gender, '$email', $country, $area_city, '$sdt')";

        $cmd = Yii::app()->db->createCommand($q);

        $mb_id = $cmd->queryScalar();

echo Yii::app()->db->getLastInsertID();

Be careful, that method just work fine after an insertion to table.

http://www.yiiframework.com/doc/api/1.1/CDbCommandBuilder#getLastInsertID-detail

4

Guys thanks a lot for your answers, got it but to tell you the truth I’m a little disappointed. This is because he problem I posted here is a simplified version of what I originally wanted to do. Basically what I want is execute 2 insert statements into tables with auto_increment ids and get back with a select the two ids. I combined the statements in a stored procedure like this:




DELIMITER $$

CREATE PROCEDURE `sp_member_signup`(INOUT `mb_id` INT, INOUT `mbs_id` INT, IN `username` VARCHAR(25), IN `email` VARCHAR(50), IN `password` VARCHAR(255), IN `country_id` INT, IN `state_id` INT, IN `gender_id` INT, IN `birth_dt` DATE, IN `promo_code` VARCHAR(50))

BEGIN

  INSERT INTO `as_members`(mb_type, mb_email, mb_country, mb_state, mb_birthday)

  VALUES(@gender_id, @email, @country_id, @state_id, @birth_dt);

  

  SET @mb_id := LAST_INSERT_ID();

  

  INSERT INTO `as_membership`(mb_id, mbs_username, mbs_password)

  VALUES(@mb_id, @username, @password);

  

  SET @mbs_id := LAST_INSERT_ID();

END$$

DELIMITER ;

And then executed the stored procedure from Yii:




        $q = "set @mb_id = 0, @mbs_id=0, @email = '$email', @country_id = $country, "

            . "@state_id = $area_city, @birth_dt = '$sdt', @gender_id = $gender, "

            . "@username = '$username', @password = '$password', @promo_code ='$promoCode'; "

            . "call sp_member_signup(@mb_id, @mbs_id, @username, @email, "

            . "@password, @country_id, @state_id, @gender_id, @birth_dt, @promo_code); "

            . "select @mb_id as MB_ID, @mbs_id as MBS_ID; ";

        $cmd = Yii::app()->db->createCommand($q);

        $array = $cmd->queryAll();

The strange part is that although the stored procedure is called doing the actual inserts I get the posted error message due the limitation of running a statement at a time. Isn’t there a way to overcome this - like doing a batch statement ?

Thanks a lot again.

5

Just wrap the two separate queries in a transaction.

6

This is actually a security feature. The idea is to make SQL injections more difficult.

As Keith pointed out, the proper way of doing what you want would involve a transaction.

7

Thanks a lot again for the responses. Finally I refactored to the following:




        $q = <<<HERE

        set @mb_id = 0, @mbs_id=0, @email = '$email', @country_id = $country, @state_id = $area_city, 

	 	  @birth_dt = '$sdt', @gender_id = $gender, @username = '$username', @password = '$password',

          @promo_code ='$promoCode';

    

        call sp_member_signup(@mb_id, @mbs_id, @username, @email, 

          @password, @country_id, @state_id, @gender_id, @birth_dt, @promo_code);

        /*select @mb_id as MB_ID, @mbs_id as MBS_ID;*/

HERE;

        

        $q2 = <<<HERE

        SELECT m.MB_ID, MBS_ID

        FROM as_members m INNER JOIN as_membership s ON m.MB_ID = s.MB_ID

        WHERE m.MB_EMAIL = '$email';              

HERE;

        $conn = Yii::app()->db;

        $trans = $conn->beginTransaction();

        try {

            $conn->createCommand($q)->execute();

            $arr = $conn->createCommand($q2)->queryAll();

            $trans->commit();

        } catch (Exception $e) { // an exception is raised if a query fails

            $trans->rollback();

        }

My first transaction attempt using


'select @mb_id as MB_ID, @mbs_id as MBS_ID;'

as the second query failed in the sense that a got zeros for both variables as a result. Finally I settled doing a join query to get the ids. I wonder what is the suggested way to call a stored procedure from yii and get back values in the OUT / INOUT params?