Обсуждение: The column name was not found in this ResultSet

Поиск
Список
Период
Сортировка

The column name was not found in this ResultSet

От
Zsolt Kúti
Дата:
Hello,

In a query like this:
select * form T1 a, T2 b where a.id='xx' and a.id=b.id

calling the result set:
rs.getString("o.id")

I get:
org.postgresql.util.PSQLException: The column name o.id was not found
in this ResultSet. at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2562)
at
org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2405)
at
hu.prolan.emgmt.fre.service.store.impl.OrderStoreImpl.loadOrder(OrderStoreImpl.java:236)

Getting data by index is OK, just as simply using getString('id'). This
nonqualified access however cannot get to the next column with the
same name...

How can the columns accessed by qualified name?
Is it a bug?

I use:
postgresql-9.1-901.jdbc4.jar
openjdk6


Please cc to me as I am not on the list!
Thanks!

Zsolt

Re: The column name was not found in this ResultSet

От
"David Johnston"
Дата:
> In a query like this:
> select * form T1 a, T2 b where a.id='xx' and a.id=b.id
>
> calling the result set:
> rs.getString("o.id")   <<<<<<<<<<<<<<<<<<<<<  "o" is neither "a" nor "b"
<<<<<<<<<<<
>

Re: The column name was not found in this ResultSet

От
Zsolt Kúti
Дата:
Thu, 15 Nov 2012 17:19:51 -0500 -n
David Johnston <polobo@yahoo.com> írta:

> > In a query like this:
> > select * form T1 a, T2 b where a.id='xx' and a.id=b.id
> >
> > calling the result set:
> > rs.getString("o.id")   <<<<<<<<<<<<<<<<<<<<<  "o" is neither "a"
> > nor "b"

Hello David,

Information has distorted while I tried to simplify presenting my case.
rs.getString("o.id") must be read as rs.getString("a.id")

Sorry for the confusion.

Enumeration all the fields needed after the SELECT works around,
but it is rather tedious for 30+ fields.

Zsolt

Re: The column name was not found in this ResultSet

От
Craig Ringer
Дата:
On 11/14/2012 06:42 PM, Zsolt Kúti wrote:
> Hello,
>
> In a query like this:
> select * form T1 a, T2 b where a.id='xx' and a.id=b.id
>
> calling the result set:
> rs.getString("o.id")
>
> I get:
> org.postgresql.util.PSQLException: The column name o.id was not found
> in this ResultSet. at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2562)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2405)
> at
> hu.prolan.emgmt.fre.service.store.impl.OrderStoreImpl.loadOrder(OrderStoreImpl.java:236)
>
> Getting data by index is OK, just as simply using getString('id'). This
> nonqualified access however cannot get to the next column with the
> same name...
>
> How can the columns accessed by qualified name?
> Is it a bug?

I don't think so, but it isn't simple.

Consider the following:

regress=> SELECT a.n, 'somevalue' AS "a.n" FROM (VALUES ('othervalue'))
a(n);
     n      |    a.n
------------+-----------
 othervalue | somevalue
(1 row)

Which of these columns is "a.n" to PgJDBC? The one actually called "a.n"
(note the identifier quotes)? Or the one called "n" with table-alias "a"?

The JDBC API does not offer us a "ResultSet.getString(String columnname,
String schemaname)" that would allow us to disambiguate these cases. So
we either have to:

(a) Intepret all identifiers passed to get...() functions as if they
were PostgreSQL identifier literals, unescaping them and splitting them
into schema-part and literal-part; or

(b) Just get the first column with the given name and require users to
alias column names to disambiguate them.


In my opinion (a) would be horrible to use, and it appears to be
contrary to the JDBC Resultset spec as well. You'd have to "double
quote" every identifier if you wanted to use mixed case or any kind of
non-alphanumeric strings, so:

    rs.getString("B.F. Name")

would have to become:

    rs.getString("\"B.F. Name\"")

or users would get syntax errors, identifier parsing errors, or bizarre
errors about columns not being found.

So (b) is the best compromise, really. Just alias your columns to
disambiguate them in the result set by specifying `AS` aliases.

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: The column name was not found in this ResultSet

От
Zsolt Kúti
Дата:
Mon, 19 Nov 2012 09:31:17 +0800 -n
Craig Ringer <craig@2ndQuadrant.com> írta:

...
> In my opinion (a) would be horrible to use, and it appears to be
> contrary to the JDBC Resultset spec as well. You'd have to "double
> quote" every identifier if you wanted to use mixed case or any kind of
> non-alphanumeric strings, so:
>
>     rs.getString("B.F. Name")
>
> would have to become:
>
>     rs.getString("\"B.F. Name\"")
>
> or users would get syntax errors, identifier parsing errors, or
> bizarre errors about columns not being found.
>
> So (b) is the best compromise, really. Just alias your columns to
> disambiguate them in the result set by specifying `AS` aliases.

Craig! Thank you for the explanation!

I am to live with b...

Zsolt