[hibernate-dev] Function support in ProcedureCall/StoredProcedureQuery

[Steve Ebersole]

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).