JDBC - ODBC SQLState Conflict

General
Teradata Employee

JDBC - ODBC SQLState Conflict

Hi ,

We are seeing dissimilarities of returned SQLState between the SQLState returned while executing a stored procedure from SQL Assistant and when the same Stored procedure is called through Java/JDBC driver 13, the exception block in java based program shows the SQLState is different (rather it is always showing HY000) instead of what is returned from SQL Assistant that might be T5568.

 For your convenience to understand the stated problem, I am attaching the two stored procedures.

  1. Child Stored Procedure (the code in this stored procedure has been intentionally written to raise an SQL Exception)
  2. Parent Stored Procedure (the code in this Stored procedure is such that it calls the child stored procedure and catches the returned Exception in its Exception Handling block). In this parent Stored procedure the SQLState is captured and returned in output param to reveal the resulted SQLState. In this code example, the  SQLState returned = T5568

 Now when I call the same child stored procedure through java program, the exception is caught in Java code’s Exception handling block. When I print the SQLState through following code of java, then the SQLState is always HY000 (not the expected SQLSTATE), and that is not the SQLState that was being returned by calling same Stored procedure in SQL Assistant. 


catch (SQLException se) {


            System.out.println("*** SQLException caught ***");


            System.out.println(se.getClass().toString());


               while (se != null) {


                         System.out.println(" Error code: " + se.getErrorCode());


                         System.out.println(" SQL State: " + se.getSQLState());


                         System.out.println(" Message: " + se.getMessage());


                         se = se.getNextException();


               }

}

You may see the two Stored Procedures Code given below for your quick insight or in case you want to execute in your environment.

 Parent Procedure:

REPLACE PROCEDURE EDEV1P_PROC.CTLFW_Test_Procedure

(

OUT rowCount INTEGER

,OUT oRtn_Code SMALLINT

,OUT oRtn_Msg VARCHAR(100)

)

MAIN: BEGIN

 DECLARE v_SQL_STMT VARCHAR(4000) ;

DECLARE vSQL_Code INTEGER;

 /* Define Error Handler */

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

/* Preserve Diagnostic Codes from errored Statement*/

SET vSQL_Code = SQLCODE;

/* Set Error Return Code and Message */

SET oRtn_Code = 1 ; /* 0: Successful; 1: Error */

SET oRtn_Msg = 'Process Failed, SQLCODE=' || SQLCODE || ' SQLSTATE=' || SQLSTATE;

END;

 CALL EDEV1P_PROC.CTLFW_Test_Proc_Child(:oRtn_Code, :oRtn_Msg );

SET oRtn_Code = 0 ; /* 0: Successful; 1: Error */

SET oRtn_Msg = 'Process Succeeded, SQLCODE=' || SQLCODE;

END MAIN;

Child Procedure:

REPLACE PROCEDURE EDEV1P_PROC.Test_Procedure_Child(

OUT oRtn_Code SMALLINT

,OUT oRtn_Msg VARCHAR(100)

)

MAIN: BEGIN

 DECLARE v_SQL_STMT VARCHAR(4000) ;

DECLARE vSQL_Code INTEGER;

 SET v_SQL_STMT = 'SELECT COUNT(1) FROM EDEV1T_STG.T1_KYKMS;';

CALL DBC.SysExecSQL(v_SQL_STMT);

 SET oRtn_Code = 0 ; /* 0: Successful; 1: Error */

SET oRtn_Msg = 'Inner SP Process Succeeded, SQLCODE=' || SQLCODE;

 END MAIN;