473,320 Members | 1,846 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,320 software developers and data experts.

Stored Procedure - Handling all error code with a single condition !!

vj

Please let me know how can i handle any kind of error conditions with a
single (condition and continue handler).

I need to catch any kind of exception thrown in the procedure with
a single
( declare condition and a declare continue handler)

Thanks in advance.

Vijay

Nov 12 '05 #1
3 6348
vj wrote:
Please let me know how can i handle any kind of error conditions with a
single (condition and continue handler).

I need to catch any kind of exception thrown in the procedure with
a single
( declare condition and a declare continue handler)

Thanks in advance.

Vijay

That should do it:
DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
....

Keep me on record for saying that this is a really bad idea.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
I have to agree with Serge here. For instance, if your procedure gets a
-1224 SQLCODE (no database agent -- typically meaning your connection
has been forced), coding up a continue handler, especially if you're
processing result data inside the procedure, can mean your procedure
will loop forever. The negative offshoot of that is that at best you'll
have an db2fmp process orphaned on your system (for any FENCED
procedure) or at worst the agent won't be able to be forced off as
control is inside the procedure (for any NOT FENCED LANGUAGE C
procedure, or SQL procedures created pre-V82 ie FP7).

Serge Rielau wrote:
vj wrote:
Please let me know how can i handle any kind of error conditions with a
single (condition and continue handler).

I need to catch any kind of exception thrown in the procedure with
a single
( declare condition and a declare continue handler)

Thanks in advance.

Vijay

That should do it:
DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
...

Keep me on record for saying that this is a really bad idea.

Cheers
Serge

Nov 12 '05 #3
Serge Rielau wrote:
vj wrote:
Please let me know how can i handle any kind of error conditions with a
single (condition and continue handler).

I need to catch any kind of exception thrown in the procedure with
a single
( declare condition and a declare continue handler)

Thanks in advance.

Vijay

That should do it:
DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
...

Keep me on record for saying that this is a really bad idea.

Cheers
Serge


Kind of makes me think of the lame ON ERROR RESUME statement in Visual
Basic. It's a really sloppy way of (not) handling exceptions.

So I agree with Serge here.

-R-
Nov 12 '05 #4

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

Similar topics

1
by: Jen S | last post by:
I feel like I'm missing something obvious here, but I'm stumped... I have a stored procedure with code that looks like: INSERT INTO MyTableA ( ...fields... ) VALUES (...values...) IF...
2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
9
by: dtwilliams | last post by:
OK, i'm trying to do some error checking on stored procedures and am following the advise in Erland Sommarskog's 'Implementing Error Handling with Stored Procedures' document. Can anybody help...
0
by: Rhino | last post by:
I've written several Java stored procedures now (DB2 V7.2) and I'd like to write down a few "best practices" for reference so that I will have them handy for future development. Would the...
5
by: Raquel | last post by:
This is a very simple DB2 SQLJ stored procedure. The problem is that it seems to run fine but returns NOTHING. I mean..as if nothing has happened..not resultset is returned. I am passing value...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
4
by: William F. Robertson, Jr. | last post by:
One of my developers came to me with this question and I don't have an answer for them. The only suggestion I had for them was to change the return to a output parameter and put a try catch around...
10
by: PJackson | last post by:
Trying to declare a simple handler for SQLEXCEPTION. Simple stored proc (UDB 8.2) ---- CREATE PROCEDURE TFBUDB.SMACF_SM_DI (IN POL_NBR CHAR(10) , OUT ErrNo INTEGER , OUT ErrMsg CHAR(80) )...
5
by: william.david.anderson | last post by:
Hi there, I have a newbie stored procedures performance question that I'm hoping someone can answer. It seems like one of the benefits of using SQL stored procedures is that they can reduce...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
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...
0
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...
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.