PDA

View Full Version : stored proc error handling



BubikolRamios
06-27-2011, 08:50 AM
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET i_result = -2;

This does it, but anyway , since there is a bunch of sqls there, I can't see which failed. Could write a zilion of DECLARE EXIT HANDLER ..., but that would only tell me which error, not which sql.

I imagined that the failed sql should be written to mysql-error-log.err file
but there is nothing.

Any tip appreciated.

Old Pedant
06-27-2011, 08:35 PM
?? Why can't you get both pieces of information??

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET problem = CONCAT( '2: ', someOtherVariable);

That is, have the number tell you WHERE the error came from and a reason code of some code (maybe obtained from MySQL itself) tell you what the error is.

BubikolRamios
06-28-2011, 12:56 AM
so what you are saying:



DECLARE EXIT HANDLER FOR SQLEXCEPTION SET problem = CONCAT( '2: ', someOtherVariable);

Select from ...;
SET someOtherVariable= 'sql1';


Select from ...;
SET someOtherVariable= 'sql2';




That gets confusing as soon you add/insert other sql-s into procedure.

Figured that error sqls probably does not get into file, coz I have output of slow_sql_log set to table, and that somehow prevent logging error sqls to file. Or something .... Will try to figure out that first.