refcursor handling patch - Mailing list pgsql-jdbc

The inlined patch allows current CVS to deal with ref cursor return
types.

Barry Lind and I have talked about this implementation before, I
submit it now as similar as it could be to our previous
discussions. However, I think that a couple of things might have
changed that might mean we change the implementation of this:


- the introduction of 'base' classes for implementation might make
  PGRefCursorResultSet obsolete. However, since it still does
  something that the user might want site of I think it's in a
  valuable place.

- the statement class heirarchys should descend from their nearest
  relative, not from the Abstract... version? if not we end up having
  to put nearlly the same code (to create ref cursor result sets) in
  each one.


I'd very much appreciate testing of this, I only have a java1.2
environment for postgresql at the moment. Whilst it works for me
there we need to know it's okay on java1.1 and java1.4.



Nic Ferrier

the patch follows>>>>


Index: org/postgresql/PGRefCursorResultSet.java
===================================================================
RCS file: PGRefCursorResultSet.java
diff -N PGRefCursorResultSet.java
--- /dev/null    Mon Apr 14 18:19:00 2003
+++ PGRefCursorResultSet.java    Mon Apr 14 18:44:12 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: 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
--- org/postgresql/core/BaseStatement.java    2003/03/07 18:39:41    1.1
+++ org/postgresql/core/BaseStatement.java    2003/04/14 22:44:12
@@ -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: 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
--- org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    2003/03/08 06:06:55    1.11
+++ org/postgresql/jdbc1/AbstractJdbc1ResultSet.java    2003/04/14 22:44:13
@@ -575,11 +575,20 @@ public abstract class AbstractJdbc1Resul
                 return getBytes(columnIndex);
             default:
                 String type = field.getPGType();
+
+                                System.err.println("type is: " + type);
+
                 // 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: 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
--- org/postgresql/jdbc1/Jdbc1CallableStatement.java    2003/03/07 18:39:44    1.3
+++ org/postgresql/jdbc1/Jdbc1CallableStatement.java    2003/04/14 22:44:13
@@ -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: 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
--- org/postgresql/jdbc1/Jdbc1PreparedStatement.java    2003/03/07 18:39:44    1.3
+++ org/postgresql/jdbc1/Jdbc1PreparedStatement.java    2003/04/14 22:44:13
@@ -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: org/postgresql/jdbc1/Jdbc1RefCursorResultSet.java
===================================================================
RCS file: Jdbc1RefCursorResultSet.java
diff -N Jdbc1RefCursorResultSet.java
--- /dev/null    Mon Apr 14 18:19:00 2003
+++ Jdbc1RefCursorResultSet.java    Mon Apr 14 18:44:13 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: 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
--- org/postgresql/jdbc1/Jdbc1Statement.java    2003/03/07 18:39:44    1.5
+++ org/postgresql/jdbc1/Jdbc1Statement.java    2003/04/14 22:44:13
@@ -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: 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
--- org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2003/03/25 02:24:07    1.18
+++ org/postgresql/jdbc2/AbstractJdbc2ResultSet.java    2003/04/14 22:44:14
@@ -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: 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
--- org/postgresql/jdbc2/Jdbc2CallableStatement.java    2003/03/07 18:39:45    1.4
+++ org/postgresql/jdbc2/Jdbc2CallableStatement.java    2003/04/14 22:44:14
@@ -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: 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
--- org/postgresql/jdbc2/Jdbc2PreparedStatement.java    2003/03/07 18:39:45    1.4
+++ org/postgresql/jdbc2/Jdbc2PreparedStatement.java    2003/04/14 22:44:15
@@ -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: org/postgresql/jdbc2/Jdbc2RefCursorResultSet.java
===================================================================
RCS file: Jdbc2RefCursorResultSet.java
diff -N Jdbc2RefCursorResultSet.java
--- /dev/null    Mon Apr 14 18:19:00 2003
+++ Jdbc2RefCursorResultSet.java    Mon Apr 14 18:44:15 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: 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
--- org/postgresql/jdbc2/Jdbc2Statement.java    2003/03/07 18:39:45    1.5
+++ org/postgresql/jdbc2/Jdbc2Statement.java    2003/04/14 22:44:15
@@ -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: 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
--- org/postgresql/jdbc3/Jdbc3CallableStatement.java    2003/03/07 18:39:45    1.4
+++ org/postgresql/jdbc3/Jdbc3CallableStatement.java    2003/04/14 22:44:15
@@ -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: 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
--- org/postgresql/jdbc3/Jdbc3PreparedStatement.java    2003/03/07 18:39:45    1.4
+++ org/postgresql/jdbc3/Jdbc3PreparedStatement.java    2003/04/14 22:44:15
@@ -2,6 +2,7 @@ package org.postgresql.jdbc3;


 import java.sql.*;
+import org.postgresql.PGRefCursorResultSet;
 import org.postgresql.core.BaseResultSet;
 import org.postgresql.core.Field;

@@ -18,5 +19,9 @@ public class Jdbc3PreparedStatement exte
         return new Jdbc3ResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
     }

+     public PGRefCursorResultSet createRefCursorResultSet (Field[] fields, java.util.Vector tuples, String status, int
updateCount,long insertOID, boolean binaryCursor) throws SQLException 
+    {
+        return new Jdbc3RefCursorResultSet(this, fields, tuples, status, updateCount, insertOID, binaryCursor);
+    }
 }

Index: org/postgresql/jdbc3/Jdbc3RefCursorResultSet.java
===================================================================
RCS file: Jdbc3RefCursorResultSet.java
diff -N Jdbc3RefCursorResultSet.java
--- /dev/null    Mon Apr 14 18:19:00 2003
+++ Jdbc3RefCursorResultSet.java    Mon Apr 14 18:44:15 2003
@@ -0,0 +1,41 @@
+package org.postgresql.jdbc3;
+
+
+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
+    {
+                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 + "\";" };
+        Jdbc3Connection execr = (Jdbc3Connection) statement.getConnection();
+        execr.execSQL(toExec, new String[0], statement, this);
+        isInitialized = true;
+        return super.next();
+    }
+}
Index: 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
--- org/postgresql/jdbc3/Jdbc3Statement.java    2003/03/07 18:39:45    1.4
+++ org/postgresql/jdbc3/Jdbc3Statement.java    2003/04/14 22:44:15
@@ -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);
+    }
 }