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.