Handler for SQLException does not execute on SQL exception

Database
Teradata Employee

Handler for SQLException does not execute on SQL exception

Hi all,

I am new to teradata. I am using a procedure to do some DMLs. I am using DBC.SysExecSQL to execute dynamic DMLs inside the procedure. I have used a HANDLER FOR SQLException and i am logging all the exception statements (i-e SQLState and SQLCode) in a log table. But when i encounter an error, while doing a merge, "5747 - Target row updated by multiple source rows.", exception handler does not trigger for this SQL exception and i find this error statement on the status bar.

Kindly provide me a solution, that how can i catch this exception using Exception Handler.

Thanks in advance.

8 REPLIES
Senior Supporter

Re: Handler for SQLException does not execute on SQL exception

How about sharing your code?

Teradata Employee

Re: Handler for SQLException does not execute on SQL exception

Hi Ulrich,

Due to some restriction i can not share the whole procedure, i am sharing the exception handling part of the procedure.

DECLARE MySQLCode INTEGER;

DECLARE MySQLState CHAR(5);

DECLARE MyErrorMsg VARCHAR(1024) DEFAULT '';

DECLARE CurrentStep SMALLINT;

DECLARE START_TIME TIMESTAMP;

/*******************************************************************************************************************

Error Handler

*******************************************************************************************************************/

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

SET MySQLState=SQLSTATE;

SET MySQLCode=SQLCODE;

/* Resolve the Error message, if the variable is empty (otherwise we assume it's a USER-raised ERROR)*/

IF MyErrorMsg=''

THEN

SELECT ErrorText INTO MyErrorMsg  FROM dbc.errormsgs WHERE ErrorCode=MySQLCode;

END IF;

/* Log the error message into the Log Table     */                     

SET v_LogStr = 'INSERT INTO My_DB.SP_LOG(

LOG_TYPE,

TARGET_TABLE ,

STEP_NUM  ,

STEP_DESC,

TM_START ,

STEP_START,

SQL_REQUEST_TEXT,

SQL_STATE,

SQL_CODE,

NM_ACTIVITY_COUNT,

STEP_END,

LOAD_ID

)

VALUES(

''MERGE'', 

'''|| P_TGT_TABLE ||''', 

9999, 

''ERROR'', 

CURRENT_TIMESTAMP, 

CURRENT_TIMESTAMP,

'''||MyErrorMsg||''', 

'||MySQLSTATE||', 

'||MySQLCODE||', 

NULL,

CURRENT_TIMESTAMP, 

'||P_LOAD_ID|| ');';       

CALL DBC.SysExecSQL(v_LogStr);

SET OUT_PUT = 1;

END;

--End Error Handling

NOTE:

where P_TGT_TABLE, P_LOAD_ID are input parameters


Teradata Employee

Re: Handler for SQLException does not execute on SQL exception

Hi,

This very likely means that the code you are executing in the exception handler is failing at some point, before it reaches the END. This makes the procedure returning passing the "original" error message. Manually check if the insert that is dynamically executed works...

I can remember writing this exception handler three or four years ago by the way ;-)... This is not related to the "type" of error message, I am quite sure I had this scenario too.

//Remi

Senior Supporter

Re: Handler for SQLException does not execute on SQL exception

Remi was right and this is ugly to debug :-)

The SQLSTATE is a char so you need to give some more ''...

with

'''||MySQLSTATE||''',

it should do.

Teradata Employee

Re: Handler for SQLException does not execute on SQL exception

Ulrich,

This Exception is catching other SQL exceptions, but not this specific exception. "5747 - Target row updated by multiple source rows". 

In all other exceptions both '||MySQLSTATE||', '||MySQLCODE||' are working fine, i dont have to put extra quotes, which  u suggested.

Thanks in advance

Senior Supporter

Re: Handler for SQLException does not execute on SQL exception

you might consider the documentation:

Structure of SQLSTATE

SQLSTATE is a five-character string value divided logically into a two-character class and a

three-character subclass. “SQLSTATE Class Definitions” on page 453 lists the ANSI SQL:2008-

defined SQLSTATE classes.

so it is a charcter and would need the extra ' if you really get chars.

If it is not working you would need to debug further. What I did was write the generated SQL into a table. Retrieve the SQL and check the message you get if you try to execute.

Senior Supporter

Re: Handler for SQLException does not execute on SQL exception

I get T7547 as SQLCODE...

CREATE SET TABLE SP_LOG 
(
LOG_TYPE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TARGET_TABLE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
STEP_NUM INTEGER,
STEP_DESC VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
TM_START TIMESTAMP(0),
STEP_START TIMESTAMP(0),
SQL_REQUEST_TEXT VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
SQL_STATE VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
SQL_CODE INTEGER,
NM_ACTIVITY_COUNT INTEGER,
STEP_END TIMESTAMP(0),
LOAD_ID INTEGER)
PRIMARY INDEX ( STEP_START );

drop table test_a;
drop table test_b;

create table test_a
( a integer, b integer) primary index (a);

create table test_b
( a integer, b integer) primary index (a);

insert into test_a values (1,1);
insert into test_b values (1,1);
insert into test_b values (1,2);

REPLACE PROCEDURE "TEST_DB_ULI"."p_h_test" (
IN "P_TGT_TABLE" VARCHAR(30) CHARACTER SET LATIN,
IN "P_LOAD_ID" INTEGER
)

BEGIN

DECLARE MySQLCode INTEGER;
DECLARE MySQLState CHAR(5);
DECLARE MyErrorMsg VARCHAR(1024) DEFAULT '';
DECLARE CurrentStep SMALLINT;
DECLARE START_TIME TIMESTAMP;
DECLARE v_LogStr VARCHAR(1000);
DECLARE out_put INTEGER ;

/***************************************************** ****************************************************** ********
Error Handler

****************************************************** ****************************************************** *******/
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN

SET MySQLState=SQLSTATE;
SET MySQLCode=SQLCODE;
SET MyErrorMsg='';
/* Resolve the Error message, if the variable is empty (otherwise we assume it's a USER-raised ERROR)*/

IF MyErrorMsg=''
THEN
SELECT ErrorText INTO MyErrorMsg FROM dbc.errormsgs WHERE ERRORCODE=MySQLCode;
END IF;

/* Log the error message into the Log Table */
SET v_LogStr = 'INSERT INTO SP_LOG(
LOG_TYPE,
TARGET_TABLE ,
STEP_NUM ,
STEP_DESC,
TM_START ,
STEP_START,
SQL_REQUEST_TEXT,
SQL_STATE,
SQL_CODE,
NM_ACTIVITY_COUNT,
STEP_END,
LOAD_ID
)
VALUES(
''MERGE'',
'''|| P_TGT_TABLE ||''',
9999,
''ERROR'',
CURRENT_TIMESTAMP(0),
CURRENT_TIMESTAMP(0),
'''||MyErrorMsg||''',
'''||MySQLSTATE||''',
'||MySQLCODE||',
NULL,
CURRENT_TIMESTAMP(0),
'||P_LOAD_ID|| ');';

CALL DBC.SysExecSQL(:v_LogStr);

SET OUT_PUT = 1;

END;

update a
from test_a a,
test_b b
set b = b.b
where a.a = b.a
;

END;

delete
from SP_LOG;
call p_h_test('dd',1);
select *
from SP_LOG;
Teradata Employee

Re: Handler for SQLException does not execute on SQL exception

Hi Ulrich and Remi,

Thanks a lot for your inputs.

Putting quotes to MySQLSTATE really worked. 

Thanks a lot once again for your guidance and taking some time to solve my problem.

May Allah bless you both.