Thread: The column name was not found in this ResultSet
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
> 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" <<<<<<<<<<< >
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
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
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