Re: ResultSet Column Name Problem in pgjdbc2.jar? - Mailing list pgsql-jdbc

Mike,

The portable way of doing what you want across databases is to alias the
columns you are selecting to give them unique names.

select users.id as u_id, users.class as u_class items.class as i_class

The MySQL behavior you are relying on is non standard and won't work on
Postgres or other databases like Oracle.

thanks,
--Barry


Mike Abraham wrote:
 > I've searched the archives for help on this, but come up empty. My
 > apologies if this problem has already been addressed.
 >
 > In a nutshell, my problem is this: A resultset returned from a query
 > containing table & column names (as opposed to column names alone)
 > contains only the column names (rather than table & column names).
 >
 > Here's a simplified version of the code that's giving me a problem:
 >
 > ...
 >
 > ResultSet rs = stmt.executeQuery(
 >     "select users.id, users.class, items.class " +
 >     "from users " +
 >     "left join items on (users.id = items.id)");
 >
 > while (rs.next()) {
 >     System.out.println(
 >     rs.getInt("users.user_id") + " | " +
 >     rs.getInt("users.class") + " | " +
 >     rs.getInt("items.class"));
 > }
 >
 > ...
 >
 > You get the picture. I'm joining on the 'id' column of tables 'users'
 > and 'items' in order to return the values in their respective 'class'
 > fields.
 >
 > This works like a dream in MySQL, both via the admin query tool, as well
 > as my java code. It works with the pgAdmin query tool, but my java code
 > gives the following error:
 >
 > "The column name transactions.transaction_id not found.
 >     at org.postgresql.jdbc2.ResultSet.findColumn(ResultSet.java:821)
 >     at org.postgresql.jdbc2.ResultSet.getInt(ResultSet.java:574)"
 >
 > Here's why. The query passes through to the database just fine, the
 > ResultSet that's returned, however, only has the column names, without
 > the table prefixes, in its Fields collection.
 >
 > In other words, taking a look at the Fields collection in the debugger
 > shows 3 fields: Fields(0) = "user_id", Fields(1) = "class", Fields(2) =
 > "class".
 >
 > Running against the MySQL JDBC driver gives the proper result, including
 > the table prefixes: Fields(0) = "users.user_id", Fields(1) =
 > "users.class", Fields(2) = "items.class".
 >
 > So, in order to use the PostgreSQL driver, you gotta reference all
 > columns in a ResultSet by column number, not name. This is not a huge
 > problem, of course, but I thought I should make you aware of it.
 >
 > Thanks for your attention.
 >
 > Mike Abraham
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 2: you can get off all lists at once with the unregister command
 >    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
 >