 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / April 2007Declaring a Condition handler in DB2 Stored Proc
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) ) ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE NOTFOUND INTEGER DEFAULT 100; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ErrMsg = 'Houston, we have a problem'; DECLARE D1 CURSOR WITH RETURN TO CALLER FOR SELECT DI_NME_TXT ,DI_OCCR_NBR FROM TFBUDB.SM_DI_NME_INFO WHERE POL_NBR_TXT = POL_NBR; OPEN D1; END P1 -------------------------------------------------------- Attempting to build in DB2 Development center I get the error below - it certainly does not like it. Syntax for handler right out of book: TFBUDB.SMACF_SM_DI - Build started. DROP SPECIFIC PROCEDURE TFBUDB.SQL070413170923800 TFBUDB.SMACF_SM_DI - Drop stored procedure completed. Create stored procedure returns -104. TFBUDB.SMACF_SM_DI: 20: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=20. SQLSTATE=42601 TFBUDB.SMACF_SM_DI - Build failed. TFBUDB.SMACF_SM_DI - Roll back completed successfully. On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote: > Trying to declare a simple handler for SQLEXCEPTION. > Simple stored proc (UDB 8.2) [quoted text clipped - 33 lines] > TFBUDB.SMACF_SM_DI - Build failed. > TFBUDB.SMACF_SM_DI - Roll back completed successfully. There's an order that cursors, handlers, DGTTs, and variables must be declared in. I'm not at my desk with my manuals and cheat sheets (so I'm not 100% on this), but try putting your handler declaration after your cursor declaration (but before the open). --Jeff On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote: > Trying to declare a simple handler for SQLEXCEPTION. > Simple stored proc (UDB 8.2) [quoted text clipped - 33 lines] > TFBUDB.SMACF_SM_DI - Build failed. > TFBUDB.SMACF_SM_DI - Roll back completed successfully. OK--back at my desk. Here's the declaration order: 1. Variables 2. Conditions 3. Cursors 4. Handlers 5. DGTTs --Jeff > On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote: >> Trying to declare a simple handler for SQLEXCEPTION. [quoted text clipped - 42 lines] > 4. Handlers > 5. DGTTs Actually DGTT have nothing to do with it. They are just regular statements which have to come after all the "real" DECLAREs Cheers Serge  SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Interesting....as I have been: variable declarations cursor declaration... open cursor code to handle data coming back.... another cursor declaration... open cursor code to handle data coming back... .and so forth MANY times.... and am now going back to put in some error handling...... so do I have to major reorganize this thing to add a handler for sql exceptions? thanks, Phil Jackson > > On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote: > >> Trying to declare a simple handler for SQLEXCEPTION. [quoted text clipped - 52 lines] > DB2 Solutions Development > IBM Toronto Lab > Interesting....as I have been: > > variable declarations BEGIN > cursor declaration... > open cursor > code to handle data coming back.... END BEGIN > another cursor declaration... > open cursor > code to handle data coming back... END .. just nest the compounds. Every new BEGINning starts again. SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Fascinating... So I could have: variable declarations BEGIN cursor declaration... error handling code here open cursor code to handle data coming back.... END BEGIN another cursor declaration... error handling code here open cursor code to handle data coming back... I am up to about a 1,000 line main stored procedure that is calling about 14-15 other stored procedures, which all are like the fairly simple one I posted earlier - I wrapped the main stored proc within an Atomic, Most of these simply declare a cursor for some select...SQL and return leaving the cursor open. One procedure is called afterwards each time, after some processing in the main SP, to insert a row into a table. This happens many, many times. I think the main SP being Atomic is going to rolll back every thing ok if there is any failure along the way, I just need to report back specifics to the client application - what part failed, why it failed, and the specific insurance policy it failed on. Guessing that maybe this is pushing the limit of what a stored procedure should be doing? Trying to get a handle of the architecture of such things in SQL PL... Thanks for all the good advice to everyone, Phil Jackson No problem, not sure if you have experience with other modern languages like e.g. Java. It is generally considered good practice to have condition handlers scoped to only the block that they are meant to handle. The same can be said for local variables. All this was incorporated into the SQL/PSM standard which DB2 implements. Cheers Serge SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab Yes, I've been programming in things like VB,ASP,PHP, etc. for years. This is my first attempt at doing anything other than a simple declare cursor for ..some sql... open cursor.... in SQL PL. Still trying to get a handle on how a more complex stored procedure should be structured. All the examples I've seen are just pretty much linear, fall through from top to bottom with a few loops and such interspersed. thanks! Phil Jackson > No problem, not sure if you have experience with other modern languages > like e.g. Java. [quoted text clipped - 9 lines] > DB2 Solutions Development > IBM Toronto Lab > Yes, I've been programming in things like VB,ASP,PHP, etc. for years. > This is my first attempt at doing anything other than a simple [quoted text clipped - 6 lines] > pretty much linear, fall through from top to bottom with a few loops > and such interspersed. http://www.amazon.com/DB2-SQL-PL-Essential-Windows/dp/0131477005 SignatureSerge Rielau DB2 Solutions Development IBM Toronto Lab > > On Apr 18, 2:39 pm, PJack...@txfb-ins.com wrote: > >> Trying to declare a simple handler for SQLEXCEPTION. [quoted text clipped - 54 lines] > > - Show quoted text - I see; good to know--thanks! --Jeff |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.