Re: ResultSet Column Name Problem in pgjdbc2.jar?

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)
 >