Function calls with parameters

[niklas...@gmail.com]

Hi,

I have a question regarding the following test code:

Class.forName("org.h2.Driver");

Connection conn = DriverManager.getConnection("jdbc:h2:mem:db1", "SA", "");

conn.createStatement().execute("CREATE TABLE A (B TIMESTAMP)");

PreparedStatement ps = conn.prepareStatement("SELECT  B FROM A WHERE (TIMESTAMPDIFF(?, B, B) > ?)");

ps.setString(1, "HOUR");

ps.setInt(2, 99999);

With version 1.4.197 this works.

With version 1.4.200 I get the exception:

org.h2.jdbc.JdbcSQLDataException: 

Invalid value "2" for parameter "parameterIndex" [90008-200]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:590)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

at org.h2.message.DbException.get(DbException.java:205)

at org.h2.message.DbException.getInvalidValueException(DbException.java:280)

at org.h2.jdbc.JdbcPreparedStatement.setParameter(JdbcPreparedStatement.java:1503)

at org.h2.jdbc.JdbcPreparedStatement.setInt(JdbcPreparedStatement.java:394)

With the current master branch I get:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT  B FROM A WHERE (TIMESTAMPDIFF(?[*], B, B) > ?)"; expected "date-time field"; SQL statement:

SELECT  B FROM A WHERE (TIMESTAMPDIFF(?, B, B) > ?) [42001-201]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)

at org.h2.message.DbException.getJdbcSQLException(DbException.java:435)

at org.h2.message.DbException.getSyntaxError(DbException.java:238)

at org.h2.command.Parser.getSyntaxError(Parser.java:1260)

at org.h2.command.Parser.readDateTimeField(Parser.java:4858)

at org.h2.command.Parser.readBuiltinFunctionIf(Parser.java:4385)

at org.h2.command.Parser.readFunction(Parser.java:4059)

Was this a cosncious decision to not support these kind of parameters, or did this just break along the way?

My problem is, that the "real" use case comes from a query generated by eclipselink using a expression dimilar to "function('TIMESTAMPDIFF', 'HOUR', B, B)" in JPQL. And there does not seem to be any way to prevent eclipselink from using a query parameter for 'HOUR'.

Has anybody faced similar issues?

Thanks & best regards,

  Niklas

 

[Evgenij Ryazanov]

Hello.

1. You can't legally use expressions as the first parameter of this function, H2 officially supports only datetime fields:

https://h2database.com/html/functions.html#datediff

The valid syntax is TIMESTAMPDIFF(HOUR, arg1, arg2).

H2 accepts character string literals too for some limited compatibility with old unsupported versions, but intentionally does not allow other expressions any more. The reason is very simple: H2 need to know what to return from this function during its compilation, but type of result depends on used datetime field.

2. It is better to use DATEDIFF (the primary name of this function) instead of its second name TIMESTAMPDIFF in generated code.

3. It is rarely appropriate to use this function, it actually returns the number of crossed unit boundaries and not the real difference between two timestamps. Recent releases of H2 supports standard datetime and interval arithmetic, for example, you can use WHERE timestamp1 > timestamp2 + INTERVAL '9999' DAY or something like it. You can pass intervals as parameters, but in some cases you may need to add explicit casts around them (something like CAST(? AS INTERVAL(18) DAY) or whatever you need).

[Evgenij Ryazanov]

CAST(? AS INTERVAL(18) DAY)

I meant CAST(? AS INTERVAL DAY(18))