Thread: refcursor result set patch

Attached is another refcursor result set patch.

This one has some changes that Barry Lind requested. It includes a
test class for running within the postgresql junit framework.


Nic Ferrier

Nic,

Patch applied.

--Barry


Nic Ferrier wrote:
> Attached is another refcursor result set patch.
>
> This one has some changes that Barry Lind requested. It includes a
> test class for running within the postgresql junit framework.
>
>
> Nic Ferrier
>
>
>
> ------------------------------------------------------------------------
>
> Index: src/interfaces/jdbc/org/postgresql/PGRefCursorResultSet.java
> ===================================================================
> RCS file: PGRefCursorResultSet.java
> diff -N PGRefCursorResultSet.java
> --- /dev/null    Wed Apr 30 19:19:00 2003
> +++ PGRefCursorResultSet.java    Wed Apr 30 19:51:26 2003
> @@ -0,0 +1,25 @@
> +/*-------------------------------------------------------------------------
> + *
> + * PGRefCursorResultSet.java
> + *      Describes a PLPGSQL refcursor type.
> + *
> + * Copyright (c) 2003, PostgreSQL Global Development Group
> + *
> + * IDENTIFICATION
> + *      $Header$
> + *
> + *-------------------------------------------------------------------------
> + */
> +package org.postgresql;
> +
> +
> +/** A ref cursor based result set.
> + */
> +public interface PGRefCursorResultSet
> +{
> +
> +        /** return the name of the cursor.
> +         */
> +    public String getRefCursor ();
> +
> +}
> Index: src/interfaces/jdbc/org/postgresql/core/BaseStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/core/BaseStatement.java,v
> retrieving revision 1.1
> diff -p -u -r1.1 BaseStatement.java
> --- src/interfaces/jdbc/org/postgresql/core/BaseStatement.java    2003/03/07 18:39:41    1.1
> +++ src/interfaces/jdbc/org/postgresql/core/BaseStatement.java    2003/04/30 23:51:26
> @@ -12,13 +12,14 @@
>   */
>  package org.postgresql.core;
>
> -
> +import org.postgresql.PGRefCursorResultSet;
>  import java.sql.*;
>  import java.util.Vector;
>
>  public interface BaseStatement extends org.postgresql.PGStatement
>  {
> -    public BaseResultSet createResultSet(Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException; 
> +        public BaseResultSet createResultSet(Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException; 
> +        public PGRefCursorResultSet createRefCursorResultSet(String cursorName) throws SQLException;
>
>      public BaseConnection getPGConnection();
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java,v
> retrieving revision 1.11
> diff -p -u -r1.11 AbstractJdbc1ResultSet.java
> --- src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    2003/03/08 06:06:55    1.11
> +++ src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    2003/04/30 23:51:27
> @@ -575,11 +575,18 @@ public abstract class AbstractJdbc1Resul
>                  return getBytes(columnIndex);
>              default:
>                  String type = field.getPGType();
> +
>                  // if the backend doesn't know the type then coerce to String
>                  if (type.equals("unknown"))
>                  {
>                      return getString(columnIndex);
>                  }
> +                                // Specialized support for ref cursors is neater.
> +                                else if (type.equals("refcursor"))
> +                                {
> +                                        String cursorName = getString(columnIndex);
> +                                        return statement.createRefCursorResultSet(cursorName);
> +                                }
>                  else
>                  {
>                      return connection.getObject(field.getPGType(), getString(columnIndex));
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1CallableStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1CallableStatement.java,v
> retrieving revision 1.3
> diff -p -u -r1.3 Jdbc1CallableStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1CallableStatement.java    2003/03/07 18:39:44    1.3
> +++ src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1CallableStatement.java    2003/04/30 23:51:27
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc1;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -17,6 +18,11 @@ public class Jdbc1CallableStatement exte
>      public BaseResultSet createResultSet (Field[] fields, java.util.Vector tuples, String status, int updateCount,
longinsertOID, boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc1ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +     public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc1RefCursorResultSet(this, cursorName);
>      }
>  }
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1PreparedStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1PreparedStatement.java,v
> retrieving revision 1.3
> diff -p -u -r1.3 Jdbc1PreparedStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1PreparedStatement.java    2003/03/07 18:39:44    1.3
> +++ src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1PreparedStatement.java    2003/04/30 23:51:27
> @@ -2,6 +2,7 @@ package org.postgresql.jdbc1;
>
>
>  import java.sql.*;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -16,5 +17,10 @@ public class Jdbc1PreparedStatement exte
>      public BaseResultSet createResultSet (Field[] fields, java.util.Vector tuples, String status, int updateCount,
longinsertOID, boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc1ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +     public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc1RefCursorResultSet(this, cursorName);
>      }
>  }
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1RefCursorResultSet.java
> ===================================================================
> RCS file: Jdbc1RefCursorResultSet.java
> diff -N Jdbc1RefCursorResultSet.java
> --- /dev/null    Wed Apr 30 19:19:00 2003
> +++ Jdbc1RefCursorResultSet.java    Wed Apr 30 19:51:27 2003
> @@ -0,0 +1,44 @@
> +package org.postgresql.jdbc1;
> +
> +
> +import org.postgresql.core.QueryExecutor;
> +import org.postgresql.core.BaseStatement;
> +import org.postgresql.PGRefCursorResultSet;
> +
> +/** A real result set based on a ref cursor.
> + *
> + * @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
> + */
> +public class Jdbc1RefCursorResultSet extends AbstractJdbc1ResultSet
> +        implements PGRefCursorResultSet
> +{
> +
> +        // The name of the cursor being used.
> +        String refCursorHandle;
> +
> +        // Indicates when the result set has activaly bound to the cursor.
> +        boolean isInitialized = false;
> +
> +
> +        Jdbc1RefCursorResultSet(BaseStatement statement, String refCursorName)
> +        {
> +                super(statement, null, null, null, -1, 0L, false);
> +                this.refCursorHandle = refCursorName;
> +        }
> +
> +        public String getRefCursor ()
> +        {
> +                return refCursorHandle;
> +        }
> +
> +        public boolean next () throws SQLException
> +        {
> +                if (isInitialized)
> +                        return super.next();
> +                // Initialize this res set with the rows from the cursor.
> +                String[] toExec = { "FETCH ALL IN \"" + refCursorHandle + "\";" };
> +                QueryExecutor.execute(toExec, new String[0], this);
> +                isInitialized = true;
> +                return super.next();
> +        }
> +}
> Index: src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Statement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Statement.java,v
> retrieving revision 1.5
> diff -p -u -r1.5 Jdbc1Statement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Statement.java    2003/03/07 18:39:44    1.5
> +++ src/interfaces/jdbc/org/postgresql/jdbc1/Jdbc1Statement.java    2003/04/30 23:51:27
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc1;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -22,5 +23,10 @@ public class Jdbc1Statement extends org.
>      public BaseResultSet createResultSet (Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc1ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +     public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc1RefCursorResultSet(this, cursorName);
>      }
>  }
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java,v
> retrieving revision 1.18
> diff -p -u -r1.18 AbstractJdbc2ResultSet.java
> --- src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2003/03/25 02:24:07    1.18
> +++ src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2003/04/30 23:51:29
> @@ -148,11 +148,18 @@ public abstract class AbstractJdbc2Resul
>
>              default:
>                  String type = field.getPGType();
> +
>                  // if the backend doesn't know the type then coerce to String
>                  if (type.equals("unknown"))
>                  {
>                      return getString(columnIndex);
>                  }
> +                                // Specialized support for ref cursors is neater.
> +                                else if (type.equals("refcursor"))
> +                                {
> +                                        String cursorName = getString(columnIndex);
> +                                        return statement.createRefCursorResultSet(cursorName);
> +                                }
>                  else
>                  {
>                      return connection.getObject(field.getPGType(), getString(columnIndex));
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java,v
> retrieving revision 1.4
> diff -p -u -r1.4 Jdbc2CallableStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java    2003/03/07 18:39:45    1.4
> +++ src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2CallableStatement.java    2003/04/30 23:51:29
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc2;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -17,6 +18,11 @@ public class Jdbc2CallableStatement exte
>      public BaseResultSet createResultSet (Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc2ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc2RefCursorResultSet(this, cursorName);
>      }
>  }
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2PreparedStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2PreparedStatement.java,v
> retrieving revision 1.4
> diff -p -u -r1.4 Jdbc2PreparedStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2PreparedStatement.java    2003/03/07 18:39:45    1.4
> +++ src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2PreparedStatement.java    2003/04/30 23:51:29
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc2;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -17,6 +18,12 @@ public class Jdbc2PreparedStatement exte
>      public BaseResultSet createResultSet (Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc2ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc2RefCursorResultSet(this, cursorName);
>      }
>  }
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2RefCursorResultSet.java
> ===================================================================
> RCS file: Jdbc2RefCursorResultSet.java
> diff -N Jdbc2RefCursorResultSet.java
> --- /dev/null    Wed Apr 30 19:19:00 2003
> +++ Jdbc2RefCursorResultSet.java    Wed Apr 30 19:51:29 2003
> @@ -0,0 +1,43 @@
> +package org.postgresql.jdbc2;
> +
> +
> +import org.postgresql.core.QueryExecutor;
> +import org.postgresql.core.BaseStatement;
> +import org.postgresql.PGRefCursorResultSet;
> +
> +
> +/** A real result set based on a ref cursor.
> + *
> + * @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
> + */
> +public class Jdbc2RefCursorResultSet extends Jdbc2ResultSet
> +        implements PGRefCursorResultSet
> +{
> +
> +        String refCursorHandle;
> +
> +        // Indicates when the result set has activaly bound to the cursor.
> +        boolean isInitialized = false;
> +
> +        Jdbc2RefCursorResultSet(BaseStatement statement, String refCursorName) throws java.sql.SQLException
> +        {
> +                super(statement, null, null, null, -1, 0L, false);
> +                this.refCursorHandle = refCursorName;
> +        }
> +
> +        public String getRefCursor ()
> +        {
> +                return refCursorHandle;
> +        }
> +
> +        public boolean next () throws java.sql.SQLException
> +        {
> +                if (isInitialized)
> +                        return super.next();
> +                // Initialize this res set with the rows from the cursor.
> +                String[] toExec = { "FETCH ALL IN \"" + refCursorHandle + "\";" };
> +                QueryExecutor.execute(toExec, new String[0], this);
> +                isInitialized = true;
> +                return super.next();
> +        }
> +}
> Index: src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Statement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Statement.java,v
> retrieving revision 1.5
> diff -p -u -r1.5 Jdbc2Statement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Statement.java    2003/03/07 18:39:45    1.5
> +++ src/interfaces/jdbc/org/postgresql/jdbc2/Jdbc2Statement.java    2003/04/30 23:51:29
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc2;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -22,5 +23,10 @@ public class Jdbc2Statement extends org.
>      public BaseResultSet createResultSet (Field[] fields, Vector tuples, String status, int updateCount, long
insertOID,boolean binaryCursor) throws SQLException 
>      {
>          return new Jdbc2ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +    }
> +
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc2RefCursorResultSet(this, cursorName);
>      }
>  }
> Index: src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java,v
> retrieving revision 1.4
> diff -p -u -r1.4 Jdbc3CallableStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java    2003/03/07 18:39:45    1.4
> +++ src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3CallableStatement.java    2003/04/30 23:51:29
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc3;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -19,5 +20,9 @@ public class Jdbc3CallableStatement exte
>          return new Jdbc3ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
>      }
>
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc3RefCursorResultSet(this, cursorName);
> +    }
>  }
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java,v
> retrieving revision 1.4
> diff -p -u -r1.4 Jdbc3PreparedStatement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java    2003/03/07 18:39:45    1.4
> +++ src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3PreparedStatement.java    2003/04/30 23:51:29
> @@ -2,7 +2,9 @@ package org.postgresql.jdbc3;
>
>
>  import java.sql.*;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
> +import org.postgresql.core.BaseStatement;
>  import org.postgresql.core.Field;
>
>  public class Jdbc3PreparedStatement extends org.postgresql.jdbc3.AbstractJdbc3Statement implements
java.sql.PreparedStatement
> @@ -15,8 +17,12 @@ public class Jdbc3PreparedStatement exte
>
>      public BaseResultSet createResultSet (Field[] fields, java.util.Vector tuples, String status, int updateCount,
longinsertOID, boolean binaryCursor) throws SQLException 
>      {
> -        return new Jdbc3ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
> +                return new Jdbc3ResultSet((BaseStatement)this, fields, tuples, status, updateCount, insertOID,
binaryCursor);
>      }
> -
> +
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc3RefCursorResultSet(this, cursorName);
> +    }
>  }
>
> Index: src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3RefCursorResultSet.java
> ===================================================================
> RCS file: Jdbc3RefCursorResultSet.java
> diff -N Jdbc3RefCursorResultSet.java
> --- /dev/null    Wed Apr 30 19:19:00 2003
> +++ Jdbc3RefCursorResultSet.java    Wed Apr 30 19:51:30 2003
> @@ -0,0 +1,47 @@
> +package org.postgresql.jdbc3;
> +
> +import org.postgresql.core.QueryExecutor;
> +import org.postgresql.core.Field;
> +import org.postgresql.core.BaseStatement;
> +import java.util.Vector;
> +import org.postgresql.PGConnection;
> +import org.postgresql.PGRefCursorResultSet;
> +
> +/** A real result set based on a ref cursor.
> + *
> + * @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
> + */
> +public class Jdbc3RefCursorResultSet extends Jdbc3ResultSet implements PGRefCursorResultSet
> +{
> +
> +    String refCursorHandle;
> +
> +    // Indicates when the result set has activaly bound to the cursor.
> +    boolean isInitialized = false;
> +
> +    Jdbc3RefCursorResultSet(java.sql.Statement statement, String refCursorName) throws java.sql.SQLException
> +    {
> +                // This casting is a GCJ requirement.
> +                super((BaseStatement)statement,
> +                      (Field[])null,
> +                      (Vector)null,
> +                      (String)null, -1, 0L, false);
> +                this.refCursorHandle = refCursorName;
> +    }
> +
> +    public String getRefCursor ()
> +    {
> +        return refCursorHandle;
> +    }
> +
> +    public boolean next () throws java.sql.SQLException
> +    {
> +        if (isInitialized)
> +            return super.next();
> +        // Initialize this res set with the rows from the cursor.
> +        String[] toExec = { "FETCH ALL IN \"" + refCursorHandle + "\";" };
> +                QueryExecutor.execute(toExec, new String[0], this);
> +        isInitialized = true;
> +        return super.next();
> +    }
> +}
> Index: src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Statement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Statement.java,v
> retrieving revision 1.4
> diff -p -u -r1.4 Jdbc3Statement.java
> --- src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Statement.java    2003/03/07 18:39:45    1.4
> +++ src/interfaces/jdbc/org/postgresql/jdbc3/Jdbc3Statement.java    2003/04/30 23:51:30
> @@ -3,6 +3,7 @@ package org.postgresql.jdbc3;
>
>  import java.sql.*;
>  import java.util.Vector;
> +import org.postgresql.PGRefCursorResultSet;
>  import org.postgresql.core.BaseResultSet;
>  import org.postgresql.core.Field;
>
> @@ -24,4 +25,8 @@ public class Jdbc3Statement extends org.
>          return new Jdbc3ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
>      }
>
> +      public PGRefCursorResultSet createRefCursorResultSet (String cursorName) throws SQLException
> +    {
> +                return new Jdbc3RefCursorResultSet(this, cursorName);
> +    }
>  }
> Index: src/interfaces/jdbc/org/postgresql/test/jdbc2/RefCursorTest.java
> ===================================================================
> RCS file: RefCursorTest.java
> diff -N RefCursorTest.java
> --- /dev/null    Wed Apr 30 19:19:00 2003
> +++ RefCursorTest.java    Wed Apr 30 19:51:30 2003
> @@ -0,0 +1,99 @@
> +package org.postgresql.test.jdbc2;
> +
> +import org.postgresql.test.TestUtil;
> +import junit.framework.TestCase;
> +import java.io.*;
> +import java.sql.*;
> +
> +/*
> + * RefCursor ResultSet tests.
> + * This test case is basically the same as the ResultSet test case.
> + *
> + * @author Nic Ferrier <nferrier@tapsellferrier.co.uk>
> + */
> +public class RefCursorTest extends TestCase
> +{
> +    private Connection con;
> +
> +    public RefCursorTest(String name)
> +    {
> +        super(name);
> +    }
> +
> +    protected void setUp() throws Exception
> +    {
> +                // this is the same as the ResultSet setup.
> +        con = TestUtil.openDB();
> +        Statement stmt = con.createStatement();
> +
> +        TestUtil.createTable(con, "testrs", "id integer");
> +
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (1)");
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (2)");
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (3)");
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (4)");
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (6)");
> +        stmt.executeUpdate("INSERT INTO testrs VALUES (9)");
> +
> +
> +                // Create the functions.
> +                stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getRefcursor () RETURNS refcursor AS '"
> +                              + "declare v_resset; begin open v_resset for select id from testrs; "
> +                              + "return v_resset; end;' LANGUAGE 'plpgsql';");
> +                stmt.execute ("CREATE OR REPLACE FUNCTION testspg__getEmptyRefcursor () RETURNS refcursor AS '"
> +                              + "declare v_resset; begin open v_resset for select id from testrs where id < 1; "
> +                              + "return v_resset; end;' LANGUAGE 'plpgsql';");
> +                stmt.close();
> +    }
> +
> +    protected void tearDown() throws Exception
> +    {
> +                Statement stmt = con.createStatement ();
> +        stmt.execute ("drop FUNCTION testspg__getRefcursor ();");
> +        TestUtil.dropTable(con, "testrs");
> +        TestUtil.closeDB(con);
> +    }
> +
> +    public void testResult() throws Exception
> +    {
> +        CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
> +        call.registerOutParameter(1, Types.OTHER);
> +        call.execute();
> +                ResultSet rs = (ResultSet) call.getObject(1);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 1);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 2);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 3);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 4);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 6);
> +
> +                assertTrue(rs.next());
> +                assertTrue(rs.getInt(1) == 9);
> +
> +                assertTrue(!rs.next());
> +
> +        call.close();
> +    }
> +
> +
> +        public void testEmptyResult() throws Exception
> +    {
> +                CallableStatement call = con.prepareCall("{ ? = call testspg__getRefcursor () }");
> +                call.registerOutParameter(1, Types.OTHER);
> +                call.execute();
> +
> +                ResultSet rs = (ResultSet) call.getObject(1);
> +                assertTrue(!rs.next());
> +
> +                call.close();
> +    }
> +}
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

