Better way to get error messages outside?

[Ulrich Winterberg]

Hello,

I have a Stored Procedure which has a Transaction inside, called by a programm within a Try-Catch Block.

If there occures an error inside the Transaction, I have to know what happend and if there was a Commit or a Rollback.

I'm looking for a more elegant solution for this. If anybody has an idea please let me know.

The number for Code in the table tblTest must be unique.

I look for the OutError in my programm, if it's not 0 there should have been an error with a rollback.


This is an example for the Stored Procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `daten`.`spTransaktiontest` $$
CREATE DEFINER=`root`@`%` PROCEDURE `spTransaktiontest`(IN InGMNr Integer, In InCode Integer, OUT OutError Integer)
SQL SECURITY INVOKER
BEGIN

Declare Exit Handler for SQLEXCEPTION
BEGIN

Select 1 Into OutError;
Rollback;
END;


Declare Exit Handler for SQLWARNING
BEGIN

Select 2 Into OutError;
Rollback;
END;


Declare Exit Handler for NOT FOUND
BEGIN

Select 3 Into outError;
Rollback;
END;

Start Transaction;

Update tblTest Set Code = InCode Where GMNr = InGMNr;

Update tblTest Set Code = (InCode +1) Where GMNr = InGMNr +1;

Select 0 Into OutError;

Commit;

END $$

DELIMITER ;


Thanks for help

Ulrich