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;
Wow...lots wrong here... At a quick glance...
%EOF()
works with RPG record level access, you need to be checking
SQLCODE or SQLSTATEFETCH
OPEN
are all SQL statements, need to be in an EXEC SQL
DEALLOCATE
is not neededFETCH
the row from the cursor into an RPG variableTake 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.