Joel Brown's Weblog

CachedRowSetDataProvider and CachedRowSet Info

The CachedRowSetDataProvider is a wrapper to data in a CachedRowSet.  It's primary purpose is to facilitate binding to components. 

The only piece of information the CachedRowSetDataProvider holds is a cursor position into the CachedRowSet. If multiple CachedRowSetDataProvider instances reference the same CachedRowSet, they can each have their own cursor position (caution: see the section below on threading).   Again, all data from the database is stored in the CachedRowSet.

CachedRowSet Cursor Position

Many the CachedRowSetDataProvider methods may move the cursor position in the underlying CachedRowSet.  The CachedRowSetDataProvider methods findFirst(), setCursorRow(), getValue(), setValue() and others may/will move the CachedRowSet's cursor.

Big Rule:  if you rely on cursor postion in the CachedRowSet,  access it only through CachedRowSet methods or only through CachedRowSetDataProvider methods.  Any cursor movement through CachedRowSet methods will not be seen by a CachedRowSetDataProvider.

Closing your CachedRowSetDataProvider

If your CachedRowSet is in a "higher" scope, remember to call close() on your CachedRowSetDataProvider.  For example, if your CachedRowSetDataProvider  is in your page (request scope) and the CachedRowSet is in your session bean (session scope), call close().  This is the default for Creator, so you'll notice we add the call to close() the CachedRowSetDataProvider into the page's destroy() method.

Internally, the dataProvider adds a listener to the RowSet instance to detect changes so that listeners to the dataProvider can be notified of those changes.  The method close() removes that listener.

Without the close()  (i.e., removing the listener) the DataProvider instance cannot be garbage collected even though it's in request scope and should be gc'd   after the response is rendered.  Failure to close the CachedRowSetDataProvider in this situation will manifest itself as a slow memory leak.

CachedRowSetDataProvider.close() also move's the CachedRowSet's cursor to the first row.

The CachedRowSetDataProvider.close() does nothing else to the CachedRowSet: the CachedRowSet's data and properties are not changed.

What happens in a CachedRowSet.close()?
The row data is released and many properties are reset to default, such as showDeleted, queryTimeout, maxRows,
maxFieldSize, type, concurrency, readOnly, and transactionIsolation.

What happens in a CachedRowSet.release()?
The row data is released (see the javadoc for more details).  Use release() if you intend to re-execute the query at a later time

What happens in a CachedRowSetDataProvider.refresh()?
This calls CachedRowSet.release() and resets the CachedRowSetDataProvider's cursor.  It does not execute() the CachedRowSet at this time.

When is a CachedRowSet executed?
CachedRowSetDataProvider methods that would require an executed CachedRowSet will automagically execute it.
You can force execution by called CachedRowSet.execute().

CachedRowSet and Multiple Request Threads

