Andrej Koelewijn

Using postgresql ref cursors in java

CREATE FUNCTION reffunc() RETURNS refcursor AS '
DECLARE
  ref refcursor;
BEGIN
  OPEN ref FOR SELECT * FROM table1;
  RETURN ref;
END;
' LANGUAGE plpgsql;

import java.sql.*;
public class RefFunc {
  public static void main(String arg[])
      throws Exception {
    System.out.println("RefFunc");
    Class
        .forName("org.postgresql.Driver");
    Connection db = DriverManager
        .getConnection(
            "jdbc:postgresql:dev1",
            "scott", "tiger");
    //
    // Query rows using prepared statement
    //
    System.out.println("Using query:");
    PreparedStatement stmt = db
        .prepareStatement("select * from table1");
    ResultSet rset = stmt
        .executeQuery();
    while (rset.next()) {
      System.out.println(rset
          .getString(1));
    }
    rset.close();
    stmt.close();
    //
    // Query rows using cursor
    //
    System.out.println("Using cursor:");
    db.setAutoCommit(false);
    CallableStatement proc = db
        .prepareCall("{ ? = call reffunc() }");
    proc.registerOutParameter(1,
        Types.OTHER);
    proc.execute();
    ResultSet rset2 = (ResultSet) proc
        .getObject(1);
    while (rset2.next()) {
      System.out.println(rset2
          .getString(1));
    }
    rset2.close();
    proc.close();
    db.close();
  }
}

3 Responses to “Using postgresql ref cursors in java”

1. Kyle Says:
   April 11th, 2004 at 4:22 am

   Isn’t there a way to do this without including the DB password like that?

2. Andrej Says:
   April 11th, 2004 at 10:07 am

   Sure, you can put it in a configuration file, or you can ask the user to enter the password. Web applications usually request a connection from the application server, the password is stored in the data source configuration file (data-sources.xml in oc4j). The web application itself doesn’t have to know the user and password.

3. rambabu Says:
   February 15th, 2007 at 11:32 am

   I tried this cursor program but i am getting the following Exceptions
   Please give me the solution—————————————————————————————————-
   org.postgresql.util.PSQLException: ERROR: cursor “” does not exist

   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1531)
   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1313)
   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)
   at org.postgresql.jdbc2.AbstractJdbc2Connection.execSQLQuery(AbstractJdbc2Connection.java:202)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:179)
   at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2328)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:347)

Leave a Reply