Error SQL0104 when creating a function in System i V7R1

Question

I'm creating a SQL function on System i V7R1:

CREATE FUNCTION MYSCHEMA.GROUPDIBAS(v_code VARCHAR(50))
RETURNS VARCHAR(2048)
LANGUAGE SQL
BEGIN
    DECLARE str VARCHAR(2048);
    SET str = '';
    FOR row AS (
        SELECT 
            FIELD2
        FROM MYSCHEMA.DIBAS
        WHERE FIELD1 = v_code
    )
    DO
        SET str = 'Bubi'; --I removed many statements to make clear the problem doesn't come from them
    END FOR;
    RETURN str;
END
;

I execute it with "Run SQL script" tool, which is part of the iSeries Navigator V7R1. It works on another V7R1 server (using iSeries Navigator V5R4), but not in that one where I'm working now. It fails with this message:

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
  Cause . . . . . :   A syntax error was detected at token <END-OF-STATEMENT>.
  Token <END-OF-STATEMENT> is not a valid token.  A partial list of valid tokens is ;.
  This list assumes that the statement is correct up to the token.
  The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point.
  Recovery  . . . :   Do one or more of the following and try the request again:
  -- Verify the SQL statement in the area of the token <END-OF-STATEMENT>. Correct the statement.
     The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses.
  -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

If I remove the FOR block, it works.

Moreover if I execute the statement with 5250 Emulator, command STRSQL, it works. So it seems like a bug in "Run SQL script" client.

Any hint will be appreciated!

Answer 1

Given the tests made by @JamesA and me, I fear the problem can be in the Program Temporary Fix (PTF) that this server hasn't and the other ones have. Specifically, running WRKPTFGRP command, I can guess it probably misses this PTF group:

PTF group  Level  Text
SF99701        5  DB2 FOR IBM I

Unfortunately I can't try installing it now :(.

Answer 2

The issue is with the FOR statement. The query analyzer is inconsistent on when the cursor-name CURSOR FOR is optional and when it is required even though the documentation states if it is not specifified a unique cursor name is generated. SQL submitted via the IBM Access Navigator Run Scripts utility require it.

The parenthesis are also incorrect but sometimes they are accepted (STRSQL, Navigator Run SQL Scripts) and sometimes they aren't (DBVisualizer/JDBC).

TIL there must be a different query analyzer running depending on the source of the query.

CREATE FUNCTION MYSCHEMA.GROUPDIBAS(v_code VARCHAR(50))
RETURNS VARCHAR(2048)
LANGUAGE SQL
BEGIN
    DECLARE str VARCHAR(2048);
    SET str = '';
    FOR row AS C1 CURSOR FOR
        SELECT 
            FIELD2
        FROM MYSCHEMA.DIBAS
        WHERE FIELD1 = v_code
    DO
        SET str = 'Bubi'; --I removed many statements to make clear the problem doesn't come from them
    END FOR;
    RETURN str;
END

---

Answer 3

In the session properties of your IDE change the Statement Separator field value from ; to | then reconnect your session. then use | instead of ;. this way you can run your statement or procedure or function.

usage example,
CREATE FUNCTION MYSCHEMA.GROUPDIBAS(v_code VARCHAR(50))
RETURNS VARCHAR(2048)
LANGUAGE SQL
BEGIN
    DECLARE str VARCHAR(2048);
    SET str = '';
    FOR row AS C1 CURSOR FOR
        SELECT 
            FIELD2
        FROM MYSCHEMA.DIBAS
        WHERE FIELD1 = v_code
    DO
        SET str = 'Bubi'; --I removed many statements to make clear the problem doesn't come from them
    END FOR;
    RETURN str;
END |