How to get error message associated with an SQLSATE from DB2 SP

[CK]

I am using output parameters to populate the SQL error information to
the client. I would like to populate SQLCODE, SQLSTATE and its message
but now only either SQLCODE or SQLSTATE is able to be populated when
an error happens from a SQL statement.

Here is the code

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
set p_sql_state = SQLSTATE;
-- set p_sql_code = SQLCODE will not work
-- because SQLCODE has been set 0
-- Don't know how to get its message

END;

p_sql_state is an output parameter of a SQL stored procedure.

Could someone give me an example to set SQLCODE, SQLSTATE and its
message to output parameters when an error occurs from a SQL
statement?

Thanks.

[Knut Stolze]

CK wrote on Monday 11 November 2002 20:31:

> I am using output parameters to populate the SQL error information to
> the client. I would like to populate SQLCODE, SQLSTATE and its message
> but now only either SQLCODE or SQLSTATE is able to be populated when
> an error happens from a SQL statement.
>
> Here is the code
>
> DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
> BEGIN
> set p_sql_state = SQLSTATE;
> -- set p_sql_code = SQLCODE will not work
> -- because SQLCODE has been set 0

That's the correct behaviour. The SET command succeeded, so the sqlcode has
to be 0 (zero).

> -- Don't know how to get its message
>
> END;
>
> p_sql_state is an output parameter of a SQL stored procedure.
>
> Could someone give me an example to set SQLCODE, SQLSTATE and its
> message to output parameters when an error occurs from a SQL
> statement?

You might want to assign both values at the same time. Try:

SET ( p_sql_state, p_sql_code ) = ( SQLSTATE, SQLCODE );

--
Knut Stolze
DB2 Spatial Extender
IBM Germany / University of Jena

[PM (pm3iinc-nospam)]

<snip>
The GET DIAGNOSTICS statement does not change the contents of the
diagnostics area (SQLCA).
If an SQLSTATE or SQLCODE special variable is declared in the SQL procedure,
these are set to the SQLSTATE or SQLCODE returned from issuing the GET
DIAGNOSTICS statement.
<snip>

I also know there is an api to get a message description.
sqlaintp - Get Error Message

In v8, there are new things like MESSAGE_TEXT.
I never tried it but may help in the future.

PM

[CK]

Tried the following code of SQL stored procedure from SPB,

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN

SET ( p_sql_state, p_sql_code ) = ( SQLSTATE, SQLCODE );

-- SET p_sql_state = SQLSTATE;
END;

Got an error when built it as following

XX.GUID_INFO_SP - Create stored procedure returns -104.

XX.GUID_INFO_SP: 35: [IBM][CLI Driver][DB2/SUN] SQL0104N An
unexpected token "SET" was found following "ING BEGIN ".
Expected tokens may include: "IF". LINE NUMBER=35. SQLSTATE=42601

XX.GUID_INFO_SP - Build failed.

XX.GUID_INFO_SP - Changes rolled back.

"PM \(pm3iinc-nospam\)" <Pm3iinc...@sympatico.ca> wrote in message news:<DU9A9.5842$2x4.6...@news20.bellglobal.com>...

[CK]

Tried several ways, it looks that both the following code will do the
job:

First one, p_sql_code, p_sql_state, p_sql_message are OUT parameters
of the SQL stored procedure. JDBC client can get them but is not able
to catch the SQLException.

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN

GET DIAGNOSTICS EXCEPTION 1 p_sql_message = MESSAGE_TEXT;
VALUES (SQLSTATE, SQLCODE) INTO p_sql_state, p_sql_code;
END;

Second one which I don't understand why. v_sql_state and v_sql_code
are the local variables from the SQL stored procedure. JDBC client is
able to catch the SQLException.

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN

SELECT SQLSTATE, SQLCODE into v_sql_state, v_sql_code
FROM SYSIBM.SYSDUMMY1;
END;

Could someone give an advise that which one is better to handle the
error condition above? And explain how DB2 works with the second one
why client is able to see the SQLException but not the first one?

Thanks.

[PM (pm3iinc-nospam)]

I guess that in code-1, maybe you need to RESIGNAL.
Maybe de Values into is not considered the first statement
(if that matters when get diag is used) so it resets the error codes.
I'm a bit confused myself by the doc. (first statement vs Get diagnostics,
...)
Try to not use the Values to see if they are automatically set by the Get
diagnostics.
Could you also try the Values into V_... (after a get diag)
(did not try it myself)

code-2
Only 1 statement so it's the first. No problemo.
But do you get the message if you don't resignal?

Condition Handler Declarations
If you use a compound statement to define the behavior of a condition
handler,
and you want the handler to retain the value of either the SQLSTATE or
SQLCODE variables,
you must assign the value of the variable to a local variable or parameter
in the first statement of the compound block. If the first statement
of a compound block does not assign the value of SQLSTATE or SQLCODE
to a local variable or parameter, SQLSTATE and SQLCODE cannot
retain the value that caused DB2 to invoke the condition handler.

This is confusing (the word access) :
Note:
When you access the SQLCODE or SQLSTATE variables in an SQL procedure, DB2
sets the value of SQLCODE to 0 and SQLSTATE to '00000' for the subsequent
statement.
DB2(R) implicitly sets these variables whenever a statement is executed. If
a statement raises a condition for which a handler exists, the values of the
SQLSTATE and SQLCODE variables are available at the beginning of the handler
execution. However, the variables are reset as soon as the first statement
in the handler is executed. Therefore, it is common practice to copy the
values of SQLSTATE and SQLCODE into local variables in the first statement
of the handler.

PM

[CK]

The GET DIAGNOSTICS statement does not change the contents of the
diagnostics area (SQLCA).

code-1 did get the proper error value of SQLSTATE, SQLCODE and the
error message too. But the client(caller) must use OUT parameters to
obtain these three values and is not able to catch the SQLException
from callableStatement.execute() when the stored procedure occurs an
error.

code-2. The client(caller) will catch the SQLException from
callableStatement.execute() when the stored procedure occurs an error.

Greatly appreciate any explanation.

Thanks.

[Swaminaathan]

Cliffo...@excite.com (CK) wrote in message news:<a5ea0803.02111...@posting.google.com>...

PM,

Can you tell me where did you find the statements on Connection
Handler you had mentioned above?

[PM (pm3iinc-nospam)]

Condition Handlers, not connection handlers...

I probably looked in
compound statements
get diagnostics
declare handler
signal, resignal

(search 'handler')

I use
v7 doc on the web or local
v8 doc local

PM

"Swaminaathan" <swaminaatha...@wipro.com> a écrit dans le message
de news: 177290b.02111...@posting.google.com...