How do I get DBeaver to do a DB2 UPDATE via Cursor

All we need is an easy explanation of the problem, so here it is.

The following query works when run from the console via db2 command. But fails when running via DBeaver.

BEGIN NOT ATOMIC
  FOR R AS C CURSOR WITH HOLD FOR
    SELECT ROW_NUMBER() OVER() AS INDEX, ID
    FROM A_TABLE a
    WHERE a.ID BETWEEN 1 AND 1000
    ORDER BY a.ID
  DO
    UPDATE A_TABLE SET A_COL = 1 WHERE ID = R.ID;--
    IF MOD(R.INDEX, 100) = 0 THEN
      COMMIT;--
    END IF;--
  END FOR;--
  COMMIT;--
END;

DBeaver gives the following result

SQL Error [42601]: An unexpected token "END-OF-STATEMENT" was found following "WHERE ID = R.ID". Expected tokens may include: "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As mustaccio points out, you need to change your statement terminator. In DBeaver you can do that by:

  1. Right-click on the data source and choose "Edit connection"
  2. In the leftmost frame at the bottom, there is SQL Processing
  3. Check the Datasource checkbox

How do I get DBeaver to do a DB2 UPDATE via Cursor

Now you can alter the statement delimiter

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply