Array parameter

17 views
Skip to first unread message

Hugo Larson

unread,
Sep 26, 2021, 6:17:25 AM9/26/21
to Firebird-java
Hi,

Why this does not work? The parameter is not an actual ARRAY but rather values

String vals = {1,2,3,4,5...}

SELECT * FROM PRODUCT WHERE PRODUCT.ID IN (:ARRAY)

Array array = statement.getConnection().createArrayOf("varchar", vals );
statement.setArray("ARRAY", array);

Mark Rotteveel

unread,
Sep 26, 2021, 6:38:47 AM9/26/21
to firebi...@googlegroups.com
There are basically two reasons: Jaybird doesn't support `setArray` (in
fact, it doesn't support arrays at all), and even if it did, Firebird
doesn't support arrays in `IN` (in fact, arrays are hardly usable at all
in DSQL and PSQL).

A year ago there was some talk[1] about even deprecating and eventually
removing array support from Firebird entirely, though for now the
decision is to keep the status quo.

At this point of time, I myself won't add array support to Jaybird. Both
the Firebird and JDBC parts are pretty complex, and given the limited
usability of arrays in Firebird, I don't want to invest time and energy
in it.

I might consider pull requests providing an implementation of arrays[2],
but that wouldn't help with this particular case.

In the meantime, I'd recommend looking at alternative solutions, like
[3] and [4] (though some of this solutions will not work in Firebird).

Mark

[1]:
https://sourceforge.net/p/firebird/mailman/firebird-devel/thread/10e2f3c9-f8d2-00a4...@yandex.ru/
[2]: https://github.com/FirebirdSQL/jaybird/issues/149
[3]:
https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause
[4]:
https://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives
--
Mark Rotteveel

Hugo Larson

unread,
Sep 26, 2021, 9:56:25 AM9/26/21
to firebi...@googlegroups.com
--
You received this message because you are subscribed to the Google Groups "firebird-java" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-java+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebird-java/62f8a5f2-5c76-f7bc-d9e2-3580abaa2139%40lawinegevaar.nl.

Arioch The

unread,
Sep 27, 2021, 5:56:52 AM9/27/21
to firebi...@googlegroups.com
I suggest reading my answer at https://stackoverflow.com/a/43997801/976391

It may help you think out another approach to the task

And some extension of that approach at
https://stackoverflow.com/a/66276056/976391
Reply all
Reply to author
Forward
0 new messages