Re: [JDBC] patch for cursor based querys in JDBC

Nic,

Patch applied.

I have one questions about the patch though.  There is a comment that
states "Need to confirm this with Barry Lind."  I wasn't sure what you
wanted to confirm with me.

thanks,
--Barry


Nic Ferrier wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>