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)

Thanks guys. I took care of it after I got the result back and it worked great. JMarkMurphy, Player1 answered first, I thought it would only be fair to mark is answer. Sorry. I appreciate you taking the time to answer my question once again. — Tretiak
– Tretiak, Commented Aug 10, 2018 at 18:59

jmarkmurphy · Accepted Answer · 2018-08-10 18:06:44Z

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.

answered Aug 10, 2018 at 18:06

jmarkmurphy

11.6k37 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charles Over a year ago

You might be able to use SQLERR(2) or GET DIAGNOSTICS as mentioned in my answer to your other question

jmarkmurphy Over a year ago

But those numbers are only estimates when the open occurs, and in my experience are not very accurate.

Player1st · Accepted Answer · 2018-08-10 18:06:50Z

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.

edited Aug 10, 2018 at 18:06

answered Aug 10, 2018 at 17:51

Player1st

1,60511 silver badges14 bronze badges

6 Comments

Tretiak Over a year ago

I am not sure what you mean with the external procedure. I am calling from the web in Lotus Notes. Not sure how to do what you are suggesting.

Tretiak Over a year ago

I was using SQLSTATE before. I just went and tried that. and it always says '00000' even when I know there is no record in the search. IF SQLSTATE = '00000'; RSL = '00'; ELSEIF SQLSTATE = '02000'; RSL = '10'; ELSE; RSL = '20'; ENDIF;

Tretiak Over a year ago

I think I understand what you are saying here with regards to the SQLSTATE. When I get the result back in my call from the web, if the result is empty/null whatever, then at that point do what I have to specify there is no records. correct?

Player1st Over a year ago

As is expected. The line EXEC SQL SET RESULT SETS Cursor RSCURSOR' has no reason to return a SQLSTATE other than 0. If you want to know how many rows were returned, you can either attempt to read from the cursor or you can get an estimate after you open the cursor from SQLERRD(3).

Player1st Over a year ago

Yes. Taking action after you get the results back is a perfectly feasible solution in this case. If you want to do in on the server side, you can use the the suggestion in my last comment as well.

|

Collectives™ on Stack Overflow

DB2 SQL - Issue with SQLCODE

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related