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 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/
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.
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.
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/
Sorry Serge.
Hadn't gone through your reply properly.
Silly mistake.
Thanks a lot
Praveen This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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
>>>...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
by: Gancy |
last post by:
PROGRAM
StringBuilder sbTest1 = new StringBuilder();
StringBuilder sbTest2 = new StringBuilder();
for (int i = 1; i <= 54; i++)
sbTest1.Append("a");...
|
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;
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
| |