Skip site navigation (1) Skip section navigation (2)

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

From: Stefan Reiser <stefanreiser(at)arcor(dot)de>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: PostgreSQL JDBC: Inserts/Updates on ResultSet fail when using column aliases. Exception 'The column name {0} was not found in this ResultSet'
Date: 2010-12-29 02:44:34
Message-ID: 4D1AA092.8030307@arcor.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
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?
*/



pgsql-jdbc by date

Next:From: Stefan ReiserDate: 2010-12-29 02:48:45
Subject: PostgreSQL JDBC: Inserts/Updates on ResultSet fail when using column aliases. Exception 'The column name {0} was not found in this ResultSet'
Previous:From: Samuel GendlerDate: 2010-12-28 18:08:59
Subject: Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group