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 ?

Can you post full definition of your stored procedure starting with `CREATE PROCEDURE...`? What is `insertedProductID` and where it's been declared??? — peterm, Sep 15 '13 at 23:08
@tohid, I you have forgot ',' between "SQLEXCEPTION SQLWARNING". you should write as follows : DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; END; — Manish Sapkal, Jan 09 '15 at 06:31

score 7 · Answer 1 · answered Sep 15 '13 at 21:46

7

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;

answered Sep 15 '13 at 21:46

Bill Karwin

538,548
86
673
828

Thanks .. I applied the changes you suggested but still getting the same error ! – Tohid Sep 15 '13 at 21:59
What version of MySQL are you using? `SELECT VERSION();` – Bill Karwin Sep 15 '13 at 22:12
I used your code and there's no error (v.5.6.17)... I really don't understand why the [accepted answer](http://stackoverflow.com/a/20046066/1326147) was upvoted that much, since he just separated the declares, provides no references and his "explanation" just states the obvious... – Armfoot Dec 01 '15 at 11:57

score 5 · Answer 2 · edited Nov 15 '19 at 23:17

5

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

edited Nov 15 '19 at 23:17

Advena

1,664
2
24
45

answered Sep 15 '13 at 20:27

Rahul Tripathi

168,305
31
280
331

Thanks for the quick reply. Still get the following error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE poid INT; DECLARE EXIT HANDLER FOR SQLEXCEPTION SQLWARNING BEGIN RO' at line 11 – Tohid Sep 15 '13 at 21:18
3

It seems the problem is that after `START TRANSACTION` MySQL does not expect a `DECLARE`... – Armfoot Dec 01 '15 at 11:43
1

You can't use `DECLARE` after using something else written after `BEGIN` – Pishang Ujeniya Jun 03 '19 at 08:51

score -1 · Answer 3 · answered May 16 '19 at 14:39

-1

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.

answered May 16 '19 at 14:39

Henrik Erlandsson

3,797
5
43
63

How can I use transactions in my MySQL stored procedure?

3 Answers3

Linked