Thread: Re: Wrong column names in ResultSetMetaData
I wrote:
> With the new V3 driver the column names in ResultSetMetaData
> don't reflect the aliases used in the SQL. I.e. if I do:
>
> SELECT name as name_alias FROM ...
>
> the metadata says I have a result column called "name" instead
> of "name_alias".
I think I see the problem. v2/QueryExecutorImpl.java:400 says:
fields[i] = new Field(columnLabel, columnLabel, typeOid, typeLength,
typeModifier, 0, 0);
whereas v3/QueryExecutorImpl.java:1097 says:
fields[i] = new Field(columnLabel,
null, /* name not yet determined */
typeOid, typeLength, typeModifier, tableOid,
positionInTable);
and a separate query is later done in getColumnName() to try
to return the unaliased *source* column name used in the query.
I'm almost certain this is wrong from a JDBC standpoint.
rsmd.getColumnName() is supposed to return the given name of
the *result* column, which SQL has rules to define.
rsmd.getColumnLabel() is "for use in printouts and displays"
and will often equate to the column name unless the DBMS has
some "prettier" column title for display purposes.
For programmatic purposes the column name concept is pretty
well defined by the docs on ResultSet. They're supposed to
behave such that:
String colname = rsmd.getColumnName(col);
return rs.getXXX(colname);
is equivalent to:
return rs.getXXX(col);
for every column that has a (non-duplicate) name.
I have to think this is going to break more code than just
ours.
Mike
On Wed, 28 Jul 2004, Mike Martin wrote: > I wrote: > > [new driver returns a column alias as the label, not name] > > I'm almost certain this is wrong from a JDBC standpoint. > > rsmd.getColumnName() is supposed to return the given name of > the *result* column, which SQL has rules to define. > rsmd.getColumnLabel() is "for use in printouts and displays" > and will often equate to the column name unless the DBMS has > some "prettier" column title for display purposes. > > For programmatic purposes the column name concept is pretty > well defined by the docs on ResultSet. They're supposed to > behave such that: > > String colname = rsmd.getColumnName(col); > return rs.getXXX(colname); > > is equivalent to: > > return rs.getXXX(col); > I am not sure I see where it states that. Your logic makes some sense, but I don't see anywhere it says the above explicity. The problem is how to return both pieces of information (the alias and the underlying column name) within the JDBC API. Certainly the alias makes more sense as the label when you have to pick between the two of them. Would you expect the results of getTableName() to return the underlying table or the table's alias in the query? To be consistent with your argument you'd have to claim the alias name which is useless here. Further code doesn't make a whole lot of sense doing: rs.getString(rsmd.getColumnName(1)); Why wouldn't it just do: rs.getString(1); Kris Jurka
Mike Martin wrote: > For programmatic purposes the column name concept is pretty > well defined by the docs on ResultSet. Can you point me to these docs? I can't see this requirement from a glance through the 1.4 javadoc, but the JDBC javadoc is pretty useless.. > I have to think this is going to break more code than just > ours. I don't remember intentionally changing this behaviour in the V3 overhaul so it may actually predate those changes. However, Field was rearranged when doing the V3 changes so it's entirely possible I accidentally broke this behaviour. -O
On Thu, 29 Jul 2004, Oliver Jowett wrote: > I don't remember intentionally changing this behaviour in the V3 > overhaul so it may actually predate those changes. However, Field was > rearranged when doing the V3 changes so it's entirely possible I > accidentally broke this behaviour. > This was done intentionally. Back in January I applied this patch: date: 2004/01/13 03:07:09; author: jurka; state: Exp; lines: +95 -31 This patch is the culmination of Jan-Andre le Roux's work on enhancing the ResultSetMetaData methods with new information available in the V3 protocol. He wandered off of the verge of completing it and I've just done some minor reworking and editing. This implements getSchemaName, getTableName, getColumnName, getColumnLabel, and isNullable. Kris Jurka
Kris Jurka wrote: > This was done intentionally. Back in January I applied this patch: > > date: 2004/01/13 03:07:09; author: jurka; state: Exp; lines: +95 -31 > This patch is the culmination of Jan-Andre le Roux's work on enhancing > the ResultSetMetaData methods with new information available in the V3 > protocol. He wandered off of the verge of completing it and I've just > done some minor reworking and editing. > > This implements getSchemaName, getTableName, getColumnName, > getColumnLabel, and isNullable. I notice that the implementation of updateable resultsets use Field.getColumnLabel() when generating the UPDATE/SELECT/etc SQL. Presumably this should be using Field.getColumnName() given the current semantics? -O
"Kris Jurka" wrote:
> > For programmatic purposes the column name concept is pretty
> > well defined by the docs on ResultSet.
>
> I am not sure I see where it states that. Your logic makes some sense,
> but I don't see anywhere it says the above explicity. The problem is how
> to return both pieces of information (the alias and the underlying column
> name) within the JDBC API. Certainly the alias makes more sense as the
> label when you have to pick between the two of them.
It depends on how explicit you need it to be. :)
Unfortunately the JDBC spec itself lacks much of anything that
explicit. The Javadoc for ResultSet says:
"The column name option is designed to be used when column
names are used in the SQL query that generated the result set.
For columns that are NOT explicitly named in the query, it
is best to use column numbers."
I read "named" to mean named according to the usual SQL rules.
Combine that with the other references to the semantics of column
name (findColumn(), getXXX(colName)) and it seems like the
reasonable reading.
My other support is circumstantial:
1. An SQL query produces a result set which is itself a table,
that table has columns, those columns have names, and those
names are defined according to SQL rules. The column being
named is that of the result table, not that of the underlying
source(s) that contributed to it. I can't imagine
getColumnName() referring to any other concept.
2. Every other JDBC driver I'm familiar with behaves that way
(with the exception, I hear, of MimerSQL, which confuses
getColumnLabel() with SQL alias the way our new code does).
I'm not clear why a JDBC client would want or need to know the
"underlying" column name, if there even is one. I can see why
the driver might need to know that internally for purposes of
updateability but that's something different.
> Would you expect the results of getTableName() to return the underlying
> table or the table's alias in the query? To be consistent with your
> argument you'd have to claim the alias name which is useless here.
The alias. By SQL naming rules every column may also have a
table name component that can be used to disambiguate it from
other columns of the same name. If a query represents a single
table expression, and that expression has been given an alias
name (aka "range variable"), then every column takes that alias
as its table name.
Likewise with getCatalogName(int) except I believe a column loses
any catalog affiliation as soon as it's aliased in any way.
> Further code doesn't make a whole lot of sense doing:
>
> rs.getString(rsmd.getColumnName(1));
>
> Why wouldn't it just do:
>
> rs.getString(1);
In real life you would. I was just illustrating the expected
semantics.
Mike
On Thu, 29 Jul 2004, Mike Martin wrote: > 2. Every other JDBC driver I'm familiar with behaves that way > (with the exception, I hear, of MimerSQL, which confuses > getColumnLabel() with SQL alias the way our new code does). What do they return for getColumnLabel()? Previously we returned the same value for both methods simply because we couldn't implement getColumnName as we do now. If they all return the alias in getColumnLabel that would be telling. > I'm not clear why a JDBC client would want or need to know the > "underlying" column name, if there even is one. I can see why > the driver might need to know that internally for purposes of > updateability but that's something different. > To allow client software to do the updates or allow the user to navigate from a query result column to the base table or to/through foreign keys on it. There could be a number of uses. Kris Jurka
Kris Jurka wrote:
> > 2. Every other JDBC driver I'm familiar with behaves that way
> > (with the exception, I hear, of MimerSQL, which confuses
> > getColumnLabel() with SQL alias the way our new code does).
>
> What do they return for getColumnLabel()? Previously we returned the same
> value for both methods simply because we couldn't implement getColumnName
> as we do now. If they all return the alias in getColumnLabel that would
> be telling.
Most return the same thing as getColumnName(); not surprising
since that's a good default display title. I've heard that
some drivers return a string containing extra metadata fluff
about the column type.
Consider:
SELECT c1 + c2 FROM t1
The name/label distinction allows the DBMS/driver, if it wants,
to give the column a label for display purposes of, say,
"Expr c1+c2" or "Result, type=decimal(9,2)". But the column
has no SQL name. Nor could it have a name like "Expr c1+c2"
since that's not a valid SQL identifier.
> > I'm not clear why a JDBC client would want or need to know the
> > "underlying" column name, if there even is one. I can see why
> > the driver might need to know that internally for purposes of
> > updateability but that's something different.
>
> To allow client software to do the updates or allow the user to navigate
> from a query result column to the base table or to/through foreign keys on
> it. There could be a number of uses.
What about:
SELECT col1 AS price FROM t1
UNION
SELECT col2 AS price FROM t2
What is the "true" column name? SQL says that query produces a
table with one column named "PRICE".
Client code simply can't reverse-engineer query result columns to
find their origins. If a user is trying to do so for updating,
that's what updatable result sets are for, and that's why only a
small subset of queries are updatable.
From http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame5.html:
Due to differences in database implementations, the JDBC API
does not specify an exact set of SQL queries which must yield
an updatable result set for JDBC drivers that support
updatability. Developers can, however, generally expect
queries which meet the following criteria to produce an
updatable result set:
1. The query references only a single table in the database.
2. The query does not contain any join operations.
3. The query selects the primary key of the table it references.
In addition, an SQL query should also satisfy the conditions
listed below if inserts are to be performed.
4. The query selects all of the non-nullable columns in the
underlying table.
5. The query selects all columns that don't have a default
value.
Mike
Oliver Jowett wrote:
> Mike Martin wrote:
> > For programmatic purposes the column name concept is pretty
> > well defined by the docs on ResultSet.
>
> Can you point me to these docs? I can't see this requirement from a
> glance through the 1.4 javadoc, but the JDBC javadoc is pretty useless..
From the ResultSet Javadoc:
"The column name option is designed to be used when column
names are used in the SQL query that generated the result set.
For columns that are NOT explicitly named in the query, it
is best to use column numbers."
The only way to "name" a SQL query column is with AS. There's
corroboration in DatabaseMetaData:
public boolean supportsColumnAliasing() throws SQLException
Retrieves whether this database supports column aliasing.
If so, the SQL AS clause can be used to provide names for
computed columns or to provide alias names for columns as
required.
In both cases, "names" and not "labels".
I too wish the docs were more definitive on this. Googling on
the topic reveals that there has been confusion on it for some
time. Anyone know someone at Sun who could confirm the true
intent?
Mike
On Mon, 2 Aug 2004, Mike Martin wrote: > > [I want getColumnName to return the alias name used in a query.] > All the documentation you've pointed to is circumstancial at best. The phrase "column name" is not a well defined term and seems to be used differently in different places. A little time with Google shows people desiring the current driver behavior which shows there is some need for this. While it doesn't show your side of the argument because not many drivers have implemented it this way, you haven't given any other well defined behavior for implementing getColumnLabel. The bottom line is that I am comfortable with the driver's current behavior. Perhaps not as comfortable as I originally was when the frontend/backend protocol was adjusted specifically for this purpose, or when the patch utilizing this new data was committed, but I believe it's a logical and defensible position. You will need to find clear documentation that this is incorrect or rally some other users to your position. Kris Jurka
On Thu, 29 Jul 2004, Oliver Jowett wrote: > I notice that the implementation of updateable resultsets use > Field.getColumnLabel() when generating the UPDATE/SELECT/etc SQL. > Presumably this should be using Field.getColumnName() given the current > semantics? > Fixed. Kris Jurka
Kris Jurka wrote:
> All the documentation you've pointed to is circumstancial at best.
Come now, I think it's a little better than that. I guess I
don't see as much ambiguity in "... the SQL AS clause can be
used to provide names for computed columns" as you do.
> You will need to find clear
> documentation that this is incorrect or rally some other users to your
> position.
Then I call on all users using any of the following DBMS's
to weigh in on this issue:
SELECT PRICE AS ORIG_PRICE, PRICE + 1.00 AS PRICE FROM T
getColumnName(1) getColumnName(2)
DB/driver getColumnLabel(1) getColumnLabel(2)
Cloudscape ORIG_PRICE ORIG_PRICE PRICE PRICE
Firebird ORIG_PRICE ORIG_PRICE PRICE PRICE
SQL Server (JTDS) ORIG_PRICE ORIG_PRICE PRICE PRICE
SQL Server (MS) ORIG_PRICE ORIG_PRICE PRICE PRICE
MySQL Connector/J ORIG_PRICE ORIG_PRICE PRICE PRICE
Oracle thin ORIG_PRICE ORIG_PRICE PRICE PRICE
PostgreSQL 7.4 orig_price orig_price price price
PostgreSQL 7.5 price orig_price price ""
Those are the drivers I have handy at the moment. I have a couple
more I can try tomorrow. I invite others to post their results.
Of course, this is probably the wrong NG for a cross-DB call to
arms. :) But I should think that sample is large enough to cause
worry about how much code is going to be broken.
Mike
On Mon, 2 Aug 2004, Mike Martin wrote: > SELECT PRICE AS ORIG_PRICE, PRICE + 1.00 AS PRICE FROM T > > getColumnName(1) getColumnName(2) > DB/driver getColumnLabel(1) getColumnLabel(2) > > Cloudscape ORIG_PRICE ORIG_PRICE PRICE PRICE > Firebird ORIG_PRICE ORIG_PRICE PRICE PRICE > SQL Server (JTDS) ORIG_PRICE ORIG_PRICE PRICE PRICE > SQL Server (MS) ORIG_PRICE ORIG_PRICE PRICE PRICE > MySQL Connector/J ORIG_PRICE ORIG_PRICE PRICE PRICE > Oracle thin ORIG_PRICE ORIG_PRICE PRICE PRICE > PostgreSQL 7.4 orig_price orig_price price price > PostgreSQL 7.5 price orig_price price "" I think you have the name and column for 2 backwards. It should return "" for the column name because it is a derived column, so there is no underlying table column to return. This shows the results are different, but it doesn't indicate why. Is it simply because the other dbs can't implement getColumnName as we have? I certainly wouldn't expect this to be a common feature. Further it doesn't show that users can't write portable code using the ResultSetMetaData calls. It shows all drivers returning the same value for getColumnLabel that you would expect. Earlier you had speculated that other drivers would return something different for this, but you haven't shown that. Yes it may break existing code, but it doesn't show that they weren't simply using the wrong call. Kris Jurka
Mike Martin wrote: > The only way to "name" a SQL query column is with AS. There's > corroboration in DatabaseMetaData: > > public boolean supportsColumnAliasing() throws SQLException > > Retrieves whether this database supports column aliasing. > If so, the SQL AS clause can be used to provide names for > computed columns or to provide alias names for columns as > required. > > In both cases, "names" and not "labels". Is an "alias name" the same as a "column name"? -O
Kris wrote:
> > PostgreSQL 7.5 price orig_price price ""
>
> I think you have the name and column for 2 backwards. It should return ""
> for the column name because it is a derived column, so there is no
> underlying table column to return.
Sorry, you're right. I inadvertently reversed them in the process
of table-ization.
> This shows the results are different, but it doesn't indicate why. Is it
> simply because the other dbs can't implement getColumnName as we have?
> I certainly wouldn't expect this to be a common feature.
>
> Further it doesn't show that users can't write portable code using the
> ResultSetMetaData calls. It shows all drivers returning the same value
> for getColumnLabel that you would expect. Earlier you had speculated that
> other drivers would return something different for this, but you haven't
> shown that. Yes it may break existing code, but it doesn't show that they
> weren't simply using the wrong call.
No, I speculated that a driver *could*, if it had a special pretty
print label available from the DBMS, return it from getColumnLabel().
Most DBMS's don't. Oracle does in SQL*Plus; for example:
column c1 heading "# OF ROWS" format 99,999,999;
column c2 heading "#_rows*row_len" format 9,999,999,999;
column c3 heading "PCT USED" format 999;
select num_rows c1,
num_rows*avg_row_len c2,
((num_rows*avg_row_len)/(blocks*&blksz))*100 c3
from perfstat.stats$tab_stats a
...
Does their driver support that? I don't know. But it is an
example of exactly what the docs for getColumnLabel() describe:
the "suggested title for use in printouts and displays". In the
absence of such support, doesn't it make sense that driver
writers would have simply defaulted label to column name?
I don't see anything ambiguous in "suggested title for use in
printouts and displays", nor anything that would even remotely
tie it to SQL alias. But I do see, and have given you, text that
explicitly ties column names to SQL aliases. Plus evidence that
many other well-known drivers work on that basis.
And you can't just ask everyone to switch their getColumnName() to
getColumnLabel(). It doesn't even work because of findColumn()
and rs.getXXX(String). Those methods are *required* to resolve a
duplicate name to the first matching column (ResultSet Javadoc).
With your code, my rs.getFloat("PRICE") *must* resolve to column
1, and that's simply the wrong answer.
Mike
Oliver Jowett wrote:
> > The only way to "name" a SQL query column is with AS. There's
> > corroboration in DatabaseMetaData:
> >
> > public boolean supportsColumnAliasing() throws SQLException
> >
> > Retrieves whether this database supports column aliasing.
> > If so, the SQL AS clause can be used to provide names for
> > computed columns or to provide alias names for columns as
> > required.
> >
> > In both cases, "names" and not "labels".
>
> Is an "alias name" the same as a "column name"?
In SQL, yes. A result column has a name, and the AS clause is
the way to reassign that name.
Try this on your favorite DBMS:
SELECT COL FROM (SELECT COL AS FOO FROM T) T1
It fails because the parenthesized query result has no column
named COL.
Mike
Kris Jurka wrote: > This shows the results are different, but it doesn't indicate why. Is it > simply because the other dbs can't implement getColumnName as we have? > I certainly wouldn't expect this to be a common feature. > > Further it doesn't show that users can't write portable code using the > ResultSetMetaData calls. It shows all drivers returning the same value > for getColumnLabel that you would expect. Earlier you had speculated that > other drivers would return something different for this, but you haven't > shown that. Yes it may break existing code, but it doesn't show that they > weren't simply using the wrong call. I'm leaning towards Mike's argument here. It seems reasonable that the column name returned is the same as what you'd use to look up a column by name via ResultSet, and the only sensible thing to do in ResultSet lookups is to use the column aliases. The argument for exposing the real column name as a way to allow portable updating of the underlying table seems weak given that other drivers don't support the same behaviour. The more portable behaviour seems to be to always use getColumnName() and forbid the use of AS in queries that you want to be updatable. getColumnLabel() isn't the right place for the underlying column name, though. JDBC doesn't actually seem to have a mapping for this concept at all. I'd suggest adding a PG-specific interface if we want to expose this. How about: RSMD.getColumnName() returns the column alias RSMD.getColumnLabel() returns the column alias, or maybe something like the column's comment (can you COMMENT ON columns?) later. PGResultSetMetadata.getSourceColumnName() returns the underlying table column name. The driver's updateable resultset code can use this when constructing update SQL. On a related note, if I execute this query: SELECT * FROM footable f should RSMD.getTableName() return "footable" or "f"? By analogy to RSMD.getColumnName() it should return "f", but it seems more useful to return "footable". -O
On Tue, 3 Aug 2004, Oliver Jowett wrote: > RSMD.getColumnLabel() returns the column alias, or maybe something > like the column's comment (can you COMMENT ON columns?) later. This is my major beef with the argument, what do you do for getColumnLabel? Returning the column's comment (which you can do) isn't the right thing either. I've got comments running to paragraph length describing some of the more obscure columns in my databases. No other driver in Mike's list showed an implementation of this different from getColumnName, so what's the point? The spec authors must have had some existing implementation to inspire this feature. The argument that getColumnLabel and getColumnName should do the exact same thing just seems wrong to me. Kris Jurka
Kris Jurka wrote: > > On Tue, 3 Aug 2004, Oliver Jowett wrote: > > >> RSMD.getColumnLabel() returns the column alias, or maybe something >>like the column's comment (can you COMMENT ON columns?) later. > > > The spec authors must have had some > existing implementation to inspire this feature. The argument that > getColumnLabel and getColumnName should do the exact same thing just seems > wrong to me. That wasn't the argument. The column name is just that a good default label if you have nothing better to hand. If Postgres had a (hypothetical) column label field associated with each column, for example, we could return that. As for existing implementations, a quick google turned this up: http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst69.htm which seems to indicate that DB2 has a column display label (via LABEL ON COLUMN) in addition to column name & column comment. -O
Kris wrote: > > RSMD.getColumnLabel() returns the column alias, or maybe something > > like the column's comment (can you COMMENT ON columns?) later. > > This is my major beef with the argument, what do you do for > getColumnLabel? Returning the column's comment (which you can do) isn't > the right thing either. I've got comments running to paragraph length > describing some of the more obscure columns in my databases. No other > driver in Mike's list showed an implementation of this different from > getColumnName, so what's the point? The spec authors must have had some > existing implementation to inspire this feature. The argument that > getColumnLabel and getColumnName should do the exact same thing just seems > wrong to me. If I'm not mistaken the spec authors were heavily influenced by ODBC. The two specs have far too much alignment for it to have been otherwise. Have a look at the FieldIdentifiers table at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlcolattribute.asp SQLColAttribute is the ODBC function that retrieves result set metadata. SQL_DESC_NAME The column alias, if it applies. If the column alias does not apply, the column name is returned. In either case, SQL_DESC_UNNAMED is set to SQL_NAMED. If there is no column name or a column alias, an empty string is returned and SQL_DESC_UNNAMED is set to SQL_UNNAMED. This information is returned from the SQL_DESC_NAME record field of the IRD. SQL_DESC_LABEL The column label or title. For example, a column named EmpName might be labeled Employee Name or might be labeled with an alias. If a column does not have a label, the column name is returned. If the column is unlabeled and unnamed, an empty string is returned. Note the close alignment of all the Fields to RSMD concepts. Note also that they have explicit fields for SQL_DESC_BASE_COLUMN_NAME and SQL_DESC_BASE_TABLE_NAME which JDBC evidently chose not to inherit. I think that's what the new V3 info would have corresponded to. Bottom line is, I think PG does not have a concept that corresponds directly to getColumnLabel(). By ODBC rules a label should default to the column's alias or name. Mike
Oliver wrote:
> RSMD.getColumnName() returns the column alias
> RSMD.getColumnLabel() returns the column alias, or maybe something
> like the column's comment (can you COMMENT ON columns?) later.
> PGResultSetMetadata.getSourceColumnName() returns the underlying
> table column name. The driver's updateable resultset code can use this
> when constructing update SQL.
I like that, not just because I think it jives with the spec
but because a new method can explicitly document that the
source column name is not available if the result column has
multiple sources.
> On a related note, if I execute this query:
>
> SELECT * FROM footable f
>
> should RSMD.getTableName() return "footable" or "f"? By analogy to
> RSMD.getColumnName() it should return "f", but it seems more useful to
> return "footable".
According to SQL "f" is the right answer. One way convince yourself
is to observe the difference between:
SELECT f.col FROM footable f
SELECT footable.col FROM footable f
The former works as you might expect. The latter fails on some
DBMS's and I'm pretty sure it's invalid SQL. Interestingly, on
PG the latter is evaluated as though it were:
SELECT f2.col FROM footable f1 CROSS JOIN footable f2
which for a footable of N rows returns N*N rows!
Mike
On Tue, 3 Aug 2004, Oliver Jowett wrote: > As for existing implementations, a quick google turned this up: > > http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst69.htm > > which seems to indicate that DB2 has a column display label (via LABEL > ON COLUMN) in addition to column name & column comment. > This is certainly interesting. OK, I'm pretty much convinced, but would like to go over some issues with regard to getTableName and getSchemaName. I believe we agree that for consistency's sake we should return the table alias (if any) in getTableName. How could we do that, and what would the purpose be? At the moment there is no way to even detect that a column's source has been aliased and if you could what would a user do with that alias? The inability to detect an alias means that we cannot report the base table and schema names only in the non-aliased case. Is it worth creating pg specific getBaseXXXName functions for table/schema while the official ones go unused (return "" always) solely for consistency? Kris Juraka
Kris Jurka:
> I believe we agree that for consistency's sake we should return the table
> alias (if any) in getTableName. How could we do that, and what would the
> purpose be?
CREATE TABLE person (
id int4 primary key,
parent_id int4 references person (id),
name varchar(50)
)
SELECT * FROM person inner join person parent on person.parent_id =
parent.id
rs.getTableName() distinguishes person.name from parent.name.
In my experience, drivers correctly implement getTableName()
*far* less frequently. I've never been able to rely on it.
Of the drivers I have handy, only DB2J and MySQL get it right.
Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return
the underlying base table name. JTDS, Oracle, and pg74 all
return empty strings!
Mike
On Tue, 3 Aug 2004, Mike Martin wrote: > SELECT * FROM person inner join person parent on person.parent_id = > parent.id > > rs.getTableName() distinguishes person.name from parent.name. > > In my experience, drivers correctly implement getTableName() > *far* less frequently. I've never been able to rely on it. > > Of the drivers I have handy, only DB2J and MySQL get it right. > Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return > the underlying base table name. JTDS, Oracle, and pg74 all > return empty strings! > You seem surprised that it would return an empty string, while that was what I was planning on making it do. Why should it return the base table name when it can't determine if it has been aliased? Isn't this returning incorrect information? Kris Jurka
Kris wrote: > On Tue, 3 Aug 2004, Mike Martin wrote: > > > SELECT * FROM person inner join person parent on person.parent_id = > > parent.id > > > > rs.getTableName() distinguishes person.name from parent.name. > > > > In my experience, drivers correctly implement getTableName() > > *far* less frequently. I've never been able to rely on it. > > > > Of the drivers I have handy, only DB2J and MySQL get it right. > > Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return > > the underlying base table name. JTDS, Oracle, and pg74 all > > return empty strings! > > You seem surprised that it would return an empty string, while that was > what I was planning on making it do. Why should it return the base table > name when it can't determine if it has been aliased? Isn't this returning > incorrect information? You're right, that exclamation point is undeserved. Based on the description of getTableName() (and getSchemaName() and getCatalogName()) I would think a driver should either return the correct information or "" if not available. Mike
Fixed, in this commit message. ---------------------------- revision 1.1 date: 2004/08/05 03:50:19; author: jurka; state: Exp; Revert ResultSetMetaData methods getColumnName, getTableName and getSchemaName to previous implementation that returned the column's alias if available and "" for the table and schema. Move the new functionality to access the underlying sources of the query to a new PGResultSetMetaData interface which offers getBaseXXXName. Per complaints of Mike Martin.