f you have mysql stored procedure that changes database tables \u2014 you probably want to enclose the whole procedure in a single transaction and add error handling to roll back the work if anything fails.<\/p>\n
Here is how to do it:
\nKey points:
\nUse START TRANSACTION at top after BEGIN
\nUse COMMIT at end<\/p>\n
Turn on Transactions with START TRANSACTION after the BEGIN<\/p>\n
And end the transaction with COMMIT at the end of your procedure<\/p>\n
Add an error handling block that calls ROLLBACK work if there is any SQL EXCEPTION.<\/p>\n
This is example SENDs a custom crafted ERROR message ( similar to RAISE_APPLICATION_ERROR in ORACLE ) which our PHP code recognizes and displays \/ logs.<\/p>\n
Here is the magic:
\nCREATE
\nDEFINER = CURRENT_USER
\nPROCEDURE bocs_create_budget(psource_bud_id INT(11), ptitle VARCHAR(200), pfy INT(4), pout_years INT(11))
\nBEGIN<\/p>\nDECLARE new_bud_id INT(11);<\/p>\n
DECLARE EXIT HANDLER FOR SQLEXCEPTION<\/strong>
\n BEGIN<\/strong>
\n GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,<\/strong>
\n @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;<\/strong>
\n SET @full_error = CONCAT(\u201cERROR \u201c, @errno, \u201d (\u201c, @sqlstate, \u201c): \u201c, @text);<\/strong>
\n ROLLBACK;<\/strong>
\n SIGNAL SQLSTATE \u201845000\u2019<\/strong>
\n SET MESSAGE_TEXT = @full_error, MYSQL_ERRNO = 1001;<\/strong>
\n END;<\/strong><\/p>\nSTART TRANSACTION;<\/strong><\/p>\n
SET @TRIGGER_CHECKS = FALSE;<\/p>\n
\u2014 Procedure work that may raise errors is done here<\/p>\n
COMMIT;<\/p>\n
SET @TRIGGER_CHECKS = TRUE;
\nEND $$
\nDELIMITER ;<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"f you have mysql stored procedure that changes database tables \u2014 you probably want to enclose the whole procedure in a single transaction and add error handling to roll back the work if anything fails. Here is how to do … Continue reading