Thread: Updatable query and column aliases

Hi folks.

I cant moveToInsertRow when use queries with assigned names in select
list.

Following code works good:

     stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                       ResultSet.CONCUR_UPDATABLE);
     rs = stmt.executeQuery("select id from test");
     rs.moveToInsertRow();

, but then I change query to "select id val from test" exception "The
column name id was not found in this ResultSet." raised on
moveToInsertRow.

Is it any limitation on updatable queries?

--
Anton

Anton Bobov schrieb:
> Hi folks.
>
> I cant moveToInsertRow when use queries with assigned names in select
> list.
>
> Following code works good:
>
>     stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>                                       ResultSet.CONCUR_UPDATABLE);
>     rs = stmt.executeQuery("select id from test");
>     rs.moveToInsertRow();
>
> , but then I change query to "select id val from test" exception "The
> column name id was not found in this ResultSet." raised on
> moveToInsertRow.
>
> Is it any limitation on updatable queries?
>
> --
> Anton
>
>
Hello Anton,

yes, the limitations are:
1) query must only use one table
2) no column aliases can be used

Point 2 is a bug which I have described here:
    http://archives.postgresql.org/pgsql-jdbc/2010-12/msg00084.php
and here:
    http://archives.postgresql.org/pgsql-jdbc/2011-08/msg00077.php

The patch mentioned above does not work anymore since some things have
changed in later versions of the driver.

Here is a patch for version 9.0.801 (the only file that has to be
changed is "AbstractJdbc2ResultSet").


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

... sorry, I'm not familiar with the versioning system - maybe one of
the developers can take a look at my patch?

Regards
Stefan Reiser

Sorry, what I just posted is in fact for the current version _9.1-901_
an it can be found here:


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


Stefan Reiser schrieb:
> Anton Bobov schrieb:
>> Hi folks.
>>
>> I cant moveToInsertRow when use queries with assigned names in select
>> list.
>>
>> Following code works good:
>>
>>     stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
>>                                       ResultSet.CONCUR_UPDATABLE);
>>     rs = stmt.executeQuery("select id from test");
>>     rs.moveToInsertRow();
>>
>> , but then I change query to "select id val from test" exception "The
>> column name id was not found in this ResultSet." raised on
>> moveToInsertRow.
>>
>> Is it any limitation on updatable queries?
>>
>> --
>> Anton
>>
>>
> Hello Anton,
>
> yes, the limitations are:
> 1) query must only use one table
> 2) no column aliases can be used
>
> Point 2 is a bug which I have described here:
>    http://archives.postgresql.org/pgsql-jdbc/2010-12/msg00084.php
> and here:
>    http://archives.postgresql.org/pgsql-jdbc/2011-08/msg00077.php
>
> The patch mentioned above does not work anymore since some things have
> changed in later versions of the driver.
>
> Here is a patch for version 9.0.801 (the only file that has to be
> changed is "AbstractJdbc2ResultSet").
>
>
> http://home.arcor.de/stefanreiser/docs/postgresql/9.0.801/AbstractJdbc2ResultSet.java
>
> ... sorry, I'm not familiar with the versioning system - maybe one of
> the developers can take a look at my patch?
>
> Regards
> Stefan Reiser
>