473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Strange Behaviour of SP due to GET DIAGNOSTICS position

Hi All
db2 8.1.3 Windows
I have folowing table structures
CREATE TABLE tb_RTB(
EMP_ID INTEGER,
DESC VARCHAR(20));

CREATE TABLE tb_ERROR(
SQL_STATE CHAR(5),
SQL_DESC VARCHAR(20),
REFER varchar(20));

Following entries are inserted in tb_RTB

INSERT INTO tb_RTB VALUES(1,'MGR'),(2,'CLK'),(3,'CLK')

I have a stored procedure USP_PROC1

CREATE PROCEDURE WSACES.USP_PROC1
(IN iEMP_ID INTEGER,
OUT SQLDESC_OUT VARCHAR(20),
OUT SQLSTATE_OUT CHAR(5))
SPECIFIC PROC1
LANGUAGE SQL
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NO_DATA_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE vSQL_MSG VARCHAR(4096) DEFAULT 'SUCCESS';

DECLARE EXIT HANDLER FOR NO_DATA_FOUND
BEGIN
--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';

END;

UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1

This procedure updates the table tb_RTB properly.
Now I update the value back to 'CLK' for EMP_ID 2.

I change the GET DIAGNOSTICS position and place it out of Begin - End
Block of handler.
Now the SP is

CREATE PROCEDURE WSACES.USP_PROC1
(IN iEMP_ID INTEGER,
OUT SQLDESC_OUT VARCHAR(20),
OUT SQLSTATE_OUT CHAR(5))
SPECIFIC PROC1
LANGUAGE SQL
P1: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE NO_DATA_FOUND CONDITION FOR SQLSTATE '02000';
DECLARE vSQL_MSG VARCHAR(4096) DEFAULT 'SUCCESS';

DECLARE EXIT HANDLER FOR NO_DATA_FOUND

--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

BEGIN
--Get the error message text
-- GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';

END;

UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1
This SP doesnot update the table tb_RTB.
Can any gurus explain why is this happening?

Regards
Praveen

Aug 17 '06 #1
5 3054
Praveen_db2 wrote:
DECLARE EXIT HANDLER FOR NO_DATA_FOUND

--Get the error message text
GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;
This is the end of of handler
>
BEGIN
A nested compound statement, nothing more
--Get the error message text
-- GET DIAGNOSTICS EXCEPTION 1 vSQL_MSG = MESSAGE_TEXT;

SELECT SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;

SELECT SQL_DESC
INTO SQLDESC_OUT
FROM tb_ERROR
WHERE SQL_STATE = SQLSTATE_OUT
AND REFER = 'PROC1';
My bet: You're getting a 02000 right here. Exit handler, game over.
END;
UPDATE tb_RTB
SET DESC = 'MGR'
WHERE EMP_ID = iEMP_ID;
END P1
If you drive this through the debugger it should show what happens.
Note that Developer Workbench works against DB2 V8.2, in case you don't
like Developer Center V8.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 17 '06 #2
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
help by explaining this strange behaviour.

Aug 17 '06 #3
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
help by explaining this strange behaviour.

Aug 18 '06 #4
Praveen_db2 wrote:
Hi Serge
I have debugged it using Development center.It directly jumps into "SELECT
SQLSTATE
INTO SQLSTATE_OUT
FROM SYSIBM.SYSDUMMY1;" statement after going through the
variable declarations.It does not even go into the UPDATE statement.Please
help by explaining this strange behaviour.
I did. Please reread my post.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 18 '06 #5
Sorry Serge.
Hadn't gone through your reply properly.
Silly mistake.

Thanks a lot
Praveen

Aug 18 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Syver Enstad | last post by:
Here's the interactive session Python 2.2.2 (#37, Oct 14 2002, 17:02:34) on win32 Type "help", "copyright", "credits" or "license" for more information. >>> ord('\xe5') 229 >>>...
0
by: john | last post by:
Hi,All Gurus: It is kind of complicated, please bear with me and let me know if you have any questions. Thanks a lot in advance. John I have a csharp method, using emit to dynamically generate...
10
by: Curious | last post by:
Hi, I have a worker thread running, where basically its operation is to execute in an infinite loop. Now, when I am suspending this thread, the CPU usage time is still remaining 100%. What...
2
by: Lenster | last post by:
When using PostMessage to post myself a message, the msg and wparam parameters somehow get swapped over. They are in the correct order when calling PostMessage but by the time wndproc handles the...
1
by: VJ | last post by:
I get the below error, System.InvalidOperationException: Process performance counter is disabled, so the requested operation cannot be performed. at...
31
by: gamehack | last post by:
Hi all, I've been testing out a small function and surprisingly it does not work okay. Here's the full code listing: #include "stdlib.h" #include "stdio.h" char* escaped_byte_cstr_ref(char...
8
by: Dox33 | last post by:
I ran into a very strange behaviour of raw_input(). I hope somebody can tell me how to fix this. (Or is this a problem in the python source?) I will explain the problem by using 3 examples....
5
by: Gancy | last post by:
PROGRAM StringBuilder sbTest1 = new StringBuilder(); StringBuilder sbTest2 = new StringBuilder(); for (int i = 1; i <= 54; i++) sbTest1.Append("a");...
160
by: DiAvOl | last post by:
Hello everyone, Please take a look at the following code: #include <stdio.h> typedef struct person { char name; int age; } Person;
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.