MySQL Forums
Forum List  »  Stored Procedures

transaction in stored procedure
Posted by: arun parajuli
Date: April 06, 2005 01:15AM

I am trying to write a stored procedure , which uses transaction,in MySql 5.0.2 alpha.

the following is a sample code.

create procedure test_db.test_proc(IN id int,IN name varchar(20) )
start transaction;
--insert into table test1;

-- use some SQL which generates error.

the table test is InnoDB

my broblem is
1) when error occurs, the procedure exits without rolling back( no commit either)the modifications and when i do rollback manually, it rolls back ( i.e the insertsion in the table test1 is undone). I want this automatic.
2)how can I know if any error occured or not ? I want to write a handler which can trap all errors, warnings etc so that i can use a rollback statement there

please somebody suggest me a solution.. Its killing me

Options: ReplyQuote

Written By
transaction in stored procedure
April 06, 2005 01:15AM

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.