MySQL function/procedure error handling

Hi there,

I need to implement some error handling in MySQL. I come from a .net background so was expecting this be straightforward but it doesn't seem that simple.  Another EE member kinly provided the following links.  

Maybe I am missing something but these seem to assume that I will know what type of error it is that I am going to catch, is there no way to create a function that will run a piece of code regardless of any error being thrown?  My function calls a view and inserts the results into a table, if the view cannot be called for any reason I need to know and trap the error.

Is there any chance one of you MySQL experts could provide an example of a simple function that will catch any errors that are thrown?

MySQL Signal Conditions
http://dev.mysql.com/doc/refman/5.5/en/signal.html

MySQL Declare Handlers
http://dev.mysql.com/doc/refman/5.5/en/declare-handler.html

Thanks,

Let me know if you need any more info
cp30Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

KalpanCommented:
Please refer the below examples
CREATE PROCEDURE sp_add_location
         (in_location    VARCHAR(30),
          in_address1    VARCHAR(30),
          in_address2    VARCHAR(30),
          zipcode        VARCHAR(10),
          OUT out_status VARCHAR(30))
    MODIFIES SQL DATA
BEGIN
  DECLARE CONTINUE HANDLER FOR 1062
     SET out_status='Duplicate Entry';

  SET out_status='OK';
  INSERT INTO locations
    (location,address1,address2,zipcode)
   VALUES 
 (in_location,in_address1,in_address2,zipcode); END; 

the DECLARE CONTINUE HANDLERstatement tells MySQL that “if you encounter MySQL error 1062 (duplicate entry for key), then continue execution but set the variablep_statusto
'Duplicate Entry'.” 

DELIMITER //

DROP PROCEDURE IF EXISTS decount_test;
CREATE DEFINER = 'root'@'localhost' PROCEDURE decount_test ( p_id bigint )
DETERMINISTIC MODIFIES SQL DATA
BEGIN
  DECLARE EXIT HANDLER FOR SQLSTATE '42000'
    SELECT 'Invoiced barcodes may not have accounting removed.';
  IF (SELECT invoice_id 
       FROM accounted_barcodes
       WHERE id = p_id
    ) THEN
    CALL raise_error;
 END IF;
 DELETE FROM accounted_barcodes WHERE id = p_id;
END //

DELIMITER ;

Output:

call decount_test(123456);
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. |
+----------------------------------------------------+
| Invoiced barcodes may not have accounting removed. | 
+----------------------------------------------------+

Open in new window

0
cp30Author Commented:
Hi, thanks for examples but it seems that in your examples you specify the type of error your are expecting.  My question is, is there anyway to handle ANY type of error, and also, can you access the error info, i.e. to write to a log so you know what error was thrown?

Thanks
0
KalpanCommented:
The usual drill is to do as little as possible in your stored procedures. Your PHP program will get errors and exceptions and that's what you wanted to happen. Since Scripting language will get the exceptions, what are you worrying about? Just let them pop out of your stored procedure.

Your Scripting language will have to "alert the user that something has broken".

If you want to somehow "wrap" all your stored procedure in some kind of error logging, do this in Scripting language. Don't mess with over-writing your stored procedures. Don't worry about I/O errors. You have a database to handle that.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

cp30Author Commented:
Hi, thanks for your response.  I see where you are coming from, but in this instance the stored proc is being executed on a routine basis by the event scheduler, so there is no interface directly with the application/gui.  OK, I could write a small program to call the SP and then run this every x minutes and then log any errors from the new app but that introduces more complexity to the system having to rely on another scheduling tool to run the new app and developing new program to connect and execute the SP.

Are we saying there's no way to catch or handle an unknown excpetion type? This seems a little poor and I can't believe it's that uncommon a request.

Many thanks
0
cp30Author Commented:
Hi, I think I've figured part of the question out myself...

It seems that I can declare a handler for the SQLEXPECTION class that covers all errors and that means I can detect when an error is thrown.  It still doesn't allow me to actually see what the error was, does anyone know if this is possible?

Here's what I'm doing now to call my code that must run even if an error is thrown

-- ADD HANDLER IN CASE OF ERROR
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    call odds.updateFail(
    'an error was detected in sp');

Open in new window


Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cp30Author Commented:
Managed to find out way to do it myself while waiting for solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.