SYNOPSIS
--------
CachedRowSetSwriter.insertNewRow() throws SQLException

OPERATING SYSTEMS
-----------------
ALL

JDK VERSIONS
------------
Latest Oracle JDK6 / JDK7.
Does not occur with Java 5.0.

DESCRIPTION
-----------
In the provided testcase, we see an unexpected SQLException ("Invalid column name") when trying to insert a row in a CachedRowSet.

This Exception does not occur with Java 5.0. The root cause appears to be new code in CachedRowSetWriter.insertNewRow(), introduced in Java 6.

In our testcase, we use an SQL database created as follows:

  CREATE TABLE [MonthLookup] (
    ? [month_id] INT IDENTITY(1,1) NOT NULL,
    ? [month_value] VARCHAR(40) NOT NULL,
    ? CONSTRAINT [PK_MonthLookup] PRIMARY KEY ([month_id])
  )

In this database, "month_id" is the primary key which is automatically numbered.

Now consider the new (Java 6 onwards) code in CachedRowSetWriter.insertNewRow():

...
boolean returnVal = false;
PreparedStatement pstmtSel = con.prepareStatement(selectCmd,
                             ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs, rs2 = null;
DatabaseMetaData dbmd = con.getMetaData();
rs = pstmtSel.executeQuery();
String table = crs.getTableName();
rs2 = dbmd.getPrimaryKeys(null, null, table);
String [] primaryKeys = new String[icolCount];
int k = 0;
while (rs2.next()) {
    String pkcolname = rs2.getString("COLUMN_NAME");
    primaryKeys[k] = pkcolname;
    k++;
}

if (rs.next()) {
    for (int j=0;j<primaryKeys.length;j++) {
        if (primaryKeys[j] != null) {
            if (crs.getObject(primaryKeys[j]) == null) {
                break;
            }
            String crsPK = (crs.getObject(primaryKeys[j])).toString();
            String rsPK = (rs.getObject(primaryKeys[j])).toString();
            if (crsPK.equals(rsPK)) {
                returnVal = true;
                this.crsResolve.moveToInsertRow();
                for (i = 1; i <= icolCount; i++) {
                    String colname = (rs.getMetaData()).getColumnName(i);
                    if (colname.equals(primaryKeys[j]))
                        this.crsResolve.updateObject(i,rsPK);
                    else
                        this.crsResolve.updateNull(i);
                }
                this.crsResolve.insertRow();
                this.crsResolve.moveToCurrentRow();
            }
        }
    }
}
if (returnVal)
    return returnVal;
...


'rs' is the ResultSet of selectCmd which is "SELECT month_value FROM MonthLookup". This points to the first row of 'rs'. The ResultSet consists of rows of containing only column "month_value" from MonthLookup table

'crs' is the CachedRowSet we created in testcase using query "SELECT month_value FROM MonthLookup" and into which we inserted an additional record (passed as an argument to insertNewRow()). This points to the new record we added in our testcase.

'primaryKeys' is the array of primary keys. In this case, there is just one key: "month_id"

The newly added code is supposed to check if there are any duplicates of the primary key value we entered in the new Row. If there are, we should set the conflict to true and present the current row as a null row to the user. While carrying out this operation, the code seems to make the following incorrect assumptions:
   
1. It expects that primary key column should not be auto increment and
   no insert operation takes place without primary key. Hence the
   following conditional:

   if(crs.getObject(primaryKeys[j]) == null){
       break;
   }

   throws the java.sql.SQLException because the primary key "month_id"
   is not present in the cachedRowSet ("crs") created from the query
   "SELECT month_value FROM MonthLookup". In fact, this check is not
   really required because the primary key should always be present due
   to database constraints.

   This is the issue that we are most interested in, and this is what
   our suggested fix aims to address.

2. The code is only checking the first row of the ResultSet ("rs"), due
   to the line that reads "if(rs.next() {". This means that primary key
   values in other rows are not being checked for duplicates of the
   primary key for the new row being inserted.

   This issue is not our primary focus - it's just something we noticed
   while investigating the main problem.

RECREATION INSTRUCTIONS
-----------------------
1. Create a table in an SQL database as follows:

     CREATE TABLE [MonthLookup] (
       ? [month_id] INT IDENTITY(1,1) NOT NULL,
       ? [month_value] VARCHAR(40) NOT NULL,
       ? CONSTRAINT [PK_MonthLookup] PRIMARY KEY ([month_id])
     )

2. Edit the testcase to add the relevant server details and access
   credentials, and run the testcase.

Expected result
New record should be inserted properly and a new automatically incremented ID generated

Observed result
The following Exception is thrown:

java.sql.SQLException: Invalid column name
    at com.sun.rowset.CachedRowSetImpl.getColIdxByName(CachedRowSetImpl.java:1643)
    at com.sun.rowset.CachedRowSetImpl.getObject(CachedRowSetImpl.java:3023)
    at com.sun.rowset.internal.CachedRowSetWriter.insertNewRow(CachedRowSetWriter.java:847)
    at com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:346)
    at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:875)
    at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:934)
    at TestRowset2.performTest(TestRowset2.java:116)
    at TestRowset2.main(TestRowset2.java:125)

TESTCASE
--------
Attached

SUGGESTED FIX
-------------
Remove the following unnecessary check from CachedRowSetWriter.insertNewRow():

   if(crs.getObject(primaryKeys[j]) == null){
       break;
   }