catch sql exceptions in sql procedure

as400

    Sponsored Links

    Next

  • 1. Making a function available in embedded SQL
    Hi, I'm making a module using CRTSQLCI, this creates a module just fine, but when I do a crtpgm which includes the module (amongst others), crtpgm reports: Definition not found for symbol 'get_user_data' where get_user_data is the function in the SQL module. I did a DSPMOD on the SQL module, and there are no procedures listed, which is presumably the problem. This is a bit odd, as I've done this sort of thing before without problems, any suggestions? Thanks Garry
  • 2. Webfacing all or nothing?
    Question. My shop is in the process of rewriting one of our main apps. Unfortunately, we are doing this in phases, so we cannot just rip the app up all at once. Question, is it possible to Webface only certain screens and if the screen has not been webfaced, then fall back into 5250? We want this to be seamless to the users, IE one client. I know 5250 can be streamed to a browser, so I'm guessing Websphere would need to be configured to know what to serve up to the user (webfaced screen, or 5250 screen). Is this possible? Thanks Chris
  • 3. Problems with inbound UTF-8 characters
    We have a client that is sending us data via XML from FileMaker Pro on a Mac that contains high-ASCII characters. When this hits the iSeries (810 running V5R2), we get blocks. Aside from telling them not to send that information, what can I do so that information is stored correctly? I did a dejanews search and didn't see much that was helpful. Thanks! Geoff
  • 4. VLDCMDKEY behavior changes based on HELP position in DDS member
    Hey all, I just found a wierd one. If the HELP keyword is specified *after* the VLDCMDKEY keyword in a DDS source member the VLDCMDKEY indicator is turned on. But if the HELP keyword is specified *before* VLDCMDKEY, then the indicator is left off. Must be a DDS compiler issue. Just an FYI. Example: *IN90 is left off when control passes to the program. -------------------------------------------------------------- A ALTHELP(CA01) A HELP A VLDCMDKEY(90) A PRINT A CA03 A CA12 Example: *IN90 is turned on when control passes to the program. --------------------------------------------------------------- A ALTHELP(CA01) A VLDCMDKEY(90) A HELP A PRINT A CA03 A CA12

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