Article:		Stored Procedures for Java Programmers
Subject:		JDBC support for Cursors
Date:		2003-08-15 10:45:59
From:		gokulsingh
<extract>Because returning cursors from procedures is not directly supported by JDBC, we use Types.OTHER to declare the return type of the procedure and then cast from the call to getObject().</extract> JDBC does support returning of Cursors as ResultSet. The javadocs state the following : "A CallableStatement can return one ResultSet or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement." I have worked with stored procedures on DB2 where a open cursor in the stored procedure was returned as a result set via the method CallableStatement.executeQuery().

Showing messages 1 through 3 of 3.

• JDBC support for Cursors
  2003-08-15 13:14:07  nferrier [View]
  
  
  Hi Gokul.
  
  Technically you're right, but most DBMS don't seem to support this style. Maybe it has just escaped most people attention, tucked away in the javadoc there. I'm not sure from what version it came into being.
  
  I think the getObject() style works on DB2 as well though, doesn't it? Whereas doing:
  
  ResultSet rs = someCallable.executeQuery();
  
  definitely doesn't work on (at least) early version of Oracle JDBC and not at all on PostgreSQL (I know coz I wrote the PG support).
  
  Cheers!
• JDBC support for Cursors
  2003-08-22 15:56:05  anonymous2 [View]
  
  
  I've been trying to get Postgres to return a cursor as a ResultSet for a long time. I've seen that they can be returned as strings, and then a second query can be issued with the "FETCH ALL IN "<unnamed cursor 1>" syntax in a standard JDBC PreparedStatement.executeQuery(). Obviously, it would be much better to get the ResultSet in one query. However, I tried your example (after fixing the many typos in your stored procedures and Java code), but the JDBC driver throws this exception:
  
  Exception in thread "main" No class found for refcursor
  at org.postgresql.jdbc1.AbstractJdbc1Connection.getObject(AbstractJdbc1Connection.java:693)
  
  This is using PostgreSQL 7.3.4 with its supplied JDBC driver. What version did you write this support for?
• JDBC support for Cursors
  2003-08-23 07:07:58  nferrier [View]
  
  
  I wrote the PG JDBC code for getting a ResultSet from a cursor return value earlier this year.
  
  Unfortunately, PostgreSQL 7.4 has taken much longer to reach release than I hoped. It is now in BETA so you should be able to get a tar ball going quite easily. The 7.4 JDBC driver will work on a 7.3 database with no problems.