catch sql exceptions in sql procedure

as400

    Sponsored Links

    Next

  • 1. Some help creating a file
    I need to build a file and I just don't know if it can be done within the rules I need. We need to consolidate our inventory from several warehouses into an item total for a web application. First the data. I have the following four fields and they are all within the same file: Item#, Warehouse, Location, Qty On-Hand Item# W/H Loc Qty ======================= ABC 001 001 2 ABC 021 101 0 ABC 022 020 1 ---------------------- ABC 3 DEF 020 001 5 DEF 022 002 7 ---------------------- DEF 12 The file I need to created should be: 1) Maintained like any other logical file 2) Use Item# as the key for an RPG chain operation. 3) Contain just the Item# and Total Qty My preference is to use SQL but DDS is acceptable. Thanks for the help! Mike LaFountain
  • 2. Joined Logical Question
    Hi, I have a question regarding joined logicals. In times gone past the DB2/400 joined logicals could not have a key with fields from the secondary physicals. Is this still the case? Is there a way to get around this, JDUPSEQ is not an option.
  • 3. JDBC driver fro DB2?
    Java web application, based on J2EE and JBOSS 4.0.0.2, installed on Windows 2003 Server Database: DB2 on AS/400 iSeries V5R3M0 Which JDBC driver can be used? One choice is DataDirect, but they are *too* expensive. TIA!

catch sql exceptions in sql procedure

Postby Steve Richter » Wed, 03 Aug 2005 00:01:24 GMT

in an sql procedure I create an alias in qtemp so I can access a member
of a file:

Create procedure MKJBUYRS (
   IN  InSeasonId    char(3),
   IN  InFromDate    DATE,
   IN  InToDate      DATE,
   IN  InMidDate     DATE )
LANGUAGE    SQL
BEGIN
  drop   alias qtemp/mkjbuyra ;
  create alias qtemp/mkjbuyra for mkemailprp(em_050624) ;

when the procedure runs, the alias may or may not exist in qtemp. In my
procedure code, can I monitor or catch the error that sql throws when
the alias being created already exists?

thanks,

-Steve


Re: catch sql exceptions in sql procedure

Postby Kent Milligan » Wed, 03 Aug 2005 01:35:54 GMT

Yes, you can declare a handler to do this - there is a redbook titled "Stored
Procedures, Triggers, and Functions on DB2 UDB for iSeries" at ibm.com/redbooks
that covers handlers.


-- 
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
 XXXX@XXXXX.COM  (spam trick)   GO HAWKEYES!! 
(opinions stated are not necessarily those of my employer)

Re: catch sql exceptions in sql procedure

Postby Steve Richter » Wed, 03 Aug 2005 04:14:04 GMT






very good documentation.  This is the solution I was looking for.

thanks!

-Steve


Re: catch sql exceptions in sql procedure

Postby Steve Richter » Wed, 03 Aug 2005 22:57:44 GMT




can I enable and disable a handler in the procedure?  I only want the
handler to apply to a specific statement in the procedure.

or can I throw an exception from my handler depending on if the
statement I want the handler to apply to is executing or not?
  declare  AliasBeingDropped char(1) default('N') ;
  declare  continue handler for sqlstate '42704'
    if  AliasBeingDropped = 'N' then
       throw exception
    end if ;
...
  set   AliasBeingDropped = 'Y' ;
  drop alias qtemp/mkjbuyra ;
  set   AliasBeingDropped = 'N' ;

thanks,

-Steve


Re: catch sql exceptions in sql procedure

Postby Kent Milligan » Thu, 04 Aug 2005 04:00:27 GMT

Yes, if you put the statement and the handler within a nested compound
statement.

BEGIN
 ....
 BEGIN
   declare  AliasBeingDropped char(1) default('N') ;
  declare  continue handler for sqlstate '42704'
    if  AliasBeingDropped = 'N' then
       throw exception
    end if ;

  set   AliasBeingDropped = 'Y' ;
  drop alias qtemp/mkjbuyra ;
  set   AliasBeingDropped = 'N' ;
 END;
 ....
