Thread: ResultSet Column Name Problem in pgjdbc2.jar?

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

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

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

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

I have just wondered about the implementation of Connection.setAutoCommit().
At least the JDBC 1.3 API has this note:
NOTE: If this method is called during a transaction, the transaction is
committed.

With postgresql that is not the case, at least not for 7.2 of the jdbc
driver:

public void setAutoCommit(boolean autoCommit) throws SQLException
{
        if (this.autoCommit == autoCommit)
                return;
...

It think this is important to be consistent, otherwise it's not possible to
know the transaction state after setAutoCommit()
Any comments?

Another question: is it a generally good idea to start a new transaction
just after every setAutoCommit(), commit() or rollback()? Wouldn't it be
better to "begin;" just before the first statement to be executed? That
wouldn't leave so many open transactions when you have many open
connections.

Regards,
Michael Paesold

Michael Paesold wrote:
> I have just wondered about the implementation of Connection.setAutoCommit().
> At least the JDBC 1.3 API has this note:
> NOTE: If this method is called during a transaction, the transaction is
> committed.

Can you point out where in the JDBC specs this is documented.  I would
like to look at this some more.

>
> With postgresql that is not the case, at least not for 7.2 of the jdbc
> driver:
>
> public void setAutoCommit(boolean autoCommit) throws SQLException
> {
>         if (this.autoCommit == autoCommit)
>                 return;
> ...

The above is the behavior I would expect, if you have already turned
autocommit on and you attempt to do it again then I would expect a noop.
  Of course if the spec says otherwise we should follow the spec.


>
> It think this is important to be consistent, otherwise it's not possible to
> know the transaction state after setAutoCommit()
> Any comments?

You do know the transaction state after setAutoCommit, you will always
be in a transaction.

>
> Another question: is it a generally good idea to start a new transaction
> just after every setAutoCommit(), commit() or rollback()? Wouldn't it be
> better to "begin;" just before the first statement to be executed? That
> wouldn't leave so many open transactions when you have many open
> connections.

But if the open transactions haven't done anything yet then it really
doesn't make any difference.


--Barry