MySQL Forums
Forum List  »  Stored Procedures

Re: Keep inserted rows in Temp table - after a rollback
Posted by: Anandkumar Sagar
Date: February 03, 2019 11:43PM

Hi,
I think savepoint does not work. because insert/update to hard tables and temporary tables is inside a loop(which iterates a cursor).

here is my actual sampel stored procedure. if i put savepoint in loop and if exceptions occurs at nth iterations then it will either rollback or commit all executions before nth iterations.


CREATE PROCEDURE CustomerCleanup()
BEGIN
CREATE TEMPORARY TABLE log(logmessage LONGTEXT);
BLOCK1:BEGIN


DECLARE tableName_cursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedTables = TRUE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- GET DIAGNOSTICS 1 msg = MESSAGE_TEXT;
INSERT INTO temptable VALUES('Rolling back transaction ');
ROLLBACK ;
END;

START TRANSACTION;

tableProcess: repeat
FETCH tableName_cursor INTO tablename;
//insert into table1
//insert into table2


INSERT INTO temptable(logMessage) VALUES ('log message '));

UNTIL finishedTables END REPEAT tableProcess;

COMMIT;


END BLOCK1;
select * from temptable;
END;

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Keep inserted rows in Temp table - after a rollback
491
February 03, 2019 11:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.