The changes to JDBC stored proc calls (made a while ago) highlighted
some bits missing in the manual.

This fills them in.



--
Nic Ferrier
http://www.tapsellferrier.co.uk

Hi Nic,

It is great to see someone trying to improve the documentation.

I have a few questions about the first part (about fetch size):



> +
> +     <para>Changing code to cursor mode is as simple as setting the
> +       fetch size of the <classname>Statement</classname> to the
> +       appropriate size. Setting the fecth size back to 0 will cause
> +       all rows to be cached (the default behaviour).
> +

Perhaps you should mention the retrieval all rows must happen inside a
transaction.  The cursor that is created is not HOLD or anything.


> +<programlisting>
> +Statement st = db.createStatement();
> +// Turn use of the cursor on.
> +st.setFetchSize(50);
> +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> +while (rs.next()) {
> +   System.out.print("a row was returned.");
> +}
> +rs.close();
> +// Turn the cursor off.
> +st.setFetchSize(0);
> +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> +while (rs.next()) {
> +   System.out.print("many rows were returned.");
> +}
> +rs.close();
> +// Close the statement.
> +st.close();
> +</programlisting>
> +     </para>
> +
> +


I do not understand what the example is supposed to show.  The fetch
size is transparent to the user (it is just a hint to the driver about
what should be the best strategy) so you should be able to get all the
rows, and the same number of them, as you go doing rs.next().


