Results 1 to 2 of 2

Thread: Procedure help

  1. #1
    Join Date
    Apr 2012
    Posts
    17

    Unanswered: Procedure help

    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.

    Code:
    drop procedure Proc_Test
     
    CREATE PROCEDURE Proc_Test
                    (OUT p_Cnt integer,
                    OUT p_SQLSTATE VARCHAR(100))
                    LANGUAGE SQL
     
    BEGIN
        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 (
        sql_text varchar(100),
        sql_id integer,
        cnt INTEGER,
        sql_state varchar(6)
        )
        with replace
        on commit
        preserve rows ; 
     
        Open cursor1;
        REPEAT
         fetch cursor1 into v_Tid, v_Tsql;    
         PREPARE V_DYNAMIC from v_Tsql;
         SET p_SQLSTATE=SQLCODE;
         
             open cursor2;                                   
             fetch cursor2 into p_Cnt;
              
              insert into session.Rslt values(v_Tsql, v_Tid,  p_Cnt, p_SQLSTATE);
            set p_cnt=0;
              close cursor2;
          UNTIL v_exitcode = 1
        end REPEAT ;
       close cursor1;
       call sysproc.admin_cmd('export to c:\test\test.csv of del select * from session.Rslt') ;
       drop table session.Rslt;
    END
    Any help is appreciated. Thanks.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 23:37. Reason: Add (a), (b) and (d).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •