How can I use transactions in my MySQL stored procedure?

Question

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 ?

Answer 1

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;