RE: https://hibernate.atlassian.net/browse/HHH-8445
I'd like to get opinions on this. Essentially the JPA spec
(StoredProcedureQuery) is silent on how/if calling database functions
through this API is supposed to be supported. I asked for clarification
of this from the rest of the EG, but got no responses. So at this point
we are on our own to implement this as we see fit.
And the problem is that we will need to support it. Some databases only
support REF_CURSOR "parameters" via function return. In fact, as far as
I know, only Oracle supports returning cursors from procedures via
parameters (although on HHH-8445 Christian claims DB2 does as well); for
all other databases which support returning cursors at all, doing so
requires a function where the cursor is the function return.
There are 2 aspects to the distinction between a procedure call and a
function call. First is the syntax used. Second is handling of
"parameter registrations".
A procedure is called using the syntax "{call theProcedureName(...)}"; a
function using "{?=call theFunctionName(...)}". Given a call that has
just one "parameter registration" representing a cursor, the difference
would be "{call the_proc(?)}" versus "{?=call the_func()}".
JPA disallows mixing of named and positional parameter registrations
through StoredProcedureQuery. That is potentially a problem here. The
function return is inherently positional (as far as I know the JDBC spec
does not discuss metadata about a function return, let alone naming of
it). If the StoredProcedureQuery is defined using positional
parameters, no problem. If the StoredProcedure is defined using named
parameters, that is where we run into this potential problem mainly
because the spec says that the order in which parameters are registered
is irrelevant.
In my opinion, ideally the JPA spec would have defined a separate method
to register a "function return". Luckily since the spec does not say
anything and the EG never responded we are free to do WhateverMakesSense
:) I have an example of what "providing a separate method to register
the function return" might look like in the comments of HHH-8445.
Ideally we'd allow access to the function return via the same mechanism
as registered "output" (IN/INOUT/REF_CURSOR) parameters. Just not sure
that is possible. The tricky part is that (via JPA StoredProcedureQuery
at least) access to parameter output values are only defined to be
available by name and position.
The other downside to using a separate method, as of now at least, would
be the difficulty to hook in to JPA @NamedStoredProcedureQuery support.
We'd either need to define a new @NamedFunctionCallQuery with explicit
support for defining the function return, or go a different route
altogether (using query hints maybe).
_______________________________________________
hibernate-dev mailing list
hibernate-dev@lists.jboss.org
https://lists.jboss.org/mailman/listinfo/hibernate-dev
