catch sql exceptions in sql procedure

as400

    Sponsored Links

    Next

  • 1. QZDASOINIT jobs start in the wrong subsystem
    Hi, We are having a problem with an application which starts a bunch of of QZDASOINIT (stored proc calls from an external system) jobs. Usually the jobs start in subsystem QUSRWRK. But, occasionally they start in QSERVER which is problematic for our operations guys. Anyone have an idea on how to force QZDASOINIT jobs to always start in QUSRWRK?
  • 2. Trace AS/400 Spool
    Hi, any idea how can i trace who has delete spool files ? Thx. Erich
  • 3. Accessing DB2 8.1 hosted on Windows from OS400 V5R2
    Has anyone setup DB2 8.1 (fix patch 7) on Windows 2000 to be a DRDA server? I want to have an iSeries with OS400 V5R2 act as a DRDA client to an 8.1 DB2 UDB database hosted on Windows and so it can access the data stored on the windows database. What are the steps to set this up? Also, is this the best way to go about accessing a windows db2 8.1 database from an application running on os400 v5R2 release?
  • 4. Holes in my SOX (user exit program)
    I have holes in my SOX. I am writing a user exit program to handle the X'18nn' function codes that are passed when file download or upload request are processed. We have users that use the Excel add-in from client access and other ODBC and API calls. It seems that every request has it's own data format. Just about the time I think that I have coded for all formats another one fills my log file with garbage. At this time I am trapping the inbound request and capturing them in a log file. I need to know who and what is being transferred before my program starts locking unauthorized request. I have to do the same for FTP request. Has anyone does this already? I could use any knowledge anyone wants to share. I was never good at re-inventing the wheel. Thanks in advance for your input.
  • 5. IFS - QNTC
    I have a couple of problems with the IFS. When I use As/400 Opperations navagator and view the file system Qntc it doesnt show me all the machines I can see when I use WRKLNK. Can anyone offer some insight. Also I have one particular PC machine disapear even from the WRKLNK command. The machine is there I can ping it and it responds, I can use netserver and control it. But it is not consistant on showing up in the IFS. I know that this particualar machine is outside of the domain and from what I have read it is suposed to be for it to work, and I can't bring it into the domain for other reasons. But it shows up sometimes , its very strange. I am just looking for suggestions on what to look for or maybe something I have overlooked. We are still running 4R5 if that helps. Is it just quirks with IFS. I have searched the group and doesnt seem to be a common occurance. Anyone have any ideas. Thanks.

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