I am trying to write a stored procedure , which uses transactions

the following is a sample code.

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.

Hi ozzii!

When an error occurs mysql doesn't automatically rollback. You must create handlers (see MySQL :: MySQL 5.0 Reference Manual :: 12.8.4.2 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)
begin
declare exit handler for not found rollback;
declare exit handler for sqlwarning rollback;
declare exit handler for sqlexception rollback;

set ret=0;
start transaction

-- insert into ...
-- update ...
-- any other DDL operation

commit;

set ret = 1;
end

Listing 2. PHP CODE:

/* MySQL connection code here. */

$res = mysql_query("CALL sp_answer(@res)");

/* TO CHECK PROCEDURE RETURN STATUS: */
$res = mysql_query("SELECT @res");
$row = mysql_fetch_array($res);
$ret = (int)$row[0];
if( $ret ) {
print "sucess!";
}
else {
print "failed!";
}

Let me know if you have any doubt!!!

Peace!