Archives of the TeradataForum
Message Posted: Wed, 12 Mar 2008 @ 10:31:26 GMT
"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:
BEGIN DECLARE EXIT HANDLER /* or a continue handler, dependig on the approach */ FOR SQLEXCEPTION BEGIN >>>>>your code to check the :SQLSTATE variable and create table if required END; >>>>>your other code to do DML (please keep in mind the "retry" situation if table needed to be created) END;
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|