Thread: PostgreSQL JDBC: Inserts/Updates on ResultSet fail when using column aliases. Exception 'The column name {0} was not found in this ResultSet'

Hello,

I found that any calls to insertRow() or updateRow() on an updateable
ResultSet will raise an exception if any column aliases were used in the
query.

For example an update on "SELECT foo AS bar FROM ..." leads to the
exception "The column name foo was not found in this ResultSet."

The reason is that there is a confusion between column names and column
labels in  org.postgresql.jdbc2.AbstractJdbc2ResultSet .
Internaly it uses findColumn("foo") to look up the column's index by
it's name, but fails because findColumn() works with column labels
instead of names.

For my own needs I have patched  AbstractJdbc2ResultSet.java  from
version 8.4-702 by introducing another HashMap that maps column names to
indexes in addition to the already existing one which does the mapping
for column labels. (I'm using the patched version for about two weeks
now with no more problems so far.)

Here is the changed file:

http://home.arcor.de/stefanreiser/docs/postgresql/AbstractJdbc2ResultSet.java

The following detailed description of the problem and the changes I have
made can also be found in the header comment of that file.

Regards
Stefan Reiser

/* 2010-12-15/2010-12-29, errors:

    If column name aliases were used in the query (e.g. "SELECT foo AS
bar") then
    the calls to updateRowBuffer() in updateRow() and insertRow() fail
with the
    exception "The column name foo was not found in this ResultSet."

    Reason: Confusion between column names and column labels.

    In detail:
    1) updateRow() uses the columns' _names_ for creating an sql update
statement.
       These names come from the keySet of HashMap "updateValues" as
defined in
          protected void updateValue(...) {
            ...
            updateValues.put(fields[columnIndex -
1].getColumnName(connection), value);
            ...
          }

    2) The update then is successfully committed to the database. After
that,
       updateRowBuffer() is called and raises an exception (i.e. the
committed data does not become visible in the ResultSet) :

         Method updateRowBuffer() uses the column name from
updateValues.keySet()
         to lookup the corresponding index via findColumn(key). But
findColumn(key)
         expects column _labels_, so the column is not found if name !=
label.

    3) A similar problem exists in insertRow(). The same goes for
isUpdateable() if
       column aliases were used for any primary key fields.

    Workaround: Don't use any column aliases in your sql query :-(

    Solution:
       - This patch introduces a second HashMap: "columnNameToIndexMap".
       - The old "columnNameIndexMap" was renamed to
"columnLabelToIndexMap".
       - Method
          private int findColumnIndex(String columnName) was renamed to
          private int findColumnIndexByLabel(String columnLabel).
       - A new method private int findColumnIndexByName(String
columnName) was
         created and is now called from updateRowBuffer() and from
isUpdateable().

    Todo:
       - Is the case-insensitive behaviour in the method
"findColumnIndexByName"
         correct according to specifications?
*/