END



-- 
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
 XXXX@XXXXX.COM  (spam trick)   GO HAWKEYES!! 
(opinions stated are not necessarily those of my employer)

Similar Threads:

1.DB2 9.5 SQL Procedure Developer exam 735 prep, Part 2: DB2 SQL Procedures

Hi folks,

I am interesting in this DB2 9.5 prep for new certification exam, but I get 
this from DeveloperWorks:
The link which brought you here is invalid. Please reload the previous page 
and try again.

message code: 57e

Can this be fixed ?

Regards,



JM




2.sql procedure exception handling

how robust and informative can a global exception handler be in sql
procedures?  Currently, when my sql procedures bomb the system
provides very little information in terms of what statement bombed and
what is the text of the failing statement.

so far I am not getting very far with my sql procedure exception
handler. The handler runs as expected, but the GET DIAGNOSTICS
information available seems to be rather limited. What I want to do is
retrieve the source code statement number and the sql statement text
of the failing statement.  Is this doable?

here is what I have so far:
begin
declare         dummy integer ;
declare         vMsg char(256) ;
declare         vErr char(256) ;
declare         vFullText varchar(256) ;

begin
  declare continue handler for sqlexception
    if  1 = 1 then
      get diagnostics EXCEPTION 1 vErr = MESSAGE_TEXT ;
      get diagnostics vMsg = COMMAND_FUNCTION ;
      set       vFullText = 'Error message: ' || trim(vMsg)
                            || ' ' || vErr ;
      call sndmsg( vFullText, 'SRICHTER' ) ;
    end if ;
...   sql procedure statements follow ...
end ;
end ;

thanks,

-Steve

3.How to match a Java SQL Exception to the SQL error message

Hi,

is it possible to relate a Java SQL exception (for example: SQL Editor
- bad syntax in a user's sql statement)  to the corresponding db2 error
message in a java application?
Does it depend on the JDBC driver type?

Thanks
Florian

4.How to caught unknown exceptions when parsing the XML in stored procedures

Hi

How can we find out that there is some problem like invalida data type
or data is null or some data type errors occured when parsing the XML
in OPENXML.

Basically what I want is , Is there any way to find out in the SQL
Server 2000 stored procedures to catch exceptions that are occured
during the execution and exit the procedure and display the information
as the output or store the error log.

I tried using the @@ERROR.. but failed..

In my procedure i'm passing a very big XML as the input parameter to
the procedure. Through OPENXML i parsed the XML and storing the each
attribute values into respective tables. It is working fine.

In one of my database table there is column with data type "money" and
for testing purpose in the XML this attribute value is passed as
"character" like 100_00 instead of 100.00.

When parsing  this value through the OPENXML procedure is giving me an
error saying that "Implicit conversion is required to convert data type
varchar to data type money.".

I want to caught this error and display to client. I'm unable to catch
such type of exceptions. using @@ERROR i tried but it's always
displaying the value as 0.

So, Can any one help me out on this issue to handle such type of
unknown exceptions when executing the procedure?

5.exec more than 1 sql statement within an sql procedure handler

I would like the continue handler in my sql procedure to execute a
series of statements within the handler declaration ( such as get
diagnostics and a call to qcmdexc to send a message ). Problem is the
syntax appears to permit only one statement.

I have it working where the sole statement is an IF that is always
true. Is this the best way?

begin
  declare continue handler for sqlexception
    if  1 = 1 then
      get diagnostics vMsg = COMMAND_FUNCTION ;
      set       vFullText = 'Error message: ' || vMsg ;
      call sndmsg( vFullText, 'SRICHTER' ) ;
    end if ;

6. Parallel SQL (forking) in SQL stored procedures

7. CALL (SQL)Stored Procedure from (SQL)UDF ?

8. Relative Performance between SQL Procedures & SQL Functions



Return to as400

 

Who is online

Users browsing this forum: No registered users and 51 guest