Regards,
Fernando



--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fernando Nasser <fnasser@redhat.com> writes:

> > +
> > +     <para>Changing code to cursor mode is as simple as setting the
> > +       fetch size of the <classname>Statement</classname> to the
> > +       appropriate size. Setting the fecth size back to 0 will cause
> > +       all rows to be cached (the default behaviour).
> > +
>
> Perhaps you should mention the retrieval all rows must happen inside a
> transaction.  The cursor that is created is not HOLD or anything.

Ok. I'll do that.


> > +<programlisting>
> > +Statement st = db.createStatement();
> > +// Turn use of the cursor on.
> > +st.setFetchSize(50);
> > +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> > +while (rs.next()) {
> > +   System.out.print("a row was returned.");
> > +}
> > +rs.close();
> > +// Turn the cursor off.
> > +st.setFetchSize(0);
> > +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> > +while (rs.next()) {
> > +   System.out.print("many rows were returned.");
> > +}
> > +rs.close();
> > +// Close the statement.
> > +st.close();
> > +</programlisting>
> > +     </para>
> > +
> > +
>
> I do not understand what the example is supposed to show.  The fetch
> size is transparent to the user (it is just a hint to the driver about
> what should be the best strategy) so you should be able to get all the
> rows, and the same number of them, as you go doing rs.next().

