Re: Keep inserted rows in Temp table - after a rollback

[Anandkumar Sagar]

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;