OracleCachedRowSet updating data in memory without writing to database

2k Views Asked by At

I am using oracle jdbc cachedrowset implementation to select several rows returning from a query. Then i update some data using cachedrowset.updateInt() or other update methods. I get the cursor back at first using cachedrowset.beforeFirst() and then traverse through the rowset again to print data.

The thing is the data i get using getInt() again is the original data.I want get the data that is replaced with the original one. I am not intended to commit changes to db.

I thought i can use Rowset object as a data wrapper without changing any data on the db, only for data manipulation and view. Is there any way i can get the updated date instead of the original one ? I didn't want to code a data wrapper object of my own

Edit: This is how i get data, and below is how i update it

public OracleCachedRowSet getCachedRowset( String query, Connection con)
        throws DTSException {
    try {
        OracleCachedRowSet cachedRowSet = new OracleCachedRowSet();
        cachedRowSet.setReadOnly(false);
        cachedRowSet.setCommand(query);
        cachedRowSet.execute(con);
        return cachedRowSet;
    } catch (SQLException sqle) {
        throw new DTSException("Error fetching data! :" + sqle.getMessage(), sqle);
    }
}

Update Code:

public void updateRowSetData(CachedRowSet cachedRowSet, int columnIndex, int columnType,    Object data)
        throws SQLException {

    switch (columnType) {
    case Types.NUMERIC:
    case Types.DECIMAL:
        cachedRowSet.updateBigDecimal(columnIndex, (BigDecimal) data);
        return;
    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGNVARCHAR:
        cachedRowSet.updateString(columnIndex, data == null ? null : data.toString());
        return;
    case Types.INTEGER:
        cachedRowSet.updateInt(columnIndex, (Integer) data);
        return;
    case Types.DATE:
        cachedRowSet.updateDate(columnIndex, (Date) data);
        return;
    case Types.TIMESTAMP:
        cachedRowSet.updateTimestamp(columnIndex, (Timestamp) data);
        return;
    case Types.TIME:
        cachedRowSet.updateTime(columnIndex, (Time) data);
        return;
    case Types.BIGINT:
        cachedRowSet.updateLong(columnIndex, data == null ? null : Long.parseLong(data.toString()));
        return;
    case Types.DOUBLE:
    case Types.FLOAT:
        cachedRowSet.updateDouble(columnIndex, (Double) data);
        return;
    case Types.SMALLINT:
        cachedRowSet.updateShort(columnIndex, data == null ? null : Short.parseShort(data.toString()));
        return;
    case Types.TINYINT:
        cachedRowSet.updateByte(columnIndex, Byte.parseByte(data == null ? null : data.toString()));
        return;
    case Types.BINARY:
    case Types.VARBINARY:
        cachedRowSet.updateBytes(columnIndex, (byte[]) data);
        return;
    case Types.CLOB:
        if (data != null) {
            cachedRowSet.updateClob(columnIndex, ((Clob) data).getCharacterStream());
        } else {
            cachedRowSet.updateClob(columnIndex, (Clob) data);
        }
        return;
    case Types.ARRAY:
        cachedRowSet.updateArray(columnIndex, (Array) data);
        return;
    case Types.BLOB:
        if (data != null) {
            cachedRowSet.updateBlob(columnIndex, data == null ? null : ((Blob) data).getBinaryStream());
        } else {
            cachedRowSet.updateBlob(columnIndex, (Blob) data);
        }
        return;
    case Types.REAL:
        cachedRowSet.updateFloat(columnIndex, (Float) data);
        return;
    case Types.BIT:
    case Types.BOOLEAN:
        cachedRowSet.updateBoolean(columnIndex, (Boolean) data);
        return;
    case Types.REF:
        cachedRowSet.updateRef(columnIndex, (Ref) data);
        return;
    case Types.LONGVARBINARY:
        cachedRowSet.updateBinaryStream(columnIndex, (InputStream) data);
        return;
    default:
        cachedRowSet.updateObject(columnIndex, data);
        return;
    }
}
2

There are 2 best solutions below

0
On BEST ANSWER

The solution is to call cachedRowSet.updateRow() after using appropriate update method( updateInt(), updateString() etc. ) to make changes written to memory. I didn't use it before because JavaDoc of this upateRow() says: "Updates the underlying database with the new contents of the current row of this ResultSet object."

Only that is not what happens. updateRow() updates the data on memory, not on the underlying db. I have found the solution in the document at the link: http://www.scribd.com/doc/68052701/8/Setting-Up-a-CachedRowSet-Object

So what i did was simply call updateRow after updating the data:

    while (cachedRowSet.next()) {
        for (int i = 0; i < columnCount; i++) {

                dataHandler.updateRowSetData(cachedRowSet, i + 1, columnTypes[i], getUpdatedData());
                cachedRowSet.updateRow(); //Adding this line solves the problem. Otherwise changes are not made

        }
    }
5
On

Please try changing the read only settings of OracleCachedRowSet as follows.

oracleCachedRowSet.setReadOnly(false);

This method is defined in javax.sql.RowSet which the row set classes implement.

EDIT: Based on the code you posted,

You are right in observing that you are doing a pass by value. In fact, in java its always pass by value and never pass by reference.

Solution:

Right now your function is returning void, change this to return the updated cachedRowSet. Your function definition will look like

public CachedRowSet updateRowSetData(CachedRowSet cachedRowSet, int columnIndex, int columnType,    Object data)
        throws SQLException