Skip to content

Commit 51acf97

Browse files
author
Barry Lind
committed
Applied patch submitted by Nic Ferrier with some cleanups of his previous
patch to add cursor based queries. Modified Files: jdbc/org/postgresql/core/BaseConnection.java jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java
1 parent 3fd5fae commit 51acf97

File tree

2 files changed

+84
-110
lines changed

2 files changed

+84
-110
lines changed

‎src/interfaces/jdbc/org/postgresql/core/BaseConnection.java

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,7 @@
66
* Copyright (c) 2003, PostgreSQL Global Development Group
77
*
88
* IDENTIFICATION
9-
* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseConnection.java,v 1.1 2003/03/07 18:39:41 barry Exp $
9+
* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/core/Attic/BaseConnection.java,v 1.2 2003/04/13 04:10:07 barry Exp $
1010
*
1111
*-------------------------------------------------------------------------
1212
*/
@@ -27,6 +27,7 @@ public interface BaseConnection extends PGConnection
2727
public void cancelQuery() throws SQLException;
2828
public Statement createStatement() throws SQLException;
2929
public BaseResultSet execSQL(String s) throws SQLException;
30+
public boolean getAutoCommit() throws SQLException;
3031
public String getCursorName() throws SQLException;
3132
public Encoding getEncoding() throws SQLException;
3233
public DatabaseMetaData getMetaData() throws SQLException;
@@ -38,6 +39,7 @@ public interface BaseConnection extends PGConnection
3839
public int getSQLType(String pgTypeName) throws SQLException;
3940
public boolean haveMinimumCompatibleVersion(String ver) throws SQLException;
4041
public boolean haveMinimumServerVersion(String ver) throws SQLException;
42+
public void setAutoCommit(boolean autoCommit) throws SQLException;
4143
public void setCursorName(String cursor) throws SQLException;
4244

4345
}

‎src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java

Lines changed: 81 additions & 109 deletions
Original file line numberDiff line numberDiff line change
@@ -13,15 +13,14 @@
1313
import org.postgresql.largeobject.*;
1414
import org.postgresql.util.*;
1515

