CODEWITHSUNDEEP
mysqltransactions
robert
robert

Reputation: 1533

mysql transaction error handling

 DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
BEGIN 
    ROLLBACK; 
END;
START TRANSACTION;      

    UPDATE tbl_order SET TransactionID="abc" WHERE OrderID=1;
    UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;
    UPDATE tbl_order SET TransactionID="zzz" WHERE OrderID=13;


COMMIT;

for some reason order 1 and 13 are filled without rollback and i get syntax error for the exit hadler.

Query:  DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN ROLLBACK

Error Code: 1064
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 EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND 
BEGIN 
    ROLLBACK' at line 1

can someone help me figure out what i'm doing wrong?

Thanks in advance

EDIT

UPDATE tbl_order SET TransactionID="xyz" WHERE OrderID=;

is intentional

Upvotes: 5

Views: 7230

Answers (2)

Emperor Transisthor
Emperor Transisthor

Reputation: 135

I know this is quite outdated topic, but I encountered the same error when declaring my own procedure with transaction and error handling. The above procedure looks well, something OP didn't copy as it seems. The order is important and it should look like this:

  1. Procedure declaration
  2. Declaration of variables
  3. ERROR HANDLING
  4. Start transaction
  5. Statements
  6. Commit
  7. End

In my case, it looks like this:

DELIMITER //
CREATE PROCEDURE pAddUser(IN _nick VARCHAR(30))
BEGIN
    DECLARE doesUserExist BOOLEAN;
    DECLARE accountCreationDate DATE;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
    END;

    START TRANSACTION;

    SET doesUserExist = (SELECT EXISTS(SELECT * FROM tblUser WHERE nick=_nick));
    IF NOT doesUserExist THEN
        SET accountCreationDate = CURDATE();
        INSERT INTO tblUser (nick, accountCreation) VALUES (_nick, CURDATE());
    ELSE
        SELECT CONCAT("Nick '", _nick, "' is in use!") AS 'Console output';
    END IF;

    COMMIT;

END;
//
DELIMITER ;

Upvotes: 0

sreimer
sreimer

Reputation: 4998

I believe exit handlers can only be used in stored procedures. The documentation doesn't explicitly state this, but alludes to

Conditions may arise during stored program execution that require special handling

http://dev.mysql.com/doc/refman/5.1/en/condition-handling.html

Upvotes: 2

Related Questions