Re: refcursor result set patch - Mailing list pgsql-jdbc

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