Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 10:31:26 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

Subj:   Re: Stored Procedure Error Handling
From:   Stieger, Etienne E

Hi David,

"NOT FOUND" is used for when data is not found (related to DML transactions). I might be wrong (haven't checked) but this probably does not cover SQLSTATE codes for objects that do not exist.

Perhaps you could follow one of 2 approaches:

1) First Select from dbc.tables to see if table exists, before doing the DML.

2) Check the specific :SQLSTATE variable from within a more generic exception handler such as "SQLEXCEPTION"

Also, use a proper "BEGIN .... END" construct for the specific code segment:

          DECLARE EXIT HANDLER /* or a continue handler, dependig on
          the approach */
              >>>>>your code to check the :SQLSTATE variable and
                   create table if required
              >>>>>your other code to do DML (please keep in mind
                   the "retry" situation if table needed to be created)

Due to the issues with retrying the DML operation when the table actually had to be created, the approach of first checking dbc.tables is probably the more elegant way of doing this.

!!!EVEN BETTER!!!: Try and create the table irrespective of whether it exists or not (and handle any failure of the create attempt). Then run the DML. This is a much more robust approach from a batch processing point of view. But DO check the value of :SQLSTATE to ensure you know what you are dealing with.

Kind regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019