Although multiple CachedRowSetDataProvider instances may use the same CachedRowSet (there's nothing to stop you)  care should be taken that those CachedRowSetDataProvider instances do not access the CachedRowSet at the same time.   Creator (and JSF) does nothing to help prevent simultaneous access.

The chances for problems are small, but they do exist.

Sample problem: Let's say each dataProvider calls setValue() on a different row.  The dp.setValue() essentially does two steps:  it moves the CachedRowSet cursor to the row and then calls setObject(). If the first thread yields after moving the cursor, the second dataProvider may sneak in and move the CachedRowSet cursor.  This means the first thread would then call setObject() on the wrong row.

Multiple button clicks before a response is returned can generate multiple requests and thus multiple threads.

Posted at 08:03PM Dec 15, 2005 by jfbrown in Creator  | 

CachedRowSet Logging in Creator 2

This applies to the production release of Creator 2.

To log our CachedRowSetX select/insert/update/insert/delete statements, set the "printStatements" property of the CachedRowSet.

(In EA2, you can see when the SELECT is executed and when acceptChanges() is called, but other logging isn't there or is incomplete.)

---

SELECT statement execution

When the RowSet is executed, you'll see the select statement and any parameters.

[#|2005-12-10T14:56:50.906-0800|INFO

Reader executing query ps=0 SELECT ALL TRAVEL.TRIPTYPE.TRIPTYPEID, 
                    TRAVEL.TRIPTYPE.NAME, 
                    TRAVEL.TRIPTYPE.DESCRIPTION 
FROM TRAVEL.TRIPTYPE
WHERE TRAVEL.TRIPTYPE.TRIPTYPEID > ? 
  Param[1]=(java.lang.Integer,5)|#]<BR>
[#|2005-12-10T14:56:50.968-0800|INFO

Reader executing finished|#]

Notes:

the "ps=0" is short for the pagesize=0

The "executing finished" logs when all rows have been retrieved from the database and cached into the CachedRowSet instance. Based on the timestamps, this query took .062 seconds to run.

The parameter logging is "(datatypeName,value.toString())" where the string representation of the value is everything between the "." and ")".

For null values, you'll see one of two formats: "(null, typeNumber)". Refer to java.sql.Types javadoc for typeNumber values. For example, 12 means VARCHAR.

For null values sometime you'll see something cryptic :like this:
Param[1]=([Ljava.lang.Object;,[Ljava.lang.Object;@ae324b)|#]

---

Method acceptChanges() Logging

First, we decided to start with something confusing.  The first thing you'll see logged is the text of an  INSERT statement

[#|2005-12-10T15:07:13.421-0800|INFO
INSERT INTO TRAVEL.TRIPTYPE (TRIPTYPEID, NAME, DESCRIPTION) VALUES (?, ?, ?)|#]

This means nothing more than the acceptChanges() was called.   It's not doing an INSERT, but if it does, this is the statement we'll use.

From here, we process each row in order. If no other output is found, it means none of the CachedRowSet rows were updated, deleted, or inserted.

If you don't see this, it means there aren't any columns in the rowset that are updatable.  If so, check your CachedRowSet's tableName parameter or other advanced properties.

Example if your tableName is completely bogus:

[#|2005-12-10T16:37:48.046-0800|WARNING|java.sql.SQLException: No columns in table: xxxTRIPTYPE
        at com.sun.sql.rowset.internal.CachedRowSetXWriter.initSQLStatements(CachedRowSetXWriter.java:1251)
        at com.sun.sql.rowset.internal.CachedRowSetXWriter.writeData(CachedRowSetXWriter.java:327)

---

Inserted Row Logging

We just log the INSERT execution with its parameters.  The actual INSERT statement is logged when the acceptChanges() begins.

[#|2005-12-10T15:28:52.656-0800|INFO
Writer: executing insert , params: Col[1]=(java.lang.Integer,69) Col[2](java.lang.String,Stuff) Col[3]=(null:12)|#]

---

Updated Row Logging

We log:

• the SELECT to get the original row
  
• the parameters for that select
  
• the execution of that select
  
• the UPDATE statement
  
• the values for the columns to be updated. The parameters within the WHERE clause are logged three lines up
  
• the update execution

Example of a successful update:

[#|2005-12-10T15:50:46.921-0800|INFO
SELECT TRIPTYPEID, NAME, DESCRIPTION FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION = ? |#]

[#|2005-12-10T15:50:46.921-0800|INFO Paramm[1]=(java.lang.Integer,6) Paramm[2]=(java.lang.String,CONF) Paramm[3]=(java.lang.String,Conference/Tradeshow)|#]

[#|2005-12-10T15:50:46.937-0800|INFO Writer: executing pre-update SELECT|#]

[#|2005-12-10T15:50:46.968-0800|INFO Writer: UPDATE TRAVEL.TRIPTYPE SET NAME = ? WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION = ? |#]

[#|2005-12-10T15:50:46.968-0800|INFO UpdateCol[1]=(java.lang.String,YoYo CONF)|#]

[#|2005-12-10T15:50:46.968-0800|INFO Writer: executing update() |#]

---

Deleted Row Logging

\What's logged:

• the SELECT for retrieving the original row, called the "pre-delete select"
  
• the parameter(s) for that SELECT.
  
• actual execution the pre-delete select.
  
• the execution of the actual DELETE. The parameters for the DELETE were logged previously with the pre-delete SELECT

Example:

[#|2005-12-10T15:37:30.656-0800|INFO Writer: pre-delete select SELECT TRIPTYPEID, NAME, DESCRIPTION FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION IS NULL |#]

[#|2005-12-10T15:37:30.656-0800|INFO DeleteParam[1]=(java.lang.Integer,69) DeleteParam[2]=(java.lang.String,Stuff)|#]

[#|2005-12-10T15:37:30.671-0800|INFO Writer: executing pre-delete select|#]

[#|2005-12-10T15:37:30.781-0800|INFO Writer: executing delete DELETE FROM TRAVEL.TRIPTYPE WHERE TRIPTYPEID = ? AND NAME = ? AND DESCRIPTION IS NULL |#]

If the original row no longer exists, you will not the the "executing delete" but rather the resulting exception:

Writer: executing pre-delete select|#]
[#|2005-12-10T15:46:08.875-0800|SEVERE| Error Description javax.sql.rowset.spi.SyncProviderException: Number of conflicts while synchronizing: 1
    at com.sun.sql.rowset.internal.CachedRowSetXWriter.writeData(CachedRowSetXWriter.java:512)

Posted at 04:48PM Dec 10, 2005 by jfbrown in Creator  |  Comments[0]