CODEWITHSUNDEEP
mysqlstored-procedurestransactions
Tohid
Tohid

Reputation: 22613

How can I use transactions in my MySQL stored procedure?

I'm trying to modify my MySQL stored procedure and make it transactional. The existing stored procedure works fine with no problem but as soon as I make it transactional it does not even allow me to save my changes. I checked MySQL documentation and searched online but I cannot find any problem with my code. It seems to be pretty straight forward but can't figure it out.

BEGIN

DECLARE poid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING
BEGIN
    ROLLBACK;
END

START TRANSACTION;

    -- ADD option 5
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0);
    SET poid = (SELECT LAST_INSERT_ID());
    INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+');

    -- ADD option 12
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1);

    -- ADD option 13
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0);

COMMIT;

END

any idea ?

Upvotes: 28

Views: 57884

Answers (3)

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

Try like this ie, include your Declare statement inside the START TRANSACTION;. Earlier your ROLLBACK was not a part of TRANSACTION as you wrote it above the START TRANSACTION:-

BEGIN

DECLARE poid INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING

START TRANSACTION;

BEGIN
    ROLLBACK;
END

    -- ADD option 5
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,5,0);
    SET poid = (SELECT LAST_INSERT_ID());
    INSERT INTO product_option_value(product_option_id,product_id,option_id,option_value_id,quantity,subtract,price,price_prefix,points,points_prefix,weight,weight_prefix) VALUES(poid,insertedProductID,5,50,0,0,4.99,'+',0,'+',0,'+');

    -- ADD option 12
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,12,1);

    -- ADD option 13
    INSERT INTO product_option(product_id,option_id,required) VALUES(insertedProductID,13,0);

COMMIT;

END

Upvotes: 6

Henrik Erlandsson
Henrik Erlandsson

Reputation: 3830

Put your DECLAREs after the first BEGIN and it should work.

If you use BEGIN and END to group multiple statements, you normally also need to declare an alternate DELIMITER at the top and replace the ; after the last END with it.

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562951

Two syntax errors:

  • You need commas in between the conditions for your exit handler. Notice the syntax documentation shows commas.

  • You need to terminate the END of the exit handler with a semicolon. The DECLARE statement itself (including its BEGIN...END block) is a statement like any other, and need to have a terminator.

So you need this:

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
    ROLLBACK;
END;

Upvotes: 7

Related Questions