I'm currently on writing a SQL-Procedure on IBM i 5 V7R1. If I want to return a result set after my for loop I can't create the procedure. Here is the code:
create or replace procedure test1()
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC EDVVAEH1.test1
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
proc: BEGIN
DECLARE x VARCHAR(255);
DECLARE return_cur CURSOR with return FOR
select * from sysibm.sysdummy1;
loop1: for record as C1 cursor for
SELECT TABLE_NAME
from qsys2.systables
DO
SET x = record.table_name;
END FOR loop1;
open return_cur;
END proc
As the options define, I want to return a result set after both loops, but it doesn't let me create this procedure.
The Error is -104 token: "End of Statement" is not valid, but everything is there :(
It seems I found the root cause of my problem, but I can't fix it. As far as I can see, my i Access has a Problem with the for-statement. I copied the statement to my RDi 9.1 in the Database Development view and ran it there. I also set a different end of statement command and after that I was able to create my procedure.
Now I need to wait until someone in my company can provide me a PTF for the client access. I already tried to install all PTFs delivered with the IBM i, but no improvements ...
If anyone has an idea, where I can download a PTF for client Access please give me a hint. The IBM site is not possible for me since, I am not registered for out IBM i.
Thanks.