Thread: 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
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)
>
Barry Lind <blind@xythos.com> writes: > 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. That prepared statements (either server side or client side) don't work at all with cursors. They certainly don't seem to. Nic