H2 PreparedStatement and Arrays

[Helmut Leininger]

I am using H2 2.1.214 and facing this problem:
part of SQL Select statement:
...
            + "where events.mstb_recno in (select mstb_recno from events where events.mstb_edate >= ? and events.mstb_edate <= ?) and "

            + "mstb_etypenum in (?) and "

...

this statement can be prepared without errors. To supply the values for mstb_etypenum I have:
ArrayList<Integer> types ...

    java.sql.Array typeArray = pstmtGetEventsByCityMultipleTypes.getConnection()
                    .createArrayOf("INT", types.toArray());
     pstmtGetEventsByCityMultipleTypes.setArray(4, typeArray);

...

Executing this query leads to the following error:
org.h2.jdbc.JdbcSQLDataException: Datenumwandlungsfehler beim Umwandeln von "ARRAY to BIGINT"
Data conversion error converting "ARRAY to BIGINT"; SQL statement:
select distinct on (events.mstb_recno) events.*, tagtypes.mstb_etypenum as tt_etypenum, tagtypes.mstb_etypename as tt_etypename, tagtypes.mstb_properties as tt_properties, tagtypes.mstb_tsentence as tt_sentence, tagtypes.mstb_prinrole as tt_principalrole, tagtypes.mstb_witrole as tt_witnessrole, tagtypes.mstb_witdisp as tt_witnessdisp, tagtypes.mstb_gedcom_tag as tt_gedcomtag, tagtypes.mstb_etypenum as tt_etypenum, tagtypes.mstb_tsentence as tt_tsentence, witnesses.mstb_primary as wt_primary, witnesses.mstb_role as wt_role, witnesses.mstb_eper as wt_eper from events inner join placedict on placepv.mstb_uid = placedict.mstb_uid and placepv.mstb_type = ? inner join placepv on events.mstb_placenum = placepv.mstb_recno inner join tagtypes on events.mstb_etype = tagtypes.mstb_etypenum inner join witnesses on events.mstb_recno = witnesses.mstb_gnum where events.mstb_recno in (select mstb_recno from events where events.mstb_edate >= ? and events.mstb_edate <= ?) and mstb_etypenum in (?) and placedict.mstb_value = ? order by events.mstb_srtdate [22018-214]

Question:
Does H2 support arrays with PreparedStatement ? If yes, what is the correct handling?

Note:
excuting the basic SQL with
...
where mstb_etypenum in (2,3,4,5)
...

runs ok.

Thanks and regards

[Noel Grandin]

You can do something like:

final Long[] messageIds = ...
final String sqlQuery = "SELECT * FROM FROM FOO WHERE FOO.MessageID IN (SELECT ID FROM TABLE(ID BIGINT=?))";
PreparedStatement ps = conn.prepareStatement(sSqlQuery);
ps.setObject(1, messageIds );

[Helmut Leininger]

Unfortunately, this does not solve my problem.

there is no table or column named BIGINT. The error seems to happen within H2 when it should  the Array typeArray as an arry of values to be used in the IN clause. It seems to try to convert the Array to a single BIGINT value for whatever reason (which must fail).

I get the values for mstb_etypenum from an external source, not from a SQL statement. Therefor, I cannot use your approach.

[Evgenij Ryazanov]

Your code actually constructs something like mstb_etypenum IN (ARRAY[2, 3, 4, 5]) instead of mstb_etypenum IN (2, 3, 4, 5). 

In SQL statements from JDBC (unlike in JPQL from JPA) you cannot pass parameters in that way. One parameter always creates one value.

You can use

mstb_etypenum = ANY(?)

instead, see Prepared Statements and IN(...) section in documentation for an example:

https://h2database.com/html/performance.html#database_performance_tuning

[Helmut Leininger]

Thank you very much for this hint. It does the trick.

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/GRmSbAQzwJk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/3bd02bc0-5625-4bc7-9045-a12caeb7d16fn%40googlegroups.com.