Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2007

    Unanswered: transaction in stored procedure

    I am trying to write a stored procedure , which uses transactions

    the following is a sample code.

    create procedure test_proc(IN id int,IN name varchar(20) )
    start transaction;
    --insert into table1 some data and get last insert id;
    --insert into table2 some data using last insert id;
    however am not sure of the correct syntax. In the above procedure if an error occurred would the procedure automatically rollback? If it failed would it generate an error message and if so how would you report that in php? If it was successful again how would you know this. Basically I need some way of knowing if the procedure was successful or not in my php code but at the same time need to know if the procedure would automatically rollback if all or any insert failed.

  2. #2
    Join Date
    May 2009
    Hi ozzii!

    When an error occurs mysql doesn't automatically rollback. You must create handlers (see MySQL :: MySQL 5.0 Reference Manual :: DECLARE for Handlers) that are executed when an error occurs. I do that in listing 1.
    As you can see in the stored procedure I added the parameter ret. This parameter holds the return code of the stored procedure (1-sucess, 0-failure).

    In listing 2, I present a snippet of PHP code that calls the stored procedure and checks if it returned successfully or not.

    Listing 1. STORED PROCEDURE:

    create procedure sp_answer(IN name VARCHAR(20),OUT ret TINYINT)
    declare exit handler for not found rollback;
    declare exit handler for sqlwarning rollback;
    declare exit handler for sqlexception rollback;

    set ret=0;
    start transaction

    -- insert into ...
    -- update ...
    -- any other DDL operation


    set ret = 1;

    Listing 2. PHP CODE:

    /* MySQL connection code here. */

    $res = mysql_query("CALL sp_answer(@res)");

    $res = mysql_query("SELECT @res");
    $row = mysql_fetch_array($res);
    $ret = (int)$row[0];
    if( $ret ) {
    print "sucess!";
    else {
    print "failed!";

    Let me know if you have any doubt!!!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts