Mysql print error message and rollback
I am trying to write a stored procedure that first prints an error and then rolls back
I tried this but this dose did not work
I can rollback, but if there is an error, the dose does not display an error message
DELIMITER
CREATE PROCEDURE transaction_sp ()
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
--------------------------------------------------------------------------------------
select "error message '%s' and errorno '%d'"------- this part in not working
--------------------------------------------------------------------------------------
ROLLBACK;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
--------------------------------------------------------------------------------------
select "warning message '%s' and errorno '%d'"------- this part in not working
--------------------------------------------------------------------------------------
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,pr ice_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
$$
so how can i achieve this with this stored procedure
+4
source to share
2 answers
it worked for me
DELIMITER
CREATE PROCEDURE transaction_sp ()
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
--------------------------------------------------------------------------------------
--select "error message '%s' and errorno '%d'"------- this part in not working
--------------------------------------------------------------------------------------
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 as RETURNED_SQLSTATE , @p2 as MESSAGE_TEXT;
ROLLBACK;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
--------------------------------------------------------------------------------------
-- select "warning message '%s' and errorno '%d'"------- this part in not working
--------------------------------------------------------------------------------------
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1 as RETURNED_SQLSTATE , @p2 as MESSAGE_TEXT;
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,pr ice_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
$$
+12
source to share
Sorry, I may be late for the party, but at your request
BEGIN
-- ERROR
--------------------------------------------------------------------------------------
select "error message '%s' and errorno '%d'"------- this part in not working
--------------------------------------------------------------------------------------
ROLLBACK;
END;
be changed as
BEGIN
select "error message '%s' and errorno '%d'"; -- use missed the semicolon
ROLLBACK;
END;
Thus, it will display an error message with the column name as "ROLLBACK" instead of printing and rolling back the transaction.
0
source to share