16-
/* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Statement.java,v 1.18 2003/03/07 18:39:44 barry Exp $
16+
/* $Header: /cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Attic/AbstractJdbc1Statement.java,v 1.19 2003/04/13 04:10:07 barry Exp $
1717
* This class defines methods of the jdbc1 specification. This class is
1818
* extended by org.postgresql.jdbc2.AbstractJdbc2Statement which adds the jdbc2
1919
* methods. The real Statement class (for jdbc1) is org.postgresql.jdbc1.Jdbc1Statement
2020
*/
2121
public abstract class AbstractJdbc1Statement implements BaseStatement
2222
{
23-
24-
// The connection who created us
23+
// The connection who created us
2524
protected BaseConnection connection;
2625

2726
/** The warnings chain. */
@@ -58,6 +57,7 @@ public abstract class AbstractJdbc1Statement implements BaseStatement
5857

5958
protected String[] m_bindTypes = new String[0];
6059
protected String m_statementName = null;
60+
protected boolean m_statementIsCursor = false;
6161

6262
private boolean m_useServerPrepare = false;
6363
private static int m_preparedCount = 1;
@@ -159,14 +159,16 @@ public java.sql.ResultSet executeQuery(String p_sql) throws SQLException
159159
{
160160
try
161161
{
162-
connection.execSQL("DEALLOCATE " + m_statementName);
162+
if (!m_statementIsCursor)
163+
connection.execSQL("DEALLOCATE " + m_statementName);
163164
}
164165
catch (Exception e)
165166
{
166167
}
167168
finally
168169
{
169170
m_statementName = null;
171+
m_statementIsCursor = false;
170172
m_origSqlFragments = null;
171173
m_executeSqlFragments = null;
172174
}
@@ -183,11 +185,8 @@ public java.sql.ResultSet executeQuery(String p_sql) throws SQLException
183185
*/
184186
public java.sql.ResultSet executeQuery() throws SQLException
185187
{
186-
if (fetchSize > 0)
187-
this.executeWithCursor();
188-
else
189-
this.execute();
190-
188+
this.execute();
189+
191190
while (result != null && !result.reallyResultSet())
192191
result = (BaseResultSet) result.getNext();
193192
if (result == null)
@@ -268,9 +267,13 @@ public boolean execute(String p_sql) throws SQLException
268267
* Some prepared statements return multiple results; the execute method
269268
* handles these complex statements as well as the simpler form of
270269
* statements handled by executeQuery and executeUpdate
270+
*
271+
* This method also handles the translation of the query into a cursor based
272+
* query if the user has specified a fetch size and set the connection
273+
* into a non-auto commit state.
271274
*
272275
* @return true if the next result is a ResultSet; false if it is an
273-
* * update count or there are no more results
276+
* update count or there are no more results
274277
* @exception SQLException if a database access error occurs
275278
*/
276279
public boolean execute() throws SQLException
@@ -353,10 +356,75 @@ public boolean execute() throws SQLException
353356
}
354357
}
355358

356-
// New in 7.1, pass Statement so that ExecSQL can customise to it
359+
// Use a cursor if directed and in a transaction.
360+
else if (fetchSize > 0 && !connection.getAutoCommit())
361+
{
362+
// The first thing to do is transform the statement text into the cursor form.
363+
String[] cursorBasedSql = new String[m_sqlFragments.length];
364+
// Pinch the prepared count for our own nefarious purposes.
365+
String statementName = "JDBC_CURS_" + m_preparedCount++;
366+
// Setup the cursor decleration.
367+
// Note that we don't need a BEGIN because we've already
368+
// made sure we're executing inside a transaction.
369+
String cursDecl = "DECLARE " + statementName + " CURSOR FOR ";
370+
String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + statementName + ";";
371+
372+
// Copy the real query to the curs decleration.
373+
try
374+
{
375+
// Need to confirm this with Barry Lind.
376+
if (cursorBasedSql.length > 1)
377+
throw new IllegalStateException("cursor fetches not supported with prepared statements.");
378+
for (int i = 0; i < cursorBasedSql.length; i++)
379+
{
380+
if (i == 0)
381+
{
382+
if (m_sqlFragments[i].trim().toUpperCase().startsWith("DECLARE "))
383+
throw new IllegalStateException("statement is already cursor based.");
384+
cursorBasedSql[i] = cursDecl;
385+
}
386+
387+
if (cursorBasedSql[i] != null)
388+
cursorBasedSql[i] += m_sqlFragments[i];
389+
else
390+
cursorBasedSql[i] = m_sqlFragments[i];
391+
392+
if (i == cursorBasedSql.length - 1)
393+
{
394+
// We have to be smart about adding the delimitting ";"
395+
if (m_sqlFragments[i].endsWith(";"))
396+
cursorBasedSql[i] += endCurs;
397+
else
398+
cursorBasedSql[i] += (";" + endCurs);
399+
}
400+
else if (m_sqlFragments[i].indexOf(";") > -1)
401+
{
402+
throw new IllegalStateException("multiple statements not "
403+
+ "allowed with cursor based querys.");
404+
}
405+
}
406+
407+
// Make the cursor based query the one that will be used.
408+
if (org.postgresql.Driver.logDebug)
409+
org.postgresql.Driver.debug("using cursor based sql with cursor name " + statementName);
410+
411+
// Do all of this after exceptions have been thrown.
412+
m_statementName = statementName;
413+
m_statementIsCursor = true;
414+
m_sqlFragments = cursorBasedSql;
415+
}
416+
catch (IllegalStateException e)
417+
{
418+
// Something went wrong generating the cursor based statement.
419+
if (org.postgresql.Driver.logDebug)
420+
org.postgresql.Driver.debug(e.getMessage());
421+
}
422+
}
423+
424+
// New in 7.1, pass Statement so that ExecSQL can customise to it
357425
result = QueryExecutor.execute(m_sqlFragments,
358-
m_binds,
359-
this);
426+
m_binds,
427+
this);
360428

