You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Mamta Satoor <ma...@Remulak.Net> on 2005/02/08 23:08:24 UTC
[VOTE][PATCH]updates on forward only resultset using JDBC 2.0
UpdatableResultset APIs
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
Re: [VOTE][PATCH]updates on forward only resultset using JDBC 2.0
UpdatableResultset APIs
Posted by Daniel John Debrunner <dj...@debrunners.com>.
The way the UPDATE ... WHERE CURRENT OF statement is created uses
specific values for each statement, e.g.
UPDATE ITEMS SET PRICE = 23.45 WHERE CURRENT OF priceupdate
UPDATE ITEMS SET PRICE =1932.34 WHERE CURRENT OF priceupdate
This is inefficient for a couple of reasons.
1) Each UPDATE will result in a new compilation.
2) These statements will flush useful statements out of the cache if a
large number
of rows are updated though a ResultSet or many ResultSets.
For the same reasons JDBC encourages applications to use parameter
markers, internal code should take the same approach.
UPDATE ITEMS SET PRICE = ? WHERE CURRENT OF priceupdate
I think you also have to always quote any identifiers in generated SQL,
otherwise identifiers that were originally quoted will not match.
That may also be an issue with the DELETE statement for the delete in
updateable result sets.
Dan.
>+ StringBuffer updateWhereCurrentOfSQL = new StringBuffer("UPDATE ");
>+ CursorActivation activation = getEmbedConnection().getLanguageConnection().lookupCursorActivation(getCursorName());
>+ ExecCursorTableReference targetTable = activation.getPreparedStatement().getTargetTable();
>+ updateWhereCurrentOfSQL.append(getFullBaseTableName(targetTable));//got the underlying (schema.)table name
>+ updateWhereCurrentOfSQL.append(" SET ");
>+ ResultDescription rd = theResults.getResultDescription();
>+
>+ for (int i=1; i<=rd.getColumnCount(); i++) { //in this for loop we are constructing columnname=newvalue,... part of the update sql
>+ if (columnGotUpdated[i-1]) { //if the column got updated, do following
>+ if (foundOneColumnAlready)
>+ updateWhereCurrentOfSQL.append(",");
>+
>+ updateWhereCurrentOfSQL.append(rd.getColumnDescriptor(i).getName() + "=");
>+
>+ //Need some special handling depending on the column type
>+ if (currentRow.getColumn(i).isNull()) //if new value is null, there should not be any special handling
>+ updateWhereCurrentOfSQL.append("null");
>+ else if (currentRow.getColumn(i) instanceof DateTimeDataValue)
>+ updateWhereCurrentOfSQL.append("cast('"+ currentRow.getColumn(i).getString() + "' as " + currentRow.getColumn(i).getTypeName() + ")");
>+ else if (currentRow.getColumn(i) instanceof StringDataValue)
>+ updateWhereCurrentOfSQL.append("'" + currentRow.getColumn(i).getString() + "'");
>+ else if (currentRow.getColumn(i) instanceof BitDataValue) {
>+ if (currentRow.getColumn(i).getTypeName().equals(TypeId.BLOB_NAME))
>+ updateWhereCurrentOfSQL.append("cast(X'"+ currentRow.getColumn(i).getString() + "' as " + currentRow.getColumn(i).getTypeName() + ")");
>+ else
>+ updateWhereCurrentOfSQL.append("X'" + currentRow.getColumn(i).getString() + "'");
>+ } else
>+ updateWhereCurrentOfSQL.append(currentRow.getColumn(i).getObject());
>+ foundOneColumnAlready = true;
>+ }
>
Re: [VOTE][PATCH]updates on forward only resultset using JDBC 2.0
UpdatableResultset APIs
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Only looking at the way the update row is implemented in the
EmbedResultSet it looks correct but inefficient.
Every time a column is updated a new DataValueDescriptor implementation
instance is created, this is a short lived object that will be garbage
collected. This doesn't match the way values are set in
EmbedPreparedStatement and EmbedCallableStatement. As an example calling
updateInt will result in something like
tempDvd = new SQLInteger();
tempDvd.setValue(value);
colDvd.setValue(tempDvd);
// discard tempDvd
While this looks ok-ish, when you consider having a ResultSet of a
milltion rows and updating them that's a lot of objects going thorough
the garbage collector for no value. The goal for Derby is to not create
objects on a per-row basis during SQL processing, unless obligated by
Java/JDBC semantics.
If the updateXXX() methods followed the pattern of the setXXX methods in
EmbedPreparedStatement then your updateColumn() method would change to
return the DataValueDescriptor to modify, thus updateInt() would be coded:
updateColumn(columnIndex).setValue(value);
though I think updateColumn would need to be renamed to better reflect
its role.
Similar for the arrays and their contents that manage the update row and
the state of the current row, these are discarded after every next() or
update of the row. It would be better if the objects were re-used.
Dan.
Re: [VOTE][PATCH]updates on forward only resultset using JDBC 2.0UpdatableResultset
APIs
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Mamta Satoor wrote:
>
> Daniel John Debrunner wrote:
>
>
>>>
>>>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
>>>
>>
>>Mamta, JSR 169 supports the api's for updateable result sets and my
>>design doc indicates that EmbedResultSet is the ResultSet implementation
>>class for JSR 169.
>>http://issues.apache.org/jira/secure/attachment/18405/derbyJSR169Design.html
>>
>>Thus is it posible that I move the current updateXXX and related methods
>>from EmbedResultSet20 to EmbedResultSet before you submit a new version
>>of your patch? I think this would be better than having them move after
>>they have been implemented by you. Otherwise their history will be
>>spread over two files. I can move these methods very soon and I need to
>>as I'm close to making an experimental JSR 169 verson of Derby.
>>
>>Thanks,
>>Dan.
>
>
> Hi Dan,
>
> Yes, that is fine.
Done.
Sending jdbc\EmbedResultSet.java
Sending jdbc\EmbedResultSet20.java
Transmitting file data ..
Committed revision 153569.
Thanks,
Dan.
Re: [VOTE][PATCH]updates on forward only resultset using JDBC
2.0UpdatableResultset APIs
Posted by Mamta Satoor <ma...@Remulak.Net>.
Daniel John Debrunner wrote:
> >
> >
> >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
> >
>
> Mamta, JSR 169 supports the api's for updateable result sets and my
> design doc indicates that EmbedResultSet is the ResultSet implementation
> class for JSR 169.
> http://issues.apache.org/jira/secure/attachment/18405/derbyJSR169Design.html
>
> Thus is it posible that I move the current updateXXX and related methods
> from EmbedResultSet20 to EmbedResultSet before you submit a new version
> of your patch? I think this would be better than having them move after
> they have been implemented by you. Otherwise their history will be
> spread over two files. I can move these methods very soon and I need to
> as I'm close to making an experimental JSR 169 verson of Derby.
>
> Thanks,
> Dan.
Hi Dan,
Yes, that is fine.
Mamta
Re: [VOTE][PATCH]updates on forward only resultset using JDBC 2.0
UpdatableResultset APIs
Posted by Daniel John Debrunner <dj...@debrunners.com>.
>
>
>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
>
Mamta, JSR 169 supports the api's for updateable result sets and my
design doc indicates that EmbedResultSet is the ResultSet implementation
class for JSR 169.
http://issues.apache.org/jira/secure/attachment/18405/derbyJSR169Design.html
Thus is it posible that I move the current updateXXX and related methods
from EmbedResultSet20 to EmbedResultSet before you submit a new version
of your patch? I think this would be better than having them move after
they have been implemented by you. Otherwise their history will be
spread over two files. I can move these methods very soon and I need to
as I'm close to making an experimental JSR 169 verson of Derby.
Thanks,
Dan.