db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [VOTE][PATCH]updates on forward only resultset using JDBC 2.0 UpdatableResultset APIs
Date Fri, 11 Feb 2005 00:51:02 GMT
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;
>+                }
>



Mime
View raw message