Обсуждение: Missing functionality in ResultSetMetaData ?
Hi, I'm trying to extract the source tables from a result set for a arbitrary select query. For this I use the ResultSetMetaData.getTableName(int columnIndex) method. However with the postgresql-8.0-311.jdbc3.jar driver against a PostgreSQL 8.0.1, I never get any table names, regardless of whether the query is a straight SELECT * FROM simpletable or a complicated query using multiple joins. Are there plans to incorporate this functionality, or perhaps a driver version that already supports this ? regards, Tom
Check the archives, there's no way for us to get the tables. The problem is that the server doesn't tell us what the table is that the column came from consider select a as newcol from foo; we don't get the column a back from the server. Dave On 9-Aug-05, at 4:38 PM, Thomas Dudziak wrote: > Hi, > > I'm trying to extract the source tables from a result set for a > arbitrary select query. For this I use the > > ResultSetMetaData.getTableName(int columnIndex) > > method. However with the postgresql-8.0-311.jdbc3.jar driver against a > PostgreSQL 8.0.1, I never get any table names, regardless of whether > the query is a straight SELECT * FROM simpletable or a complicated > query using multiple joins. > Are there plans to incorporate this functionality, or perhaps a driver > version that already supports this ? > > regards, > Tom > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly > >
Dave Cramer <pg@fastcrypt.com> writes:
> The problem is that the server doesn't tell us what the table is that
> the column came from
> consider select a as newcol from foo;
> we don't get the column a back from the server.
Uh, that is there in protocol 3.0 ...
regards, tom lane
On 8/9/05, Kris Jurka <books@ejurka.com> wrote: > > I'm trying to extract the source tables from a result set for a > > arbitrary select query. For this I use the > > > > ResultSetMetaData.getTableName(int columnIndex) > > http://archives.postgresql.org/pgsql-jdbc/2005-01/msg00220.php > > Please see this message and the discussion referenced at the bottom of it > for why we do not support getTableName, but instead provide a pg specific > getBaseTableName method. Ok, thanks, I see. However, from what I read in the thread the question in the mail that you referenced, as to why PGResultSetMetadata does not extend ResultSetMetadata, and as a result the getTableName method is mapped onto getBaseTableName, is still valid IMO. Especially if the information seems to be readily available. Would you consider this change for an upcoming release ? regards, Tom
On Tue, 9 Aug 2005, Thomas Dudziak wrote: > I'm trying to extract the source tables from a result set for a > arbitrary select query. For this I use the > > ResultSetMetaData.getTableName(int columnIndex) http://archives.postgresql.org/pgsql-jdbc/2005-01/msg00220.php Please see this message and the discussion referenced at the bottom of it for why we do not support getTableName, but instead provide a pg specific getBaseTableName method. Kris Jurka
On Tue, 9 Aug 2005, Thomas Dudziak wrote: > Ok, thanks, I see. However, from what I read in the thread the > question in the mail that you referenced, as to why > PGResultSetMetadata does not extend ResultSetMetadata, and as a result > the getTableName method is mapped onto getBaseTableName, is still > valid IMO. Especially if the information seems to be readily > available. Would you consider this change for an upcoming release ? > No the question about extension means that given a PGResultSetMetaData object you cannot call the standard ResultSetMetaData methods. This is solely a convenience issue. If you wanted to call both getColumnName and getBaseColumnName you'd need to maintain two references (one to the ResultSetMetaData object and another to the PGResultSetMetaData object). If PGResultSetMetaData extended ResultSetMetaData you'd only need a reference to the pg version. We have a separate pg specific method because we believe that function is useful, but does not match the spec for getTableName. Have you read the entire thread? http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008 Kris Jurka
On 8/10/05, Kris Jurka <books@ejurka.com> wrote: > > > On Tue, 9 Aug 2005, Thomas Dudziak wrote: > > > Ok, thanks, I see. However, from what I read in the thread the > > question in the mail that you referenced, as to why > > PGResultSetMetadata does not extend ResultSetMetadata, and as a result > > the getTableName method is mapped onto getBaseTableName, is still > > valid IMO. Especially if the information seems to be readily > > available. Would you consider this change for an upcoming release ? > > > > No the question about extension means that given a PGResultSetMetaData > object you cannot call the standard ResultSetMetaData methods. This is > solely a convenience issue. If you wanted to call both getColumnName and > getBaseColumnName you'd need to maintain two references (one to the > ResultSetMetaData object and another to the PGResultSetMetaData object). > If PGResultSetMetaData extended ResultSetMetaData you'd only need a > reference to the pg version. We have a separate pg specific method > because we believe that function is useful, but does not match the spec > for getTableName. Have you read the entire thread? > > http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008 Nope, I only browsed over the one you posted earlier (regarding PGResultSetMetaData). I take it that the issue with getColumnName/getBaseColumnName is the same as with getTableName/getBaseTableName ? Let me state what I understand of this issue: it is only possible to return the base (real ?) table name, not the alias. And Sun didn't define this properly in the JDBC spec (at least not in the API and the 3.0 spec, but this might be some useful input for the 4.0 spec ?). However if this is the case, then I don't think your argument given in http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00021.php is valid, because from what I understand of the ResultSetMetaData, the getTableName method returns the name of the column's table, ie. IMO the name of the table where the column is defined in. So in your example this cannot be 'f' but must be 'footable'. I mean, what would I do with 'f' anyway ? IMO it should be possible to build structurally valid select statements from the ResultSetMetaData information which is not the case when returning the aliases. I think this is actually supported by the few places in the JDBC 3 spec that deal with ResultSetMetaData, eg. 13.2.3: "The method PreparedStatement.getMetaData retrieves a ResultSetMetaData object containing a description of the columns that will be returned by a prepared statement when is it executed. The ResultSetMetaData object contains a record for each column being returned. Methods in the ResultSetMetaData interface provide information about the number of columns being returned and the characteristics of each column." Here they specifically speak about the column' characteristics (ie. definition) not the query. IMO the whole intention of the meta data is to be able to determine the database structure by 'reflection' (which incidentially is what I'm doing in my app). As I said, a good reason to ask in the JSR expert group for clarification in the next JDBC standard (if not already done, I havn't checked the current draft). regards, Tom
Thomas Dudziak wrote: > Ok, thanks, I see. However, from what I read in the thread the > question in the mail that you referenced, as to why > PGResultSetMetadata does not extend ResultSetMetadata, and as a result > the getTableName method is mapped onto getBaseTableName, is still > valid IMO. Especially if the information seems to be readily > available. Would you consider this change for an upcoming release ? Err, all I was suggesting in that mail was that PGResultSetMetadata extend ResultSetMetaData so you can do this: PGResultSetMetadata md = (PGResultSetMetadata)rs.getMetaData(); String baseName = md.getBaseTableName(...); String aliasName = md.getTableName(...); Currently you can't do that because PGResultSetMetadata has no getTableName() method; you have to sometimes deal with the standard interface and sometimes with the extension interface, depending on the method you call. It would not change the behaviour of getBaseTableName or getTableName() at all; it's merely a syntactic convenience. I'm not sure what you thought I meant, but it was obviously something different :) -O
Tom Lane wrote: > Dave Cramer <pg@fastcrypt.com> writes: > >>The problem is that the server doesn't tell us what the table is that >>the column came from >>consider select a as newcol from foo; >>we don't get the column a back from the server. > > > Uh, that is there in protocol 3.0 ... Dave has it backwards; the issue is that for: SELECT * FROM foo f the protocol tells us the result columns come from table 'foo'; but JDBC's getTableName() should return the alias 'f', which we don't generally know. -O
On Wed, 10 Aug 2005, Thomas Dudziak wrote: > Nope, I only browsed over the one you posted earlier (regarding > PGResultSetMetaData). I take it that the issue with > getColumnName/getBaseColumnName is the same as with > getTableName/getBaseTableName ? Let me state what I understand of this > issue: it is only possible to return the base (real ?) table name, not > the alias. The issue actually started with column names. The pg server currently can return aliases for columns, but not for tables. The original complaint was that RSMD.getColumnName better match up with something if we call ResultSet.findColumn with it. After much discussion it was agreed that getColumnName means the name of the query's result column not the base table's column. Now, to be consistent getTableName must mean the query's table as well. > However if this is the case, then I don't think your argument given in > http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00021.php is > valid, because from what I understand of the ResultSetMetaData, the > getTableName method returns the name of the column's table, ie. IMO > the name of the table where the column is defined in. > So in your example this cannot be 'f' but must be 'footable'. I mean, > what would I do with 'f' anyway ? This isn't completely useless, consider a self join: "SELECT a.c, b.c FROM tab a, tab b". getBaseTableName cannot distinguish this, but if getTableName did return the alias it could. This isn't a real strong use case though... > IMO it should be possible to build > structurally valid select statements from the ResultSetMetaData > information which is not the case when returning the aliases. > I think this is actually supported by the few places in the JDBC 3 > spec that deal with ResultSetMetaData, eg. 13.2.3: > > "The method PreparedStatement.getMetaData retrieves a > ResultSetMetaData object containing a description of the columns that will be > returned by a prepared statement when is it executed. The ResultSetMetaData > object contains a record for each column being returned. Methods in the > ResultSetMetaData interface provide information about the number of columns > being returned and the characteristics of each column." > > Here they specifically speak about the column' characteristics (ie. > definition) not the query. That's not entirely clear. That's how you read it, but it is just as easily read as "the query result's columns". The spec is awfully vague in this area. Kris Jurka
Thomas Dudziak wrote: > However if this is the case, then I don't think your argument given in > http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00021.php is > valid, because from what I understand of the ResultSetMetaData, the > getTableName method returns the name of the column's table, ie. IMO > the name of the table where the column is defined in. [...] We disagree :) In addition to what Kris posted elsewhere in the thread, there is the compatibility angle: why should we be gratuitously incompatible with other JDBC drivers? > So in your example this cannot be 'f' but must be 'footable'. I mean, > what would I do with 'f' anyway ? Use it distinguish that particular "footable" from another "footable" in a self-join. -O
On 8/10/05, Kris Jurka <books@ejurka.com> wrote: > The issue actually started with column names. The pg server currently can > return aliases for columns, but not for tables. The original complaint > was that RSMD.getColumnName better match up with something if we call > ResultSet.findColumn with it. After much discussion it was agreed that > getColumnName means the name of the query's result column not the base > table's column. Now, to be consistent getTableName must mean the query's > table as well. Ah, ok, I understand this. > This isn't completely useless, consider a self join: "SELECT a.c, > b.c FROM tab a, tab b". getBaseTableName cannot distinguish this, but if > getTableName did return the alias it could. This isn't a real strong > use case though... > > "The method PreparedStatement.getMetaData retrieves a > > ResultSetMetaData object containing a description of the columns that will be > > returned by a prepared statement when is it executed. The ResultSetMetaData > > object contains a record for each column being returned. Methods in the > > ResultSetMetaData interface provide information about the number of columns > > being returned and the characteristics of each column." > > > > Here they specifically speak about the column' characteristics (ie. > > definition) not the query. > > That's not entirely clear. That's how you read it, but it is just as > easily read as "the query result's columns". The spec is awfully vague in > this area. Yep, that's the problem. Interestingly, the Javadoc for RSMD.getCatalogName says "Gets the designated column's table's catalog name" where the Javadoc for getTableName says "Gets the designated column's table name." So, taken together this could be interpreted as meaning the structural info (real table). But as you said, this is guess work. Perhaps you could write a comment to the JDBC4 JSR group and ask for clarification of the semantics of the RSMD in the new spec ? regards, Tom
Thomas Dudziak wrote: > Perhaps you could write a comment to the JDBC4 JSR group and ask for > clarification of the semantics of the RSMD in the new spec ? BTW, I have had nothing but silence from anything I've sent to Sun's JDBC group, I've given up on it now. -O
On Wed, 10 Aug 2005, Oliver Jowett wrote: > Thomas Dudziak wrote: > > > Perhaps you could write a comment to the JDBC4 JSR group and ask for > > clarification of the semantics of the RSMD in the new spec ? > > BTW, I have had nothing but silence from anything I've sent to Sun's > JDBC group, I've given up on it now. > Perhaps we can make some use of Dave's Sun contacts from his CTS work? Even if not it wouldn't be a bad idea to get all of the questions we've got for Sun into one place. What else do we have other than the getColumnDisplaySize() < 0 issue? Kris Jurka
On 9-Aug-05, at 7:35 PM, Kris Jurka wrote: > > > On Wed, 10 Aug 2005, Oliver Jowett wrote: > > >> Thomas Dudziak wrote: >> >> >>> Perhaps you could write a comment to the JDBC4 JSR group and ask for >>> clarification of the semantics of the RSMD in the new spec ? >>> >> >> BTW, I have had nothing but silence from anything I've sent to Sun's >> JDBC group, I've given up on it now. >> >> > > Perhaps we can make some use of Dave's Sun contacts from his CTS work? > Even if not it wouldn't be a bad idea to get all of the questions > we've > got for Sun into one place. What else do we have other than the > getColumnDisplaySize() < 0 issue? > I'd be more than happy to, can we get all the questions in one place so that we can ask them all at once. > Kris Jurka > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > >
On Wed, Aug 10, 2005 at 08:46:25AM -0400, Dave Cramer wrote: > > > >Perhaps we can make some use of Dave's Sun contacts from his CTS work? > >Even if not it wouldn't be a bad idea to get all of the questions > >we've > >got for Sun into one place. What else do we have other than the > >getColumnDisplaySize() < 0 issue? > > > I'd be more than happy to, can we get all the questions in one place > so that we can ask them all at once. "Blob.setBytes(...)" methods are under-specified IMHO. But maybe it was on purpose, to let the door open for each DBMS to have its own and different interpretation adapted to its specific constraints. The return value (number of bytes successfully written) seems to be an indication of that; it looks like: "in corner cases, weird things can happen, so you'd better check everything you wanted was written". - When we don't write until the end of the Blob, do we keep the tail as is? Yes looks like the most sensible answer, but a comment about this would not harm. - On the other hand, what happens if the pre-existing Blob is too small, is it extended? - if it's extended, how are initialized bytes < pos ?
Dave Cramer wrote: > > On 9-Aug-05, at 7:35 PM, Kris Jurka wrote: > >> On Wed, 10 Aug 2005, Oliver Jowett wrote: >> >>> BTW, I have had nothing but silence from anything I've sent to Sun's >>> JDBC group, I've given up on it now. >>> >>> >> >> Perhaps we can make some use of Dave's Sun contacts from his CTS work? >> Even if not it wouldn't be a bad idea to get all of the questions we've >> got for Sun into one place. What else do we have other than the >> getColumnDisplaySize() < 0 issue? >> > I'd be more than happy to, can we get all the questions in one place so > that we can > ask them all at once. Oops, forgot about this email. Two things come to mind: - is the driver required to do an implicit cast from VARCHAR to random other types (e.g. setString() used against a smallint column); the spec says nothing about this, and it's inconvenient for us to support, but many people seem to expect it. - is PreparedStatement.setObject(i,null) allowed? It's the only way to sneak an untyped parameter in and seems like an oversight. If it's allowed, what's the equivalent setNull() call that it corresponds to? -O