MODE=DB2 - "FETCH ... ONLY" doesn't work with embedded in-memory (but works with embedded persistent).

[Davy Toch]

We normally use DB2 as production database. In the code we have for
example the following statement:

SELECT * FROM TABLE_NAME FETCH FIRST 50 ROWS ONLY

When using an embedded file-persistent H2 database with MODE=DB2
(jdbc:h2:D:\tmp\mydb;MODE=DB2;), then we don't have any problems, but
when we use an embedded in-memory H2 database
(jdbc:h2:mem:MODE=DB2;DB_CLOSE_DELAY=-1), then the execution fails
with :

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement
SELECT * FROM TEST_TABLE FETCH[*] FIRST 50 ROWS ONLY ;
[42000-116]
at org.h2.message.Message.getSQLException(Message.java:105)
at org.h2.message.Message.getSQLException(Message.java:116)
at org.h2.message.Message.getSQLException(Message.java:75)
at org.h2.message.Message.getSyntaxError(Message.java:128)
at org.h2.command.Parser.getSyntaxError(Parser.java:469)
at org.h2.command.Parser.prepareCommand(Parser.java:250)
at org.h2.engine.Session.prepareLocal(Session.java:409)
at org.h2.engine.Session.prepareCommand(Session.java:370)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:
1049)
at org.h2.jdbc.JdbcPreparedStatement.<init>
(JdbcPreparedStatement.java:74)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:
233)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
...

[Davy Toch]

Update :
- I tested using H2 1.1.116, 1.1.118
- calling SET MODE=DB2 as first SQL statement instead of passing it in
the URL fixes the problem :-)

[Thomas Mueller]

Hi,

The problem is the database URL.
jdbc:h2:mem:MODE=DB2;DB_CLOSE_DELAY=-1 means: in-memory database with
the name "MODE=DB2". You need to add ";" after the "mem:"

jdbc:h2:mem:;MODE=DB2;DB_CLOSE_DELAY=-1

To avoid such problem, database names that contain "=" could be
rejected. I'm not sure if that's a good idea, as = is a valid
character within file names.

Regards,
Thomas