OracleCachedRowSet updating data in memory without writing to database

Question

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;
    }
}

Santosh · Answer 1 · 2012-03-27T12:14:05.430

0

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

edited Mar 27 '12 at 12:14

answered Mar 27 '12 at 09:34

Santosh

17,667
4
54
79

It appears when i pass cachedrowset object it passes by value. When i debugged i saw after updating data i can get changes right after update.It is like it updates another copy of the object. I am missing something here – Bren Mar 27 '12 at 11:53
Yes! in java its always pass by value and never pass by reference. I have edited my answer based on your input. – Santosh Mar 27 '12 at 12:14
Well actually java as i know only passes primitive types as by value. Not objects. And i have found the solution. Seems like i need to call cachedrowset.updateRow() in order to write the change to memory. As it says on at this document: http://www.scribd.com/doc/68052701/8/Setting-Up-a-CachedRowSet-Object On the other hand Java Document of this method says : "Updates the underlying database with the new contents of the current row of this ResultSet object." Javadoc missleaded me at first. It does not update underlying database until u accept changes it seems. Well it is working now – Bren Mar 27 '12 at 12:21
Well by passing by reference i mean it passes reference of object by value :D So it does not make a copy of object in memory, copy of the reference.In case my previous comment would cause a misunderstanding i wanted to clarify :) – Bren Mar 27 '12 at 12:48
I have posted an answer about that mate, btw thx for helping. @Santosh – Bren Mar 28 '12 at 08:03

score 0 · Accepted Answer · answered Mar 28 '12 at 08:02

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

        }
    }

OracleCachedRowSet updating data in memory without writing to database

2 Answers2