catch sql exceptions in sql procedure

as400

    Sponsored Links

    Next

  • 1. Printing Duplex on Royal Copier 2530 from AS/400
    Hey..I need your help. I have a Royal Copier from Kyocra and it has duplex capabilities. However, in the Printer Configuration, it is not working. I have printed several test pages and they are not being duplex. Does anyone have insight as to how to create the proper configuration for this Royal Copier 2530. It is directly connected via TCP/IP Thank you for your help. Gil
  • 2. Need Network Advise Etc.
    I have a large library roughly 50GIG, that I need to share between a couple machines at any given time(on demand though). Today these machines are all on 1 ASP, no IASP and no VLAN or HSL, and the machines are far apart. In the future I am looking at the machines being co-located. Does it make sense to spend the $$$'s and and management time to go HSL and IASP, or just spend the bucks and do GIG ethernet between them and just use FTP or DDM. I am hoping someone can shed some light for me on this. I think the HSL route to be cost prohibitive. The other downside to IASP from what I can find as well is that I cannot have a similiar library with the same name on the same box in the other IASP. I cannot have a QGPL in ASP1 and a QGPL in ASP2. I hope I have provided enough info that someone could give me a pretty good answer. Lastly if the answer is GIG ethernet, how can I do this so that the routing doesn't travel to far and can act just like a VLAN between 2 LPAR's within the same machine type approach using 192.X.X.X.
  • 3. Create own command
    Hi, we use V5R2M0. I want to create an own command with two or three parameters. It should only show the third parameter, if in parameter two is the Value "Y". If i use the command at the command-line with F4, it shows the two parameters. Only if i put Y in the second parameter, it shows the third parameter. Its a long time ago, that i create an command, but i think there is a solution. Best regards. Christian
  • 4. problem using jdbc for oracle
    Dear All, I connecting my java program in as400 to a oracle machine on network using oracle thin driver. I have jdk 1.3 installed on as400. i have copied classes12.jar file in '/QIBM/ProdData/Java400/jdk13/' directory and executed crtjvapgm on this jar file. But when i execute RUNJVA CLASS('salman') CLASSPATH('/QIBM/ProdData/Java400/jdk13/classes12.jar') i got this msg in java shell display java.lang.NoClassDefFoundError: oracle/jdbc/driver/OracleDriver java/lang/Throwable.<init>(Ljava/lang/String;)V+4 (Throwable.java:90) salman.main([Ljava/lang/String;)V+63 (salman.java:35) Java program completed with exit code 1 I have also added CLASSPATH through wrkenvvar Please suggest what can be problem here. Thank Salman
  • 5. pointer to inputbuffer.
    is it posible to set a pointer in ilerpg to a input or output buffer of a printer/db file? I know it is posible in ilec++ thanx

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