SQL FETCH, Cursors and RPG

Question

I am not sure what is wrong with my code, I am trying to make a while loop that will fetch the next characters in the field (code) with a cursor that is declared in SQL. The goal is to get the next matching characters with the loop. Then I want return the results at the end of the loop. The goal is to make a partial match to the code, if there is no exact match. I have never used cursors before so I a trying to learn as much as I can about using fetch and cursors.

EXEC SQL
SELECT field FROM file
WHERE  field = :code

UNION

DECLARE UserInput CURSOR FOR
SELECT field FROM file
WHERE  field LIKE '%' || :code || '%'
ORDER BY field ASC

OPEN UserInput
FETCH NEXT FROM UserInput
BEGIN
   DO WHILE <> %EOF 
         FETCH NEXT FROM UserInput
END
CLOSE UserInput
DEALLOCATE UserInput;

score 9 · Accepted Answer · answered May 16 '16 at 21:13

Wow...lots wrong here... At a quick glance...

you're trying to declare your cursor in the wrong place.
%EOF() works with RPG record level access, you need to be checking SQLCODE or SQLSTATE
FETCH OPEN are all SQL statements, need to be in an EXEC SQL
DEALLOCATE is not needed
Need to FETCH the row from the cursor into an RPG variable

Take a look at this code:

EXEC SQL                                            
  DECLARE UserInput CURSOR FOR                      
    SELECT field FROM file                          
    WHERE  field = :code                            
    UNION                                           
    SELECT field FROM file                          
    WHERE  field LIKE '%' || :code || '%'           
    ORDER BY field ASC;                             
EXEC SQL                                            
  OPEN UserInput;                                   
--really should check SQLSTATE here too!            

EXEC SQL                                            
  FETCH NEXT FROM UserInput INTO :MyRpgVar;         

Dow SQLSTATE = '00000';                              
   --note 00000 = no errors or warning              
   --     02000 = no data                           
   <do somthing?>                                   
   EXEC SQL                                         
      FETCH NEXT FROM UserInput INTO :MyRpgVar;     
ENDDO;                                              

EXEC SQL                                            
  CLOSE UserInput;

I suggest you read the RPGLE section of the Embedded SQL Programming reference.

one more thing, assuming `CODE` and `FIELD` are a fix length character fields, you probably need `'%' || trim(:code) || '%'`. — Charles, May 17 '16 at 14:21
Though please do abandon the variant-characters, the _bar_ symbol "|", and replace the || as operator, with the CONCAT as operator. — CRPence, May 28 '16 at 16:14
Unlikely a result-set should include duplicates for any value meeting the equal predicate? Plus UNION is rather silly here. Just code an OR between the two predicates; depending on implied or actual N of optimize for N rows and actual number of rows in the table, perhaps code only the LIKE predicate [if, with trim].? Finally, depending on the _field_ data, the ordering requested may not effect exact-match-first, if there is an exact match and an inexact match; seems perhaps that exact-match-first may be desired from the descriptive [vs presentation of actual inputs and desired output]. — CRPence, May 28 '16 at 16:18

SQL FETCH, Cursors and RPG

1 Answers1