Hi,
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.
Following is a brief description of what the patch provides
The JDBC 2.0 API introduced the ability to update/delete/insert rows
from a ResultSet using methods in the Java programming language rather
than having to send an SQL command. In order to be able to update a
ResultSet using these new JDBC 2.0 apis, the ResultSet should be
updatable. The JDBC programmer gets an updatable ResultSet by supplying
ResultSet.CONCUR_UPDATABLE to the cresteStatement method.
Derby currently supports FORWARD_ONLY and SCROLL_INSENSITIVE
resultsets in read only mode. There is no support for SCROLL_SENSITIVE
resultsets. In addition, SQL standards do not support updatable
SCROLL_INSENSITIVE cursors.
Based on these facts, this patch will support updatable ResultSet for
only ResultSet.TYPE_FORWARD_ONLY. You get such a ResultSet
with following createStatement call
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
If createStatement tries to get an updatable ResultSet for
SCROLL_INSENSITIVE or SCROLL_SENSITIVE ResultSet, there will
be a warning accumulated on the Connection object and the driver will
return SCROLL_INSENSITIVE READ_ONLY ResultSet.
Implementation of this forward only updatable ResultSet is coded based
on the existing positioned update/delete cursor code. Because of that,
the select sql sent on the Statement object will have the same syntax
and restrictions as for FOR UPDATE sql for positioned updatable cursors.
eg
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT c32 FROM t3 FOR UPDATE");
Please refer to "SELECT statement" in Derby Reference manual index.
In that same section, there is "Requirements for Updatable Cursors"
sub-section which goes over what is allowed in the SELECT sql for an
updatable cursor.
One difference to notice between updatable cursors and updatable
ResultSets is that the JDBC program is not required to have autocommit
off when using updatable ResultSets JDBC apis..
Now, moving on to what happens when a deleteRow method is called
after the updatable ResultSet is positioned on a row. deleteRow will
delete the row from the database and the ResultSet object will be
positioned before the next row. The program then need to reposition
the ResultSet with next() before issuing any methods other than close
on the ResultSet object.
Also, because the Resultset is positioned before the next row,
the deleted row is not detected and is not visible. Hence, the
DatabaseMetaData methods, ownDeletesAreVisible and
deletesAreDetected will return false for FORWARD_ONLY
updatable ResultSets. And as per the JDBC specs,
ResultSet.rowDeleted should not be relied upon, since the
driver can't detect deletes.
I think that covers everything. Following is a good list of reference
material related to this patch
JDBC Tutotial
http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/makingupdates.html
SQL standards
Derby Documentation for Updatable Cursors
ResultSet, DatabaseMetaData apis in JDBC 2.0
Please send in your vote/comments/concerns.
Mamta
Output of svn stat
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\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
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (revision
122616)
+++ 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;
Index: java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
(revision 122616)
+++ 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;
}
@@ -2446,13 +2452,13 @@
* @return true if changes are visible for the result set type
*/
public boolean othersUpdatesAreVisible(int type) {
- return true;
+ return false;
}
public boolean othersDeletesAreVisible(int type) {
- return true;
+ return false;
}
public boolean othersInsertsAreVisible(int type) {
- return true;
+ return false;
}
/**
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet20.java
(revision 122616)
+++ 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;
@@ -214,7 +217,7 @@
* @exception SQLException Thrown on error.
*/
public boolean isAfterLast() throws SQLException
- {
+ {
return checkRowPosition(ResultSet.ISAFTERLAST, "isAfterLast");
}
@@ -520,14 +523,14 @@
* JDBC 2.0
*
* Return the concurrency of this result set. The concurrency
- * used is determined by the statement that created the result set.
+ * is determined by checking if this resultset is for a select for update
statement.
*
* @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 (isForUpdate() ? JDBC20Translation.CONCUR_UPDATABLE :
JDBC20Translation.CONCUR_READ_ONLY);
}
//---------------------------------------------------------------------
@@ -579,7 +582,7 @@
* @see EmbedDatabaseMetaData#deletesAreDetected
*/
public boolean rowDeleted() throws SQLException {
- throw Util.notImplemented();
+ return false;
}
/**
@@ -1336,9 +1339,47 @@
* 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_DIALLOWED,
"deleteRow");
+
+ setupContextStack();
+ try {
+ if (theResults.isClosed())
+ throw
Util.generateCsSQLException(SQLState.LANG_RESULT_SET_NOT_OPEN, "deleteRow");
+ //if there is already a hole here, driver will catch it. That
will then give us the same behavior as positioned deletes
+ 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, getSQLText(), getParameterValueSet(), 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();
+ 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
122616)
+++ 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
122616)
+++ 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
122616)
+++ 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_DIALLOWED = "XJ083.U";
+
//following are session severity.
String DATABASE_NOT_FOUND = "XJ004.C";
String LOGIN_FAILED = "08004";
@@ -1409,7 +1412,7 @@
//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";
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties (revision
122616)
+++ 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,7 +1104,7 @@
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.
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,761 @@
+/*
+
+ 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));
+ 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
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_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ 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 Test5 - 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");
+ 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 Test6 - 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("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 Test7 - 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");
+ 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 Test8 - 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 Test9 - 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 Test10 - 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 Test11 - 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 Test12 - 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,209 @@
+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
+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 updatable resultset for sql with no FOR UPDATE
clause
+Make sure that we got CONCUR_UPDATABLE? false
+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 Test5 - request updatable resultset for sql with FOR READ ONLY
clause
+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 Test6 - attempt to deleteRow on updatable resultset when the
resultset is not positioned on a row
+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 Test7 - attempt deleteRow on updatable resultset after closing the
resultset
+Got expected exception ResultSet not open, operation 'deleteRow' not
permitted. Verify that autocommit is OFF.
+---Negative Test8 - try updatable resultset on system table
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test9 - try updatable resultset on a view
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test10 - 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 Test11 - 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 Test12 - 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
+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 122616)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(working copy)
@@ -126,6 +126,7 @@
lang/ungroupedAggregatesNegative.sql
lang/union.sql
lang/unlimited.sql
+lang/updatableResultSet.java
lang/update.sql
lang/updateCursor.java
lang/valuesclause.sql
