Re: How does HSQLDB resolve which user-defined function to invoke?

Hi, thanks for replying.

I was hoping that JPA's `setParameter()` API could somehow inform the
SQL parser about the value's _actual_ type at runtime :-(.

Does this mean that swapping the order of the CREATE FUNCTION
statement so that the variant with the INT parameter is created first
is not _supposed_ to make any difference?

Thanks again,
Chris

On Sat, 29 Jul 2023 at 19:26, Fred Toussi via Hsqldb-user
<[email protected]> wrote:
>
> HSQLDB can use the correct signature of the function if enough information is available in the SELECT statement.
>
> SELECT ... WHERE JsonFieldAsText(a, :index) = :value
>
> In the above statement, the type of the :index variable is not known to the SQL parser and it defaults to a VARCHAR type. When writing an SQL statement, JsonFieldAsText(a, CAST(;index AS INT))  can be used to force the INT type.
>
> Fred
>
> On Sat, Jul 29, 2023, at 17:49, Chris Rankin wrote:
> > Hi,
> >
> > I am using HSQLDB 2.7.2, and I am writing some Java functions that act
> > like the Postgres "->>" operator, e.g.
> >
> > a ->> b = field 'b' from JSON object
> > a ->> 1 = element [1] from JSON array
> >
> > So I've mapped two Java functions into HSQLDB, using SQL like this:
> >
> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIndex INT)
> >     RETURNS VARCHAR(32768)
> >     LANGUAGE JAVA DETERMINISTIC NO SQL
> >     EXTERNAL NAME 'CLASSPATH:MyClass.fieldByIndex
> >
> > CREATE FUNCTION JsonFieldAsText(IN json VARCHAR(32768), IN fieldIName
> > VARCHAR(50))
> >     RETURNS VARCHAR(32768)
> >     LANGUAGE JAVA DETERMINISTIC NO SQL
> >     EXTERNAL NAME 'CLASSPATH:MyClass.fieldByName
> >
> > The idea is that;
> >
> > "a ->> b" becomes "JsonFieldAsText(a, 'b')", and
> > "a ->> 1" becomes "JsonFieldAsText(a, 1)"
> >
> > This all seems OK, except for when I tried to execute a JPA Query:
> >
> > "SELECT ... WHERE JsonFieldAsText(a, :index) = :value"
> >
> > having set the index parameter using "setParameter("index", 2)".
> >
> > My understanding was that HSQLDB would choose the JsonFieldAsText()
> > function whose signature provided the best match to the parameters,
> > i.e. the one that maps to MyClass.fieldAsIndex(). However, I was
> > alarmed to see HSQLDB choose to execute the MyClass.fieldAsName()
> > variant instead.
> >
> > I have currently "resolved" this issue by swapping the order of my
> > CREATE FUNCTION statements so that the one mapping to
> > MyClass.fieldAsIndex() is executed first, but I could be relying on
> > undocumented behaviour here, for all I know.
> >
> > Can anyone advise me as to whether there's a better way to do this please?
> >
> > Thanks,
> > Chris
> >
> >
> > _______________________________________________
> > Hsqldb-user mailing list
> > [email protected]
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-user
>
>
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user