Hi,
There is a H2 specific solution for this problem, see
http://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/samples/Function.java
http://www.h2database.com/html/functions.html#table
// Calling the built-in 'table' function
stat.execute("CREATE TABLE TEST(ID INT) AS " +
"SELECT X FROM SYSTEM_RANGE(1, 100)");
PreparedStatement prep;
prep = conn.prepareStatement("SELECT * FROM TABLE(X INT=?, O INT=?) J " +
"INNER JOIN TEST T ON J.X=T.ID ORDER BY J.O");
prep.setObject(1,
new Integer[] { new Integer(30), new Integer(20) });
prep.setObject(2,
new Integer[] { new Integer(1), new Integer(2) });
rs = prep.executeQuery();
I didn't know about =ANY(...), I will add that to the roadmap.
Regards,
Thomas
On Tue, Aug 4, 2009 at 10:59 AM, Bernd<[email protected]> wrote:
>
> Thanks for pointing out that the problem is not missing support for
> =ANY but the r.h.s. data type. If I understand you correctly, you
> suggest to write a stored procedure ARRAY_TO_RESULT_SET which takes an
> array and returns a SimpleResultSet and then do:
>
> "SELECT * FROM T WHERE ID = ANY (ARRAY_TO_RESULT_SET(?))"
>
> I'll give that a try.
>
> On Aug 3, 12:31 am, bob mcgee <[email protected]> wrote:
>> On Aug 2, 4:44 pm, TerryG <[email protected]> wrote:> If storing the array
>> in a separate table is an option, you could use
>> > the syntax [...]
>>
>> The grammer says H2 DOES support the ANY operator --
>> see:http://www.h2database.com/html/grammar.html#condition_right_hand_side
>>
>> Unfortunately, the implementation of the ARRAY type limits its
>> usefulness here, and it just behaves like a more flexible form of IN
>> (...).
>>
>> I wish H2 had a way to treat an array as a list of values (without
>> converting it to a SimpleResultSet) -- I imagine that something like
>> this is in the development pipeline but tricky to implement safely.
>> You *can* write a user-defined function that converts an array to a
>> SimpleResultSet, of course. I've been thinking of adding this to my
>> utility functions, but it may not perform that well.
>>
>> Cheers,
>> Bob McGee
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
