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;

Answer 1

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

1. you're trying to declare your cursor in the wrong place.
2. %EOF() works with RPG record level access, you need to be checking SQLCODE or SQLSTATE
3. FETCH OPEN are all SQL statements, need to be in an EXEC SQL
4. DEALLOCATE is not needed
5. 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.