Hi,
Now that we have branched the codeline for a stable release, it looks
like this might be a good time to come back to the patch for
JDBC 2.0 Updatable Resultset apis - delete functionality only.
Since this is a new feature, it was decided on the list to wait until
a stable release to review and checkin the patch.
I will start out with my +1 vote :-). Please send in your votes.
The changes are pretty much the same as what I had submitted couple of
weeks back with 2 exceptions. I have added property svn:eol-style
native to the test file and the master output file for the test. In
addition, I have changed the header of the test file to use the
standard Apache Copyright header.
Following is a brief description of the proposed functionality.
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 resulset 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 resultsets 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 resultsets, 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 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 Derby documentation under "SELECT statement" section.
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.
Now, moving on to what happens when a deleteRow method is called on
an updatable resultset. A ResultSet.deleteRow api will leave a "hole"
in the resultset which means that the deleted row is not removed from
the resulset and stays visible in the resultset. The corresponding
DatabaseMetaData.ownDeletesAreVisible method will return true for
ResultSet.TYPE_FORWARD_ONLY.
A blank row ("hole") is used as a placeholder where the deleted row
used to be. All the columns in the hole would return 0/NULL on getXXX
method calls (depending on the data type). There are metadata calls in
JDBC 2.0 apis which help the JDBC user to detect such holes.
DatabaseMetaData.deletesAreDetected will return true for
ResultSet.TYPE_FORWARD_ONLY.
ResultSet.rowDeleted will return true if the resultset is positioned on
a row that has been deleted using deleteRow.
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
Index: java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (revision
110079)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedResultSet.java (working copy)
@@ -138,7 +138,21 @@
protected final boolean isAtomic;
+ /*
+ This is to keep track of the current row status. In JDBC 2.0,
rows can be inserted / updated / deleted using JDBC apis.
+ Derby at this point implements forward only updatable
resultsets and hence we only need to worry about the
+ status of the current row since there is no way to go backwards
in forward only resultset. When implementing
+ the support for other kinds of updatable resultsets, we might
need to start keeping track of all updated/deleted
+ rows in a resultset since it is possible to move anywhere in
the resultset with insensitive/sensitive resultset types.
+
+ For forward only updatable resultsets, 0 would mean not
changed, 1 would mean deleted.
+
+ The reason I have this variable in JDBC1.0 class is that
forward only cursor movement method next and close are in this class
+ and every call to those methods should initialize this variable
to 0.
+ */
+ protected short rowGotUpdatedDeleted = 0;
+
/**
* This class provides the glue between the Cloudscape
* resultset and the JDBC resultset, mapping calls-to-calls.
@@ -257,6 +271,7 @@
}
}
+ rowGotUpdatedDeleted = 0;
return movePosition(NEXT, 0, "next");
}
@@ -427,6 +442,7 @@
if (isClosed)
return;
+ rowGotUpdatedDeleted = 0;
closeCurrentStream(); // closing currentStream does not
depend on the
// underlying
connection. Do this outside of
// the
connection synchronization.
@@ -474,7 +490,6 @@
} else if (owningStmt != null)
// allow the satement to commit
if required.
owningStmt.resultSetClosing(this);
-
} else if (owningStmt != null) {
// allow the satement to commit
if required.
owningStmt.resultSetClosing(this);
Index: java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
(revision 110079)
+++ java/engine/org/apache/derby/impl/jdbc/EmbedDatabaseMetaData.java
(working copy)
@@ -2413,6 +2413,11 @@
* @see Connection
*/
public boolean supportsResultSetConcurrency(int type, int concurrency) {
+ 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;
@@ -2430,9 +2435,13 @@
public boolean ownUpdatesAreVisible(int type) {
return false;
}
+
public boolean ownDeletesAreVisible(int type) {
+ if (type == JDBC20Translation.TYPE_FORWARD_ONLY)
+ return true;
return false;
}
+
public boolean ownInsertsAreVisible(int type) {
return false;
}
@@ -2446,13 +2455,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;
}
/**
@@ -2471,7 +2480,7 @@
/**
* JDBC 2.0
*
- * Determine whether or not a visible row delete can be detected by
+ * Determine whether or not a visible row delete can be detected by
* calling ResultSet.rowDeleted(). If deletesAreDetected()
* returns false, then deleted rows are removed from the result set.
*
@@ -2479,6 +2488,8 @@
* @return true if changes are detected by the resultset type
*/
public boolean deletesAreDetected(int type) {
+ 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 110079)
+++ 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;
@@ -192,7 +195,7 @@
* JDBC 2.0
*
* <p>Determine if the cursor is before the first row in the result
- * set.
+ * set.
*
* @return true if before the first row, false otherwise. Returns
* false when the result set contains no rows.
@@ -214,35 +217,35 @@
* @exception SQLException Thrown on error.
*/
public boolean isAfterLast() throws SQLException
- {
+ {
return checkRowPosition(ResultSet.ISAFTERLAST, "isAfterLast");
}
-
+
/**
* JDBC 2.0
*
- * <p>Determine if the cursor is on the first row of the result set.
+ * <p>Determine if the cursor is on the first row of the result set.
*
- * @return true if on the first row, false otherwise.
+ * @return true if on the first row, false otherwise.
* @exception SQLException Thrown on error.
*/
- public boolean isFirst() throws SQLException
+ public boolean isFirst() throws SQLException
{
return checkRowPosition(ResultSet.ISFIRST, "isFirst");
}
-
+
/**
* JDBC 2.0
*
- * <p>Determine if the cursor is on the last row of the result set.
+ * <p>Determine if the cursor is on the last row of the result set.
* Note: Calling isLast() may be expensive since the JDBC driver
- * might need to fetch ahead one row in order to determine
+ * might need to fetch ahead one row in order to determine
* whether the current row is the last row in the result set.
*
- * @return true if on the last row, false otherwise.
+ * @return true if on the last row, false otherwise.
* @exception SQLException Thrown on error.
*/
- public boolean isLast() throws SQLException
+ public boolean isLast() throws SQLException
{
return checkRowPosition(ResultSet.ISLAST, "isLast");
}
@@ -471,12 +474,12 @@
public void setFetchSize(int rows) throws SQLException
{
if (rows < 0 || (stmt.getMaxRows()!=0 &&
- rows > stmt.getMaxRows()))
+ rows > stmt.getMaxRows()))
{
throw Util.generateCsSQLException(
SQLState.INVALID_FETCH_SIZE, new Integer(rows));
}
- else
+ else
if (rows > 0) // if it is zero ignore the call
{
fetchSize = rows;
@@ -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 (rowGotUpdatedDeleted == 1);
}
/**
@@ -1336,18 +1339,59 @@
* called when on the insert row.
*/
public void deleteRow() throws SQLException {
- throw Util.notImplemented();
+ synchronized (getConnectionSynchronization()) {
+ checkOnRow(); // first make sure there's a current row
+
+ if (rowGotUpdatedDeleted == 1) //there is a hole here and we
should not allow deleteRow on it
+ throw
Util.generateCsSQLException(SQLState.UPDATABLE_RESULTSET_API_DIALLOWED_ON_A_HOLE,
"deleteRow");
+
+ if (getConcurrency() != JDBC20Translation.CONCUR_UPDATABLE)//if
not updatable resultset, can't issue deleteRow
+ throw
Util.generateCsSQLException(SQLState.UPDATABLE_RESULTSET_API_DIALLOWED,
"deleteRow");
+
+ rowGotUpdatedDeleted = 1; //mark the flag that the current row is
deleted
+
+ 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() + "\"");
+
+ setupContextStack();
+ LanguageConnectionContext lcc =
getEmbedConnection().getLanguageConnection();
+ try {
+ 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();
+ int numberOfColumns = getMetaData().getColumnCount();
+ for (int i=1; i<=numberOfColumns; i++) //marking the row as
delete hole by setting all the columns to 0/null depending on the datatype
+ currentRow.getColumn(i).setToNull();
+ 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
*
- * Refresh the value of the current row with its current value in
+ * Refresh the value of the current row with its current value in
* the database. Cannot be called when on the insert row.
*
- * The refreshRow() method provides a way for an application to
+ * The refreshRow() method provides a way for an application to
* explicitly tell the JDBC driver to refetch a row(s) from the
- * database. An application may want to call refreshRow() when
+ * database. An application may want to call refreshRow() when
* caching or prefetching is being done by the JDBC driver to
* fetch the latest value of a row from the database. The JDBC driver
* may actually refresh multiple rows at once if the fetch size is
Index: java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java
===================================================================
--- java/engine/org/apache/derby/impl/jdbc/EmbedConnection.java (revision
110079)
+++ 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
110079)
+++ 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
110079)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java (working copy)
@@ -1409,7 +1409,9 @@
//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 UPDATABLE_RESULTSET_API_DIALLOWED = "01J06";
+ String UPDATABLE_RESULTSET_API_DIALLOWED_ON_A_HOLE = "01J07";
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
110079)
+++ java/engine/org/apache/derby/loc/messages_en.properties (working copy)
@@ -1103,7 +1103,9 @@
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.
+01J06=''{0}'' not allowed because the resultset is not an updatable resultset.
+01J07=''{0}'' was attempted against a hole.
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,659 @@
+/*
+
+ 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 resultsets");
+
+ 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");
+ System.out.println("got TYPE_SCROLL_INSENSITIVE? " +
(stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("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("01J06"))
+ 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 Test2 - request
updatable resultset for scroll sensitive type 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");
+ System.out.println("got TYPE_SCROLL_INSENSITIVE? " +
(stmt.getResultSetType() == ResultSet.TYPE_SCROLL_INSENSITIVE));
+ System.out.println("got CONCUR_READ_ONLY? " +
(stmt.getResultSetConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ System.out.println("JDBC 2.0 updatable resultset api
will fail 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("01J06"))
+ 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 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("updatablity of resultset is
CONCUR_READ_ONLY? " + (rs.getConcurrency() == ResultSet.CONCUR_READ_ONLY));
+ rs.next();
+ System.out.println("attempting to send a delete on a read only
resultset. Should fail!");
+ 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("01J06"))
+ 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("updatablity of resultset is
CONCUR_UPDATABLE? " + (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE));
+ rs.next();
+ System.out.println("attempting to send a delete on a sql with no for
update clause. Should fail!");
+ 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("01J06"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("row didn't get deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ //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("attempting to send a delete on a sql with for read
only clause. Should fail!");
+ 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("01J06"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ System.out.println("row didn't get deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ //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 not 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("attempt a deleteRow without first doing next on the
resultset. Should fail!");
+ 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);
+ }
+ System.out.println("row didn't get deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ //have to close the resultset because by default,
resultsets are held open over commit
+ while (rs.next());//read all the rows from the
resultset and position after the last row
+ System.out.println("After the last row in the resultset. 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("24000"))
+ 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");
+ 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 - attempt to
drop a table when there is a open updatable resultset on it should fail");
+ stmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ stmt1 =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
+ 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);
+ }
+ rs.close();
+
+ 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);
+ }
+ rs.close();
+
+ 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));
+ System.out.println("JDBC 2.0 updatable resultset api
will pass because this is an updatable resultset");
+ rs = stmt.executeQuery("SELECT * FROM t1 FOR UPDATE");
+ System.out.println("Not on a current row yet. rowDeleted should return?"
+ rs.rowDeleted());
+ rs.next();
+ System.out.println("row is not deleted yet with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ 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("row just got deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ System.out.println("we should see a hole after deleteRow. ie all numeric
columns will return 0 and rest of the columns will return null");
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ System.out.println("column 2 on this deleted row is " + rs.getString(2));
+ System.out.println("calling deleteRow more than once on a row should
throw an exception?");
+ 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("01J07"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.next();
+ System.out.println("Move to next row. Should have status of not deleted.
Confirm with rs.rowDeleted()? " + rs.rowDeleted());
+ //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();
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ 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("row just got deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ //have to close the resultset because by default,
resultsets are held open over commit
+ rs.close();
+
+ 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);
+ pStmt.setInt(1,0);
+ rs = pStmt.executeQuery();
+ 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));
+ System.out.println("JDBC 2.0 updatable resultset api
will pass because this is an updatable resultset");
+ 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("row just got deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ System.out.println("calling deleteRow more than once on a row should
throw an exception?");
+ 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("01J07"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.next();
+ System.out.println("Move to next row. Should have status of not deleted.
Confirm with rs.rowDeleted()? " + rs.rowDeleted());
+ //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));
+ System.out.println("JDBC 2.0 updatable resultset api
will pass because this is an updatable resultset");
+ 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("row just got deleted with deleteRow. Confirm with
rs.rowDeleted()? " + rs.rowDeleted());
+ System.out.println("column 1 on this deleted row is " + rs.getInt(1));
+ System.out.println("calling deleteRow more than once on a row should
throw an exception?");
+ 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("01J07"))
+ System.out.println("Got expected
exception " + e.getMessage());
+ else
+ dumpSQLExceptions(e);
+ }
+ rs.next();
+ System.out.println("Move to next row. Should have status of not deleted.
Confirm with rs.rowDeleted()? " + rs.rowDeleted());
+ //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("row not deleted yet. Confirm with rs.rowDeleted()? "
+ rs.rowDeleted());
+ 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();
+ System.out.println("row got deleted. Confirm with rs.rowDeleted()? " +
rs.rowDeleted());
+ System.out.println("column 1 on this deleted whole is " + rs.getInt(1));
+ //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 should return true for ownDeletesAreVisible
and deletesAreDetected");
+ dbmt = conn.getMetaData();
+ System.out.println("ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? "
+ dbmt.ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY));
+ System.out.println("deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? " +
dbmt.deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY));
+
System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? "
+ dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE));
+
System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? " +
dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE));
+
System.out.println("ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? " +
dbmt.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE));
+ System.out.println("deletesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE)?
" + dbmt.deletesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE));
+
+ 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("row got deleted. Confirm with rs.rowDeleted()? " +
rs.rowDeleted());
+ System.out.println("change the cursor name one more time with
setCursorName and then try to deleteRow");
+ stmt.setCursorName("CURSORNOUPDATE1");
+ rs = stmt.executeQuery("SELECT 1, 2 FROM t1 FOR UPDATE");
+ rs.next();
+ rs.deleteRow();
+ System.out.println("row got deleted. Confirm with rs.rowDeleted()? " +
rs.rowDeleted());
+ 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("row not deleted yet. Confirm with rs.rowDeleted()? "
+ rs.rowDeleted());
+ System.out.println("column 1 on this row is " + rs.getInt(1));
+ System.out.println("now try to deleteRow");
+ rs.deleteRow();
+ System.out.println("row got deleted. Confirm with rs.rowDeleted()? " +
rs.rowDeleted());
+ System.out.println("column 1 on this deleted hole row is " +
rs.getInt(1));
+ 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();
+ rs.deleteRow();
+ System.out.println("delete using first resultset
succedded? " + rs.rowDeleted());
+ 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("after the 2nd deleteRow");
+ rs.close();
+ rs1.close();
+
+ 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)");
+
+ //conn.rollback();
+
+ 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 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 view v1 as select * 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("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.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,195 @@
+Start testing delete using JDBC2.0 updateable resultsets
+---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
+got TYPE_SCROLL_INSENSITIVE? true
+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 updatable resultset for scroll sensitive type
resultset will give a read only scroll insensitive resultset
+requested TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE
+got TYPE_SCROLL_INSENSITIVE? true
+got CONCUR_READ_ONLY? true
+JDBC 2.0 updatable resultset api will fail 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
+updatablity of resultset is CONCUR_READ_ONLY? true
+attempting to send a delete on a read only resultset. Should fail!
+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
+updatablity of resultset is CONCUR_UPDATABLE? false
+attempting to send a delete on a sql with no for update clause. Should fail!
+Got expected exception 'deleteRow' not allowed because the resultset is not an
updatable resultset.
+row didn't get deleted with deleteRow. Confirm with rs.rowDeleted()? false
+---Negative Test5 - request updatable resultset for sql with for read only
clause
+attempting to send a delete on a sql with for read only clause. Should fail!
+Got expected exception 'deleteRow' not allowed because the resultset is not an
updatable resultset.
+row didn't get deleted with deleteRow. Confirm with rs.rowDeleted()? false
+---Negative Test6 - attempt to deleteRow on updatable resultset when not on a
row
+attempt a deleteRow without first doing next on the resultset. Should fail!
+Got expected exception Invalid cursor state - no current row.
+row didn't get deleted with deleteRow. Confirm with rs.rowDeleted()? false
+After the last row in the resultset. 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 Invalid cursor state - no current row.
+---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
+expected exception FOR UPDATE is not permitted on this type of statement.
+---Negative Test11 - attempt to drop a table when there is a open updatable
resultset on it should fail
+expected exception Operation 'DROP TABLE' cannot be performed on object 'T1'
because there is an open ResultSet dependent on that object.
+---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.
+---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 api will pass because this is an updatable
resultset
+Not on a current row yet. rowDeleted should return?false
+row is not deleted yet with deleteRow. Confirm with rs.rowDeleted()? false
+column 1 on this row before deleteRow is 1
+column 2 on this row before deleteRow is aa
+row just got deleted with deleteRow. Confirm with rs.rowDeleted()? true
+we should see a hole after deleteRow. ie all numeric columns will return 0 and
rest of the columns will return null
+column 1 on this deleted row is 0
+column 2 on this deleted row is null
+calling deleteRow more than once on a row should throw an exception?
+Got expected exception 'deleteRow' was attempted against a hole.
+Move to next row. Should have status of not deleted. Confirm with
rs.rowDeleted()? false
+---Positive Test2 - even if no columns from table specified in the column
list, we should be able to get updatable resultset
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+row just got deleted with deleteRow. Confirm with rs.rowDeleted()? true
+column 1 on this deleted row is 0
+---Positive Test3 - use prepared statement with concur updatable status
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+JDBC 2.0 updatable resultset api will pass because this is an updatable
resultset
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+row just got deleted with deleteRow. Confirm with rs.rowDeleted()? true
+column 1 on this deleted row is 0
+calling deleteRow more than once on a row should throw an exception?
+Got expected exception 'deleteRow' was attempted against a hole.
+Move to next row. Should have status of not deleted. Confirm with
rs.rowDeleted()? false
+---Positive Test4 - use callable statement with concur updatable status
+requested TYPE_FORWARD_ONLY, CONCUR_UPDATABLE
+got TYPE_FORWARD_ONLY? true
+got CONCUR_UPDATABLE? true
+JDBC 2.0 updatable resultset api will pass because this is an updatable
resultset
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+row just got deleted with deleteRow. Confirm with rs.rowDeleted()? true
+column 1 on this deleted row is 0
+calling deleteRow more than once on a row should throw an exception?
+Got expected exception 'deleteRow' was attempted against a hole.
+Move to next row. Should have status of not deleted. Confirm with
rs.rowDeleted()? false
+---Positive Test5 - donot have to select primary key to get an updatable
resultset
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+now try to delete row when primary key is not selected for that row
+row got deleted. Confirm with rs.rowDeleted()? true
+column 1 on this deleted whole is 0
+---Positive Test6 - For Forward Only resultsets, DatabaseMetaData should
return true for ownDeletesAreVisible and deletesAreDetected
+ownDeletesAreVisible(ResultSet.TYPE_FORWARD_ONLY)? true
+deletesAreDetected(ResultSet.TYPE_FORWARD_ONLY)? true
+ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
+deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)? false
+ownDeletesAreVisible(ResultSet.TYPE_SCROLL_SENSITIVE)? false
+deletesAreDetected(ResultSet.TYPE_SCROLL_SENSITIVE)? 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
+row got deleted. Confirm with rs.rowDeleted()? true
+change the cursor name one more time with setCursorName and then try to
deleteRow
+row got deleted. Confirm with rs.rowDeleted()? true
+---Positive Test9 - using correlation name for the table in the select sql is
not a problem
+row not deleted yet. Confirm with rs.rowDeleted()? false
+column 1 on this row is 1
+now try to deleteRow
+row got deleted. Confirm with rs.rowDeleted()? true
+column 1 on this deleted hole row is 0
+---Positive Test10 - 2 updatable resultsets going against the same table, will
they conflict?
+delete using first resultset succedded? true
+attempt to send deleteRow on the same row through a different resultset should
throw an exception
+Got expected exception Cursor 'SQLCUR9' is not on a row.
+after the 2nd deleteRow
+---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
+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 110079)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
(working copy)
@@ -128,6 +128,7 @@
lang/unlimited.sql
lang/update.sql
lang/updateCursor.java
+lang/updatableResultSet.java
lang/valuesclause.sql
lang/views.sql
lang/wisconsin.sql
