You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2022/07/04 10:01:55 UTC
ResultSetMetaData question
I have a query SELECT COUNT(*) AS X FROM ...
If I use ResultMetaData.getColumnLabel(), I get "X".
If I use ResultMetaData.getColumnName(), I also get "X".
Here is the output from a test program:
Query: SELECT country,count(*) FROM customer GROUP BY country ORDER BY
country
Name = COUNTRY
Label = COUNTRY
Name = COUNT(*)
Label = COUNT(*)
columns = COUNT(*), COUNTRY
Query: select country, count(*) as customers from customer group by
country order by country asc
Name = COUNTRY
Label = COUNTRY
Name = CUSTOMERS
Label = CUSTOMERS
columns = COUNTRY, CUSTOMERS
I was hoping/expecting that getColumnName() would return "COUNT(*)" in
the second case.
Is there any way to get COUNT(*) short of parsing the query myself?
Thanks,
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
Re: ResultSetMetaData question
Posted by Rick Hillegas <ri...@gmail.com>.
On 7/7/22 2:03 AM, John English wrote:
> On 05/07/2022 17:26, Rick Hillegas wrote:
>> In any event, as you've noticed, getLabelName() returns the same
>> value as getColumnName() in Derby.
>
> So basically I need to write my own SQL parser for a sequence of
> SelectItems between SELeCT and FROM if I want to get the unaliased item?
>
If you want getColumnName() and getLabelName() to return different
values, then you will have to write some custom code.
Re: ResultSetMetaData question
Posted by John English <jo...@gmail.com>.
On 05/07/2022 17:26, Rick Hillegas wrote:
> In any event, as you've noticed, getLabelName() returns the same value
> as getColumnName() in Derby.
So basically I need to write my own SQL parser for a sequence of
SelectItems between SELeCT and FROM if I want to get the unaliased item?
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
Re: ResultSetMetaData question
Posted by Rick Hillegas <ri...@gmail.com>.
On 7/4/22 11:50 AM, John English wrote:
> On 04/07/2022 16:21, Rick Hillegas wrote:
>> I'm afraid I don't understand your results. When I run your
>> experiment, "2" is the name and label of the second column of the
>> query "SELECT country,count(*) FROM customer GROUP BY country ORDER
>> BY country". Does the following give you what you want:
>>
>> SELECT country,count(*) AS "count(*)" FROM customer GROUP BY
>> country ORDER BY country
>
> What I'm after if I have "x AS y" is a way of getting "x". I
> understood (incorrectly as it seems) that getColumnName() would give
> "x" and getColumnLabel() would give "y".
>
> As far as I can tell from experiments, both methods ALWAYS produce
> identical results. So I'm no longer sure why two different methods exist.
>
As I understand the SQL Standard, the columns in query expressions (like
SELECT statements) have "derived column names". The SQL Standard has no
concept of a separate label name. The AS clause simply overrides the
derived column name. The original authors of the ResultSetMetaData
interface clearly imagined some distinction between getColumnName() and
getColumnLabel(), but that distinction is not in the SQL Standard and it
isn't clarified by either the ResultSetMetaData javadoc or the JDBC 4.3
spec. Maybe there is some corresponding distinction in the older ODBC
spec which inspired the first JDBC spec.
In any event, as you've noticed, getLabelName() returns the same value
as getColumnName() in Derby.
Re: ResultSetMetaData question
Posted by John English <jo...@gmail.com>.
On 04/07/2022 16:21, Rick Hillegas wrote:
> I'm afraid I don't understand your results. When I run your experiment,
> "2" is the name and label of the second column of the query "SELECT
> country,count(*) FROM customer GROUP BY country ORDER BY country". Does
> the following give you what you want:
>
> SELECT country,count(*) AS "count(*)" FROM customer GROUP BY country
> ORDER BY country
What I'm after if I have "x AS y" is a way of getting "x". I understood
(incorrectly as it seems) that getColumnName() would give "x" and
getColumnLabel() would give "y".
As far as I can tell from experiments, both methods ALWAYS produce
identical results. So I'm no longer sure why two different methods exist.
--
John English
--
This email has been checked for viruses by AVG.
https://www.avg.com
Re: ResultSetMetaData question
Posted by Rick Hillegas <ri...@gmail.com>.
I'm afraid I don't understand your results. When I run your experiment,
"2" is the name and label of the second column of the query "SELECT
country,count(*) FROM customer GROUP BY country ORDER BY country". Does
the following give you what you want:
SELECT country,count(*) AS "count(*)" FROM customer GROUP BY country
ORDER BY country
On 7/4/22 6:01 AM, John English wrote:
> I have a query SELECT COUNT(*) AS X FROM ...
>
> If I use ResultMetaData.getColumnLabel(), I get "X".
> If I use ResultMetaData.getColumnName(), I also get "X".
>
> Here is the output from a test program:
>
> Query: SELECT country,count(*) FROM customer GROUP BY country ORDER BY
> country
> Name = COUNTRY
> Label = COUNTRY
> Name = COUNT(*)
> Label = COUNT(*)
> columns = COUNT(*), COUNTRY
>
> Query: select country, count(*) as customers from customer group by
> country order by country asc
> Name = COUNTRY
> Label = COUNTRY
> Name = CUSTOMERS
> Label = CUSTOMERS
> columns = COUNTRY, CUSTOMERS
>
> I was hoping/expecting that getColumnName() would return "COUNT(*)" in
> the second case.
>
> Is there any way to get COUNT(*) short of parsing the query myself?
>
> Thanks,