0

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

Improve this question
3
  • ;-- for an in-block statement terminator only works in the CLP. You need to choose a different terminator for the block and configure your client accordingly Commented Apr 19, 2021 at 13:49
  • Thanks @mustaccio Do you know how to do this for DBeaver? Commented Apr 19, 2021 at 14:21
  • @Lennart will make your comment into an answer? Commented Apr 20, 2021 at 6:06

1 Answer 1

Reset to default
2

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

enter image description here

Now you can alter the statement delimiter

Improve this answer
2
  • Thanks @lennart I'll accept the answer as a workaround. I don't think it is possible to change the 'in-block' terminator this solution changes the 'regular' terminator. I changed "Statements delimiter" to @, "Ignore native delimiter" and "Blank line is statement delimiter" is checked, then my statement works by replacing only the last ; to @ or by leaving a blank line Commented Apr 21, 2021 at 18:37
  • Yes, the statement separator inside for example a procedure can not be changed, it has to be; One therefor change the statement terminator to something different, I usually choose @ for this Commented Apr 21, 2021 at 21:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.