All Forums Database
06 Jul 2011
Help with CONTINUE HANDLER!!

Hi all.
I have a process that reads a table where the user defines wich stats should run each day of the week.
Once I have the list of stats to run in a table, I use a Sp to read each record, register the start of the collect stats in a log table, run the collect sentence using dbc.sysexecsql (the collect sentence is built into a field of the table).

My problem is, if the user defines a stat for a field that does not exists in a table. In this case the sysexecsql will fail and the sp will stop. But I need it to run for every stat defined, and if it fails just write the error.

According to what i have read i can use a CONTINUE HANDLER so that the Sp wont fail if the field orthe table does not exists or more general if the collect stats fails.

What im trying to do but im not sure ofthe syntax is this. This is working beacuse the DECLARE HANDLER line is commented, but as soon as I uncomment it it fails.

Thanks for any help in advance....

BEGIN
FOR C11 AS C_Stats CURSOR FOR
SELECT IdEstadistica, FechaIni, Qry
FROM DWH_STAGE.STATS_GRUPO_1
ORDER BY orden, IDESTADISTICA

DO

UPDATE DWH_LOG.LOG_STATS_TD
SET Fecha_Hora_Inicio = CURRENT_TIMESTAMP(0),
Estado = 'Running'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Inicio IS NULL;

INS DWH_STAGE.SP_STATS_MSG ('RUNNING',);

--DECLARE CONTINUE HANDLER FOR NOT FOUND

CALL dbc.sysexecsql (C11.Qry);

INS DWH_STAGE.SP_STATS_MSG (C11.Qry,);

IF SQLSTATE <> '00000' THEN
UPDATE DWH_LOG.LOG_STATS_TD
SET Estado = 'Error'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Fin IS NULL;

INS DWH_STAGE.SP_STATS_MSG ('FALLA',);

ELSE
UPDATE DWH_LOG.LOG_STATS_TD
SET Fecha_Hora_Fin = CURRENT_TIMESTAMP(0),
Estado = 'Finished Ok'
WHERE IdEstadistica = C11.IdEstadistica
AND Fecha_Inicio = C11.FechaIni
AND Fecha_Hora_Fin IS NULL
AND Fecha_Hora_Inicio IS NOT NULL;

INS DWH_STAGE.SP_STATS_MSG ('OK',);
END IF;

END FOR;

END;

dnoeth 4628 posts Joined 11/04
07 Jul 2011

A handler must be declared at the beginning of a "compound statement" after any variables and before any SQL statement:

BEGIN
DECLARE variables ...
DECLARE HANDLER ...
FOR ...
END;

Btw, a NOT FOUND handler is used for a select returning no rows.
But if you try to COLLECT STATS on a non-existing column you should check for a SQLSTATE 'T5628'.

Dieter

Dieter

07 Jul 2011

Dieter thank you for your quick response.
I changed the handler to DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628', and put it an the begining of the SP, but now when the SP is called, it wont do anything (compiled ok tough).
Any last recomendation? i feel so close so far...

