From 7ecb6ede284fdf9f138028cc9db042c316e2f65e Mon Sep 17 00:00:00 2001 From: Dave Cramer Date: Wed, 29 Oct 2003 02:39:10 +0000 Subject: [PATCH] Patches from Oliver Jowett to fix CursorFetchTest, 7.4 now does not automatically delete cursors --- .../org/postgresql/core/BaseConnection.java | 4 +- .../org/postgresql/core/BaseStatement.java | 6 +- .../jdbc1/AbstractJdbc1Connection.java | 5 +- .../jdbc1/AbstractJdbc1ResultSet.java | 71 ++- .../jdbc1/AbstractJdbc1Statement.java | 437 ++++++++++-------- .../jdbc2/AbstractJdbc2ResultSet.java | 16 +- .../test/jdbc2/CursorFetchTest.java | 112 +++++ 7 files changed, 416 insertions(+), 235 deletions(-) diff --git a/src/interfaces/jdbc/org/postgresql/core/BaseConnection.java b/src/interfaces/jdbc/org/postgresql/core/BaseConnection.java index 30a4ba909a..a463d4120c 100644 --- a/src/interfaces/jdbc/org/postgresql/core/BaseConnection.java +++ b/src/interfaces/jdbc/org/postgresql/core/BaseConnection.java @@ -6,7 +6,7 @@ * Copyright (c) 2003, PostgreSQL Global Development Group * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseConnection.java,v 1.3 2003/05/29 03:21:32 barry Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseConnection.java,v 1.4 2003/10/29 02:39:09 davec Exp $ * *------------------------------------------------------------------------- */ @@ -26,7 +26,7 @@ public interface BaseConnection extends PGConnection public void cancelQuery() throws SQLException; public Statement createStatement() throws SQLException; public BaseResultSet execSQL(String s) throws SQLException; - public boolean getAutoCommit() throws SQLException; + public boolean getAutoCommit(); public String getCursorName() throws SQLException; public Encoding getEncoding() throws SQLException; public DatabaseMetaData getMetaData() throws SQLException; diff --git a/src/interfaces/jdbc/org/postgresql/core/BaseStatement.java b/src/interfaces/jdbc/org/postgresql/core/BaseStatement.java index c91e259e1d..71fc85ff9e 100644 --- a/src/interfaces/jdbc/org/postgresql/core/BaseStatement.java +++ b/src/interfaces/jdbc/org/postgresql/core/BaseStatement.java @@ -6,7 +6,7 @@ * Copyright (c) 2003, PostgreSQL Global Development Group * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseStatement.java,v 1.5 2003/08/24 22:10:09 barry Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseStatement.java,v 1.6 2003/10/29 02:39:09 davec Exp $ * *------------------------------------------------------------------------- */ @@ -30,11 +30,11 @@ public interface BaseStatement extends org.postgresql.PGStatement */ public void addWarning(String p_warning) throws SQLException; public void close() throws SQLException; - public int getFetchSize() throws SQLException; + public int getFetchSize(); public int getMaxFieldSize() throws SQLException; public int getMaxRows() throws SQLException; public int getResultSetConcurrency() throws SQLException; - public String getStatementName(); + public String getFetchingCursorName(); public SQLWarning getWarnings() throws SQLException; public void setMaxFieldSize(int max) throws SQLException; diff --git a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java index e3146a11c9..fd451f5db7 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java @@ -9,7 +9,7 @@ * Copyright (c) 2003, PostgreSQL Global Development Group * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Connection.java,v 1.26 2003/09/13 04:02:15 barry Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Connection.java,v 1.27 2003/10/29 02:39:09 davec Exp $ * *------------------------------------------------------------------------- */ @@ -1270,10 +1270,9 @@ public abstract class AbstractJdbc1Connection implements BaseConnection * gets the current auto-commit state * * @return Current state of the auto-commit mode - * @exception SQLException (why?) * @see setAutoCommit */ - public boolean getAutoCommit() throws SQLException + public boolean getAutoCommit() { return this.autoCommit; } diff --git a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java index 67071fa84f..eb7df0cd49 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1ResultSet.java @@ -9,7 +9,7 @@ * Copyright (c) 2003, PostgreSQL Global Development Group * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1ResultSet.java,v 1.21 2003/09/22 04:54:59 barry Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1ResultSet.java,v 1.22 2003/10/29 02:39:09 davec Exp $ * *------------------------------------------------------------------------- */ @@ -61,6 +61,9 @@ public abstract class AbstractJdbc1ResultSet implements BaseResultSet private SimpleDateFormat m_tstzFormat = null; private SimpleDateFormat m_dateFormat = null; + private int fetchSize; // Fetch size for next read (might be 0). + private int lastFetchSize; // Fetch size of last read (might be 0). + public abstract ResultSetMetaData getMetaData() throws SQLException; public AbstractJdbc1ResultSet(BaseStatement statement, @@ -82,6 +85,8 @@ public abstract class AbstractJdbc1ResultSet implements BaseResultSet this.this_row = null; this.current_row = -1; this.binaryCursor = binaryCursor; + + this.lastFetchSize = this.fetchSize = (statement == null ? 0 : statement.getFetchSize()); } public BaseStatement getPGStatement() { @@ -111,7 +116,21 @@ public abstract class AbstractJdbc1ResultSet implements BaseResultSet this.current_row = -1; this.binaryCursor = binaryCursor; } + + // + // Part of the JDBC2 support, but convenient to implement here. + // + public void setFetchSize(int rows) throws SQLException + { + fetchSize = rows; + } + + + public int getFetchSize() throws SQLException + { + return fetchSize; + } public boolean next() throws SQLException { @@ -120,30 +139,32 @@ public abstract class AbstractJdbc1ResultSet implements BaseResultSet if (++current_row >= rows.size()) { - int fetchSize = statement.getFetchSize(); - // Must be false if we weren't batching. - if (fetchSize == 0) - return false; - // Use the ref to the statement to get - // the details we need to do another cursor - // query - it will use reinit() to repopulate this - // with the right data. - String[] sql = new String[1]; - String[] binds = new String[0]; - // Is this the correct query??? - String cursorName = statement.getStatementName(); - //if cursorName is null, we are not batching (likely because the - //query itself can't be batched) - if (cursorName == null) - return false; - sql[0] = "FETCH FORWARD " + fetchSize + " FROM " + cursorName; - QueryExecutor.execute(sql, - binds, - this); - - // Test the new rows array. - if (rows.size() == 0) - return false; + String cursorName = statement.getFetchingCursorName(); + if (cursorName == null || lastFetchSize == 0 || rows.size() < lastFetchSize) + return false; // Not doing a cursor-based fetch or the last fetch was the end of the query + + // Use the ref to the statement to get + // the details we need to do another cursor + // query - it will use reinit() to repopulate this + // with the right data. + + // NB: We can reach this point with fetchSize == 0 + // if the fetch size is changed halfway through reading results. + // Use "FETCH FORWARD ALL" in that case to complete the query. + String[] sql = new String[] { + fetchSize == 0 ? ("FETCH FORWARD ALL FROM " + cursorName) : + ("FETCH FORWARD " + fetchSize + " FROM " + cursorName) + }; + + QueryExecutor.execute(sql, + new String[0], + this); + + // Test the new rows array. + lastFetchSize = fetchSize; + if (rows.size() == 0) + return false; + // Otherwise reset the counter and let it go on... current_row = 0; } diff --git a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java index 0a11f3a3b0..ad4db8cd37 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java @@ -26,7 +26,7 @@ import java.sql.Timestamp; import java.sql.Types; import java.util.Vector; -/* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Statement.java,v 1.40 2003/10/09 01:17:07 wieck Exp $ +/* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Statement.java,v 1.41 2003/10/29 02:39:09 davec Exp $ * This class defines methods of the jdbc1 specification. This class is * extended by org.postgresql.jdbc2.AbstractJdbc2Statement which adds the jdbc2 * methods. The real Statement class (for jdbc1) is org.postgresql.jdbc1.Jdbc1Statement @@ -62,15 +62,25 @@ public abstract class AbstractJdbc1Statement implements BaseStatement // Some performance caches private StringBuffer sbuf = new StringBuffer(32); - //Used by the preparedstatement style methods - protected String[] m_sqlFragments; - private String[] m_origSqlFragments; - private String[] m_executeSqlFragments; - protected Object[] m_binds = new Object[0]; - - protected String[] m_bindTypes = new String[0]; - protected String m_statementName = null; - protected boolean m_statementIsCursor = false; + protected String[] m_sqlFragments; // Query fragments. + private String[] m_executeSqlFragments; // EXECUTE(...) if useServerPrepare + protected Object[] m_binds = new Object[0]; // Parameter values + + protected String[] m_bindTypes = new String[0]; // Parameter types, for PREPARE(...) + protected String m_statementName = null; // Allocated PREPARE statement name for server-prepared statements + protected String m_cursorName = null; // Allocated DECLARE cursor name for cursor-based fetch + + // Constants for allowXXX and m_isSingleStatement vars, below. + // The idea is to defer the cost of examining the query until we really need to know, + // but don't reexamine it every time thereafter. + + private static final short UNKNOWN = 0; // Don't know yet, examine the query. + private static final short NO = 1; // Don't use feature + private static final short YES = 2; // Do use feature + + private short m_isSingleDML = UNKNOWN; // Is the query a single SELECT/UPDATE/INSERT/DELETE? + private short m_isSingleSelect = UNKNOWN; // Is the query a single SELECT? + private short m_isSingleStatement = UNKNOWN; // Is the query a single statement? private boolean m_useServerPrepare = false; @@ -115,11 +125,11 @@ public abstract class AbstractJdbc1Statement implements BaseStatement return connection; } - public String getStatementName() { - return m_statementName; + public String getFetchingCursorName() { + return m_cursorName; } - public int getFetchSize() throws SQLException { + public int getFetchSize() { return fetchSize; } @@ -138,6 +148,9 @@ public abstract class AbstractJdbc1Statement implements BaseStatement boolean inQuotes = false; int lastParmEnd = 0, i; + m_isSingleSelect = m_isSingleDML = UNKNOWN; + m_isSingleStatement = YES; + for (i = 0; i < l_sql.length(); ++i) { int c = l_sql.charAt(i); @@ -149,6 +162,8 @@ public abstract class AbstractJdbc1Statement implements BaseStatement v.addElement(l_sql.substring (lastParmEnd, i)); lastParmEnd = i + 1; } + if (c == ';' && !inQuotes) + m_isSingleStatement = m_isSingleSelect = m_isSingleDML = NO; } v.addElement(l_sql.substring (lastParmEnd, l_sql.length())); @@ -161,39 +176,46 @@ public abstract class AbstractJdbc1Statement implements BaseStatement } - /* - * Execute a SQL statement that retruns a single ResultSet - * - * @param sql typically a static SQL SELECT statement - * @return a ResulSet that contains the data produced by the query - * @exception SQLException if a database access error occurs + * Deallocate resources allocated for the current query + * in preparation for replacing it with a new query. */ - public java.sql.ResultSet executeQuery(String p_sql) throws SQLException - { - String l_sql = replaceProcessing(p_sql); - m_sqlFragments = new String[] {l_sql}; - m_binds = new Object[0]; + private void deallocateQuery() + { //If we have already created a server prepared statement, we need //to deallocate the existing one if (m_statementName != null) { try { - if (!m_statementIsCursor) - connection.execSQL("DEALLOCATE " + m_statementName); + connection.execSQL("DEALLOCATE " + m_statementName); } catch (Exception e) { } - finally - { - m_statementName = null; - m_statementIsCursor = false; - m_origSqlFragments = null; - m_executeSqlFragments = null; - } } + + m_statementName = null; + m_cursorName = null; // automatically closed at end of txn anyway + m_executeSqlFragments = null; + m_isSingleStatement = m_isSingleSelect = m_isSingleDML = UNKNOWN; + } + + /* + * Execute a SQL statement that retruns a single ResultSet + * + * @param sql typically a static SQL SELECT statement + * @return a ResulSet that contains the data produced by the query + * @exception SQLException if a database access error occurs + */ + public java.sql.ResultSet executeQuery(String p_sql) throws SQLException + { + deallocateQuery(); + + String l_sql = replaceProcessing(p_sql); + m_sqlFragments = new String[] {l_sql}; + m_binds = new Object[0]; + return executeQuery(); } @@ -226,17 +248,12 @@ public abstract class AbstractJdbc1Statement implements BaseStatement */ public int executeUpdate(String p_sql) throws SQLException { + deallocateQuery(); + String l_sql = replaceProcessing(p_sql); m_sqlFragments = new String[] {l_sql}; m_binds = new Object[0]; - //If we have already created a server prepared statement, we need - //to deallocate the existing one - if (m_statementName != null) { - connection.execSQL("DEALLOCATE " + m_statementName); - m_statementName = null; - m_origSqlFragments = null; - m_executeSqlFragments = null; - } + return executeUpdate(); } @@ -270,28 +287,199 @@ public abstract class AbstractJdbc1Statement implements BaseStatement */ public boolean execute(String p_sql) throws SQLException { + deallocateQuery(); + String l_sql = replaceProcessing(p_sql); m_sqlFragments = new String[] {l_sql}; m_binds = new Object[0]; - //If we have already created a server prepared statement, we need - //to deallocate the existing one - if (m_statementName != null) { - connection.execSQL("DEALLOCATE " + m_statementName); - m_statementName = null; - m_origSqlFragments = null; - m_executeSqlFragments = null; - } + return execute(); } + /* + * Check if the current query is a single statement. + */ + private boolean isSingleStatement() + { + if (m_isSingleStatement != UNKNOWN) + return m_isSingleStatement == YES; + + // Crude detection of multiple statements. This could be + // improved by parsing the whole query for quotes, but is + // it worth it given that the only queries that get here are + // unparameterized queries? + + for (int i = 0; i < m_sqlFragments.length; ++i) { // a bit redundant, but .. + if (m_sqlFragments[i].indexOf(';') != -1) { + m_isSingleStatement = NO; + return false; + } + } + + m_isSingleStatement = YES; + return true; + } + + /* + * Helper for isSingleSelect() and isSingleDML(): computes values + * of m_isSingleDML and m_isSingleSelect. + */ + private void analyzeStatementType() + { + if (!isSingleStatement()) { + m_isSingleSelect = m_isSingleDML = NO; + return; + } + + String compare = m_sqlFragments[0].trim().toLowerCase(); + if (compare.startsWith("select")) { + m_isSingleSelect = m_isSingleDML = YES; + return; + } + + m_isSingleSelect = NO; + + if (!compare.startsWith("update") && + !compare.startsWith("delete") && + !compare.startsWith("insert")) { + m_isSingleDML = NO; + return; + } + + m_isSingleDML = YES; + } + + /* + * Check if the current query is a single SELECT. + */ + private boolean isSingleSelect() + { + if (m_isSingleSelect == UNKNOWN) + analyzeStatementType(); + + return m_isSingleSelect == YES; + } + + /* + * Check if the current query is a single SELECT/UPDATE/INSERT/DELETE. + */ + private boolean isSingleDML() + { + if (m_isSingleDML == UNKNOWN) + analyzeStatementType(); + + return m_isSingleDML == YES; + } + + /* + * Return the query fragments to use for a server-prepared statement. + * The first query executed will include a PREPARE and EXECUTE; + * subsequent queries will just be an EXECUTE. + */ + private String[] transformToServerPrepare() { + if (m_statementName != null) + return m_executeSqlFragments; + + // First time through. + m_statementName = "JDBC_STATEMENT_" + m_preparedCount++; + + // Set up m_executeSqlFragments + m_executeSqlFragments = new String[m_sqlFragments.length]; + m_executeSqlFragments[0] = "EXECUTE " + m_statementName; + if (m_sqlFragments.length > 1) { + m_executeSqlFragments[0] += "("; + for (int i = 1; i < m_bindTypes.length; i++) + m_executeSqlFragments[i] = ", "; + m_executeSqlFragments[m_bindTypes.length] = ")"; + } + + // Set up the PREPARE. + String[] prepareSqlFragments = new String[m_sqlFragments.length]; + System.arraycopy(m_sqlFragments, 0, prepareSqlFragments, 0, m_sqlFragments.length); + + synchronized (sbuf) { + sbuf.setLength(0); + sbuf.append("PREPARE "); + sbuf.append(m_statementName); + if (m_sqlFragments.length > 1) { + sbuf.append("("); + for (int i = 0; i < m_bindTypes.length; i++) { + if (i != 0) sbuf.append(", "); + sbuf.append(m_bindTypes[i]); + } + sbuf.append(")"); + } + sbuf.append(" AS "); + sbuf.append(m_sqlFragments[0]); + for (int i = 1; i < m_sqlFragments.length; i++) { + sbuf.append(" $"); + sbuf.append(i); + sbuf.append(" "); + sbuf.append(m_sqlFragments[i]); + } + sbuf.append("; "); + sbuf.append(m_executeSqlFragments[0]); + + prepareSqlFragments[0] = sbuf.toString(); + } + + System.arraycopy(m_executeSqlFragments, 1, prepareSqlFragments, 1, prepareSqlFragments.length - 1); + return prepareSqlFragments; + } + + /* + * Return the current query transformed into a cursor-based statement. + * This uses a new cursor on each query. + */ + private String[] transformToCursorFetch() + { + + // Pinch the prepared count for our own nefarious purposes. + m_cursorName = "JDBC_CURS_" + m_preparedCount++; + + // Create a cursor declaration and initial fetch statement from the original query. + int len = m_sqlFragments.length; + String[] cursorBasedSql = new String[len]; + System.arraycopy(m_sqlFragments, 0, cursorBasedSql, 0, len); + cursorBasedSql[0] = "DECLARE " + m_cursorName + " CURSOR FOR " + cursorBasedSql[0]; + cursorBasedSql[len-1] += "; FETCH FORWARD " + fetchSize + " FROM " + m_cursorName; + + // 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 " + m_cursorName); + + return cursorBasedSql; + } + + /** + * Do transformations to a query for server-side prepare or setFetchSize() cursor + * work. + * @return the query fragments to execute + */ + private String[] getQueryFragments() + { + // nb: isSingleXXX() are relatively expensive, avoid calling them unless we must. + + // We check the "mutable" bits of these conditions (which may change without + // a new query being created) here; isSingleXXX() only concern themselves with + // the query structure itself. + + // We prefer cursor-based-fetch over server-side-prepare here. + // Eventually a v3 implementation should let us do both at once. + if (fetchSize > 0 && !connection.getAutoCommit() && isSingleSelect()) + return transformToCursorFetch(); + + if (isUseServerPrepare() && isSingleDML()) + return transformToServerPrepare(); + + // Not server-prepare or cursor-fetch, just return a plain query. + return m_sqlFragments; + } + /* * 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 @@ -319,133 +507,14 @@ public abstract class AbstractJdbc1Statement implements BaseStatement rs.close(); } - //Use server prepared statements if directed - if (m_useServerPrepare) - { - if (m_statementName == null) - { - m_statementName = "JDBC_STATEMENT_" + next_preparedCount(); - m_origSqlFragments = new String[m_sqlFragments.length]; - m_executeSqlFragments = new String[m_sqlFragments.length]; - System.arraycopy(m_sqlFragments, 0, m_origSqlFragments, 0, m_sqlFragments.length); - m_executeSqlFragments[0] = "EXECUTE " + m_statementName; - if (m_sqlFragments.length > 1) - { - m_executeSqlFragments[0] = m_executeSqlFragments[0] + "("; - for (int i = 1; i < m_bindTypes.length; i++) - { - m_executeSqlFragments[i] = ", "; - } - m_executeSqlFragments[m_bindTypes.length] = ")"; - } - synchronized (sbuf) - { - sbuf.setLength(0); - sbuf.append("PREPARE "); - sbuf.append(m_statementName); - if (m_origSqlFragments.length > 1) - { - sbuf.append("("); - for (int i = 0; i < m_bindTypes.length - 1; i++) - { - sbuf.append(m_bindTypes[i]); - sbuf.append(", "); - } - sbuf.append(m_bindTypes[m_bindTypes.length - 1]); - sbuf.append(")"); - } - sbuf.append(" AS "); - sbuf.append(m_origSqlFragments[0]); - for (int i = 1; i < m_origSqlFragments.length; i++) - { - sbuf.append(" $"); - sbuf.append(i); - sbuf.append(" "); - sbuf.append(m_origSqlFragments[i]); - } - sbuf.append("; "); - - sbuf.append(m_executeSqlFragments[0]); - m_sqlFragments[0] = sbuf.toString(); - System.arraycopy(m_executeSqlFragments, 1, m_sqlFragments, 1, m_sqlFragments.length - 1); - } - - } - else - { - 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_" + next_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()); - } - } + // Get the actual query fragments to run (might be a transformed version of + // the original fragments) + String[] fragments = getQueryFragments(); // New in 7.1, pass Statement so that ExecSQL can customise to it - result = QueryExecutor.execute(m_sqlFragments, - m_binds, - this); + result = QueryExecutor.execute(fragments, + m_binds, + this); //If we are executing a callable statement function set the return data if (isFunction) @@ -721,10 +790,7 @@ public abstract class AbstractJdbc1Statement implements BaseStatement if (rs != null) rs.close(); - // If using server prepared statements deallocate them - if (m_useServerPrepare && m_statementName != null) { - connection.execSQL("DEALLOCATE " + m_statementName); - } + deallocateQuery(); // Disasociate it from us (For Garbage Collection) result = null; @@ -2093,11 +2159,8 @@ public abstract class AbstractJdbc1Statement implements BaseStatement public void setUseServerPrepare(boolean flag) throws SQLException { //Server side prepared statements were introduced in 7.3 if (connection.haveMinimumServerVersion("7.3")) { - //If turning server prepared statements off deallocate statement - //and reset statement name - if (m_useServerPrepare != flag && !flag && m_statementName != null) - connection.execSQL("DEALLOCATE " + m_statementName); - m_statementName = null; + if (m_useServerPrepare != flag) + deallocateQuery(); m_useServerPrepare = flag; } else { //This is a pre 7.3 server so no op this method diff --git a/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java b/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java index 7b4f7c1e9a..b8590dff84 100644 --- a/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java +++ b/src/interfaces/jdbc/org/postgresql/jdbc2/AbstractJdbc2ResultSet.java @@ -9,7 +9,7 @@ * Copyright (c) 2003, PostgreSQL Global Development Group * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/Attic/AbstractJdbc2ResultSet.java,v 1.24 2003/09/17 05:14:52 barry Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/Attic/AbstractJdbc2ResultSet.java,v 1.25 2003/10/29 02:39:09 davec Exp $ * *------------------------------------------------------------------------- */ @@ -389,13 +389,6 @@ public abstract class AbstractJdbc2ResultSet extends org.postgresql.jdbc1.Abstra } - public int getFetchSize() throws SQLException - { - // Returning the current batch size seems the right thing to do. - return rows.size(); - } - - public Object getObject(String columnName, java.util.Map map) throws SQLException { return getObject(findColumn(columnName), map); @@ -518,13 +511,6 @@ public abstract class AbstractJdbc2ResultSet extends org.postgresql.jdbc1.Abstra } - public void setFetchSize(int rows) throws SQLException - { - // Sub-classes should implement this as part of their cursor support - throw org.postgresql.Driver.notImplemented(); - } - - public synchronized void cancelRowUpdates() throws SQLException { diff --git a/src/interfaces/jdbc/org/postgresql/test/jdbc2/CursorFetchTest.java b/src/interfaces/jdbc/org/postgresql/test/jdbc2/CursorFetchTest.java index def403fdef..825760e1d4 100644 --- a/src/interfaces/jdbc/org/postgresql/test/jdbc2/CursorFetchTest.java +++ b/src/interfaces/jdbc/org/postgresql/test/jdbc2/CursorFetchTest.java @@ -51,7 +51,10 @@ public class CursorFetchTest extends TestCase int[] testSizes = { 0, 1, 49, 50, 51, 99, 100, 101 }; for (int i = 0; i < testSizes.length; ++i) { stmt.setFetchSize(testSizes[i]); + assertEquals(testSizes[i], stmt.getFetchSize()); + ResultSet rs = stmt.executeQuery(); + assertEquals(testSizes[i], rs.getFetchSize()); int count = 0; while (rs.next()) { @@ -63,6 +66,115 @@ public class CursorFetchTest extends TestCase } } + // + // Tests for ResultSet.setFetchSize(). + // + + // test one: + // set fetchsize = 0 + // run query (all rows should be fetched) + // set fetchsize = 50 (should have no effect) + // process results + public void testResultSetFetchSizeOne() throws Exception + { + createRows(100); + + PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value"); + stmt.setFetchSize(0); + ResultSet rs = stmt.executeQuery(); + stmt.setFetchSize(50); // Should have no effect. + + int count = 0; + while (rs.next()) { + assertEquals(count, rs.getInt(1)); + ++count; + } + + assertEquals(100, count); + } + + // test two: + // set fetchsize = 25 + // run query (25 rows fetched) + // set fetchsize = 0 + // process results: + // process 25 rows + // should do a FETCH ALL to get more data + // process 75 rows + public void testResultSetFetchSizeTwo() throws Exception + { + createRows(100); + + PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value"); + stmt.setFetchSize(25); + ResultSet rs = stmt.executeQuery(); + stmt.setFetchSize(0); + + int count = 0; + while (rs.next()) { + assertEquals(count, rs.getInt(1)); + ++count; + } + + assertEquals(100, count); + } + + // test three: + // set fetchsize = 25 + // run query (25 rows fetched) + // set fetchsize = 50 + // process results: + // process 25 rows. should NOT hit end-of-results here. + // do a FETCH FORWARD 50 + // process 50 rows + // do a FETCH FORWARD 50 + // process 25 rows. end of results. + public void testResultSetFetchSizeThree() throws Exception + { + createRows(100); + + PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value"); + stmt.setFetchSize(25); + ResultSet rs = stmt.executeQuery(); + stmt.setFetchSize(50); + + int count = 0; + while (rs.next()) { + assertEquals(count, rs.getInt(1)); + ++count; + } + + assertEquals(100, count); + } + + // test four: + // set fetchsize = 50 + // run query (50 rows fetched) + // set fetchsize = 25 + // process results: + // process 50 rows. + // do a FETCH FORWARD 25 + // process 25 rows + // do a FETCH FORWARD 25 + // process 25 rows. end of results. + public void testResultSetFetchSizeFour() throws Exception + { + createRows(100); + + PreparedStatement stmt = con.prepareStatement("select * from test_fetch order by value"); + stmt.setFetchSize(50); + ResultSet rs = stmt.executeQuery(); + stmt.setFetchSize(25); + + int count = 0; + while (rs.next()) { + assertEquals(count, rs.getInt(1)); + ++count; + } + + assertEquals(100, count); + } + // Test odd queries that should not be transformed into cursor-based fetches. public void TODO_FAILS_testInsert() throws Exception { -- 2.40.0