The example is supposed to show that you get different behaviour with
different values. The latency on the first call with be lower (with
large result sets) than the latency on the second call.


Nic

Nic wrote:
> Fernando Nasser <fnasser@redhat.com> writes:
>
>
>>>+
>>>+     <para>Changing code to cursor mode is as simple as setting the
>>>+       fetch size of the <classname>Statement</classname> to the
>>>+       appropriate size. Setting the fecth size back to 0 will cause
>>>+       all rows to be cached (the default behaviour).
>>>+
>>
>>
>>Perhaps you should mention the retrieval all rows must happen inside a
>>transaction.  The cursor that is created is not HOLD or anything.
>
>
> Ok. I'll do that.
>
>
>
>>>+<programlisting>
>>>+Statement st = db.createStatement();
>>>+// Turn use of the cursor on.
>>>+st.setFetchSize(50);
>>>+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
>>>+while (rs.next()) {
>>>+   System.out.print("a row was returned.");
>>>+}
>>>+rs.close();
>>>+// Turn the cursor off.
>>>+st.setFetchSize(0);
>>>+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
>>>+while (rs.next()) {
>>>+   System.out.print("many rows were returned.");
>>>+}
>>>+rs.close();
>>>+// Close the statement.
>>>+st.close();
>>>+</programlisting>
>>>+     </para>
>>>+
>>>+
>>
>>
>>I do not understand what the example is supposed to show.  The fetch
>>size is transparent to the user (it is just a hint to the driver about
>>what should be the best strategy) so you should be able to get all the
>>rows, and the same number of them, as you go doing rs.next().
>
>
> The example is supposed to show that you get different behaviour with
> different values. The latency on the first call with be lower (with
> large result sets) than the latency on the second call.

