catch sql exceptions in sql procedure

as400

    Sponsored Links

    Next

  • 1. Documents in folders.
    Can any one suggest the best way to do the following: I receive documents into a folder. I need to get some of the data out of the Document and put it into a physical file on the AS400. This I can do when the document has a fixed size. The problem is we are receiving documents that can vary in size, in the documents received I do have an identifier at the start of each record, but I do not know the size when I copy the Document to a phsical file. I copy it to a physical file so that I can process it with RPG.
  • 2. Odd QtmmSendMail API problem.
    If I send an email without an attachment with special characters in the subject and/or body, things work fine. If I include an attachment, the special characters get corrupted. For example in the subject: Test ??????... This will be corrupted only if there is an attachment on the email. The MIME file looks fine, so I'm assuming it's a problem with OS? SNDDST doesn't appear to have this problem. If special characters are in the attachment, they come out fine. Checked everything like CCSID of the mime file, and they're the same as well. Any ideas?
  • 3. HOD vs. iSeries access for Web
    Can anyone provide a simple explanation of the difference between these products. IBM's marketing and information on the website do a horrible job of explaining what their products are, especially since they change their names twice a week. We purchased HOD 7.0 awhile back and still have not implemented it, and have recieved 8.0 in the meantime. We are attempting to provide a method for clients to access our system through the web, and would like to know which product we should be using. We are currently on V4R5 but will be upgrading our system to V5R2 in the very near future. Any recommendations or information would be greatly appreciated. Thanks. Greg Brune
  • 4. NFS and SAV
    Hi, is the SAV Command supported for mounts of NFS? by wich release? Dieter Bender
  • 5. editing Translation Tables (i.e. qascii)
    I have a question on how to edit the data in a translation table. I have retreived the source via RTVTBLSRC, and have the data in a database source file i created. However, this looks "un'clean" as you have all the HEX Output data in one record. I wonder if there is another tool or command to do this? Thanks much, Danko

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 2 guest