Daniel John Debrunner wrote:
-----BEGIN PGP SIGNED MESSAGE-----After further going through the JDBC api and tutorial book, forward only
Hash: SHA1> I would like to resubmit the patch for Updatable ResultSet JDBC 2.0
> - delete functionality only. This patch has changes as discussed in thread
>
http://nagoya.apache.org/eyebrowse/[EMAIL PROTECTED]&msgNo=1356
>
> My vote : +1. Please send in your votes.Some comments:
EmbedDatabaseMetaData
- ---------------------Can you explain changing the other updates are visible methods to
false, maybe by comments in the code.
resultsets can see the changes made by others. This is because Derby
materializes the forward only resultset incrementally. And hence, the other
updates are visible methods should continue to return true for forward only
resultsets. But scroll insensitive resultsets by their definition are immune to
changes made by others and hence for scroll insensitive resultsets, we should
return false. I have changed the code accordingly.
ResultSet object will return CONCUR_READ_ONLY if Statement object
EmbedResultSet20
- ----------------getConcurrency() method
I think this implementation is incorrect. A ResultSet that is
created read-only, but uses a FOR UPDATE clause will return it is
updateable. This is currently used for positioned updates.
I think also a ResultSet that is created as updateable will change to be
read only if the underlying language result set is not updateable. In
this case I think a warning should be generated as well, which I don't
think will happen with your code.
has that concurrency set on it. But if the Statement object has
CONCUR_UPDATABLE, then ResultSet object will return the same if
the underlying language resultset object is updatable. If not, ResultSet
object will return CONCUR_READ_ONLY.
In case of autocommit on, if there was an exception which caused
deleteRow() method
Why is the check on theResults.isClosed() needed?
runtime rollback prior to this deleteRow, then the rollback code will
mark the language resultset closed (it doesn't mark the JDBC ResultSet closed).
That is why alongwith the earlier checkIfClosed call in this method, there
is a check for language resultset close as well.(Existing Derby code does the
same 2 checks for navigational methods like next() on the ResultSet object).
I have fixed this.
When pushing the statement context, shouldn't you use the text of
the delete SQL statement, and not the sql text of the current result
set? Also you are passing in the parameters of the query which are not
required and not relevant to the positioned delete.
Added comment.
A comment for the line 'rowData = null' would be very useful
Fixed the typo.
SQLState.java
- --------------
Typo in constant, need S in DISALLOWED
UPDATABLE_RESULTSET_API_DIALLOWED
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.orgiD8DBQFB2u2AIv0S4qsbfuQRAu6nAKC18s2fPxUDJEfif8Htktrx7QoRKwCfWzSu
ABOTO+fQyqEwHz5V/UBRzL8=
=IAO9
-----END PGP SIGNATURE-----
Attached is the new patch with all the feedback so far. Following
is the svn stat o/p
M java\engine\org\apache\derby\impl\jdbc\EmbedConnectionContext.java
M java\engine\org\apache\derby\impl\jdbc\EmbedResultSet.java
M java\engine\org\apache\derby\impl\jdbc\EmbedDatabaseMetaData.java
M java\engine\org\apache\derby\impl\jdbc\EmbedResultSet20.java
M java\engine\org\apache\derby\impl\jdbc\EmbedConnection.java
M java\engine\org\apache\derby\iapi\sql\ResultSet.java
M java\engine\org\apache\derby\iapi\reference\SQLState.java
M java\engine\org\apache\derby\jdbc\Driver169.java
M java\engine\org\apache\derby\jdbc\Driver20.java
M java\engine\org\apache\derby\loc\messages_en.properties
A java\testing\org\apache\derbyTesting\functionTests\tests\lang\updatableResultSet.java
A java\testing\org\apache\derbyTesting\functionTests\master\updatableResultSet.out
M java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall
thanks,
Mamta
Index: java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java
(revision 124477)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedConnectionContext.java
(working copy)
@@ -125,7 +125,7 @@
public java.sql.ResultSet getResultSet
(
ResultSet executionResultSet
- )
+ ) throws SQLException
{
EmbedConnection conn = (EmbedConnection) connRef.get();
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (revision
124477)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (working copy)
@@ -84,7 +84,9 @@
// mutable state
protected ExecRow currentRow;
- private DataValueDescriptor[] rowData;
+ //rowData is protected so deleteRow in EmbedResultSet20.java can make
it null.
+ //This ensures that after deleteRow, ResultSet is not positioned on the
deleted row.
+ protected DataValueDescriptor[] rowData;
protected boolean wasNull;
protected boolean isClosed;
private Object currentStream;
@@ -131,6 +133,7 @@
protected final boolean isAtomic;
+ protected final int concurrencyOfThisResultSet;
/**
* This class provides the glue between the Cloudscape
@@ -138,7 +141,7 @@
*/
public EmbedResultSet(EmbedConnection conn, ResultSet resultsToWrap,
boolean forMetaData, EmbedStatement stmt, boolean isAtomic)
- {
+ throws SQLException {
super(conn);
@@ -149,6 +152,27 @@
this.stmt = owningStmt = stmt;
this.isAtomic = isAtomic;
+ //If the Statement object has CONCUR_READ_ONLY set on it then
the concurrency on the ResultSet object will be CONCUR_READ_ONLY also.
+ //But, if the Statement object has CONCUR_UPDATABLE set on it,
then the concurrency on the ResultSet object can be
+ //CONCUR_READ_ONLY or CONCUR_UPDATABLE depending on whether the
underlying language resultset is updateable or not.
+ //If the underlying language resultset is not updateable, then
the concurrency of the ResultSet object will be CONCUR_READ_ONLY
+ //and a warning will be issued on the ResultSet object.
+ if (stmt == null) concurrencyOfThisResultSet =
JDBC20Translation.CONCUR_READ_ONLY;
+ else if (stmt.getResultSetConcurrency() ==
JDBC20Translation.CONCUR_READ_ONLY)
+ concurrencyOfThisResultSet =
JDBC20Translation.CONCUR_READ_ONLY;
+ else {
+ if (!isForUpdate()) { //language resultset not updatable
+ concurrencyOfThisResultSet =
JDBC20Translation.CONCUR_READ_ONLY;
+ SQLWarning w =
StandardException.newWarning(SQLState.QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET);
+ if (topWarning == null)
+ topWarning = w;
+ else
+ topWarning.setNextWarning(w);
+ } else
+ concurrencyOfThisResultSet =
JDBC20Translation.CONCUR_UPDATABLE;
+ }
+
+ // Fill in the column types
resultDescription = theResults.getResultDescription();
// assign the max rows and maxfiled size limit for this result set
Index: java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
(revision 124477)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
(working copy)
@@ -2412,10 +2412,16 @@
* @return true if so
* @see Connection
*/
- public boolean supportsResultSetConcurrency(int type, int concurrency) {
- if ((type == JDBC20Translation.TYPE_SCROLL_SENSITIVE) ||
- (concurrency == JDBC20Translation.CONCUR_UPDATABLE))
- return false;
+ public boolean supportsResultSetConcurrency(int type, int concurrency) {
+ //FORWARD_ONLY + CONCUR_UPDATABLE combination is supported (at
this point, delete functionality only)
+ if ((type == JDBC20Translation.TYPE_FORWARD_ONLY) &&
+ (concurrency ==
JDBC20Translation.CONCUR_UPDATABLE))
+ return true;
+
+ //requesting CONCUR_UPDATABLE on any resultset type other than
TYPE_FORWARD_ONLY will return false
+ if ((type == JDBC20Translation.TYPE_SCROLL_SENSITIVE) ||
+ (concurrency ==
JDBC20Translation.CONCUR_UPDATABLE))
+ return false;
return true;
}
@@ -2445,14 +2451,26 @@
* @param result set type, i.e. ResultSet.TYPE_XXX
* @return true if changes are visible for the result set type
*/
+ //Since Derby materializes a forward only ResultSet incrementally, it is
possible to see changes
+ //made by others and hence following 3 metadata calls will return true for
forward only ResultSets.
+ //Scroll insensitive ResultSet by their definition do not see chnages made
by others.
+ //Derby does not yet implement scroll sensitive resultsets.
public boolean othersUpdatesAreVisible(int type) {
- return true;
+ if (type == JDBC20Translation.TYPE_FORWARD_ONLY)
+ return true;
+ return false;
}
+
public boolean othersDeletesAreVisible(int type) {
- return true;
+ if (type == JDBC20Translation.TYPE_FORWARD_ONLY)
+ return true;
+ return false;
}
+
public boolean othersInsertsAreVisible(int type) {
- return true;
+ if (type == JDBC20Translation.TYPE_FORWARD_ONLY)
+ return true;
+ return false;
}
/**
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java
(revision 124477)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java
(working copy)
@@ -23,8 +23,11 @@
import org.apache.derby.iapi.reference.JDBC20Translation;
import org.apache.derby.iapi.reference.SQLState;
+import org.apache.derby.iapi.sql.Activation;
import org.apache.derby.iapi.sql.ResultSet;
+import org.apache.derby.iapi.sql.execute.ExecCursorTableReference;
+
import org.apache.derby.iapi.error.StandardException;
import org.apache.derby.impl.jdbc.Util;
import org.apache.derby.iapi.sql.conn.LanguageConnectionContext;
@@ -75,7 +78,7 @@
boolean forMetaData,
org.apache.derby.impl.jdbc.EmbedStatement stmt,
boolean isAtomic)
- {
+ throws SQLException {
super(conn, resultsToWrap, forMetaData, stmt, isAtomic);
}
@@ -214,7 +217,7 @@
* @exception SQLException Thrown on error.
*/
public boolean isAfterLast() throws SQLException
- {
+ {
return checkRowPosition(ResultSet.ISAFTERLAST, "isAfterLast");
}
@@ -519,15 +522,19 @@
/**
* JDBC 2.0
*
- * Return the concurrency of this result set. The concurrency
- * used is determined by the statement that created the result set.
+ * Return the concurrency of this result set. The concurrency is
determined as follows
+ * If Statement object has CONCUR_READ_ONLY concurrency, then ResultSet
object will also have the CONCUR_READ_ONLY concurrency.
+ * But if Statement object has CONCUR_UPDATABLE concurrency, then the
concurrency of ResultSet object depends on whether the
+ * underlying language resultset is updatable or not. If the language
resultset is updatable, then JDBC ResultSet object will
+ * also have the CONCUR_UPDATABLE concurrency. If lanugage resultset is
not updatable, then JDBC ResultSet object concurrency
+ * will be set to CONCUR_READ_ONLY.
*
* @return the concurrency type, CONCUR_READ_ONLY, etc.
* @exception SQLException if a database-access error occurs
*/
public int getConcurrency() throws SQLException
{
- return JDBC20Translation.CONCUR_READ_ONLY;
+ return concurrencyOfThisResultSet;
}
//---------------------------------------------------------------------
@@ -579,7 +586,7 @@
* @see EmbedDatabaseMetaData#deletesAreDetected
*/
public boolean rowDeleted() throws SQLException {
- throw Util.notImplemented();
+ return false;
}
/**
@@ -1336,9 +1343,50 @@
* called when on the insert row.
*/
public void deleteRow() throws SQLException {
- throw Util.notImplemented();
+ synchronized (getConnectionSynchronization()) {
+ checkIfClosed("deleteRow");
+ checkOnRow(); // first make sure there's a current row
+
+ if (getConcurrency() != JDBC20Translation.CONCUR_UPDATABLE)//if
not updatable resultset, can't issue deleteRow
+ throw
Util.generateCsSQLException(SQLState.UPDATABLE_RESULTSET_API_DISALLOWED,
"deleteRow");
+
+ setupContextStack();
+ try {
+ //in case of autocommit on, if there was an exception which
caused runtime rollback in this transaction prior to this deleteRow,
+ //the rollback code will mark the language resultset closed
(it doesn't mark the JDBC ResultSet closed).
+ //That is why alongwith the earlier checkIfClosed call in this
method, there is a check for language resultset close as well.
+ if (theResults.isClosed())
+ throw
Util.generateCsSQLException(SQLState.LANG_RESULT_SET_NOT_OPEN, "deleteRow");
+ StringBuffer deleteWhereCurrentOfSQL = new
StringBuffer("DELETE FROM ");
+ Activation activation =
getEmbedConnection().getLanguageConnection().lookupCursorActivation(getCursorName());
+
deleteWhereCurrentOfSQL.append(getFullBaseTableName(activation.getPreparedStatement().getTargetTable()));//get
the underlying (schema.)table name
+ //using quotes around the cursor name to preserve case
sensitivity
+ deleteWhereCurrentOfSQL.append(" WHERE CURRENT OF \"" +
getCursorName() + "\"");
+
+ LanguageConnectionContext lcc =
getEmbedConnection().getLanguageConnection();
+ StatementContext statementContext =
lcc.pushStatementContext(isAtomic, deleteWhereCurrentOfSQL.toString(), null,
false);
+ org.apache.derby.iapi.sql.PreparedStatement ps =
lcc.prepareInternalStatement(deleteWhereCurrentOfSQL.toString());
+ org.apache.derby.iapi.sql.ResultSet rs = ps.execute(lcc, true);
+ rs.close();
+ rs.finish();
+ //For forward only resultsets, after a delete, the ResultSet
will be positioned right before the next row.
+ rowData = null;
+ lcc.popStatementContext(statementContext, null);
+ } catch (StandardException t) {
+ throw closeOnTransactionError(t);
+ } finally {
+ restoreContextStack();
+ }
}
+ }
+ private String getFullBaseTableName(ExecCursorTableReference targetTable) {
+ if (targetTable.getSchemaName() != null)
+ return targetTable.getSchemaName() + "." +
targetTable.getBaseName();
+ else
+ return targetTable.getBaseName();
+ }
+
/**
* JDBC 2.0
*
Index: java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java (revision
124477)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java (working copy)
@@ -478,7 +478,7 @@
throw Util.noCurrentConnection();
return factory.newEmbedStatement(this, false,
- setResultSetType(resultSetType),
setResultSetConcurrency(resultSetConcurrency),
+ setResultSetType(resultSetType),
setResultSetConcurrency(resultSetType, resultSetConcurrency),
resultSetHoldability);
}
@@ -669,7 +669,7 @@
try {
return factory.newEmbedPreparedStatement(this, sql,
false,
setResultSetType(resultSetType),
-
setResultSetConcurrency(resultSetConcurrency),
+
setResultSetConcurrency(resultSetType, resultSetConcurrency),
resultSetHoldability,
autoGeneratedKeys,
columnIndexes,
@@ -762,7 +762,7 @@
{
return factory.newEmbedCallableStatement(this, sql,
setResultSetType(resultSetType),
-
setResultSetConcurrency(resultSetConcurrency),
+
setResultSetConcurrency(resultSetType, resultSetConcurrency),
resultSetHoldability);
}
finally
@@ -1675,6 +1675,19 @@
return resultSetType;
}
+ private int setResultSetConcurrency(int resultSetType, int
resultSetConcurrency) {
+
+ /* Add warning if updatable resultset is requested on cursor
type other than forward only
+ * and then downgrade the resultset to read only resultset.
+ */
+ if (resultSetType != JDBC20Translation.TYPE_FORWARD_ONLY &&
resultSetConcurrency == JDBC20Translation.CONCUR_UPDATABLE)
+ {
+
addWarning(EmbedSQLWarning.newEmbedSQLWarning(SQLState.UPDATABLE_RESULTSET_FOR_FORWARD_ONLY));
+ resultSetConcurrency =
JDBC20Translation.CONCUR_READ_ONLY;
+ }
+ return resultSetConcurrency;
+ }
+
/**
@@ -1721,19 +1734,6 @@
return getLanguageConnection().getPrepareIsolationLevel();
}
- private int setResultSetConcurrency(int resultSetConcurrency) {
-
- /* Add warning if updatable concurrency
- * and downgrade to read only.
- */
- if (resultSetConcurrency == JDBC20Translation.CONCUR_UPDATABLE)
- {
-
addWarning(EmbedSQLWarning.newEmbedSQLWarning(SQLState.NO_UPDATABLE_CONCURRENCY));
- resultSetConcurrency =
JDBC20Translation.CONCUR_READ_ONLY;
- }
- return resultSetConcurrency;
- }
-
/**
Return a unique order number for a result set.
A unique value is only needed if the result set is
Index: java/engine/org/apache/derby/iapi/sql/ResultSet.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/ResultSet.java (revision
124477)
+++ java/engine/org/apache/derby/iapi/sql/ResultSet.java (working copy)
@@ -312,7 +312,7 @@
public NoPutResultSet[] getSubqueryTrackingArray(int numSubqueries);
/**
- * ResultSet for rowss inserted into the table (contains auto-generated
keys columns only)
+ * ResultSet for rows inserted into the table (contains auto-generated
keys columns only)
*
* @return NoPutResultSet NoPutResultSets for rows inserted into
the table.
*/
Index: java/engine/org/apache/derby/iapi/reference/SQLState.java
===================================================================
--- java/engine/org/apache/derby/iapi/reference/SQLState.java (revision
124477)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java (working copy)
@@ -1379,6 +1379,9 @@
// (no message in message.properties)
String BLOB_SETPOSITION_FAILED = "XJ079.S";
+ //updatable resultset related
+ String UPDATABLE_RESULTSET_API_DISALLOWED = "XJ083.U";
+
//following are session severity.
String DATABASE_NOT_FOUND = "XJ004.C";
String LOGIN_FAILED = "08004";
@@ -1409,9 +1412,10 @@
//following are warning severity.
String DATABASE_EXISTS = "01J01";
String NO_SCROLL_SENSITIVE_CURSORS = "01J02";
- String NO_UPDATABLE_CONCURRENCY = "01J03";
+ String UPDATABLE_RESULTSET_FOR_FORWARD_ONLY = "01J03";
String LANG_TYPE_NOT_SERIALIZABLE = "01J04";
String UPGRADE_SPSRECOMPILEFAILED = "01J05";
+ String QUERY_NOT_QUALIFIED_FOR_UPDATABLE_RESULTSET = "01J06";
//following are database severity
Index: java/engine/org/apache/derby/jdbc/Driver169.java
===================================================================
--- java/engine/org/apache/derby/jdbc/Driver169.java (revision 124477)
+++ java/engine/org/apache/derby/jdbc/Driver169.java (working copy)
@@ -482,8 +482,8 @@
return new EmbedDatabaseMetaData(conn,dbname);
}
- public abstract EmbedResultSet
- newEmbedResultSet(EmbedConnection conn, ResultSet results,
boolean forMetaData, EmbedStatement statement, boolean isAtomic) ;
+ public abstract EmbedResultSet
+ newEmbedResultSet(EmbedConnection conn, ResultSet results,
boolean forMetaData, EmbedStatement statement, boolean isAtomic) throws
SQLException;
// {
// return new EmbedResultSet(conn, results, forMetaData,
statement, isAtomic, false);
// }
Index: java/engine/org/apache/derby/jdbc/Driver20.java
===================================================================
--- java/engine/org/apache/derby/jdbc/Driver20.java (revision 124477)
+++ java/engine/org/apache/derby/jdbc/Driver20.java (working copy)
@@ -157,6 +157,7 @@
public org.apache.derby.impl.jdbc.EmbedResultSet
newEmbedResultSet(EmbedConnection conn, ResultSet results, boolean
forMetaData, org.apache.derby.impl.jdbc.EmbedStatement statement, boolean
isAtomic)
+ throws SQLException
{
return new EmbedResultSet20(conn, results, forMetaData,
statement,
isAtomic);
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties (revision
124477)
+++ java/engine/org/apache/derby/loc/messages_en.properties (working copy)
@@ -1081,6 +1081,7 @@
XJ076.S=The position argument ''{0}'' exceeds the size of the Blob/Clob.
XJ077.S=Got an exception when trying to read the first byte/character of the
Blob/Clob pattern using getBytes/getSubString.
XJ082.U=BLOB/CLOB values are not allowed as method parameters or receiver.
+XJ083.U=''{0}'' not allowed because the ResultSet is not an updatable
ResultSet.
0A000.S=Feature not implemented: {0}.
@@ -1103,9 +1104,10 @@
01J01=Database ''{0}'' not created, connection made to existing database
instead.
01J02=Scroll sensitive cursors are not currently implemented.
-01J03=Updatable ResultSets are not currently implemented.
+01J03=Scroll sensitive and scroll insensitive updatable ResultSets are not
currently implemented.
01J04=The class ''{0}'' for column ''{1}'' does not implement
java.io.Serializable or java.sql.SQLData. Instances must implement one of these
interfaces to allow them to be stored.
01J05=Database upgrade succeeded. The upgraded database is now ready for use.
Revalidating stored prepared statements failed. See next exception for details
of failure.
+01J06=ResultSet not updatable. Query does not qualify to generate an updatable
ResultSet.
XJ001.U=Java exception: ''{1}: {0}''.
XJ050.U=Database requires upgrade from version {0}, set the attribute
''upgrade=true'' on the JDBC connection URL to allow upgrade to version {1}.
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
===================================================================
---
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
(revision 0)
+++
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
(revision 0)
@@ -0,0 +1,801 @@
+/*
+
+ Derby - Class org.apache.derbyTesting.functionTests.lang.updatableResultSet
+
+ Copyright 2004 The Apache Software Foundation or its licensors, as
applicable.
+
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.Statement;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+
+import org.apache.derby.tools.ij;
+import org.apache.derby.tools.JDBCDisplayUtil;
+
+/**
+ This tests JDBC 2.0 updateable resutlset - deleteRow api
+ */
+public class updatableResultSet { private static final String copyrightNotice
= org.apache.derby.iapi.reference.Copyright.SHORT_2003_2004;
+
+ private static Connection conn;
+ private static DatabaseMetaData dbmt;
+ private static Statement stmt, stmt1;
+ private static ResultSet rs;
+ private static PreparedStatement pStmt = null;
+ private static CallableStatement callStmt = null;
+
+ public static void main(String[] args) {
+ System.out.println("Start testing delete using JDBC2.0
updateable resultset apis");
+
+ try {
+ // use the ij utility to read the property file and
+ // make the initial connection.
+ ij.getPropertyArg(args);
+ conn = ij.startJBMS();
+
+ setup(true);
+
+ System.out.println("---Negative Testl - request for
scroll insensitive updatable resultset will give a read only scroll insensitive
resultset");
+ conn.clearWarnings();
+ stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
+ SQLWarning warnings = conn.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("warnings on connection = "
+ warnings);
+ warnings = warnings.getNextWarning();
+ }
+ conn.clearWarnings();
+ System.out.println("requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE
but that is not supported");
+ System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " +
(stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " +
(stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ dbmt = conn.getMetaData();
+
System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? "
+ dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+
System.out.println("othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)?
" + dbmt.othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+
System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " +
dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("JDBC 2.0 updatable resultset api
will fail on this resultset because this is not an updatable resultset");
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ rs.next();
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because Derby does not yet support scroll insensitive updatable
resultsets");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.next();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test2 - request for
scroll sensitive updatable resultset will give a read only scroll insensitive
resultset");
+ stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
+ while (warnings != null)
+ {
+ System.out.println("warnings on connection = "
+ warnings);
+ warnings = warnings.getNextWarning();
+ }
+ conn.clearWarnings();
+ System.out.println("requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE
but that is not supported");
+ System.out.println("Make sure that we got TYPE_SCROLL_INSENSITIVE? " +
(stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("Make sure that we got CONCUR_READ_ONLY? " +
(stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ System.out.println("JDBC 2.0 updatable resultset api
will fail on this resultset because this is not an updatable resultset");
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ rs.next();
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because Derby does not yet support scroll sensitive updatable
resultsets");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.next();//make sure rs.next() does not fail because
of earlier deleteRow
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test3 - request a read
only resultset and attempt deleteRow on it");
+ stmt = conn.createStatement();//the default is a read
only forward only resultset
+ rs = stmt.executeQuery("select * from t1");
+ System.out.println("Make sure that we got
CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ rs.next();
+ System.out.println("Now attempting to send a deleteRow on a read only
resultset.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because this is a read only resultset");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test4 - request a read
only resultset and send a sql with FOR UPDATE clause and attempt deleteRow on
it");
+ stmt = conn.createStatement();//the default is a read
only forward only resultset
+ rs = stmt.executeQuery("select * from t1 FOR UPDATE");
+ System.out.println("Make sure that we got
CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ rs.next();
+ System.out.println("Now attempting to send a deleteRow on a read only
resultset with FOR UPDATE clause in the SELECT sql.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because this is a read only resultset");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test5 - request
updatable resultset for sql with no FOR UPDATE clause");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("select * from t1");//notice
that we forgot to give mandatory FOR UPDATE clause for updatable resultset
+ System.out.println("Make sure that we got
CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ warnings = rs.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("Expected warnings on
resultset = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ rs.clearWarnings();
+ rs.next();
+ System.out.println("Now attempting to send a delete on a sql with no FOR
UPDATE clause.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed on sql with no FOR UPDATE clause");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test6 - request
updatable resultset for sql with FOR READ ONLY clause");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("select * from t1 FOR READ
ONLY");
+ System.out.println("Make sure that we got
CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ warnings = rs.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("Expected warnings on
resultset = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ rs.clearWarnings();
+ rs.next();
+ System.out.println("Now attempting to send a delete on a sql with FOR
READ ONLY clause.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed on sql with FOR READ ONLY clause");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XJ083"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Negative Test7 - attempt to
deleteRow on updatable resultset when the resultset is not positioned on a
row");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ System.out.println("Make sure that we got
CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ System.out.println("Now attempt a deleteRow without first doing next on
the resultset.");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because resultset is not on a row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ while (rs.next());//read all the rows from the
resultset and position after the last row
+ System.out.println("ResultSet is positioned after the last row. attempt
to deleteRow at this point should fail!");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because resultset is after the last row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.close();
+
+ System.out.println("---Negative Test8 - attempt
deleteRow on updatable resultset after closing the resultset");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ System.out.println("Make sure that we got
CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ rs.next();
+ rs.close();
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because resultset is closed");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Negative Test9 - try updatable
resultset on system table");
+ try {
+ rs = stmt.executeQuery("SELECT * FROM
sys.systables FOR UPDATE");
+ System.out.println("FAIL!!! trying to open an
updatable resultset on a system table should have failed because system tables
can't be updated by a user");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("42Y90"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Negative Test10 - try updatable
resultset on a view");
+ try {
+ rs = stmt.executeQuery("SELECT * FROM v1 FOR
UPDATE");
+ System.out.println("FAIL!!! trying to open an
updatable resultset on a view should have failed because Derby doesnot support
updates to views yet");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("42Y90"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ stmt.executeUpdate("drop view v1");
+
+ System.out.println("---Negative Test11 - attempt to
open updatable resultset when there is join in the select query should fail");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ try {
+ rs = stmt.executeQuery("SELECT c1 FROM t1,t2
where t1.c1 = t2.c21 FOR UPDATE");
+ System.out.println("FAIL!!! trying to open an
updatable resultset should have failed because updatable resultset donot
support join in the select query");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("42Y90"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Negative Test12 - With
autocommit on, attempt to drop a table when there is an open updatable
resultset on it");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ System.out.println("Opened an updatable resultset. Now
trying to drop that table through another Statement");
+ stmt1 = conn.createStatement();
+ try {
+ stmt1.executeUpdate("drop table t1");
+ System.out.println("FAIL!!! drop table should
have failed because the updatable resultset is still open");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("X0X95")) {
+ System.out.println("expected exception
" + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Since autocommit is on, the drop
table exception resulted in a runtime rollback causing updatable resultset
object to close");
+ try {
+ rs.deleteRow();
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Negative Test13 - foreign key
constraint failure will cause deleteRow to fail");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR
UPDATE");
+ rs.next();
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because it will cause foreign key constraint failure");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("23503"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Since autocommit is on, the
constraint exception resulted in a runtime rollback causing updatable resultset
object to close");
+ try {
+ rs.next();
+ System.out.println("FAIL!!! next should have
failed because foreign key constraint failure resulted in a runtime rollback");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL16"))
+ System.out.println("expected exception
" + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+
+ System.out.println("---Positive Test1 - request
updatable resultset for forward only type resultset");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ warnings = conn.getWarnings();
+ while (warnings != null)
+ {
+ System.out.println("warnings = " + warnings);
+ warnings = warnings.getNextWarning();
+ }
+ System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
+ System.out.println("got TYPE_FORWARD_ONLY? " + (stmt.getResultSetType()
== ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("got CONCUR_UPDATABLE? " +
(stmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ System.out.println("JDBC 2.0 updatable resultset apis
on this ResultSet object will pass because this is an updatable resultset");
+ rs.next();
+ System.out.println("column 1 on this row before deleteRow is " +
rs.getInt(1));
+ System.out.println("column 2 on this row before deleteRow is " +
rs.getString(2));
+ rs.deleteRow();
+ System.out.println("Since after deleteRow(), ResultSet is positioned
before the next row, getXXX will fail");
+ try {
+ System.out.println("column 1 on this deleted
row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling deleteRow again w/o first positioning the
ResultSet on the next row will fail");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because it can't be called more than once on the same row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Position the ResultSet with next()");
+ rs.next();
+ System.out.println("Should be able to deletRow() on the current row
now");
+ rs.deleteRow();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test2 - even if no
columns from table specified in the column list, we should be able to get
updatable resultset");
+ reloadData();
+ System.out.println("total number of rows in T1 ");
+ dumpRS(stmt.executeQuery("select count(*) from t1"));
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ rs.deleteRow();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+ System.out.println("total number of rows in T1 after one deleteRow is ");
+ dumpRS(stmt.executeQuery("select count(*) from t1"));
+
+ System.out.println("---Positive Test3 - use prepared
statement with concur updatable status");
+ reloadData();
+ pStmt = conn.prepareStatement("select * from t1 where
c1>? for update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
+ System.out.println("got TYPE_FORWARD_ONLY? " +
(pStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("got CONCUR_UPDATABLE? " +
(pStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ pStmt.setInt(1,0);
+ rs = pStmt.executeQuery();
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ rs.deleteRow();
+ System.out.println("Since after deleteRow(), ResultSet is positioned
before the next row, getXXX will fail");
+ try {
+ System.out.println("column 1 on this deleted
row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling deleteRow again w/o first positioning the
ResultSet on the next row will fail");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because it can't be called more than once on the same row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Position the ResultSet with next()");
+ rs.next();
+ System.out.println("Should be able to deletRow() on the current row
now");
+ rs.deleteRow();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test4 - use callable
statement with concur updatable status");
+ reloadData();
+ callStmt = conn.prepareCall("select * from t1 for
update", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = callStmt.executeQuery();
+ System.out.println("requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE");
+ System.out.println("got TYPE_FORWARD_ONLY? " +
(callStmt.getResultSetType() == ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("got CONCUR_UPDATABLE? " +
(callStmt.getResultSetConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ rs.next();
+ System.out.println("row not deleted yet. Confirm with rs.rowDeleted()? "
+ rs.rowDeleted());
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ rs.deleteRow();
+ System.out.println("Since after deleteRow(), ResultSet is positioned
before the next row, getXXX will fail");
+ try {
+ System.out.println("column 1 on this deleted
row is " + rs.getInt(1));
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("calling deleteRow again w/o first positioning the
ResultSet on the next row will fail");
+ try {
+ rs.deleteRow();
+ System.out.println("FAIL!!! deleteRow should
have failed because it can't be called more than once on the same row");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Position the ResultSet with next()");
+ rs.next();
+ System.out.println("Should be able to deletRow() on the current row
now");
+ rs.deleteRow();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test5 - donot have to
select primary key to get an updatable resultset");
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT c32 FROM t3 FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ System.out.println("now try to delete row when primary key is not
selected for that row");
+ rs.deleteRow();
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test6 - For Forward
Only resultsets, DatabaseMetaData will return false for ownDeletesAreVisible
and deletesAreDetected");
+ System.out.println("---This is because, after
deleteRow, we position the ResultSet before the next row. We don't make a hole
for the deleted row and then stay on that deleted hole");
+ dbmt = conn.getMetaData();
+ System.out.println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
+ dbmt.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+
System.out.println("othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? " +
dbmt.othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " +
dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
+ rs.next();
+ System.out.println("The JDBC program should look at rowDeleted only if
deletesAreDetected returns true");
+ System.out.println("Since Derby returns false for detlesAreDetected for
FORWARD_ONLY updatable resultset,the program should not rely on rs.rowDeleted()
for FORWARD_ONLY updatable resultsets");
+ System.out.println("Have this call to rs.rowDeleted() just to make sure
the method does always return false? " + rs.rowDeleted());
+ rs.deleteRow();
+ System.out.println("Have this call to rs.rowDeleted() just to make sure
the method does always return false? " + rs.rowDeleted());
+ rs.close();
+
+ System.out.println("---Positive Test7 - delete using
updatable resultset api from a temporary table");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt.executeUpdate("DECLARE GLOBAL TEMPORARY TABLE
SESSION.t2(c21 int, c22 int) on commit preserve rows not logged");
+ stmt.executeUpdate("insert into SESSION.t2 values(21,
1)");
+ stmt.executeUpdate("insert into SESSION.t2 values(22,
1)");
+ System.out.println("following rows in temp table before
deleteRow");
+ dumpRS(stmt.executeQuery("select * from SESSION.t2"));
+ rs = stmt.executeQuery("select c21 from session.t2 for
update");
+ rs.next();
+ rs.deleteRow();
+ rs.next();
+ rs.deleteRow();
+ System.out.println("As expected, no rows in temp table
after deleteRow");
+ dumpRS(stmt.executeQuery("select * from SESSION.t2"));
+ rs.close();
+ stmt.executeUpdate("DROP TABLE SESSION.t2");
+
+ System.out.println("---Positive Test8 - change the name
of the resultset and see if deleteRow still works");
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("change the cursor name(case sensitive name) with
setCursorName and then try to deleteRow");
+ stmt.setCursorName("CURSORNOUPDATe");//notice this name
is case sensitive
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
+ rs.next();
+ rs.deleteRow();
+ System.out.println("change the cursor name one more time with
setCursorName and then try to deleteRow");
+ stmt.setCursorName("CURSORNOUPDATE1");
+ rs.next();
+ rs.deleteRow();
+ rs.close();
+
+ System.out.println("---Positive Test9 - using
correlation name for the table in the select sql is not a problem");
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 abcde FOR UPDATE of c1");
+ rs.next();
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ System.out.println("now try to deleteRow");
+ rs.deleteRow();
+ rs.close();
+
+ System.out.println("---Positive Test10 - 2 updatable
resultsets going against the same table, will they conflict?");
+ conn.setAutoCommit(false);
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt1 =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ ResultSet rs1 = stmt1.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs1.next();
+ System.out.println("delete using first resultset");
+ rs.deleteRow();
+ try {
+ System.out.println("attempt to send deleteRow
on the same row through a different resultset should throw an exception");
+ rs1.deleteRow();
+ System.out.println("FAIL!!! delete using second
resultset succedded? " + rs1.rowDeleted());
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("XCL08"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Move to next row in the 2nd
resultset and then delete using the second resultset");
+ rs1.next();
+ rs1.deleteRow();
+ rs.close();
+ rs1.close();
+ conn.setAutoCommit(true);
+
+ System.out.println("---Positive Test11 - setting the
fetch size to > 1 will be ignored by updatable resultset. Same as updatable
cursors");
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ stmt.executeUpdate("call
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
+ stmt.setFetchSize(200);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE of c1");
+ System.out.println("Notice the Fetch Size in run time
statistics output.");
+ showScanStatistics(rs, conn);
+ System.out.println("statement's fetch size is " +
stmt.getFetchSize());
+ rs.close();
+ stmt.executeUpdate("call
SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)");
+
+ System.out.println("---Positive Test12 - make sure
delete trigger gets fired when deleteRow is issued");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ System.out.println("Verify that before delete trigger
got fired, row count is 0 in deleteTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from
deleteTriggerInsertIntoThisTable"));
+ rs = stmt.executeQuery("SELECT * FROM
tableWithDeleteTriggers FOR UPDATE");
+ rs.next();
+ System.out.println("column 1 on this row is " +
rs.getInt(1));
+ System.out.println("now try to delete row and make sure
that trigger got fired");
+ rs.deleteRow();
+ rs.close();
+ System.out.println("Verify that delete trigger got
fired by verifying the row count to be 1 in deleteTriggerInsertIntoThisTable");
+ dumpRS(stmt.executeQuery("select count(*) from
deleteTriggerInsertIntoThisTable"));
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ System.out.println("---Positive Test13 - With
autocommit off, attempt to drop a table when there is an open updatable
resultset on it");
+ reloadData();
+ conn.setAutoCommit(false);
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ System.out.println("Opened an updatable resultset. Now
trying to drop that table through another Statement");
+ stmt1 = conn.createStatement();
+ try {
+ stmt1.executeUpdate("drop table t1");
+ System.out.println("FAIL!!! drop table should
have failed because the updatable resultset is still open");
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("X0X95")) {
+ System.out.println("expected exception
" + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("Since autocommit is off, the drop
table exception will NOT result in a runtime rollback and hence updatable
resultset object is still open");
+ rs.deleteRow();
+ rs.close();
+ conn.setAutoCommit(true);
+
+ System.out.println("---Positive Test14 - After
deleteRow, resultset is positioned before the next row");
+ reloadData();
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ rs.next();
+ rs.deleteRow();
+ System.out.println("getXXX right after deleteRow will fail because
resultset is not positioned on a row, instead it is right before the next row");
+ try {
+ System.out.println("column 1 (which is not
nullable) after deleteRow is " + rs.getString(1));
+ }
+ catch (SQLException e) {
+ if (e.getSQLState().equals("24000")) {
+ System.out.println("expected exception
" + e.getMessage());
+ } else
+ dumpSQLExceptions(e);
+ }
+ rs.close();
+
+ teardown();
+
+ conn.close();
+
+ } catch (Throwable e) {
+ System.out.println("FAIL: exception thrown:");
+ JDBCDisplayUtil.ShowException(System.out,e);
+ }
+
+ System.out.println("Finished testing updateable resultsets");
+ }
+
+ // lifted from the autoGeneratedJdbc30 test
+ public static void dumpRS(ResultSet s) throws SQLException
+ {
+ if (s == null)
+ {
+ System.out.println("<NULL>");
+ return;
+ }
+
+ ResultSetMetaData rsmd = s.getMetaData();
+
+ // Get the number of columns in the result set
+ int numCols = rsmd.getColumnCount();
+
+ if (numCols <= 0)
+ {
+ System.out.println("(no columns!)");
+ return;
+ }
+
+ StringBuffer heading = new StringBuffer("\t ");
+ StringBuffer underline = new StringBuffer("\t ");
+
+ int len;
+ // Display column headings
+ for (int i=1; i<=numCols; i++)
+ {
+ if (i > 1)
+ {
+ heading.append(",");
+ underline.append(" ");
+ }
+ len = heading.length();
+ heading.append(rsmd.getColumnLabel(i));
+ len = heading.length() - len;
+ for (int j = len; j > 0; j--)
+ {
+ underline.append("-");
+ }
+ }
+ System.out.println(heading.toString());
+ System.out.println(underline.toString());
+
+
+ StringBuffer row = new StringBuffer();
+ // Display data, fetching until end of the result set
+ while (s.next())
+ {
+ row.append("\t{");
+ // Loop through each column, getting the
+ // column data and displaying
+ for (int i=1; i<=numCols; i++)
+ {
+ if (i > 1) row.append(",");
+ row.append(s.getString(i));
+ }
+ row.append("}\n");
+ }
+ System.out.println(row.toString());
+ s.close();
+ }
+
+ static void reloadData() throws SQLException {
+ Statement stmt = conn.createStatement();
+ stmt.executeUpdate("delete from t1");
+ stmt.executeUpdate("insert into t1 values (1,'aa')");
+ stmt.executeUpdate("insert into t1 values (2,'bb')");
+ stmt.executeUpdate("insert into t1 values (3,'cc')");
+ stmt.executeUpdate("delete from t3");
+ stmt.executeUpdate("insert into t3 values (1,1)");
+ stmt.executeUpdate("insert into t3 values (2,2)");
+ }
+
+ static void setup(boolean first) throws SQLException {
+ Statement stmt = conn.createStatement();
+ stmt.executeUpdate("create table t1 (c1 int, c2 char(20))");
+ stmt.executeUpdate("create view v1 as select * from t1");
+ stmt.executeUpdate("create table t2 (c21 int, c22 int)");
+ stmt.executeUpdate("create table t3 (c31 int not null primary
key, c32 smallint)");
+ stmt.executeUpdate("create table tableWithPrimaryKey (c1 int
not null, c2 int not null, constraint pk primary key(c1,c2))");
+ stmt.executeUpdate("create table tableWithConstraint (c1 int,
c2 int, constraint fk foreign key(c1,c2) references tableWithPrimaryKey)");
+ stmt.executeUpdate("create table tableWithDeleteTriggers (c1
int, c2 bigint)");
+ stmt.executeUpdate("create table
deleteTriggerInsertIntoThisTable (c1 int)");
+ stmt.executeUpdate("create trigger tr1 after delete on
tableWithDeleteTriggers for each statement mode db2sql insert into
deleteTriggerInsertIntoThisTable values (1)");
+
+ stmt.executeUpdate("insert into t1 values (1,'aa')");
+ stmt.executeUpdate("insert into t1 values (2,'bb')");
+ stmt.executeUpdate("insert into t1 values (3,'cc')");
+ stmt.executeUpdate("insert into t2 values (1,1)");
+ stmt.executeUpdate("insert into t3 values (1,1)");
+ stmt.executeUpdate("insert into t3 values (2,2)");
+ stmt.executeUpdate("insert into tableWithPrimaryKey values (1,
1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("insert into tableWithConstraint values (1,
1), (2, 2), (3, 3), (4, 4)");
+ stmt.executeUpdate("insert into tableWithDeleteTriggers values
(1, 1), (2, 2), (3, 3), (4, 4)");
+ stmt.close();
+ }
+
+
+ static void teardown() throws SQLException {
+ Statement stmt = conn.createStatement();
+ stmt.executeUpdate("drop table t1");
+ stmt.executeUpdate("drop table t2");
+ stmt.executeUpdate("drop table t3");
+ stmt.executeUpdate("drop table tableWithConstraint");
+ stmt.executeUpdate("drop table tableWithPrimaryKey");
+ conn.commit();
+ stmt.close();
+ }
+
+ public static void showScanStatistics(ResultSet rs, Connection conn)
+ {
+ Statement s = null;
+ ResultSet infors = null;
+
+
+ try {
+ rs.close(); // need to close to get statistics
+ s =conn.createStatement();
+ infors = s.executeQuery("values
SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
+ JDBCDisplayUtil.setMaxDisplayWidth(2000);
+ JDBCDisplayUtil.DisplayResults(System.out,infors,conn);
+ infors.close();
+ }
+ catch (SQLException se)
+ {
+ System.out.print("FAIL:");
+ JDBCDisplayUtil.ShowSQLException(System.out,se);
+ }
+ }
+
+ static private void dumpSQLExceptions (SQLException se) {
+ System.out.println("FAIL -- unexpected exception: " +
se.toString());
+ while (se != null) {
+ System.out.print("SQLSTATE("+se.getSQLState()+"):");
+ se = se.getNextException();
+ }
+ }
+
+}
Property changes on:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/updatableResultSet.java
___________________________________________________________________
Name: svn:eol-style
+ native
Index:
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
===================================================================
---
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
(revision 0)
+++
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
(revision 0)
@@ -0,0 +1,222 @@
+Start testing delete using JDBC2.0 updateable resultset apis
+---Negative Testl - request for scroll insensitive updatable resultset will
give a read only scroll insensitive resultset
+warnings on connection = SQL Warning: Scroll sensitive and scroll insensitive
updatable ResultSets are not currently implemented.
+requested TYPE_SCROLL_INSENSITIVE, CONCUR_UPDATABLE but that is not supported
+Make sure that we got TYPE_SCROLL_INSENSITIVE? true
+Make sure that we got CONCUR_READ_ONLY? true
+ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
+othersDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
+deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
+JDBC 2.0 updatable resultset api will fail on this resultset because this is
not an updatable resultset
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test2 - request for scroll sensitive updatable resultset will give
a read only scroll insensitive resultset
+requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE but that is not supported
+Make sure that we got TYPE_SCROLL_INSENSITIVE? true
+Make sure that we got CONCUR_READ_ONLY? true
+JDBC 2.0 updatable resultset api will fail on this resultset because this is
not an updatable resultset
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test3 - request a read only resultset and attempt deleteRow on it
+Make sure that we got CONCUR_READ_ONLY? true
+Now attempting to send a deleteRow on a read only resultset.
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test4 - request a read only resultset and send a sql with FOR
UPDATE clause and attempt deleteRow on it
+Make sure that we got CONCUR_READ_ONLY? true
+Now attempting to send a deleteRow on a read only resultset with FOR UPDATE
clause in the SELECT sql.
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test5 - request updatable resultset for sql with no FOR UPDATE
clause
+Make sure that we got CONCUR_READ_ONLY? true
+Expected warnings on resultset = java.sql.SQLWarning: ResultSet not updatable.
Query does not qualify to generate an updatable ResultSet.
+Now attempting to send a delete on a sql with no FOR UPDATE clause.
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test6 - request updatable resultset for sql with FOR READ ONLY
clause
+Make sure that we got CONCUR_READ_ONLY? true
+Expected warnings on resultset = java.sql.SQLWarning: ResultSet not updatable.
Query does not qualify to generate an updatable ResultSet.
+Now attempting to send a delete on a sql with FOR READ ONLY clause.
+Got expected exception 'deleteRow' not allowed because the ResultSet is not an
updatable ResultSet.
+---Negative Test7 - attempt to deleteRow on updatable resultset when the
resultset is not positioned on a row
+Make sure that we got CONCUR_UPDATABLE? true
+Now attempt a deleteRow without first doing next on the resultset.
+Got expected exception Invalid cursor state - no current row.
+ResultSet is positioned after the last row. attempt to deleteRow at this point
should fail!
+Got expected exception Invalid cursor state - no current row.
+---Negative Test8 - attempt deleteRow on updatable resultset after closing the
resultset
+Make sure that we got CONCUR_UPDATABLE? true
+Got expected exception ResultSet not open, operation 'deleteRow' not
permitted. Verify that autocommit is OFF.
+---Negative Test9 - try updatable resultset on system table
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test10 - try updatable resultset on a view
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test11 - attempt to open updatable resultset when there is join in
the select query should fail
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test12 - With autocommit on, attempt to drop a table when there is
an open updatable resultset on it
+Opened an updatable resultset. Now trying to drop that table through another
Statement
+expected exception Operation 'DROP TABLE' cannot be performed on object 'T1'
because there is an open ResultSet dependent on that object.
+Since autocommit is on, the drop table exception resulted in a runtime
rollback causing updatable resultset object to close
+expected exception ResultSet not open, operation 'deleteRow' not permitted.
Verify that autocommit is OFF.
+---Negative Test13 - foreign key constraint failure will cause deleteRow to
fail
+expected exception DELETE on table 'TABLEWITHPRIMARYKEY' caused a violation of
foreign key constraint 'FK' for key (1,1). The statement has been rolled back.
+Since autocommit is on, the constraint exception resulted in a runtime
rollback causing updatable resultset object to close
+expected exception ResultSet not open, operation 'next' not permitted. Verify
that autocommit is OFF.
+---Positive Test1 - request updatable resultset for forward only type resultset
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+JDBC 2.0 updatable resultset apis on this ResultSet object will pass because
this is an updatable resultset
+column 1 on this row before deleteRow is 1
+column 2 on this row before deleteRow is aa
+Since after deleteRow(), ResultSet is positioned before the next row, getXXX
will fail
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+Got expected exception Invalid cursor state - no current row.
+Position the ResultSet with next()
+Should be able to deletRow() on the current row now
+---Positive Test2 - even if no columns from table specified in the column
list, we should be able to get updatable resultset
+total number of rows in T1
+ 1
+ -
+ {3}
+column 1 on this row is 1
+total number of rows in T1 after one deleteRow is
+ 1
+ -
+ {2}
+---Positive Test3 - use prepared statement with concur updatable status
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+column 1 on this row is 1
+Since after deleteRow(), ResultSet is positioned before the next row, getXXX
will fail
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+Got expected exception Invalid cursor state - no current row.
+Position the ResultSet with next()
+Should be able to deletRow() on the current row now
+---Positive Test4 - use callable statement with concur updatable status
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+Since after deleteRow(), ResultSet is positioned before the next row, getXXX
will fail
+Got expected exception Invalid cursor state - no current row.
+calling deleteRow again w/o first positioning the ResultSet on the next row
will fail
+Got expected exception Invalid cursor state - no current row.
+Position the ResultSet with next()
+Should be able to deletRow() on the current row now
+---Positive Test5 - donot have to select primary key to get an updatable
resultset
+column 1 on this row is 1
+now try to delete row when primary key is not selected for that row
+---Positive Test6 - For Forward Only resultsets, DatabaseMetaData will return
false for ownDeletesAreVisible and deletesAreDetected
+---This is because, after deleteRow, we position the ResultSet before the next
row. We don't make a hole for the deleted row and then stay on that deleted hole
+ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? false
+othersDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true
+deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? false
+The JDBC program should look at rowDeleted only if deletesAreDetected returns
true
+Since Derby returns false for detlesAreDetected for FORWARD_ONLY updatable
resultset,the program should not rely on rs.rowDeleted() for FORWARD_ONLY
updatable resultsets
+Have this call to rs.rowDeleted() just to make sure the method does always
return false? false
+Have this call to rs.rowDeleted() just to make sure the method does always
return false? false
+---Positive Test7 - delete using updatable resultset api from a temporary table
+following rows in temp table before deleteRow
+ C21,C22
+ --- ---
+ {21,1}
+ {22,1}
+As expected, no rows in temp table after deleteRow
+ C21,C22
+ --- ---
+---Positive Test8 - change the name of the resultset and see if deleteRow
still works
+change the cursor name(case sensitive name) with setCursorName and then try to
deleteRow
+change the cursor name one more time with setCursorName and then try to
deleteRow
+---Positive Test9 - using correlation name for the table in the select sql is
not a problem
+column 1 on this row is 1
+now try to deleteRow
+---Positive Test10 - 2 updatable resultsets going against the same table, will
they conflict?
+delete using first resultset
+attempt to send deleteRow on the same row through a different resultset should
throw an exception
+Got expected exception Cursor 'SQLCUR10' is not on a row.
+Move to next row in the 2nd resultset and then delete using the second
resultset
+---Positive Test11 - setting the fetch size to > 1 will be ignored by
updatable resultset. Same as updatable cursors
+Notice the Fetch Size in run time statistics output.
+1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+Statement Name:
+ null
+Statement Text:
+ SELECT 1, 2 FROM t1 FOR UPDATE of c1
+Parse Time: 0
+Bind Time: 0
+Optimize Time: 0
+Generate Time: 0
+Compile Time: 0
+Execute Time: 0
+Begin Compilation Timestamp : null
+End Compilation Timestamp : null
+Begin Execution Timestamp : null
+End Execution Timestamp : null
+Statement Execution Plan Text:
+Project-Restrict ResultSet (3):
+Number of opens = 1
+Rows seen = 0
+Rows filtered = 0
+restriction = false
+projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+Source result set:
+ Project-Restrict ResultSet (2):
+ Number of opens = 1
+ Rows seen = 0
+ Rows filtered = 0
+ restriction = false
+ projection = true
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ restriction time (milliseconds) = 0
+ projection time (milliseconds) = 0
+ Source result set:
+ Table Scan ResultSet for T1 at read committed isolation level
using exclusive row locking chosen by the optimizer
+ Number of opens = 1
+ Rows seen = 0
+ Rows filtered = 0
+ Fetch Size = 1
+ constructor time (milliseconds) = 0
+ open time (milliseconds) = 0
+ next time (milliseconds) = 0
+ close time (milliseconds) = 0
+ scan information:
+ Bit set of columns fetched=All
+ Number of columns fetched=2
+ Number of pages visited=0
+ Number of rows qualified=0
+ Number of rows visited=0
+ Scan type=heap
+ start position:
+null stop position:
+null qualifiers:
+None
+statement's fetch size is 200
+---Positive Test12 - make sure delete trigger gets fired when deleteRow is
issued
+Verify that before delete trigger got fired, row count is 0 in
deleteTriggerInsertIntoThisTable
+ 1
+ -
+ {0}
+column 1 on this row is 1
+now try to delete row and make sure that trigger got fired
+Verify that delete trigger got fired by verifying the row count to be 1 in
deleteTriggerInsertIntoThisTable
+ 1
+ -
+ {1}
+---Positive Test13 - With autocommit off, attempt to drop a table when there
is an open updatable resultset on it
+Opened an updatable resultset. Now trying to drop that table through another
Statement
+expected exception Operation 'DROP TABLE' cannot be performed on object 'T1'
because there is an open ResultSet dependent on that object.
+Since autocommit is off, the drop table exception will NOT result in a runtime
rollback and hence updatable resultset object is still open
+---Positive Test14 - After deleteRow, resultset is positioned before the next
row
+getXXX right after deleteRow will fail because resultset is not positioned on
a row, instead it is right before the next row
+expected exception Invalid cursor state - no current row.
+Finished testing updateable resultsets
Property changes on:
java/testing/org/apache/derbyTesting/functionTests/master/updatableResultSet.out
___________________________________________________________________
Name: svn:eol-style
+ native
Index:
java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(revision 124477)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(working copy)
@@ -127,6 +127,7 @@
lang/ungroupedAggregatesNegative.sql
lang/union.sql
lang/unlimited.sql
+lang/updatableResultSet.java
lang/update.sql
lang/updateCursor.java
lang/valuesclause.sql