I'm confused: this appears to say that setFetchSize() now works in some
version of PG JDBC. As of the JDBC that builds with PG 7.3.3,
setFetchSize() claims it's "unimplemented." Also, this looks like
straight client-side Java code; what's the "stored procs" part of the
patch? We don't have Java inside PG yet, do we?
                                 REC

Nic wrote:
>
>>>+<programlisting>
>>>+Statement st = db.createStatement();
>>>+// Turn use of the cursor on.
>>>+st.setFetchSize(50);
>>>+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
>>>+while (rs.next()) {
>>>+   System.out.print("a row was returned.");
>>>+}
>>>+rs.close();
>>>+// Turn the cursor off.
>>>+st.setFetchSize(0);
>>>+ResultSet rs = st.executeQuery("SELECT * FROM mytable");
>>>+while (rs.next()) {
>>>+   System.out.print("many rows were returned.");
>>>+}
>>>+rs.close();
>>>+// Close the statement.
>>>+st.close();
>>>+</programlisting>
>>>+     </para>
>>>+
>>>+
>>
>>
>>I do not understand what the example is supposed to show.  The fetch
>>size is transparent to the user (it is just a hint to the driver about
>>what should be the best strategy) so you should be able to get all the
>>rows, and the same number of them, as you go doing rs.next().
>
>
> The example is supposed to show that you get different behaviour with
> different values. The latency on the first call with be lower (with
> large result sets) than the latency on the second call.
>

