Stored Procedures for Java Programmers
Subject:   JDBC support for Cursors
Date:   2003-08-15 13:14:07
From:   nferrier
Response to: JDBC support for Cursors

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).


Full Threads Oldest First

Showing messages 1 through 2 of 2.

  • 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(

    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.