0

I have read the documentation on Exit Handlers and have found useful code ideas in relevant SO questions here and here amongst other places.

Nevertheless, calling the stored procedure below appears to complete OK and returns a TRUE value for the parameter success. when I know it is definitely not completing OK, not least because there was a syntax error in the body SQL (it was referring to a field that did not exist).

So the exit handler should have kicked in and returned FALSE for parameter success.

Can anyone help me to understand why a transaction that fails does not return the correct value for the parameter? (I suspect it has something to do with where I set success to true)

The actual SQL inside the transaction is not important to this question so I haven't shown it. Just assume that it might or might not successfully complete the transaction. It is the success or failure of that transaction that I want to detect through the parameter

DELIMITER $$
CREATE PROCEDURE do_thing (OUT success BOOLEAN)     

DETERMINISTIC
 BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET success := FALSE; ROLLBACK; END;   # rollback on any error
DECLARE EXIT HANDLER FOR SQLWARNING  BEGIN SET success := FALSE; ROLLBACK; END;     # rollback on any warning

START TRANSACTION;

       < SQL that might cause an error >
       < in my case it was referring to a field that didn't exist>

COMMIT;
SET success := TRUE;  
END$$
DELIMITER ;
user2834566
  • 775
  • 9
  • 22

1 Answers1

0

SEE DBFIDDLE

The first part is a copy of your code, it throws an error....

The second part is corrected, both DECLARE EXIT are moved within the block.

The third part is an example where @success will be set to false.

Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Thank you for taking all that trouble. But I now have to admit that I made an error copying my code into the question. I actually did already have the DECLARE EXIT lines inside the Begin-end block, not outside as my question showed (otherwise I would have got the same syntax error as you demonstrated) I've edited my question to reflect the true situation. That said your answer is now the same as my code so I'm at a loss why mine does not return false when yours does. I've accepted your answer though as it was so good (and correct as far as my original question was concerned) – user2834566 Feb 09 '20 at 17:52
  • You write `The actual SQL inside the transaction is not important`, but maybe it is.. Can you provide an example the is returning `false`, where you would expect `true`, with an actual SQL statement in it? – Luuk Feb 10 '20 at 08:45
  • `SELECT field1 FROM test;` -- field1 does not exist in table test so this should fail and return @success as false – user2834566 Feb 19 '20 at 18:14
  • it does return `false`, see [dbfiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=44764d95c09c069dbafd1a734088040d) – Luuk Feb 19 '20 at 18:26