0

I have the following Handler and the sqlexception is being caught, but GET DIAGNOSTICS is not returning any information - they are all NULL.

I have the following code:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN 

      GET DIAGNOSTICS @cno = NUMBER;
      GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

      GET DIAGNOSTICS CONDITION 1
        currentcode = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

    GET CURRENT DIAGNOSTICS CONDITION 1 @current = RETURNED_SQLSTATE;
    GET STACKED DIAGNOSTICS CONDITION 1 @stacked = RETURNED_SQLSTATE;

  SELECT @current, @stacked, @errno INTO currentcode,stackedcode,errorcode;


GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);
SELECT @full_error;

CALL debug_msg(1,@full_error);

    SET _rollback = 1;  
    SET ResultCode = CONCAT('currentcode: ' , currentcode, ' , stackedcode: ' , stackedcode);
  END;

I admit that the condition_number element - I do not understand if it represents the index of conditions that were caught or if it is the error number to look at and the documentation on the MySQL site says condition_number (ok I get that it is a condition_number.)

  • Inside procedure you want to declare those variables and remove the @ on each reference. dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html. Also stacked diagnostics should run after a statement,otherwise will not show stacked errors. – 3manuek Sep 17 '16 at 13:58
  • @3manuek yes the third line has declared variables currentcode and msg I got that from the same link that you posted (near the bottom pf page)- so I thought I would use other examples from online. Do I need to enable something in MySQL for that to work? – StixO Sep 17 '16 at 18:03
  • No,this is a built in feature. Did you take a look at dev.mysql.com/doc/refman/5.7/en/…? – 3manuek Sep 19 '16 at 6:18
-2

Replace GET DIAGNOSTICS CONDITION 1

with GET STACKED DIAGNOSTICS CONDITION 1

In my case this helped me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.