How to implement Transaction in a MySql Stored Procedure with Cursor ?
-
Hi I am using a MySql 5.0 DB. am trying to implement a transaction inside a stored procedure which uses cursors. but the problem is the way the cursor closes it self. it throws an exception, i have written a handler for any sqlexception inside the procedure which simply rolls back, and because of the cursor , transaction gets rolled back without completing the task. Has anybody ever worked on MySql with transactions. Please Help. thanks
KSS
-
Hi I am using a MySql 5.0 DB. am trying to implement a transaction inside a stored procedure which uses cursors. but the problem is the way the cursor closes it self. it throws an exception, i have written a handler for any sqlexception inside the procedure which simply rolls back, and because of the cursor , transaction gets rolled back without completing the task. Has anybody ever worked on MySql with transactions. Please Help. thanks
KSS
Hi, the following stored procedure will work properly with transactions and cursors: DELIMITER $$ DROP PROCEDURE IF EXISTS `testdb`.`TestCursorRollback` $$ CREATE PROCEDURE `testdb`.`TestCursorRollback` () BEGIN declare n int; DECLARE done INT DEFAULT 0; declare cur1 cursor for select col1 from hotels.a; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; start transaction; open cur1; repeat fetch cur1 into n; if not done then insert into testdb.b(col1) values (n*10); end if; UNTIL done END REPEAT; close cur1; rollback; /* or commit*/ insert into testdb.b(col1) values(200); END $$ DELIMITER ;
FREE cross database comparison and synchronization tools DBTYP.NET - see database differences, for free (SQL Server, MySQL, Oracle)
-
Hi, the following stored procedure will work properly with transactions and cursors: DELIMITER $$ DROP PROCEDURE IF EXISTS `testdb`.`TestCursorRollback` $$ CREATE PROCEDURE `testdb`.`TestCursorRollback` () BEGIN declare n int; DECLARE done INT DEFAULT 0; declare cur1 cursor for select col1 from hotels.a; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; start transaction; open cur1; repeat fetch cur1 into n; if not done then insert into testdb.b(col1) values (n*10); end if; UNTIL done END REPEAT; close cur1; rollback; /* or commit*/ insert into testdb.b(col1) values(200); END $$ DELIMITER ;
FREE cross database comparison and synchronization tools DBTYP.NET - see database differences, for free (SQL Server, MySQL, Oracle)
Hi, Thanks for the reply but i was looking for another handler for the rollback part. Like : Declare Exit handler for SQLEXCEPTION ROLLBACK; I want my proc to roll back in case of any error. because once i find any error it will throw an exception to my application which can never rollback-ed thanks Siva
kss