I see, the person running it is supposed to notice a small delay between
the 50th and the 51st "a row was returned.", right?

Maybe you can make it more obvious.  You can number the lines printed
and add a comment saying that small delays _may_ be noticed every 50 lines.

Also, to make the example more realistic you could add some time
measurement for the two blocks (with and without setting the fetch size)
and suggest that the programmer play with the fetch size to find out
what is the best setting for his application needs.

Thanks for the clarifications.

Best regards,
Fernando

--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Rich Cullingford wrote:
>
> I'm confused: this appears to say that setFetchSize() now works in some
> version of PG JDBC. As of the JDBC that builds with PG 7.3.3,
> setFetchSize() claims it's "unimplemented." Also, this looks like
> straight client-side Java code; what's the "stored procs" part of the
> patch? We don't have Java inside PG yet, do we?
>

I believe that is supposed to go with the patch that is pending in the list.

If you add all pending patches to the driver you get what Red Hat and
Fujitsu have today, a fully compliant JDBC driver.  Both Red Hat and
Fujitsu have donated all the compliance work to the community.
Hopefully you will get all this in the 7.4 driver.  Just give Barry and
Dave some time to review and incorporate the changes.

Regards,
Fernando

--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Rich Cullingford <rculling@sysd.com> writes:

> I'm confused: this appears to say that setFetchSize() now works in some
> version of PG JDBC. As of the JDBC that builds with PG 7.3.3,
> setFetchSize() claims it's "unimplemented." Also, this looks like
> straight client-side Java code; what's the "stored procs" part of the
> patch? We don't have Java inside PG yet, do we?

