How to get 'message text' portion of error from SP

[jdokos]

Hello All,

I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. Here is the
output that is returned:

Value of output parameters
--------------------------
Parameter Name : V_OUTPUT
Parameter Value : ERR:-443

Return Status = 0
Error description from doc:

SQL0443N Routine "<routine-name>" (specific name "<specific-name>")
has
returned an error SQLSTATE with diagnostic text "<text>".

Explanation:

An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?

This is probably a very simple question, but I am just totally inept
at writing SPs.

Thanks in advance.

[Ian]

jdokos wrote:

Hello All,

I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. Here is the
output that is returned:

Value of output parameters
--------------------------
Parameter Name : V_OUTPUT
Parameter Value : ERR:-443

Return Status = 0
Error description from doc:

SQL0443N Routine "<routine-name>" (specific name "<specific-name>")
has
returned an error SQLSTATE with diagnostic text "<text>".

Explanation:

An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?

It would probably be helpful if you posted your stored proc so we can
see how you're doing this.

My guess is that you've set up something like,

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
set V_OUTPUT = 'ERR:' || char(SQLCODE);
END;

[jdokos]

On Jun 19, 9:19*pm, Ian <[e-mail]:

jdokos wrote:

Hello All,

I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. *Here is the
output that is returned:

Value of output parameters
* --------------------------
* Parameter Name *: V_OUTPUT
* Parameter Value : ERR:-443

* Return Status = 0

Error description from doc:

SQL0443N *Routine "<routine-name>" (specific name "<specific-name>")
has
* * * returned an error SQLSTATE with diagnostic text "<text>".

Explanation:

An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.

My question is how can I modify this procedure to get all of the error
'message text'?

It would probably be helpful if you posted your stored proc so we can
see how you're doing this.

My guess is that you've set up something like,

* * DECLARE EXIT HANDLER FOR SQLEXCEPTION
* * * *BEGIN
* * * * * set V_OUTPUT = 'ERR:' || char(SQLCODE);
* * * *END;- Hide quoted text -

- Show quoted text -

Here is the handler portion of the SP:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;

I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.

Thanks,

[Ian]

jdokos wrote:

On Jun 19, 9:19 pm, Ian <[e-mail]:

>jdokos wrote:

>>Hello All,
I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. Here is the
output that is returned:
Value of output parameters
--------------------------
Parameter Name : V_OUTPUT
Parameter Value : ERR:-443
Return Status = 0
Error description from doc:
SQL0443N Routine "<routine-name>" (specific name "<specific-name>")
has
returned an error SQLSTATE with diagnostic text "<text>".
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?

It would probably be helpful if you posted your stored proc so we can
see how you're doing this.

My guess is that you've set up something like,

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
set V_OUTPUT = 'ERR:' || char(SQLCODE);
END;- Hide quoted text -

- Show quoted text -

Here is the handler portion of the SP:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;

As expected.

I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.

The solution may be to not use the exit handler for any SQLEXCEPTION.
Currently, your stored procedure runs successfully (as far as DB2 and
the calling application are concerned). The application has to parse
the V_OUTPUT variable to determine if the procedure was actually
successful.

If you don't trap the SQLEXCEPTION, your procedure will fail "properly"
-- meaning the application will see that the "CALL YOUR_SP()" statement
failed. You'd need to change your code to handle this, but you would
get the actual SQLCODE and DB2 error message text.

Sometimes it's useful to trap specific errors, only so you can provide
more descriptive error codes/messages. For example, SQLSTATE '23505'
(primary key violation) isn't always very useful. So you could trap
it and raise a more descriptive error:

declare pk_violation condition for sqlstate '23505';
declare exit handler for pk_violation
signal sqlstate '75025'
set message_text = 'A user with this employee ID already exists.';

[db2dude]

Jeff,

You can also have ur exit handler capture the message_text instead and
pass that out as an output parameter..

DECLARE EXIT HANDLER FOR SQLEXCEPTION
get diagnostics exception 1 V_OUTPUT = message_text;

-SA

On Jun 20, 5:14*pm, Ian <[e-mail]:

jdokos wrote:

On Jun 19, 9:19 pm, Ian <[e-mail]:

jdokos wrote:
Hello All,
I have a procedure that is getting -443 after upgrading to V9.5 FP1.
The procedure was written to output only the SQLCODE. *Here is the
output that is returned:
Value of output parameters
* --------------------------
* Parameter Name *: V_OUTPUT
* Parameter Value : ERR:-443
* Return Status = 0
Error description from doc:
SQL0443N *Routine "<routine-name>" (specific name "<specific-name>")
has
* * * returned an error SQLSTATE with diagnostic text "<text>".
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>" (specific
name "<specific-name>"), along with message text "<text>". The routine
could be a user-defined function or a user-defined method.
My question is how can I modify this procedure to get all of the error
'message text'?
It would probably be helpful if you posted your stored proc so we can
see how you're doing this.

My guess is that you've set up something like,

* * DECLARE EXIT HANDLER FOR SQLEXCEPTION
* * * *BEGIN
* * * * * set V_OUTPUT = 'ERR:' || char(SQLCODE);
* * * *END;- Hide quoted text -

- Show quoted text -

Here is the handler portion of the SP:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET v_output = CONCAT('ERR:', CHAR(SQLCODE));
DECLARE CONTINUE HANDLER FOR SQLWARNING
SET l_sqlcode = 0;

As expected.

I am trying to find a way to help the application team rewrite this to
get the complete error text from the procedure.

The solution may be to not use the exit handler for any SQLEXCEPTION.
Currently, your stored procedure runs successfully (as far as DB2 and
the calling application are concerned). *The application has to parse
the V_OUTPUT variable to determine if the procedure was actually
successful.

If you don't trap the SQLEXCEPTION, your procedure will fail "properly"
-- meaning the application will see that the "CALL YOUR_SP()" statement
failed. *You'd need to change your code to handle this, but you would
get the actual SQLCODE and DB2 error message text.

Sometimes it's useful to trap specific errors, only so you can provide
more descriptive error codes/messages. *For example, SQLSTATE '23505'
(primary key violation) isn't always very useful. *So you could trap
it and raise a more descriptive error:

* * declare pk_violation condition for sqlstate '23505';
* * declare exit handler for pk_violation
* * * *signal sqlstate '75025'
* * * *set message_text = 'A user with this employee ID alreadyexists.';

You can also have ur exit handler capture the message_text instead and
pass that out as an output parameter..

DECLARE EXIT HANDLER FOR SQLEXCEPTION
get diagnostics exception 1 l_message_text = message_text;