patch for cursor based querys in JDBC

The attached patch cleans up the cursor based query code based on the
discussions Barry Lind and I had some time ago when I provided the
current code.

Here's a sort of ChangeLog.


All changes are against org.postgresql.jdbc1.AbstractJdbc1Statement
unless specifically stated otherwise.


- adds the cursor handling code to the execute() method

- removes the method executeWithCursor

- makes the cursor code only happen when the connection is already in
  transactional state (ie: getAutoCommit() == false)

- makes the cursor code fail calmly (log messages go out but the
  query still works) when the supplied statement already uses a
  cursor or there are multiple statements.

- adds a member varible m_statementIsCursor to identify when to do a
  DEALLOCATE of the current statement name and when not to.

- fail when attempting to do cursor based querys and PS.

- adds the getAutoCommit() / setAutoCommit() methods to BaseConnection
  because all extenders must provide that.


The unidiff is below. Enjoy.


Barry, sorry this has not been done before now. Any chance we can get
it in quickly so I can provide a patch for the CallableStatement
stuff (you'll have noticed that people have started to ask for that
now!).



Nic


Index: src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
retrieving revision 1.18
diff -p -u -r1.18 AbstractJdbc1Statement.java
--- src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java    2003/03/07 18:39:44    1.18
+++ src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java    2003/04/12 23:27:40
@@ -20,8 +20,7 @@ import org.postgresql.util.*;
  */
 public abstract class AbstractJdbc1Statement implements BaseStatement
 {
-
-    // The connection who created us
+        // The connection who created us
     protected BaseConnection connection;

     /** The warnings chain. */
@@ -58,6 +57,7 @@ public abstract class AbstractJdbc1State

     protected String[] m_bindTypes = new String[0];
     protected String m_statementName = null;
+        protected boolean m_statementIsCursor = false;

     private boolean m_useServerPrepare = false;
     private static int m_preparedCount = 1;
@@ -159,7 +159,8 @@ public abstract class AbstractJdbc1State
         {
             try
             {
-                connection.execSQL("DEALLOCATE " + m_statementName);
+                                if (!m_statementIsCursor)
+                                        connection.execSQL("DEALLOCATE " + m_statementName);
             }
             catch (Exception e)
             {
@@ -167,6 +168,7 @@ public abstract class AbstractJdbc1State
             finally
             {
                 m_statementName = null;
+                                m_statementIsCursor = false;
                 m_origSqlFragments = null;
                 m_executeSqlFragments = null;
             }
@@ -183,11 +185,8 @@ public abstract class AbstractJdbc1State
      */
     public java.sql.ResultSet executeQuery() throws SQLException
     {
-        if (fetchSize > 0)
-            this.executeWithCursor();
-        else
-            this.execute();
-
+                this.execute();
+
         while (result != null && !result.reallyResultSet())
             result = (BaseResultSet) result.getNext();
         if (result == null)
@@ -268,9 +267,13 @@ public abstract class AbstractJdbc1State
      * Some prepared statements return multiple results; the execute method
      * handles these complex statements as well as the simpler form of
      * statements handled by executeQuery and executeUpdate
+         *
+         * This method also handles the translation of the query into a cursor based
+         * query if the user has specified a fetch size and set the connection
+         * into a non-auto commit state.
      *
      * @return true if the next result is a ResultSet; false if it is an
-     *         *    update count or there are no more results
+     *         update count or there are no more results
      * @exception SQLException if a database access error occurs
      */
     public boolean execute() throws SQLException
@@ -352,11 +355,76 @@ public abstract class AbstractJdbc1State
                 m_sqlFragments = m_executeSqlFragments;
             }
         }
+
+                // Use a cursor if directed and in a transaction.
+                else if (fetchSize > 0 && !connection.getAutoCommit())
+                {
+                        // The first thing to do is transform the statement text into the cursor form.
+                        String[] cursorBasedSql = new String[m_sqlFragments.length];
+                        // Pinch the prepared count for our own nefarious purposes.
+                        String statementName = "JDBC_CURS_" + m_preparedCount++;
+                        // Setup the cursor decleration.
+                        // Note that we don't need a BEGIN because we've already
+                        // made sure we're executing inside a transaction.
+                        String cursDecl = "DECLARE " + statementName + " CURSOR FOR ";
+                        String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + statementName + ";";
+
+                        // Copy the real query to the curs decleration.
+                        try
+                        {
+                                // Need to confirm this with Barry Lind.
+                                if (cursorBasedSql.length > 1)
+                                        throw new IllegalStateException("cursor fetches not supported with prepared
statements.");
+                                for (int i = 0; i < cursorBasedSql.length; i++)
+                                {
+                                        if (i == 0)
+                                        {
+                                                if (m_sqlFragments[i].trim().toUpperCase().startsWith("DECLARE "))
+                                                        throw new IllegalStateException("statement is already cursor
based.");
+                                                cursorBasedSql[i] = cursDecl;
+                                        }
+
+                                        if (cursorBasedSql[i] != null)
+                                                cursorBasedSql[i] += m_sqlFragments[i];
+                                        else
+                                                cursorBasedSql[i] = m_sqlFragments[i];
+
+                                        if (i == cursorBasedSql.length - 1)
+                                        {
+                                                // We have to be smart about adding the delimitting ";"
+                                                if (m_sqlFragments[i].endsWith(";"))
+                                                        cursorBasedSql[i] += endCurs;
+                                                else
+                                                        cursorBasedSql[i] += (";" + endCurs);
+                                        }
+                                        else if (m_sqlFragments[i].indexOf(";") > -1)
+                                        {
+                                                throw new IllegalStateException("multiple statements not "
+                                                                                + "allowed with cursor based
querys.");
+                                        }
+                                }
+
+                                // Make the cursor based query the one that will be used.
+                                if (org.postgresql.Driver.logDebug)
+                                        org.postgresql.Driver.debug("using cursor based sql with cursor name " +
statementName);
+
+                                // Do all of this after exceptions have been thrown.
+                                m_statementName = statementName;
+                                m_statementIsCursor = true;
+                                m_sqlFragments = cursorBasedSql;
+                        }
+                        catch (IllegalStateException e)
+                        {
+                                // Something went wrong generating the cursor based statement.
+                                if (org.postgresql.Driver.logDebug)
+                                        org.postgresql.Driver.debug(e.getMessage());
+                        }
+                }

-        // New in 7.1, pass Statement so that ExecSQL can customise to it
+        // New in 7.1, pass Statement so that ExecSQL can customise to it
         result = QueryExecutor.execute(m_sqlFragments,
-                                       m_binds,
-                                       this);
+                                               m_binds,
+                                               this);

         //If we are executing a callable statement function set the return data
         if (isFunction)
@@ -379,102 +447,6 @@ public abstract class AbstractJdbc1State
             return (result != null && result.reallyResultSet());
         }
     }
-
-    /** version of execute which converts the query to a cursor.
-     */
-    public boolean executeWithCursor() throws SQLException
-    {
-        if (isFunction && !returnTypeSet)
-            throw new PSQLException("postgresql.call.noreturntype");
-        if (isFunction)
-        { // set entry 1 to dummy entry..
-            m_binds[0] = ""; // dummy entry which ensured that no one overrode
-            m_bindTypes[0] = PG_TEXT;
-            // and calls to setXXX (2,..) really went to first arg in a function call..
-        }
-
-        // New in 7.1, if we have a previous resultset then force it to close
-        // This brings us nearer to compliance, and helps memory management.
-        // Internal stuff will call ExecSQL directly, bypassing this.
-        if (result != null)
-        {
-            java.sql.ResultSet rs = getResultSet();
-            if (rs != null)
-                rs.close();
-        }
-
-        // I've pretty much ignored server prepared statements... can declare and prepare be
-        // used together?
-        // It's trivial to change this: you just have to resolve this issue
-        // of how to work out whether there's a function call. If there isn't then the first
-        // element of the array must be the bit that you extend to become the cursor
-        // decleration.
-        // The last thing that can go wrong is when the user supplies a cursor statement
-        // directly: the translation takes no account of that. I think we should just look
-        // for declare and stop the translation if we find it.
-
-        // The first thing to do is transform the statement text into the cursor form.
-        String[] origSqlFragments = m_sqlFragments;
-        m_sqlFragments = new String[origSqlFragments.length];
-        System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
-        // Pinch the prepared count for our own nefarious purposes.
-        m_statementName = "JDBC_CURS_" + m_preparedCount++;
-        // The static bit to prepend to all querys.
-        String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
-        String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + m_statementName + ";";
-
-        // Add the real query to the curs decleration.
-        // This is the bit that really makes the presumption about
-        // m_sqlFragments not being a function call.
-        if (m_sqlFragments.length < 1)
-            m_sqlFragments[0] = cursDecl + "SELECT NULL;";
-
-        else if (m_sqlFragments.length < 2)
-        {
-            if (m_sqlFragments[0].endsWith(";"))
-                m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
-            else
-                m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
-        }
-        else
-        {
-            m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
-            if (m_sqlFragments[m_sqlFragments.length - 1].endsWith(";"))
-                m_sqlFragments[m_sqlFragments.length - 1] += endCurs;
-            else
-                m_sqlFragments[m_sqlFragments.length - 1] += (";" + endCurs);
-        }
-
-        result = QueryExecutor.execute(m_sqlFragments,
-                                       m_binds,
-                                       this);
-
-        //If we are executing a callable statement function set the return data
-        if (isFunction)
-        {
-            if (!result.reallyResultSet())
-                throw new PSQLException("postgresql.call.noreturnval");
-            if (!result.next ())
-                throw new PSQLException ("postgresql.call.noreturnval");
-            callResult = result.getObject(1);
-            int columnType = result.getMetaData().getColumnType(1);
-            if (columnType != functionReturnType)
-            {
-                Object[] arr =
-                    { "java.sql.Types=" + columnType,
-                      "java.sql.Types=" + functionReturnType
-                    };
-                throw new PSQLException ("postgresql.call.wrongrtntype",arr);
-            }
-            result.close ();
-            return true;
-        }
-        else
-        {
-            return (result != null && result.reallyResultSet());
-        }
-    }
-

     /*
      * setCursorName defines the SQL cursor name that will be used by