Skip to main content

[jpa-spec users] [jsr338-experts] Stored Procedure Query updates

  • From: gordon yorke <gordon.yorke@...>
  • To: jsr338-experts@...
  • Subject: [jpa-spec users] [jsr338-experts] Stored Procedure Query updates
  • Date: Thu, 14 Mar 2013 17:40:57 -0300
  • List-id: <jsr338-experts.jpa-spec.java.net>
  • Organization: Oracle Corporation

Hello All,
   Working through the TCK testing has indicated that the behaviour of the stored procedure queries is really not clear and could use some clarifications.  I propose the following updates to section 3.10.17.3 and the related JavaDocs (attached) on StoredProcedureQuery.java. 
  Below removed text has been styles with strikethrough and new text is in red.  For those with plain text email clients I have attached a ODF document as well
==>
The setParameter methods are used to set the values of all required IN and INOUT parameters. It is not required to set the values of stored procedure parameters for  which default values have been defined by the stored procedure.

The case where there is only a single result set (or a single result) plus any results passed back via INOUT and OUT parameters is supported using the getResultList and getSingleResult methods.
    When calling getResultList and getSingleResult on a stored procedure query the provider will call execute() on an unexecuted stored procedure query before processing the getResultList and getSingleResult.

The case where there is only an update count plus any results passed back via INOUT and OUT parameters is supported using the executeUpdate method.
    When calling executeUpdate on a stored procedure query the provider will call execute() on an unexecuted stored procedure query followed by a getUpdateCount.  The results of an executeUpdate will be those of getUpdateCount.

The getOutputParameterValue methods are used to retrieve the values passed back from the procedure through INOUT and OUT parameters.

The execute method supports both the simple case where scalar results are passed back only via INOUT and OUT parameters as well as the most general case (multiple result sets and/or update counts, possibly also in combination with output parameter values).

The execute method returns true if the first result is a result set, and false if it is an update count or there are no results other than through INOUT and OUT parameters, if any.

If the execute method returns true, the pending result set can be obtained by calling getResultList and getSingleResult.  The hasMoreResults method can then be used to test for further results.

If execute or hasMoreResults returns false, the getUpdateCount method can be called to obtain the pending result if it is an update count. The getUpdateCount method will return either the update count (zero or greater) or -1 if there is no update count (i.e., either the next result is a result set or there is no next update count).

For portability, results that correspond to JDBC result sets and update counts need to be processed before the values of any INOUT or OUT parameters are extracted.


After results returned through getResultList and getUpdateCount have been exhausted, results returned through INOUT and OUT parameters can be retrieved.

The getOutputParameterValue methods are used to retrieve the values passed back from the procedure through INOUT and OUT parameters.

For portability, results that correspond to JDBC result sets and update counts need to be processed before the values of any INOUT or OUT parameters are extracted.

When using REF_CURSOR parameters for results sets the updates counts should be exhausted before calling getResultList to retrieve the result set.  Alternatively the REF_CURSOR result set can be retrieved through  getOutputParameterValue.  Result set mappings will be applied to REF_CURSOR results in the order the REF_CURSOR parameters were registered with the query

In the simplest case, where results are returned only via INOUT and OUT parameters, execute can be followed immediately by calls to getOutputParameterValue.

Attachment: storedprocwriteup.odt
Description: application/vnd.oasis.opendocument.text

Attachment: StoredProcedureQuery.pdf
Description: Adobe PDF document

Attachment: StoredProcedureQuery.java
Description: Text document



[jpa-spec users] [jsr338-experts] Stored Procedure Query updates

gordon yorke 03/14/2013
 
 
Close
loading
Please Confirm
Close