I am trying to write a stored procedure , which uses transactions
the following is a sample code.
create procedure test_proc(IN id int,IN name varchar(20) )
--insert into table1 some data and get last insert id;
--insert into table2 some data using last insert id;
however am not sure of the correct syntax. In the above procedure if an error occurred would the procedure automatically rollback? If it failed would it generate an error message and if so how would you report that in php? If it was successful again how would you know this. Basically I need some way of knowing if the procedure was successful or not in my php code but at the same time need to know if the procedure would automatically rollback if all or any insert failed.
When an error occurs mysql doesn't automatically rollback. You must create handlers (see MySQL :: MySQL 5.0 Reference Manual :: 220.127.116.11 DECLARE for Handlers) that are executed when an error occurs. I do that in listing 1.
As you can see in the stored procedure I added the parameter ret. This parameter holds the return code of the stored procedure (1-sucess, 0-failure).
In listing 2, I present a snippet of PHP code that calls the stored procedure and checks if it returned successfully or not.
Listing 1. STORED PROCEDURE:
create procedure sp_answer(IN name VARCHAR(20),OUT ret TINYINT)
declare exit handler for not found rollback;
declare exit handler for sqlwarning rollback;
declare exit handler for sqlexception rollback;
-- insert into ...
-- update ...
-- any other DDL operation