Re: ResultSet Column Name Problem in pgjdbc2.jar?

Mike

There is a workaround which is portable,

select users.id, users.class as usersclass, items.class as itemsclass...

Dave
On Wed, 2002-10-02 at 19:10, 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(at)postgresql(dot)org)
>
>