On 6/28/2012 1:59 PM, Steve Ebersole wrote:
> 3) WRT ParameterMode.REF_CURSOR, are those values supposed to be accessible by calling
> StoredProcedureQuery#getOutputParameterValue? Or by calling StoredProcedureQuery#getResultList/getSingleResult? If the
> former, do the Classes/ResultSetMappings used to create the StoredProcedureQuery apply to those ResultSets as well?
>
From a JPA point of view, by calling getResultList/getSingleResult. Under the covers,
the persistence provider would obtain the result from the stored procedure's output
parameter.
Lance kindly passed on to me what JDBC 4.2 is doing for REF CURSOR support --- see below.
-Linda
------------------------
13.3.3.4 REF CURSOR Support
The REF CURSOR data type is supported by several databases. To return a REF CURSOR from a stored procedure, the
CallableStatement method registerOutParameter
may be used specifying Types.REF_CURSOR as the data type to be returned. The CallableStatement method getObject,
specifying ResultSet as the type to convert the returned object to,
would be called to retrieve the ResultSet representing the REF CURSOR. The returned result set is a forward, read-only
result set.
if registerOutParameter is called specifying Types.REF_CURSOR and the JDBC driver does not support this data type, a
SQLFeatureNotSupportedException will be thrown.
CallableStatement cstmt = conn.prepareCall(" { call mySproc(?) }");
cstmt.registerOutParameter(1, Types.REF_CURSOR);
cstmt.executeQuery();
ResultSet rs = cstmt.getObject(1, ResultSet.class);
while (rs.next ()) {
System.out.println("Name="+ rs.getString(1));
}
code example 13-28 Executing a callable statement that returns a ResultSet using a REF CURSOR
To determine if a JDBC Driver supports REF CURSOR, an application may call DatabaseMetaData.supportsRefCursors.
interface DatabaseMetaData {
/**
* Retrieves whether this database supports REF CURSOR.
*
* @return {_at_code true} if this database supports REF CURSOR;
* {_at_code false} otherwise
* @exception SQLException if a database access error occurs
* @since 1.8
*/
boolean supportsRefCursors() throws SQLException;
}
The entry in Types.java
public class Types {
/**
* The constant in the Java programming language, sometimes referred to
* as a type code, that identifies the generic SQL type {_at_code REF CURSOR}.
*
* @since 1.8
*/
public static final int REF_CURSOR = 2012;
}
>
> On Thu 28 Jun 2012 03:06:09 PM CDT, Steve Ebersole wrote:
>> Some more questions/commenst from implementation, this time in regards
>> to javax.persistence.StoredProcedureQuery:
>>
>> 1) StoredProcedureQuery extends Query. Query defines a few methods
>> that are questionable being applied to a stored procedure call of any
>> sort, namely the paging values for firstResults
>> (getFirstResult/setFirstResult) and maxResults
>> (getMaxResults/setMaxResults). What is the expectation for those
>> calls (mainly the setters I guess) when applied to
>> StoredProcedureQuery? Personally I'd like to throw an exception, but
>> the javadocs on those Query methods allow only for "@throws
>> IllegalArgumentException if the argument is negative"
>>
>> 2) In regards to named versus positional parameters, I had a few
>> questions:
>> a) Could we possibly add a restriction that developers should use only
>> one form or the other? I see zero benefit to allowing developers to
>> mix named and positional parameters in a single query, and in fact see
>> only confusion about how those parameters ultimately get merged together.
>> b) Given the javadoc statement that named parameters need to be
>> registered "in order", I am assuming that their names have no relation
>> to the notion of named parameters added to java.sql.CallableStatement
>> in Java 7?
>>
>> Thanks,
>> Steve