DB2 SQL - Issue with SQLCODE

Question

When I do a search on my textbox on the web, if there is no result, I want to notify the user. I am trying to use SQLCODE reserve field and it is not working. It is always set to 000 when I run the debugger. I get no error message.

I did some research and I believe you need to add the reserve field to the stored procedure. Which I did. I am not sure what I am doing wrong.

Stored procedure:

drop   procedure BPCSO/TEST_MB                                                                      
create procedure BPCSO/TEST_MB                                                                      
(                                                                                                   
  InOut DEC(3,0),                                                                                   
  InOut DEC(3,0),                                                                                   
  In    CHAR(30),                                                                                   
  In    DEC(3,0),                                                                                   
  Out   CHAR(02)                                                                                    
)                                                                                                   
language rpgle                                                                                      
parameter style general                                                                             
not deterministic                                                                                   
READS SQL DATA                                                                                      
result sets 1                                                                                       
external name 'BPCSO/PUR027WS(PUR027)'    

Module:

**FREE                                                                                              
// RFC Main Grid                                                                                    

CTL-OPT NOMAIN OPTION (*SRCSTMT : *NODEBUGIO);                                                      

DCL-PROC PUR027 EXPORT;                                                                             
  DCL-PI PUR027 EXTPROC(*DCLCASE);                                                                  
    StartingRow PACKED(3:0);                                                                        
    NbrOfRows PACKED(3:0);                                                                          
    Search CHAR(30);                                                                                
    SQLCODE PACKED(3:0);                                                                            
    RSL CHAR(2);                                                                                    
  END-PI;                                                                                           


IF Search = '';                                                                                     

  EXEC SQL Declare RSCURSOR cursor for                                                              
  SELECT *                                                                                          
  FROM CDPL03                                                                                       
  ORDER BY CDEPT, CDESC                                                                             
  OFFSET (:StartingRow - 1) * :NbrOfRows ROWS                                                       
  FETCH NEXT :NbrOfRows ROWS ONLY;                                                                  

  EXEC SQL  Open RSCURSOR;                                                                          

  EXEC SQL SET RESULT SETS Cursor RSCURSOR;                                                         

ELSE;                                                                                               

  EXEC SQL Declare RSCURSOR2 cursor for                                                             
  SELECT *                                                                                          
  FROM CDPL03                                                                                       
  WHERE CDESC LIKE '%' concat trim(:Search) concat '%' AND                                          
        CDEPT LIKE '%' concat trim(:Search) concat '%'                                              
  ORDER BY CDESC, CDEPT                                                                             
  OFFSET (:StartingRow - 1) * :NbrOfRows ROWS                                                       
  FETCH NEXT :NbrOfRows ROWS ONLY;                                                                  

  EXEC SQL  Open RSCURSOR2;                                                                         

  EXEC SQL SET RESULT SETS Cursor RSCURSOR2;                                                        

ENDIF;                                                                                              


// Validate for SQL errors                                                                          
IF SQLCODE  = 0;                                                                                    
  RSL = '00';                                                                                       
ELSEIF SQLCODE > 0;                                                                                 
  RSL = '10';                                                                                       
ELSEIF SQLCODE < 0;                                                                                 
  RSL = '20';                                                                                       
ENDIF;                                                                                              



//EXEC SQL SELECT COUNT(*)                                                                          
//  INTO :RowCount                                                                                  
//  FROM CDPL03;                                                                                    


RETURN;                                                                                             

END-PROC PUR027;                                                                                    


// To create the service program:                                                                   
//           CRTSRVPGM SRVPGM(BPCSO/PUR027WS)                                                       
//            MODULE(BPCSO/PUR027W)                                                                 
//            SRCFILE(BPCSS/PURBNDF) SRCMBR(PUR027WB)                                               

Answer 1

I don't see any reason SQLCODE should have been set based on the example given. The following lines should succeed even if no results are returned. You wouldn't notice the lack of results until you look at the result sets.

EXEC SQL SET RESULT SETS Cursor RSCURSOR;
...
EXEC SQL SET RESULT SETS Cursor RSCURSOR2;

SQLCODE is set on every SQL statement call so even if the prior SQL statements failed you wouldn't notice here. You have to check the SQLCODE after every SQL statement to notice if anything failed on any of them.

As some additional improvement tips, you should be using SQLSTATE instead of SQLCODE. SQLSTATE is the newest standard amongst all the SQL databases and SQLCODE is deprecated everywhere although the IBM i currently has no plans to actually remove it.

Also, there doesn't seem to be any reason to make this an external procedure rather than a standard SQL stored procedure. That seems like an unnecessary wrapper around this considering all of the logic you have is done in SQL anyway. More information can be found here and here.

Answer 2

You are not going to get a no records found until you try to read the result set. The only statements you have in your code that will set SQLCODE or SQLSTATE are the OPEN and the SET RESULT SETS. Neither of these will tell you that there are no records read. You have to actually try to read from the result set to get that notification. The OPEN will fail if SQL can't open the cursor, and the SET RESULT SETS will fail if there is no open cursor, but those are the only failures you are likely to receive.