REPLACE PROCEDURE DBA.SP_STATS_GRUPO2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
BEGIN
FOR C11 AS C_Stats CURSOR FOR
SELECT IdEstadistica, FechaIni, Qry
FROM DWH_STAGE.STATS_GRUPO_1
ORDER BY orden, IDESTADISTICA
....
.
CALL DBC.sysexecsql(....
..

dnoeth 4628 posts Joined 11/04
07 Jul 2011

Hi Mauricio,
if this is your source code, then the cursor is part of the error handling action which isonly executed when there's an error :-)

REPLACE PROCEDURE DBA.SP_STATS_GRUPO2()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE 'T5628'
BEGIN
-- error handlig code
END;
FOR C11 AS C_Stats CURSOR FOR
...

Dieter

Dieter

teradatauser2 236 posts Joined 04/12
16 Mar 2015

Hi Diether,
I need some help in error handing in SP when using cursors as you mentioned in this post. Below is my current code that workds fine.

REPLACE PROCEDURE mysp (
       IN  SRC_DB_NM              VARCHAR(30)
      ,IN  SRC_TBL_NM               VARCHAR(30)
      ,OUT MESSAGE         VARCHAR(200)
)
DYNAMIC RESULT SETS 1
BEGIN 
DECLARE query VARCHAR(200);
DECLARE rec_count             INTEGER DEFAULT 0;
DECLARE Status                    CHAR(5)         DEFAULT '00000';  
DECLARE C1 CURSOR FOR S1; 
     BEGIN			
			SET QUERY = 'SELECT count(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';			 
			PREPARE S1 FROM query;
			OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
			FETCH c1 INTO rec_count;
			set MESSAGE = rec_count;
END ;
END;

I get an error maesage with sql code - 2652 that table is being loaded which is correct. I want to handle the return codes of this sql statement and populate the OUT message with different messages based on this and also leave the SP. But, how do we handle the sqlwarning/sqlexception when using cursors in SP. The normal process of handling this using :

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
      SET Status = SQLSTATE;
    END;

doesn't work with Cursors. I did check the TD SLQ manuals, but didnlt get a proper details/example. I have posted a question yesterday in this forum, but did get any response yet. you could refer to it for details of my requirement.
http://forums.teradata.com/forum/database/using-dynamic-databasename-and-tablename-in-stored-procedure

teradatauser2 236 posts Joined 04/12
17 Mar 2015
REPLACE PROCEDURE dba_dwa.mysp (
       IN  SRC_DB_NM              VARCHAR(30)
      ,IN  SRC_TBL_NM               VARCHAR(30)
      ,OUT MESSAGE         VARCHAR(200)
)
DYNAMIC RESULT SETS 1
BEGIN 
DECLARE query VARCHAR(200);
DECLARE rec_count             INTEGER DEFAULT 0;
DECLARE Status                    CHAR(5)         DEFAULT '00000';  

DECLARE CONTINUE HANDLER FOR SQLSTATE '2652'
BEGIN
set message = 'in here';
END;

DECLARE  C1 CURSOR FOR S1; 
     BEGIN			
			SET QUERY = 'SELECT count(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';			 
			PREPARE S1 FROM query;
			OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
			if (sqlcode<>0) then
					set MESSAGE = 'error';
			end if;
			FETCH c1 INTO rec_count;
			set MESSAGE = rec_count;
END ;
END;

I think i am close, but missing the link somewhere. 
if (sqlcode<>0) doesn't handle this as i get this exceoption in ''Open'' stmnt.  When giving DECLARE CONTINUE HANDLER, it gives me error in DECLARE  C1 CURSOR FOR S1. As Diether mentioned above, i want to handle exception 2635 for this open/fetch of the cursor. I dont want to execute this cursor when this exception occurs.

Rohan_Sawant 55 posts Joined 07/14
17 Mar 2015

Hi teradatauser2,

 

The below code will do the required. You can now set a MESSAGE according to the SQLCODE you get using IF.. ELSE.

 

REPLACE PROCEDURE MYSP
(
	IN SRC_DB_NM VARCHAR(30)
,	IN SRC_TBL_NM VARCHAR(30)
,	OUT MESSAGE VARCHAR(200)
)
DYNAMIC RESULT SETS 1

BEGIN

	DECLARE QUERY VARCHAR(200);
	DECLARE REC_COUNT INTEGER DEFAULT 0;
	DECLARE STATUS CHAR(10) DEFAULT '00000';  
	DECLARE C1 CURSOR FOR S1; 
	
	DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
	BEGIN
		SET STATUS = SQLCODE;
		IF(TRIM(STATUS)) = '3807' THEN
			SET MESSAGE = 'PASSED TABLE '||SRC_DB_NM||'.'||SRC_TBL_NM||' DOES NOT EXIST';
		ELSE
			SET MESSAGE = 'ERROR';
		END IF;
	END;

		BEGIN         
			SET QUERY = 'SELECT COUNT(*) FROM ' || SRC_DB_NM || '.' || SRC_TBL_NM || ';';            
			PREPARE S1 FROM QUERY;
			OPEN C1 USING SRC_DB_NM,SRC_TBL_NM;
			FETCH C1 INTO REC_COUNT;
			SET MESSAGE = REC_COUNT;
		END;
		
END;

 

Thanks,
Rohan Sawant

teradatauser2 236 posts Joined 04/12
17 Mar 2015

Hi Roahan,
I believe, i have replied on your suggestion in my other post. As you mentioned, waiting for your reply on this as it doesn't seem to be working.
Thanks !
Samir

You must sign in to leave a comment.