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

The problem though, is that for some of us writing tools, being able to
know the table name is going to be very useful. In other words if
you're doing "select a.*, b.* from a, b" I want to be able to tell which
columns are from a and which ones are from b. For instance, I might want
to put an update link on the first column of each table.

This has been available forever on Mysql, and I wish we had it too.

Dror

On Fri, Oct 04, 2002 at 09:12:44AM -0400, Dave Cramer wrote:
> 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@postgresql.org)
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com