Archives of the TeradataForum

Message Posted: Mon, 18 Apr 2011 @ 13:55:02 GMT

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

Hi guys,

Within Bteq I call a Stored Procedure, which I'm trying to get to fail with some sort of error. When I get and SqlException, I'll write a row into a GTT and then check within the Bteq to see if a row then exists. If it does then I'll fail the Bteq at that point.

Thing is, the Exception Handler is not being picked up, for some reason.

In my code I've got this piece of Code :

     ----------------------------------------------------------------
     ------  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
     BEGIN
         SET ResultCode = 8;
         SET ResultMsg = 'Error at ' || sLSTEP_CD ||' : SQL Exception,
             SQL State = '|| SQLSTATE|| ', SQL Code = '|| SQLCODE;
             -- write and error row into a Global Temporary Table
         SET sLSQLSTATEMENT_TX =  'INS INTO '|| TRIM(sLEnv)||TRIM(sPUtil_DB)
             || '.MOVE_ERR_TABLE_STATUS VALUES
             ('||''''||TRIM(sPUtil_DB)||''''||','||''''||TRIM(sPTableName)
             ||''''||','||SQL CODE||',CURRENT_TIMESTAMP(0) )';
         CALL DBC.SysExecSQL(:sLSQLSTATEMENT_TX);
         SET LastSqlMsg = sLSQLSTATEMENT_TX;
     END;
     .
     .
     .
     .
     SET sLSQLSTATEMENT_TX =  'LOCKING TABLE ' || TRIM(sLErrorTable) || ' FOR
     ACCESS  ' || ' CREATE TABLE ' || TRIM(sLNewTable) || 'X AS ' ||
     TRIM(sLErrorTable) ||' WITH DATA'  ;
     --
     CALL DBC.SysExecSQL(:sLSQLSTATEMENT_TX);

but it's failing to pick up this at the start of the Stored Procedure :

This is then what I get from the Bteq :

     +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     CALL DEVDBA_X.MOVE_ERR_TABLE_P('SCADMIN_U', 'SCRHHT01_ERR1', 'IBU', ResultCode, ResultMsg, SqlMsg);

     CALL DEVDBA_X.MOVE_ERR_TABLE_P('SCADMIN_U', 'SCRHHT01_ERR1', 'IBU', ResultCode, ResultMsg, SqlMsg);
     $
      *** Failure 3737  Name is longer than 30 characters.
      *** Total elapsed time was 1 second.


     +---------+---------+---------+---------+---------+---------+---------+----
     Select Count(*) From DEVSCADMIN_U.MOVE_ERR_TABLE_STATUS;

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

        Count(*)
     -----------
               0
     +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

As you can see, the Table or errors has zero rows, but I would expect this to be 1, since the Stored Procedure detects 3737 error.

Am I doing something wrong here ?

Regards

David Clough
Database Developer
Database Design Group

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