users@jpa-spec.java.net

[jpa-spec users] [jsr338-experts] Re: StoredProcedureQuery

From: Steve Ebersole <steve.ebersole_at_redhat.com>
Date: Thu, 05 Jul 2012 17:17:19 -0500

Perfect, that is exactly what I figured, just making sure. It is
actually the right thing to do as well in my opinion as it now makes
handling resultsets from functions/procs much more portable across
databases when using JPA.

Will there be a corollary supportsProcedureNamedParameters() added to
DatabaseMetaData for 1.8 as well? Or is the assumption that 1.8
compliant drivers *will* support it?


On Thu 05 Jul 2012 05:08:49 PM CDT, Linda DeMichiel wrote:
>
>
> 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