Thread: getColumnLabel return value
I was surprised to see that the getColumnLabel method in the ResultSetMetaData object returns the name of the column instead of the comment (when available). The documentation says that the method: "Gets the designated column's suggested title for use in printouts and displays." Any thoughts on that? I'd do it if you guys agree. Regards Andras Gerlits
Andras, I guess it boils down to what you thing the comment is. I think the common understanding is the the comment is something like "this column holds the name of the user" as opposed to the display name. I gather that you disagree; does anyone else have any comments on this? Dave On Fri, 2002-12-13 at 07:09, Gerlits András wrote: > I was surprised to see that the getColumnLabel method in the > ResultSetMetaData object returns the name of the column instead of the > comment (when available). > > The documentation says that the method: > "Gets the designated column's suggested title for use in printouts and > displays." > > Any thoughts on that? > > I'd do it if you guys agree. > > Regards > Andras Gerlits > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Dave Cramer <Dave@micro-automation.net>
Dave Cramer <Dave@micro-automation.net> writes:
>> The documentation says that the method:
>> "Gets the designated column's suggested title for use in printouts and
>> displays."
I should think that a column comment would usually be too long to serve
usefully as a heading for the column in printouts, which is clearly the
intended use of this method ...
regards, tom lane
Why include it in the JDBC then? If that was the original idea of the people specifying JDBC standards, why not only use getColumnName? My impression was that ColumnLabel is a description, while ColumnName is the (usually abbreviated, and sometimes uncomprehendable) name of the column :). Regards. Andras On Fri, 13 Dec 2002 09:52:34 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote : > Dave Cramer <Dave@micro-automation.net> writes: > >> The documentation says that the method: > >> "Gets the designated column's suggested title for use in printouts and > >> displays." > > I should think that a column comment would usually be too long to serve > usefully as a heading for the column in printouts, which is clearly the > intended use of this method ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > On Fri, 13 Dec 2002 09:52:34 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote : > Dave Cramer <Dave@micro-automation.net> writes: > >> The documentation says that the method: > >> "Gets the designated column's suggested title for use in printouts and > >> displays." > > I should think that a column comment would usually be too long to serve > usefully as a heading for the column in printouts, which is clearly the > intended use of this method ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > > >
I'm using the JDBC over a modem link and I regularly experience SocketExceptions as a result of the flakey nature of the connection. The driver can't be held responsible for unreliable sub-levels but the Exception handling could be more clear. Currently the driver prints out a stack trace of the original IOException with unspecific descriptions and then throws a PSQLException. It's not obvious where the stack trace comes from. I know you're thinking "he's dumb enough to use a JDBC connection over a modem link, of course this would confuse him". Still, the behavior could be more informative. I've attached a patch that might improve that. You can probably conceive a better phrasing. Also, is there a standard way to decipher fatal and non-fatal errors thrown by the driver? Would java.sql.Connection.isClosed() work? Thanks, Mike Adler
Attachment
Actually I would say that we currently do return the label. Since we are dealing with result sets here, we are dealing with queries and not necessarily tables. Consider the following query: select a.FOO as bar1, b.FOO as bar2 from BAZ1 a, BAZ2 b where a.X = b.X will return the following information: getColumnName(1) -> bar1 getColumnLabel(1) -> bar1 getColumnName(2) -> bar2 getColumnLabel(2) -> bar2 This is a limitation of the information the driver gets back from the server, it only returns the 'label' which the driver then uses for both columnname and label. Which usually is the columnname (unless you alias it as above). For fun consider the following query: select 'a message' as message, FOO as bar from BAZ getColumnName(1) -> ??? what should this be since there is no column getColumnLabel(1) -> message getColumnName(2) -> should be foo if we were given that information from the server, but currently is bar getColumnLabel(2) -> bar You can also have lots of fun with getTableName() as well when the 'table' may be a subquery in the from clause. It isn't clear what should be returned in many cases even if the server did support more. thanks, --Barry Gerlits AndrXs wrote: > I was surprised to see that the getColumnLabel method in the > ResultSetMetaData object returns the name of the column instead of the > comment (when available). > > The documentation says that the method: > "Gets the designated column's suggested title for use in printouts and > displays." > > Any thoughts on that? > > I'd do it if you guys agree. > > Regards > Andras Gerlits > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Mon, Dec 16, 2002 at 11:00:24AM -0800, Barry Lind wrote:
> You can also have lots of fun with getTableName() as well when the
> 'table' may be a subquery in the from clause. It isn't clear what
> should be returned in many cases even if the server did support more.
But at this point the driver doesn't return the table name even in cases
where it's clear what the tablename should be.
For instance if you do
select a from foo;
you get an empty string instead of "foo" as the table name. How hard
would it be to implement this in the driver? Is this something that the
server has available that the driver could grab?
This is an important feature for tool writers. For instance, I want to
display a URL field as a <href> in a report. I keep meta information
about this field, but I can't use it since I don't know which field it
is when the user creates a SQL report because I don't know which table
it belongs to.
Getting getTableName() to work would solve this problem.
Regards,
Dror
>
> thanks,
> --Barry
>
>
> Gerlits AndrXs wrote:
> >I was surprised to see that the getColumnLabel method in the
> >ResultSetMetaData object returns the name of the column instead of the
> >comment (when available).
> >
> >The documentation says that the method:
> >"Gets the designated column's suggested title for use in printouts and
> >displays."
> >
> >Any thoughts on that?
> >
> >I'd do it if you guys agree.
> >
> >Regards
> >Andras Gerlits
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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
--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com
Unfortunately we have no idea which table belongs with which column. We would need the server to return fully qualified names such as table.col Dave On Mon, 2002-12-16 at 14:18, Dror Matalon wrote: > On Mon, Dec 16, 2002 at 11:00:24AM -0800, Barry Lind wrote: > > You can also have lots of fun with getTableName() as well when the > > 'table' may be a subquery in the from clause. It isn't clear what > > should be returned in many cases even if the server did support more. > > > But at this point the driver doesn't return the table name even in cases > where it's clear what the tablename should be. > > > For instance if you do > > select a from foo; > > you get an empty string instead of "foo" as the table name. How hard > would it be to implement this in the driver? Is this something that the > server has available that the driver could grab? > > This is an important feature for tool writers. For instance, I want to > display a URL field as a <href> in a report. I keep meta information > about this field, but I can't use it since I don't know which field it > is when the user creates a SQL report because I don't know which table > it belongs to. > > Getting getTableName() to work would solve this problem. > > Regards, > > Dror > > > > > > thanks, > > --Barry > > > > > > Gerlits AndrXs wrote: > > >I was surprised to see that the getColumnLabel method in the > > >ResultSetMetaData object returns the name of the column instead of the > > >comment (when available). > > > > > >The documentation says that the method: > > >"Gets the designated column's suggested title for use in printouts and > > >displays." > > > > > >Any thoughts on that? > > > > > >I'd do it if you guys agree. > > > > > >Regards > > >Andras Gerlits > > > > > >---------------------------(end of broadcast)--------------------------- > > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: 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 <Dave@micro-automation.net>
On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote: > Unfortunately we have no idea which table belongs with which column. > > We would need the server to return fully qualified names such as > table.col Any idea how hard would it be to get the server to provide this functionality? By the way, mysql has had this functionality for at least a couple of years. Dror > > Dave > On Mon, 2002-12-16 at 14:18, Dror Matalon wrote: > > On Mon, Dec 16, 2002 at 11:00:24AM -0800, Barry Lind wrote: > > > You can also have lots of fun with getTableName() as well when the > > > 'table' may be a subquery in the from clause. It isn't clear what > > > should be returned in many cases even if the server did support more. > > > > > > But at this point the driver doesn't return the table name even in cases > > where it's clear what the tablename should be. > > > > > > For instance if you do > > > > select a from foo; > > > > you get an empty string instead of "foo" as the table name. How hard > > would it be to implement this in the driver? Is this something that the > > server has available that the driver could grab? > > > > This is an important feature for tool writers. For instance, I want to > > display a URL field as a <href> in a report. I keep meta information > > about this field, but I can't use it since I don't know which field it > > is when the user creates a SQL report because I don't know which table > > it belongs to. > > > > Getting getTableName() to work would solve this problem. > > > > Regards, > > > > Dror > > > > > > > > > > thanks, > > > --Barry > > > > > > > > > Gerlits AndrXs wrote: > > > >I was surprised to see that the getColumnLabel method in the > > > >ResultSetMetaData object returns the name of the column instead of the > > > >comment (when available). > > > > > > > >The documentation says that the method: > > > >"Gets the designated column's suggested title for use in printouts and > > > >displays." > > > > > > > >Any thoughts on that? > > > > > > > >I'd do it if you guys agree. > > > > > > > >Regards > > > >Andras Gerlits > > > > > > > >---------------------------(end of broadcast)--------------------------- > > > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: 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 <Dave@micro-automation.net> > -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com
Dror Matalon wrote: > On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote: > >>Unfortunately we have no idea which table belongs with which column. >> >>We would need the server to return fully qualified names such as >>table.col > The problem is that a table name is not defined when joins are involved, if there are subqueries without an alias clause, for synthetic columns (results of expressions) etc. BTW, the definition of getTableName() says "" is returned in these cases. I wonder how useful would be a feature that works "some times". The Sun JDBC book (White et al.) says "Because this feature is not widely supported, the return value from many DBMSs will be an empty string. I am not sure if I understand why you need this. If you are generating this query graphically with some tool, you would know for sure where each column come from as the user will be selecting things from listboxes with table qualified names (or for several listboxes for a specific table) and your tool will be adding them to the select-list in some fixed order. -- Fernando Nasser Red Hat - Toronto E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
On Mon, Dec 16, 2002 at 04:02:32PM -0500, Fernando Nasser wrote:
> Dror Matalon wrote:
> >On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote:
> >
> >>Unfortunately we have no idea which table belongs with which column.
> >>
> >>We would need the server to return fully qualified names such as
> >>table.col
> >
>
> The problem is that a table name is not defined when joins are
> involved, if there are subqueries without an alias clause, for synthetic
> columns (results of expressions) etc.
Table name should be defined in a join:
for instance:
table foo
a int,
b int
table bar
c int,
d int
select a, b, c from foo, bar where foo.b = bar.d;
I don't see any reason why the server shouldn't know that a and b came
from foo and c came from bar. I agree though that there will be cases
where the table is unknown.
>
> BTW, the definition of getTableName() says "" is returned in these cases.
> I wonder how useful would be a feature that works "some times".
>
> The Sun JDBC book (White et al.) says "Because this feature is not widely
> supported, the return value from many DBMSs will be an empty string.
>
> I am not sure if I understand why you need this.
> If you are generating this query graphically with some tool, you would know
> for sure where each column come from as the user will be selecting things
> from listboxes with table qualified names (or for several listboxes for a
> specific table) and your tool will be adding them to the select-list in
> some fixed order.
>
We provid JSP tags where the user can create a HTML report on the fly.
Heres the doc that describes it:
The SelectXsl tag
Create a report using an SQL query. The look and feel of the report can
be changed using an XSL stylesheet. If you don't define one, the report
will use a default stylesheet. You can also control the start row and
the number of rows per page in your report. The default is to start on
row 0 and limit to 500 rows per page.
0.1 query attribute
SQL Query that defines the report. Example: "Select * from customer" .
0.2 file attribute
The name of the XSL stylesheet used to transform this report from XML to
HTML. If you don't define a XSL stylesheet a default one is going to be
applied.
0.3 rowsPerPage attribute
Limits the number of rows per page and will display "next" and
"previous" buttons as needed. For instance, if you specified 100, and
your report only has 25 rows, it will not display the arrows. If you
specify 25 and your report has 40 rows, it will display "next" in the
first page and previous in the second page.
0.4 startRow attribute
Start displaying the report at this row. Note that the row order is
different depending on the query. So the first row of "select * from
customer order by customer_id" is different than the first row in
"select * from customer order by first_name" .
If the user has a query in a report:
select name, address, homepage from company and I want to display it in a web
page so that it looks something like:
<a href="<%=homepage%>"<%=company%></a>
So it's a link.
I have no way to tell that homepage is a field of type "url". We keep
meta information that we can only track if we know which table the field
belongs to.
Regards,
Dror
>
> --
> Fernando Nasser
> Red Hat - Toronto E-Mail: fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario M4P 2C9
>
--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com
Dror Matalon wrote: > > select a, b, c from foo, bar where foo.b = bar.d; > > I don't see any reason why the server shouldn't know that a and b came > from foo and c came from bar. I agree though that there will be cases > where the table is unknown. > The key here being that only the server can know this since the client (jdbc in this case) can't get the information from simply parsing the query string. So this is a server enhancement request for the protocol between the client and server. There has been talk of changing the protocol in 7.4. So I would suggest that you take this request over to the pgsql-hackers mail list for discussion there. If the work were done in the server to provide this information it will be easy to incorporate it into the jdbc driver. thanks, --Barry
I am a tool writer and need to figure out how to get the name of the table the resultset is for. I have written a set of tools that eventually pass a class the resultset for processing, and have absolutely no idea what the table name for each of the rows are in the resultset. Suppose i execute a query that has several tables named in the FROM clause, when i get the result back i have no idea what column comes from which row. Is there a way to figure this out? Thanks Ray -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dror Matalon Sent: Monday, December 16, 2002 11:57 AM To: PostgreSQL JDBC Subject: Re: [JDBC] getTableName On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote: > Unfortunately we have no idea which table belongs with which column. > > We would need the server to return fully qualified names such as > table.col Any idea how hard would it be to get the server to provide this functionality? By the way, mysql has had this functionality for at least a couple of years. Dror > > Dave > On Mon, 2002-12-16 at 14:18, Dror Matalon wrote: > > On Mon, Dec 16, 2002 at 11:00:24AM -0800, Barry Lind wrote: > > > You can also have lots of fun with getTableName() as well when the > > > 'table' may be a subquery in the from clause. It isn't clear what > > > should be returned in many cases even if the server did support more. > > > > > > But at this point the driver doesn't return the table name even in cases > > where it's clear what the tablename should be. > > > > > > For instance if you do > > > > select a from foo; > > > > you get an empty string instead of "foo" as the table name. How hard > > would it be to implement this in the driver? Is this something that the > > server has available that the driver could grab? > > > > This is an important feature for tool writers. For instance, I want to > > display a URL field as a <href> in a report. I keep meta information > > about this field, but I can't use it since I don't know which field it > > is when the user creates a SQL report because I don't know which table > > it belongs to. > > > > Getting getTableName() to work would solve this problem. > > > > Regards, > > > > Dror > > > > > > > > > > thanks, > > > --Barry > > > > > > > > > Gerlits AndrXs wrote: > > > >I was surprised to see that the getColumnLabel method in the > > > >ResultSetMetaData object returns the name of the column instead of the > > > >comment (when available). > > > > > > > >The documentation says that the method: > > > >"Gets the designated column's suggested title for use in printouts and > > > >displays." > > > > > > > >Any thoughts on that? > > > > > > > >I'd do it if you guys agree. > > > > > > > >Regards > > > >Andras Gerlits > > > > > > > >---------------------------(end of broadcast)--------------------------- > > > >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: 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 <Dave@micro-automation.net> > -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com ---------------------------(end of broadcast)--------------------------- TIP 3: 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
Ray, At the moment the server doesn't tell us which table a column belongs to. So the short answer is no. The longer answer is that we need support from the backend to do this. For instance the following select. select a as b, c as d, e as f from foo as c1. The backend is the only one that knows which table which came from. Dave On Sun, 2003-01-26 at 14:21, Ray Madigan wrote: > I am a tool writer and need to figure out how to get the name > of the table the resultset is for. I have written a set of > tools that eventually pass a class the resultset for processing, > and have absolutely no idea what the table name for each of the > rows are in the resultset. > > Suppose i execute a query that has several tables named in the > FROM clause, when i get the result back i have no idea what > column comes from which row. Is there a way to figure this out? > > Thanks > Ray > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dror Matalon > Sent: Monday, December 16, 2002 11:57 AM > To: PostgreSQL JDBC > Subject: Re: [JDBC] getTableName > > > On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote: > > Unfortunately we have no idea which table belongs with which column. > > > > We would need the server to return fully qualified names such as > > table.col > > Any idea how hard would it be to get the server to provide this > functionality? > > By the way, mysql has had this functionality for at least a couple of > years. > > Dror > > > > > Dave > > On Mon, 2002-12-16 at 14:18, Dror Matalon wrote: > > > On Mon, Dec 16, 2002 at 11:00:24AM -0800, Barry Lind wrote: > > > > You can also have lots of fun with getTableName() as well when the > > > > 'table' may be a subquery in the from clause. It isn't clear what > > > > should be returned in many cases even if the server did support more. > > > > > > > > > But at this point the driver doesn't return the table name even in cases > > > where it's clear what the tablename should be. > > > > > > > > > For instance if you do > > > > > > select a from foo; > > > > > > you get an empty string instead of "foo" as the table name. How hard > > > would it be to implement this in the driver? Is this something that the > > > server has available that the driver could grab? > > > > > > This is an important feature for tool writers. For instance, I want to > > > display a URL field as a <href> in a report. I keep meta information > > > about this field, but I can't use it since I don't know which field it > > > is when the user creates a SQL report because I don't know which table > > > it belongs to. > > > > > > Getting getTableName() to work would solve this problem. > > > > > > Regards, > > > > > > Dror > > > > > > > > > > > > > > thanks, > > > > --Barry > > > > > > > > > > > > Gerlits AndrXs wrote: > > > > >I was surprised to see that the getColumnLabel method in the > > > > >ResultSetMetaData object returns the name of the column instead of > the > > > > >comment (when available). > > > > > > > > > >The documentation says that the method: > > > > >"Gets the designated column's suggested title for use in printouts > and > > > > >displays." > > > > > > > > > >Any thoughts on that? > > > > > > > > > >I'd do it if you guys agree. > > > > > > > > > >Regards > > > > >Andras Gerlits > > > > > > > > > >---------------------------(end of > broadcast)--------------------------- > > > > >TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > broadcast)--------------------------- > > > > TIP 3: 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 <Dave@micro-automation.net> > > > > -- > Dror Matalon > Zapatec Inc > 1700 MLK Way > Berkeley, CA 94709 > http://www.zapatec.com > > ---------------------------(end of broadcast)--------------------------- > TIP 3: 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -- Dave Cramer <Dave@micro-automation.net>
Furthermore, you could have a select that calculates values that come from many different tables. A select is a transformation on data in the tables. The columns in a select can have an infinite number (well, lots, anyway) of relationships to the columns in the tables, especially when you consider that the resultset columns could be the output of formulas applied to 0-N different table columns. eg: select a as b, c*a as d, e+c as f, 3.14159 * 42 as j, 666 * b as k from foo as c3, bar as c2, pil as c1 where ... So, even IF that information were available, some columns in your resultset might come from 1, many, or no columns in the database. I'm not sure that what you want to do makes any sense in a real-world sort of way. However, it would make sense to map a resultset to a class IF that class generated the SQL to being with, or IF the developer of that class already knew what columns would be in the resultset. That is what we do. We define a SQL query (which is constructed by the code itself using SQL rules and table+column definitions, and also regression tested against a live DB before we release). That query's output is then associated with a class that knows what to do with it (how to display it, etc.). Hope this helps. Dave Cramer wrote: > select a as b, c as d, e as f from foo as c1. > > The backend is the only one that knows which table which came from. -- Matt Mello