The stuff supporting this doc patch is all in CVS.

The stored procs part of the patch is documentation (that was missing
previously) on the use of CallableStatment.

There is also stored proc related doc on an entirely new feature of
the JDBC driver, the ability to seamlessly handle refcursor return
values from procs.


--
Nic Ferrier
http://www.tapsellferrier.co.uk

Patch applied.

thanks,
--Barry


Nic wrote:
> The changes to JDBC stored proc calls (made a while ago) highlighted
> some bits missing in the manual.
>
> This fills them in.
>
>
>
>
> ------------------------------------------------------------------------
>
> Index: doc/src/sgml/jdbc.sgml
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/jdbc.sgml,v
> retrieving revision 1.45
> diff -u -r1.45 jdbc.sgml
> --- doc/src/sgml/jdbc.sgml    30 Jun 2003 16:39:42 -0000    1.45
> +++ doc/src/sgml/jdbc.sgml    29 Jul 2003 21:48:44 -0000
> @@ -323,8 +323,9 @@
>      a <classname>Statement</classname> or
>      <classname>PreparedStatement</classname>, you can use issue a
>      query. This will return a <classname>ResultSet</classname>
> -    instance, which contains the entire result.  <xref
> -    linkend="jdbc-query-example"> illustrates this process.
> +    instance, which contains the entire result (see <xref linkend="jdbc-query-with-cursor">
> +    here for how to alter this behaviour).
> +    <xref linkend="jdbc-query-example"> illustrates this process.
>     </para>
>
>     <example id="jdbc-query-example">
> @@ -364,6 +365,50 @@
>      </para>
>     </example>
>
> +   <sect2 id="query-with-cursor">
> +     <title>Getting results based on a cursor</title>
> +
> +     <para>By default the driver collects all the results for the
> +       query at once. This can be inconvieniant for large data sets so
> +       the JDBC driver provides a means of basing
> +       a <classname>ResultSet</classname> on a database cursor and
> +       only fetching a small number of rows.</para>
> +
> +     <para>A small number of rows are cached on the
> +       client side of the connection and when exhausted the next
> +       block of rows is retrieved by repositioning the cursor.
> +     </para>
> +
> +     <example>
> +       <title>Setting fetch size to turn cursors on and off.</title>
> +
> +     <para>Changing code to cursor mode is as simple as setting the
> +       fetch size of the <classname>Statement</classname> to the
> +       appropriate size. Setting the fecth size back to 0 will cause
> +       all rows to be cached (the default behaviour).
> +
> +<programlisting>
> +Statement st = db.createStatement();
> +// Turn use of the cursor on.
> +st.setFetchSize(50);
> +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> +while (rs.next()) {
> +   System.out.print("a row was returned.");
> +}
> +rs.close();
> +// Turn the cursor off.
> +st.setFetchSize(0);
> +ResultSet rs = st.executeQuery("SELECT * FROM mytable");
> +while (rs.next()) {
> +   System.out.print("many rows were returned.");
> +}
> +rs.close();
> +// Close the statement.
> +st.close();
> +</programlisting>
> +     </para>
> +
> +
>     <sect2>
>      <title>Using the <classname>Statement</classname> or <classname>PreparedStatement</classname> Interface</title>
>
> @@ -493,6 +538,120 @@
>     </para>
>    </example>
>    </sect1>
> +
> +
> +  <sect1 id="jdbc-callproc">
> +    <title>Calling Stored Functions</title>
> +
> +    <para><productname>PostgreSQL's</productname> jdbc driver fully
> +      supports calling <productname>PostgreSQL</productname> stored
> +      functions.</para>
> +
> +    <example id="jdbc-call-function">
> +      <title>Calling a built in stored function</title>
> +
> +      <para>This example shows how to call
> +    a <productname>PostgreSQL</productname> built in
> +    function, <command>upper</command>, which simply converts the
> +    supplied string argument to uppercase.
> +
> +<programlisting>
> +// Turn transactions off.
> +con.setAutoCommit(false);
> +// Procedure call.
> +CallableStatement upperProc = con.prepareCall("{ ? = call upper( ? ) }");
> +upperProc.registerOutParameter(1, Types.VARCHAR);
> +upperProc.setString(2, "lowercase to uppercase");
> +upperProc.execute();
> +String upperCased = upperProc.getString(1);
> +upperProc.close();
> +</programlisting>
> +      </para>
> +    </example>
> +
> +    <sect2>
> +      <title>Using the <classname>CallableStatement</classname> Interface</title>
> +
> +      <para>
> +    All the considerations that apply
> +    for <classname>Statement</classname>
> +    and <classname>PreparedStatement</classname> apply
> +    for <classname>CallableStatement</classname> but in addition
> +    you must also consider one extra restriction:
> +      </para>
> +
> +      <itemizedlist>
> +    <listitem>
> +      <para>You can only call a stored function from within a
> +        transaction.</para>
> +    </listitem>
> +      </itemizedlist>
> +
> +    </sect2>
> +
> +    <sect2>
> +      <title>Obtaining <classname>ResultSet</classname> from a stored function</title>
> +
> +      <para><productname>PostgreSQL's</productname> stored function
> +    can return results by means of a <type>refcursor</type>
> +    value. A <type>refcursor</type>.</para>
> +
> +      <para>As an extension to JDBC,
> +    the <productname>PostgreSQL</productname> JDBC driver can
> +    return <type>refcursor</type> values
> +    as <classname>ResultSet</classname> values.</para>
> +
> +      <example id="get-refcursor-from-function-call">
> +    <title>Gettig <type>refcursor</type> values from a
> +    function</title>
> +
> +    <para>When calling a function that returns
> +      a <type>refcursor</type> you must cast the return type
> +      of <methodname>getObject</methodname> to
> +      a <classname>ResultSet</classname></para>
> +
> +<programlisting>
> +// Turn transactions off.
> +con.setAutoCommit(false);
> +// Procedure call.
> +CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
> +proc.registerOutParameter(1, Types.Other);
> +proc.setInt(2, -1);
> +proc.execute();
> +ResultSet results = (ResultSet) proc.getObject(1);
> +while (results.next()) {
> +  // do something with the results...
> +}
> +results.close();
> +proc.close();
> +</programlisting>
> +      </example>
> +
> +      <para>It is also possible to treat the <type>refcursor</type>
> +      return value as a distinct type in itself. The JDBC driver
> +    provides
> +      the <classname>org.postgresql.PGRefCursorResultSet</classname>
> +    class for this purpose.</para>
> +
> +      <example>
> +    <title>Treating <type>refcursor</type> as a distinct
> +    type</title>
> +
> +<programlisting>
> +con.setAutoCommit(false);
> +CallableStatement proc = con.prepareCall("{ ? = call doquery ( ? ) }");
> +proc.registerOutParameter(1, Types.Other);
> +proc.setInt(2, 0);
> +org.postgresql.PGRefCursorResultSet refcurs
> +    = (PGRefCursorResultSet) con.getObject(1);
> +String cursorName = refcurs.getRefCursor();
> +proc.close();
> +</programlisting>
> +      </example>
> +    </sect2>
> +
> +  </sect1>
> +
>
>    <sect1 id="jdbc-ddl">
>     <title>Creating and Modifying Database Objects</title>
>
>
> ------------------------------------------------------------------------
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)