Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    17

    Unanswered: Procedure stuck in a loop

    Can someone help me to identify the problem in this procedure:

    Code:
    CREATE PROCEDURE Proc_Test
                    (OUT p_Cnt VARCHAR(4),
                    OUT p_SQLSTATE VARCHAR(100)
                    )
                    LANGUAGE SQL
    BEGIN
     
        DECLARE V_DYNAMIC VARCHAR(100) ;                     -- (2)
        DECLARE v_Tsql VARCHAR(100) ; 
        DECLARE v_Tid VARCHAR(100) ; 
        DECLARE SQLSTATE CHAR(5) DEFAULT '00000';    
        DECLARE v_SQLSTATE CHAR(5) DEFAULT '00000';                 -- (2)
        DECLARE cursor1 CURSOR with hold FOR
    		 SELECT ID,SQLTEXT FROM  test;
    		 
        DECLARE CONTINUE HANDLER FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
        SET p_SQLSTATE = SQLSTATE;
       
    	DECLARE GLOBAL TEMPORARY TABLE Rslt (
    	 sql_id varchar(100), 
    	 cnt Varchar(4),
    	 sql_state varchar(6)
    	 )
    	 on commit
    	 preserve rows 
    	 not logged ;
    
    	Open cursor1;
    	A:
    	Loop
    	    fetch cursor1 into v_Tid, v_Tsql;
    	
    	        
    	    insert into session.Rslt values(v_Tsql, v_Tid, '0');
    	    commit;
    	    
    	    call sysproc.admin_cmd('export to c:\test\test.csv of del select * from session.Rslt') ;
    	        
        end loop A;
        
        close cursor1;
        
        drop table session.Rslt;
    END
    All I am trying is to load the cursor date into a global temp table and use an export to get the result to a file. Any help is appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You created a loop with no end.

    Why not just throw out the cursor and the temp table and just do the export straight from the table being read in the cursor?

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    17
    Andy,

    Thats what I started with. Basically I need to accomplish the following:

    - Read a table that has a ID and a SQL text
    - Execute the SQL
    - put the ID and the SQL result count into a file.

    I was trying to build it incrementally.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Your other thread on this has better info. If I were you, I would abandon trying a Stored Procedure and just use some high level language like Java.

    Andy

Posting Permissions

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