Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Unanswered: SQL/PL FETCH empty result but doesn't throw "not found"

    In db2 procedure I have a bug that when I fetch row, if the result set is empty - it will still do the first loop although that it should throw "not found" exception.
    I guess I'm missing something.

    An example:

    Code:
    CREATE OR REPLACE PROCEDURE TEST()
      LANGUAGE SQL
    BEGIN
    	DECLARE EOF INT DEFAULT 0;
    	DECLARE STMT VARCHAR(200);
    	DECLARE STMT_2 VARCHAR(2000);
    	DECLARE R_TAB VARCHAR(200);
    	DECLARE SQLCODE, V_SQLCODE INT DEFAULT 0;
    	DECLARE SQLSTATE, V_SQLSTATE CHAR(5) DEFAULT '00000';
    	
    	DECLARE C_TABS CURSOR FOR S1;
    	
    	DECLARE CONTINUE HANDLER FOR NOT FOUND
    		SET EOF = 1;
    
    	set EOF=0;
    	
    	SET STMT='select tabname from syscat.tables where 1=2 for read only';
    	PREPARE S1 FROM STMT;
    	OPEN C_TABS;
    	WHILE EOF = 0 DO
    		FETCH FROM C_TABS INTO R_TAB;
    		
                    -- ** == it should not reach here since the SQL return 0 rows, but it does.   == ** --
    
    		insert into t_create_views_log(table_name, error, create_date) 
    		values ('test' ,'SQLCODE: '|| V_SQLCODE || ', SQLSTATE: ' || V_SQLSTATE,current date);
    		
    	END WHILE;
    	CLOSE C_TABS;
    END

  2. #2
    Join Date
    Feb 2016
    Posts
    6

    try executing the below code

    HTML Code:
    BEGIN
    DECLARE EOF INTEGER DEFAULT 0;
    
    
    	DECLARE STMT VARCHAR(200);
    	DECLARE STMT_2 VARCHAR(2000);
    	DECLARE R_TAB VARCHAR(200);
    	DECLARE SQLCODE, V_SQLCODE INT DEFAULT 0;
    	DECLARE SQLSTATE, V_SQLSTATE CHAR(5) DEFAULT '00000';
    	
    	DECLARE C_TABS CURSOR FOR 
      select tabname from syscat.tables where 1=2 for read only;
      
         DECLARE CONTINUE HANDLER FOR  NOT FOUND
         SET EOF = 1;
      
    	
    OPEN C_TABS;
    
    ins_loop:
    LOOP
    FETCH FROM C_TABS INTO R_TAB;
    
                 IF EOF = 1 THEN
           LEAVE ins_loop;
     ELSEIF EOF <> 1 THEN
    		insert into t_create_views_log(table_name, error, create_date) 
    		values ('test' ,'SQLCODE: '|| V_SQLCODE || ', SQLSTATE: ' || V_SQLSTATE,current date);
    
       END LOOP;
      CLOSE C_TABS WITH RELEASE;
      COMMIT;
       END;

  3. #3
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Rather obvious programming error. You have a CONTINUE for not found. When the first FETCH sets EOF=1 the code will continue to the next statement (the insert) because that's what you asked it do to. Easy to fix by a couple of different ways. Work it out or never learn anything.

  4. #4
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    But a 'CONTINUE' statement suppose to skip the rest of the code in the loop and continue to the next iteration, and since now EOD=1 - it should exit.
    The only way I get this wrong is if the 'CONTINUE' in SQL-PL is continuing from the same place, without go to the next iteration(eventually it will of course).

  5. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    Your original code shows a "continue handler" for NOT FOUND. The documented behaviour fpr SQL PL for a continue handler is the code-execution resumes at the statement following the one that raised the condition. In your case the statement raising the 'not found' is the FETCH, and the next statement is the INSERT.

  6. #6
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    So surrounding the code after the fetch with "if" statement will solve it.
    But it's stupid because "continue" should work differently :/

  7. #7
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    [QUOTE=AntiLopa;6641851...
    But it's stupid because "continue" should work differently :/[/QUOTE]
    Hmmm, is that a workman blaming a tool?
    You may be confusing the SQL PL concept of continue-handler with the behaviour of the SQL PL LEAVE statement.
    Get some education.

  8. #8
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    'Continue' is saved word that act the same on a lot of other program languages. Only IBM SQL PL works differently (which actually not surprising that IBM choose different direction), so yeah I blame them
    Or should I check how 'IF' and 'LOOP' acting as well?

    After taking out some steam, lets get back to business.

    LEAVE will work for me the same as "WHILE" loop and inside "IF", I'll still need an "IF" statement after the FETCH and I'll need to name each LOOP, e.g. IBM LEAVE.
    Is there some handler that cause the iteration of the loop to end? (Like "continue" in c/java/plsql/bash etc...)

  9. #9
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    The way that ANSI SQL PL condition-handlers work is defined by the SQL standard ( not by IBM, any RDBMS that is compliant with that standard will follow the same behaviour).
    There are continue-handlers and exit-handlers. The DB2-knowledge centre has all the details.
    As previously stated, there are several ways to handle such simple stuff.

Posting Permissions

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