Uploaded image for project: 'JDK'
  1. JDK
  2. JDK-7145913

CachedRowSetSwriter.insertNewRow() throws SQLException



    • b45
    • generic
    • generic


      CachedRowSetSwriter.insertNewRow() throws SQLException


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

      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;

      if (rs.next()) {
          for (int j=0;j<primaryKeys.length;j++) {
              if (primaryKeys[j] != null) {
                  if (crs.getObject(primaryKeys[j]) == null) {
                  String crsPK = (crs.getObject(primaryKeys[j])).toString();
                  String rsPK = (rs.getObject(primaryKeys[j])).toString();
                  if (crsPK.equals(rsPK)) {
                      returnVal = true;
                      for (i = 1; i <= icolCount; i++) {
                          String colname = (rs.getMetaData()).getColumnName(i);
                          if (colname.equals(primaryKeys[j]))
      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){

         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.

      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)


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

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




            lancea Lance Andersen
            dkorbel David Korbel (Inactive)
            0 Vote for this issue
            0 Start watching this issue