361429
//If we are executing a callable statement function set the return data
362430
if (isFunction)
@@ -379,102 +447,6 @@ public boolean execute() throws SQLException
379447
return (result != null && result.reallyResultSet());
380448
}
381449
}
382-
383-
/** version of execute which converts the query to a cursor.
384-
*/
385-
public boolean executeWithCursor() throws SQLException
386-
{
387-
if (isFunction && !returnTypeSet)
388-
throw new PSQLException("postgresql.call.noreturntype");
389-
if (isFunction)
390-
{ // set entry 1 to dummy entry..
391-
m_binds[0] = ""; // dummy entry which ensured that no one overrode
392-
m_bindTypes[0] = PG_TEXT;
393-
// and calls to setXXX (2,..) really went to first arg in a function call..
394-
}
395-
396-
// New in 7.1, if we have a previous resultset then force it to close
397-
// This brings us nearer to compliance, and helps memory management.
398-
// Internal stuff will call ExecSQL directly, bypassing this.
399-
if (result != null)
400-
{
401-
java.sql.ResultSet rs = getResultSet();
402-
if (rs != null)
403-
rs.close();
404-
}
405-
406-
// I've pretty much ignored server prepared statements... can declare and prepare be
407-
// used together?
408-
// It's trivial to change this: you just have to resolve this issue
409-
// of how to work out whether there's a function call. If there isn't then the first
410-
// element of the array must be the bit that you extend to become the cursor
411-
// decleration.
412-
// The last thing that can go wrong is when the user supplies a cursor statement
413-
// directly: the translation takes no account of that. I think we should just look
414-
// for declare and stop the translation if we find it.
415-
416-
// The first thing to do is transform the statement text into the cursor form.
417-
String[] origSqlFragments = m_sqlFragments;
418-
m_sqlFragments = new String[origSqlFragments.length];
419-
System.arraycopy(origSqlFragments, 0, m_sqlFragments, 0, origSqlFragments.length);
420-
// Pinch the prepared count for our own nefarious purposes.
421-
m_statementName = "JDBC_CURS_" + m_preparedCount++;
422-
// The static bit to prepend to all querys.
423-
String cursDecl = "BEGIN; DECLARE " + m_statementName + " CURSOR FOR ";
424-
String endCurs = " FETCH FORWARD " + fetchSize + " FROM " + m_statementName + ";";
425-
426-
// Add the real query to the curs decleration.
427-
// This is the bit that really makes the presumption about
428-
// m_sqlFragments not being a function call.
429-
if (m_sqlFragments.length < 1)
430-
m_sqlFragments[0] = cursDecl + "SELECT NULL;";
431-
432-
else if (m_sqlFragments.length < 2)
433-
{
434-
if (m_sqlFragments[0].endsWith(";"))
435-
m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + endCurs;
436-
else
437-
m_sqlFragments[0] = cursDecl + m_sqlFragments[0] + ";" + endCurs;
438-
}
439-
else
440-
{
441-
m_sqlFragments[0] = cursDecl + m_sqlFragments[0];
442-
if (m_sqlFragments[m_sqlFragments.length - 1].endsWith(";"))
443-
m_sqlFragments[m_sqlFragments.length - 1] += endCurs;
444-
else
445-
m_sqlFragments[m_sqlFragments.length - 1] += (";" + endCurs);
446-
}
447-
448-
result = QueryExecutor.execute(m_sqlFragments,
449-
m_binds,
450-
this);
451-
452-
//If we are executing a callable statement function set the return data
453-
if (isFunction)
454-
{
455-
if (!result.reallyResultSet())
456-
throw new PSQLException("postgresql.call.noreturnval");
457-
if (!result.next ())
458-
throw new PSQLException ("postgresql.call.noreturnval");
459-
callResult = result.getObject(1);
460-
int columnType = result.getMetaData().getColumnType(1);
461-
if (columnType != functionReturnType)
462-
{
463-
Object[] arr =
464-
{ "java.sql.Types=" + columnType,
465-
"java.sql.Types=" + functionReturnType
466-
};
467-
throw new PSQLException ("postgresql.call.wrongrtntype",arr);
468-
}
469-
result.close ();
470-
return true;
471-
}
472-
else
473-
{
474-
return (result != null && result.reallyResultSet());
475-
}
476-
}
477-
478450

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

0 commit comments

Comments
 (0)