Thanks Lance
On Thu 05 Jul 2012 05:22:11 PM CDT, Lance Andersen - Oracle wrote:
> There is already a supportsNamedParameters() which has been there
> since J2SE 1.4 so we should be covered.
>
> Best
> Lance
> On Jul 5, 2012, at 6:17 PM, Steve Ebersole wrote:
>
>> 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
>
> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
> <http://oracle.com/us/design/oracle-email-sig-198324.gif>Lance
> Andersen| Principal Member of Technical Staff | +1.781.442.2037
> Oracle Java Engineering
> 1 Network Drive
> Burlington, MA 01803
> Lance.Andersen_at_oracle.com <mailto:Lance.Andersen_at_oracle.com>
>
