db2ibm-midrangerpglerpgrational-developer-for-i

How to query a table in Fixed format RPG and check record with if-condition?


So, say a table ##TAB has one column #VALUE which contains 'SDM' & 'LB' as entries. The column size is 12 characters. I want to query the table using Fixed-Format RPG and compare it later in IF condition. The logic is simple but I'm not sure about the syntax.

If it was a free format, I could simply get the records into a cursor, fetch data in a loop and leave as I get the matching record. But, I'm not sure if we can use SQL in Fixed-format or I'll have to do a chain (still don't know the syntax).

 exec sql                                      
    Declare C1 cursor for                      
      Select #VALUE from ##TAB;         
                                                
  exec sql                                      
    Open C1;                                    
                                                
  Dou sqlcod <> 0;                              
  exec sql                                      
    Fetch C1 INTO :valueRes;                      
                           
    If (valueRes <> userVal); // userVal is what we get from user     
      Leave; //or set some flag to 0 to indicate               
    Endif;                
         
  Enddo;                  
                           
  exec sql                
    Close C1; 

OR We can do it through a subroutine and return true/false or 1/0 to indicate if the record is found.

While posting the answer, please include variable declarations as well.


Solution

  • You can do EXEC SQL in fixed format. Put C/EXEC SQL first (starting in column 6), then put C+ for all the continuation lines, then end with C/END-EXEC. And there's no semicolon at the end.

     C/exec sql
     C+  Declare C1 cursor for
     C+  Select #VALUE from ##TAB
     C/end-exec