Joel Brown's Weblog

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]