how to solve db2 code: -104, SQL State: 42601

Question

--/
BEGIN
     FOR V AS MYCURSOR CURSOR FOR SELECT ID,NAME,AGE FROM PEOPLE
     DO
        BEGIN
            INSERT INTO PERSON(NAME,AGE) VALUES(V.NAME,V.AGE);
        END;
     END FOR;
END;
/

DB tool is Dbvisualizer 10.0.1, so I need "--/" and "/"

Message: [Code: -104, SQL State: 42601] An unexpected token "V" was found following "BEGIN FOR ". Expected tokens may include: "JOIN".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29 Help plz.

What’s the Db2 version and platform? Have you tried to find the place in DBVisualizer, where you can change the statement delimiter from `;` to `/`? — Mark Barinstein, Nov 06 '19 at 04:50
Your code fragment works correctly in my DbVisualizer , but I have the *current* version of Dbviz - consider installing the latest build ( 10.0.24). Otherwise your code fragment differs from what Db2 is getting. — mao, Nov 06 '19 at 09:24

score 0 · Answer 1 · answered Nov 07 '19 at 22:56

In your query tool, set the statement terminator to something that is not ';'. E.g. use @

BEGIN
 FOR V AS MYCURSOR CURSOR FOR SELECT ID,NAME,AGE FROM PEOPLE
 DO
    BEGIN
        INSERT INTO PERSON(NAME,AGE) VALUES(V.NAME,V.AGE);
    END;
 END FOR;
END
@

score 0 · Answer 2 · answered May 09 '22 at 10:09

You must use different Statement Delimiter / Terminator to run compound statements. Every client tool has its own methods of setting it. Dbvisualizer:

Tools\Tool Properties\SQL Commander\Statement Delimiters: SQL Statement Delimiter:
SQL Statement Delimiter 1: @
SQL Statement Delimiter 2: @
Using the DBMS Output Tab:

Only in DbVisualizer DbVisualizer Pro edition. This feature is only available in the DbVisualizer Pro edition.

BEGIN FOR V AS MYCURSOR CURSOR FOR SELECT ID,NAME,AGE FROM PEOPLE DO BEGIN INSERT INTO PERSON(NAME,AGE) VALUES(V.NAME,V.AGE); END; END FOR; END @

how to solve db2 code: -104, SQL State: 42601

2 Answers2