db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <ma...@Remulak.Net>
Subject [VOTE][PATCH]updates on forward only resultset using JDBC 2.0 UpdatableResultset APIs
Date Tue, 08 Feb 2005 22:08:24 GMT
Hi,

Attached is the patch for update using updatable resultset apis on a forward only updatable
resultset.
I have run all the tests and the patch hasn't caused any failures.

Just to recap, 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.
Delete using updatable resultset api for forward only resultsets has already been committed
to Derby.
This patch will allow updates using JDBC 2.0 apis for forward only resultset.

This patch utilizes existing positioned update cursor code and hence the SELECT sql sent on
the
Statement object has the same syntax and restrictions as for FOR UPDATE sql for updatable
cursors.
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..
One other important restriction to remember is correlation names are not allowed for column
names
if those columns are going to be used for updateXXX. e.g.
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
rs.next();
System.out.println("column 1 on this row is " + rs.getInt(1));
try {
 System.out.println("attempt to send updateXXX on correlation name column will fail");
 rs.updateShort(1, (new Integer(123)).shortValue());
 System.out.println("FAIL!!! updateXXX should have failed");
}
catch (SQLException e) {
 dumpSQLExceptions(e);
}


Some tidbits about the functionality and implementation
1)After updateRow, ResultSet will move from the current row to right before the next row.
One
exception to this is if no updateXXX call were issued before the updateRow. i.e. if updateRow
is
issued w/o any updateXXX calls, then ResultSet will continue to stay on the current row.
This behavior would be easy to change if we decide that we should always move the ResultSet
to right before the next row after an updateRow method.
2)The main DatabaseMetaData calls for this functionality are updatesAreDetected,
ownUpdatesAreVisible, othersUpdatesAreVisible. Since ResultSet is moved off the current row
after the updateRow method, updatesAreDetected will always return false. Also, because this
patch is for forward only resultsets, ownUpdatesAreVisible will always return false. In
addition, since Derby materializes a forward only ResultSet incrementally, it is possible
to see changes made by the others and that is why othersUpdatesAreVisible will return true
for forward only ResultSets. One related method in the ResultSet is rowUpdated. As per the
JDBC specs, ResultSet.rowUpdated should only be used if drivers can detect updates. Since
Derby can't detect updates, a JDBC program should not use ResultSet.rowUpdated.
3)To reiterate the earlier comment, correlation names are not allowed for column names if
those columns are going to be used for updateXXX.
4)Before every updateXXX/updateRow/deleteRow/cancelRowUpdates, following checks are performed.
    //1)Make sure this is an updatable ResultSet
    //2)Make sure JDBC ResultSet is not closed
    //3)Make sure JDBC ResultSet is positioned on a row
    //4)Make sure underneath language resultset is not closed
    //5)Make sure for updateXXX methods, the column position is not out of range
    //6)Make sure the column corresponds to a column in the base table and it is not a derived
column
    //7)Make sure correlation names are not used for base table column names in updateXXXX.
This is because the mapping
    //  of correlation name to base table column position is not available at runtime.
5)There are 2 master files for the test now because there are few updatable resultset apis
like
updateBlob, updateClob etc which were introduced in JDBC3.0 and are not available under JDBC2.0.
When running under JDBC2.0, those tests will not be run and hence the masters are different.
6)Following new SQL States have been added by this patch
a)XCL14 - If the column index passed to updateXXX is out of range, this exception will be
thrown e.g.
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

rs = stmt.executeQuery("SELECT c1, c2 FROM t1 FOR UPDATE");
rs.next();
System.out.println("There are only 2 columns in the select list and we are trying to send
updateXXX on column position
3");
try {
 rs.updateInt(3,22);
 System.out.println("FAIL!!! updateInt should have failed because there are only 2 columns
in the select list");
}
catch (SQLException e) {
 dumpSQLExceptions(e);
}
b)XJ084 - If updateXXX is issued for a column which is a derived column or a correlation column,
then there will be
exception
System.out.println("---Negative Test15 - Can't call updateXXX methods on columns that do not
correspond to a column in
the table");
stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

rs = stmt.executeQuery("SELECT 1, 2 FROM tableWithPrimaryKey FOR UPDATE");
rs.next();
try {
 rs.updateInt(1,22);
 System.out.println("FAIL!!! updateInt should have failed because it is trying to update a
column that does not
correspond to column in base table");
}
catch (SQLException e) {
 dumpSQLExceptions(e);
}
rs = stmt.executeQuery("SELECT c1 as col1, c2 as col2 FROM t1 abcde FOR UPDATE of c1");
rs.next();
try {
 System.out.println("attempt to send updateXXX on correlation name column will fail");
 rs.updateShort(1, (new Integer(123)).shortValue());
 System.out.println("FAIL!!! updateXXX should have failed");
}
catch (SQLException e) {
 dumpSQLExceptions(e);
}
7)I have added lots of new tests. But if anyone thinks of a corner case that we should be
testing,
let me know.

Following is the output of svn stat
M      java\engine\org\apache\derby\impl\jdbc\EmbedResultSet.java
M      java\engine\org\apache\derby\impl\jdbc\EmbedDatabaseMetaData.java
M      java\engine\org\apache\derby\impl\jdbc\EmbedResultSet20.java
M      java\engine\org\apache\derby\iapi\reference\SQLState.java
M      java\engine\org\apache\derby\loc\messages_en.properties
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\build.xml
M      java\testing\org\apache\derbyTesting\functionTests\tests\lang\updatableResultSet.java
M      java\testing\org\apache\derbyTesting\functionTests\tests\jdbcapi\resultsetJdbc30.java
M      java\testing\org\apache\derbyTesting\functionTests\master\resultsetJdbc30.out
M      java\testing\org\apache\derbyTesting\functionTests\master\updatableResultSet.out
A      java\testing\org\apache\derbyTesting\functionTests\master\jdk14
A      java\testing\org\apache\derbyTesting\functionTests\master\jdk14\updatableResultSet.out

Please send in your comments/vote.
Mamta



Mime
View raw message