Hi Bob,
Tried it out. First thing to note is that H2 doesn't accept an ARRAY
as a prepared statement parameter ("arrays not supported"). I got
around that by converting the long[] to a String (like
'{104,485,1005,...}') and then parsing this String in the user-defined
function. Not beautiful, but works. H2 insists on having a 'SELECT' in
the ANY() argument, so the query looks like
"SELECT * FROM T WHERE ID = ANY (SELECT VALUE FROM ARRAY_TO_RESULT_SET
(?))"
However, the run time is quadratic in the number of rows in the table
n and the number of ids in the ANY() expression m, i.e. n*m which
really is the deal breaker. Defining ARRAY_TO_RESULT_SET as
DETERMINISTIC in "CREATE ALIAS" doesn't help that.
So I'll have to resort to a full table scan...
Thanks,
Bernd
On Aug 4, 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
-~----------~----~----~----~------~----~------~--~---
