| Author |
Message
|
| neo_revolution |
 Posted: Mon Jul 12, 2004 6:40 am Post subject: ESQL - Checking SQLSTATE, SQLCODE !! |
 |
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
Hello All,
I am using
PASSTHRU('DELETE FROM LOG WHERE DATE(TMSTP) < ? ', myDate);
Suppose there are no records earlier than 'myDate' we will get some SQLWarning.
I am thinking how to catch the SQLCODE and SQLSTATE using ESQL ?
I unchecked 'Throw Exception on Database errors' in the compute node... even I am getting the default values for the SQLSTATE and SQLCODE.
I am working with broker deployed on OS/390 and DB2 (OS/390).
Thanks. |
|
| Back to top |
|
 |
| CoolDude |
| Posted: Mon Jul 12, 2004 12:49 pm Post subject: |
|
|
Apprentice
Joined: 17 Jan 2004
Posts: 39
|
You can capture the sql codes in the environment variables like this
CREATE FIELD Environment.Variables.SQLCodes;
DECLARE SQLC REFERENCE TO Environment.Variables.SQLCodes;
SET SQLC.SQLSTATE = SQLSTATE;
SET SQLC.SQLCODE = SQLCODE;
and so on.
In order to do this you need to uncheck the "Throw Exception on Database errors" condition in the Advanced pane of the compute node.
once you capture this information you can
IF (SQLC.SQLSTATE <> 0) THEN
THROW USER EXCEPTION;
END IF;
_________________
Correct Me from Wrong  . If i am correct Appreciate Me  |
|
| Back to top |
|
|
| JT |
| Posted: Mon Jul 12, 2004 2:02 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| neo_revolution wrote: |
| Suppose there are no records earlier than 'myDate' we will get some SQLWarning. |
Not sure if there's another SQL field that denotes no rows were deleted, but the SQLCODE will be zero.
| CoolDude wrote: |
once you capture this information you can
IF (SQLC.SQLSTATE <> 0) THEN
THROW USER EXCEPTION;
END IF; |
It's not necessary to capture the SQL error values prior to interrogating them. You may wish to preserve the values only when an error is detected.
| Code: |
PASSTHRU('DELETE FROM LOG WHERE DATE(TMSTP) < ? ', myDate);
IF (SQLCODE <> 0) THEN
CREATE FIELD Environment.Variables.SQLCodes;
DECLARE SQLC REFERENCE TO Environment.Variables.SQLCodes;
SET SQLC.SQLSTATE = SQLSTATE;
SET SQLC.SQLCODE = SQLCODE;
END IF; |
|
|
| Back to top |
|
|
| kirani |
| Posted: Mon Jul 12, 2004 4:22 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Did you look at the value in SQLSTATE variable?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| JT |
| Posted: Tue Jul 13, 2004 6:18 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
| Not sure if there's another SQL field that denotes no rows were deleted, but the SQLCODE will be zero. |
............. the SQLSTATE and SQLNATIVEERROR variables were also zero. |
|
| Back to top |
|
|
| neo_revolution |
| Posted: Tue Jul 13, 2004 6:21 am Post subject: |
|
|
Voyager
Joined: 21 Oct 2003
Posts: 80
Location: NJ
|
Hi Kiran,
I gave the table name wrong intentionally and I got SQLSTATE = 42S22 and SQLCODE = -1
But if I see SQLERRORTEXT, SQLCODE = -206 and SQLSTATE = 42703,
Why WMQI SQLSTATE is different from DB2 SQLSTATE? How can I correlate them?
Thanks. |
|
| Back to top |
|
|
|
|
