Anyone cares?
On Mon 26 Aug 2013 12:59:43 PM CDT, Steve Ebersole wrote:
> There is a more generic problem with function return values in general
> and the JPA API as defined not giving the provider any indication that
> any of the registered parameters are supposed to map to the function
> return. I just assumed this is "outside the scope" of
> StoredProcedureQuery; that StoredProcedureQuery explicitly maps to
> PROCEDURES (or FUNCTIONS that return REF_CURSOR only).
>
> If that is an inaccurate assumption, then in the more general sense,
> what is the indication to the provider that a function is being called?
>
> On Mon 26 Aug 2013 12:33:38 PM CDT, Steve Ebersole wrote:
>> I realize providers could use various specific JDBC driver methods of
>> calling the actual procedure/function indicated by a
>> StoredProcedureQuery.
>>
>> But given standard JDBC calls, the recommended approach is that
>> procedures are called using:
>>
>> CallableStatement cstmt = connection.prepareCall( "{CALL
>> theProcedure(...)}" );
>>
>> and functions are called using:
>>
>> CallableStatement cstmt = connection.prepareCall( "{? = CALL
>> theFunction(...)}" );
>>
>>
>> But this gets to an important practical implication of supporting
>> REF_CURSOR parameters as defined by the JPA 2.1 spec. If you take a
>> look at supporting this for 2 popular databases that support
>> "returning cursors" in Oracle and PostgreSQL:
>>
>> For PostgreSQL returning a cursor means:
>> 1) The "procedure" will have to be a function.
>> 2) The function return will be of type refcursor
>> 3) There can be only one such REF_CURSOR parameter (in the JPA
>> StoredProcedureQuery object) and it needs to be "first"
>> 4) When creating the CallableStatement, we need to use the {? = call
>> theFunction(...)} form
>>
>> For Oracle, returning cursors is supported both via function return
>> value and via parameters, meaning:
>> 1) We could have a function or a procedure.
>> 2) There might be multiple REF_CURSOR parameters and they could occur
>> in any order/postion. If the call is to a function, the function may
>> or may not have a return type of cursorType (REF_CURSOR).
>> 3) When creating the CallableStatement, we actually have no clue
>> whether we need the {call procedureName(...)} form or the {?=call
>> functionName(...)} form
>>
>> In a generic sense, or in the Oracle case specifically, what is the
>> indication via the JPA API to tell the provider to use the procedure
>> or function call syntax?
>>
>>