Skip to main content

[jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery and REF_CURSOR parameters : practical implications

  • From: Steve Ebersole <steve.ebersole@...>
  • To: jsr338-experts@...
  • Subject: [jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery and REF_CURSOR parameters : practical implications
  • Date: Mon, 26 Aug 2013 12:59:04 -0500
  • List-id: <jsr338-experts.jpa-spec.java.net>

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?




[jpa-spec users] [jsr338-experts] StoredProcedureQuery and REF_CURSOR parameters : practical implications

Steve Ebersole 08/26/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery and REF_CURSOR parameters : practical implications

Steve Ebersole 08/26/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery and REF_CURSOR parameters : practical implications

Steve Ebersole 08/26/2013

[jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery and REF_CURSOR parameters : practical implications

Steve Ebersole 08/30/2013
 
 
Close
loading
Please Confirm
Close