JDBC 4.2 spec: expert group proposed refcursor support

Hi folks

Just so you know, the JDBC team appear to be working on refcursor
support for 4.2. Draft spec section follows below.

It might be worth joining the expert group if this feature is of
interest and you want to be able to influence it before it's finalized.

I'm pretty surprised not to see anyone from Pg, EDB or GreenPlum on
there actually:

http://jcp.org/en/jsr/detail?id=221

I heard about this via the JPA 2.1 expert group.

The JDBC EG is pretty amazingly closed. No public mailing list archives,
no public mirror of the EG list on java.net, etc. I've asked to be added
to the observer alias for the EG and was informed my request would be
acted on within /14 days/.

Anyhow, here's the proposed draft:

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

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/