Write custom H2 DB function Java

Question

I am trying to run the below code using H2DB (via junit test), while doing so i get error message as below. I understand that, there are no function available as "days" in H2. So i am trying to write a custom function, but it does not work out, can any one help on writing this function.

SQLBuilder class code:

public String dummy() {
        return new StringBuilder(new SQL() {
            {
                SELECT("date(CREATE_TMS)");
                SELECT("CASE WHEN date(CREATE_TMS) >= (CURRENT DATE - cast('1' AS integer) days) THEN 'Y' ELSE 'N' END NEW_B");
                FROM("Q.DUMMY");
            }
        }.toString().concat(" FOR READ ONLY WITH UR")).toString();
    }

Error message:

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT DATE(CREATE_TMS), CASE WHEN DATE(CREATE_TMS) >= (CURRENT DATE - CAST('1' AS INTEGER) DAYS[*]) THEN 'Y' ELSE 'N' END NEW_BILLING
FROM Q.DUMMY FOR READ ONLY WITH UR "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT date(CREATE_TMS), CASE WHEN date(CREATE_TMS) >= (CURRENT DATE - cast('1' AS integer) days) THEN 'Y' ELSE 'N' END NEW_BILLING
FROM Q.DUMMY FOR READ ONLY WITH UR [42001-199]

For some reason days are converted to DAYS[*], we can see that in error message.

Customer method i tried in schema-db2.sql:

drop ALIAS if exists days; 
CREATE ALIAS days as '
import java.lang.String;
@CODE
java.lang.String days() throws Exception { 
  return "days";
} 
';

applicaiton.properties:

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false;Mode=DB2

this code worked for me `drop ALIAS if exists days; CREATE ALIAS days as ' import java.lang.String; @CODE java.lang.String days() throws Exception { return "days"; } ';` — Omid Rostami, Sep 15 '20 at 05:06

score 0 · Answer 1 · answered Mar 03 '20 at 10:44

0

DAYS is not a function and is not a something that other databases support. Db2 also uses non-standard interval literals.

If you can build H2 from its current sources, you can use cast('1' AS integer) day in it (not the days) and such construction is also supported by Db2. You can also simply use 1 DAY, it is supported by current H2 and Db2 too.

(CURRENT_DAY - 1 DAY)

Sources of H2 are available on GitHub:

https://github.com/h2database/h2database

Building instructions are here:

https://h2database.com/html/build.html#building

You need a jar target.

To compile H2 from the current sources you need JDK 8, 9, 10, 11, or 12. Compiled jar will be compatible with more recent versions.

answered Mar 03 '20 at 10:44

Evgenij Ryazanov

6,960
2
10
18

I had already tested with "day" instead of "days" it does not work, it throws same kind of error. As well "cast('1' AS integer) days" 1 is not constant, here a column name will come (dynamic value). – user2000189 Mar 04 '20 at 11:12
You need to build H2 from the current sources, neither 1.4.199 nor 1.4.200 support such expression. – Evgenij Ryazanov Mar 04 '20 at 11:44
I see 1.4.200 as latest jar for H2 from maven central repo. can you please tell about "H2 from current sources" – user2000189 Mar 05 '20 at 08:01
You need to download sources from GitHub and compile them by yourself as described in the documentation (see links in my answer). – Evgenij Ryazanov Mar 05 '20 at 11:47

Write custom H2 DB function Java

1 Answers1