Sorry this is an old post. But with lot of improvements
My actual req is this:
there is a table that stored a SQL and each one has an ID
I need to run each SQL iteratively and see how many records it gives ((like a count *)
if the SQL fails, then also trap the error code using SQLSTATE or SQLCODE
The final file that I write should have the following - ID, SQL, Count, SQLSTATE
Here is what I did finally. Seems to work. But I am getting the same code '00000' for all statements even if there is an issue with the SQL.
drop procedure Proc_Test
CREATE PROCEDURE Proc_Test
(OUT p_Cnt integer,
OUT p_SQLSTATE VARCHAR(100))
DECLARE V_DYNAMIC VARCHAR(100) ;
DECLARE v_Tsql VARCHAR(2048) ;
DECLARE v_Tid integer ;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_SQLCODE INT DEFAULT 0;
DECLARE v_exitcode integer ;
DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';
declare CURSOR2 cursor with return for V_DYNAMIC;
DECLARE cursor1 CURSOR with hold FOR
SELECT ID,SQLtext FROM test where id>=1;--
DECLARE continue HANDLER FOR NOT FOUND
SET v_exitcode = 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
set v_SQLCODE = SQLCODE;
DECLARE GLOBAL TEMPORARY TABLE session.Rslt (
preserve rows ;
fetch cursor1 into v_Tid, v_Tsql;
PREPARE V_DYNAMIC from v_Tsql;
fetch cursor2 into p_Cnt;
insert into session.Rslt values(v_Tsql, v_Tid, p_Cnt, p_SQLSTATE);
UNTIL v_exitcode = 1
end REPEAT ;
call sysproc.admin_cmd('export to c:\test\test.csv of del select * from session.Rslt') ;
drop table session.Rslt;
But I am getting the same code '00000' for all statements even if there is an issue with the SQL.
My guess was...
(1) You declared CONTINUE HANDLER FOR SQLEXCEPTION
(2) If there was an error in PREPARE statement,
"set v_SQLCODE = SQLCODE;" would be executed.
(3) If the set statement was successful(most likely),
then SQLCODE set to 0.
(4) The value in (3) would be the value of SQLCODE in "SET p_SQLSTATE=SQLCODE;" statement after the PREPARE statement in (2).
If my guess was right, try the following...
(a) Remove "SET p_SQLSTATE=SQLCODE;" after the PREPARE statement.
(b) Add "set v_SQLCODE = 0;" just before the PREPARE statement.
(c) Use v_SQLCODE instead of p_SQLSTATE in the insert statement.
(d) If you want to catch SQLWARNING, add handler for that condition.
Last edited by tonkuma; 04-04-12 at 22:37.
Reason: Add (a), (b) and (d).