You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cocoon.apache.org by Tuomo Lesonen <tl...@cc.hut.fi> on 2002/08/28 09:07:57 UTC
Counting rows in a resultset
Hi,
How can I count the rows in my resultset? I'm using esql, but
<esql:row-count/> doesn't seem to be implemented yet. SQL COUNT(*) doesn't
work in this case either. Can I do this with XSP?
Thanks,
-Tuomo
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by "Ilya A. Kriveshko" <il...@kaon.com>.
If running two queries is not out of the question for some principal
reasons, I'd suggest the following:
Run a query with LIMIT 1, 1. That query will return results ONLY if
there are two or more rows that match.
Then run your second query both inside the
<esql:no-results></esql:no-results> and inside
<esql:results></esql:results>.
I would suggest using this only if you want to avoid coding Java by
hand. Otherwise, using a List would be recommended.
Here's an ad hoc example. As usual, there's more than one way to
implement anything.
<xsp:logic>
List rows = new ArrayList();
</xsp:logic>
<xsp:execute-query>
<xsp:query>SELECT id, name FROM names WHERE ..snip..</xsp:query>
<xsp:results>
<xsp:row-results>
<xsp:logic>
rows.add(new Object[] {new Long(<esql:get-int name="id"/>),
<esql:get-string name="name"/>});
</xsp:logic>
</xsp:row-results>
</xsp:results>
</xsp:execute-query>
<xsp:logic>
switch(rows.size()) {
case 0: {
// handle empty results
}; break;
case 1: {
// handle single row
Object[] row = (Object[])rows.get(0);
int id = ((Integer)row[0]).intValue();
String name = (String)row[1];
<xsp:content>
<single-row>
<xsp:attribute name="id"><xsp:expr>id</xsp:expr></xsp:attribute>
<xsp:attribute
name="name"><xsp:expr>name</xsp:expr></xsp:attribute>
</single-row>
</xsp:content>
}; break;
default: {
// handle multiple rows
Iterator iter = rows.iterator();
<multi-rows>
while(iter.hasNext()) {
Object[] row = (Object[])iter.next();
int id = ((Integer)row[0]).intValue();
String name = (String)row[1];
<row>
<xsp:attribute
name="id"><xsp:expr>id</xsp:expr></xsp:attribute>
<xsp:attribute
name="name"><xsp:expr>name</xsp:expr></xsp:attribute>
</single>
}
</multi-rows>
};
</xsp:logic>
<displaimer>This code was never compiled or run</displaimer>
--
Ilya
Christian Haul wrote:
>On 28.Aug.2002 -- 10:48 AM, Tuomo Lesonen wrote:
>
>>
>>On Wed, 28 Aug 2002, Christian Haul wrote:
>>
>>>On 28.Aug.2002 -- 10:07 AM, Tuomo Lesonen wrote:
>>>
>>>>Hi,
>>>>
>>>>How can I count the rows in my resultset? I'm using esql, but
>>>><esql:row-count/> doesn't seem to be implemented yet. SQL COUNT(*) doesn't
>>>>work in this case either. Can I do this with XSP?
>>>>
>>>Yes, have a new var declared before the execute-query and ++ it in
>>>your row-results
>>>
>>Thank you Chris, but in this case I need to know the number of rows
>>_before_ I do <esql:row-results>, in order to produce the right data.
>>
>
>Tuomo, there is nothing in the JDBC API that provides this data that
>I'm aware of. There are certainly vendor specific procedures to get
>this information.
>
>However, most of the time the output needs to be limited, in order to
>page through the data. This is supported by esql.
>
> Chris.
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 28.Aug.2002 -- 10:48 AM, Tuomo Lesonen wrote:
>
>
> On Wed, 28 Aug 2002, Christian Haul wrote:
>
> > On 28.Aug.2002 -- 10:07 AM, Tuomo Lesonen wrote:
> > > Hi,
> > >
> > > How can I count the rows in my resultset? I'm using esql, but
> > > <esql:row-count/> doesn't seem to be implemented yet. SQL COUNT(*) doesn't
> > > work in this case either. Can I do this with XSP?
> >
> > Yes, have a new var declared before the execute-query and ++ it in
> > your row-results
> >
>
> Thank you Chris, but in this case I need to know the number of rows
> _before_ I do <esql:row-results>, in order to produce the right data.
Tuomo, there is nothing in the JDBC API that provides this data that
I'm aware of. There are certainly vendor specific procedures to get
this information.
However, most of the time the output needs to be limited, in order to
page through the data. This is supported by esql.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Tuomo Lesonen <tl...@cc.hut.fi>.
On Wed, 28 Aug 2002, Christian Haul wrote:
> On 28.Aug.2002 -- 01:29 PM, Tuomo Lesonen wrote:
> > Time to take out some heavier arms. Since I only need to
> > get "id" and "name"
> > from my table, I'm placing these pairs in a hashtable inside
> > <esql:row-results/>. Then I have a nice hashtable, which I iterate through
> > later on. hashtables "size()" method gives the rowCount.
>
> Tuomo, please tell us more about your XSP. I'm sure, someone will be
> able to suggest a better solution than this. But we need to understand
> the task.
My XSP page needs to output certain XML tags if there are _more than one_
row in the resultset. If there's more than one, a different XML node is
created using the information in the rows. More precisely, If there are more than
one row, I create a (X)HTML select list, and a line of text in case of one
row.
>
> > BUT: How to implement "Enumeration" interface in XSP in order to use the
> > "keys()" method of the Hashtable object? (I'm pretty new to Java, so
> > there's probably a convinient way of doing this..)
>
> You need to put your inenr class before the <page> element.
Is there such a class? I thought "Enumeration" is an interface, and cannot
be included with <xsp:include> ? Little confused here... :-|
-Tuomo
>
> Chris.
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 28.Aug.2002 -- 01:29 PM, Tuomo Lesonen wrote:
> Time to take out some heavier arms. Since I only need to
> get "id" and "name"
> from my table, I'm placing these pairs in a hashtable inside
> <esql:row-results/>. Then I have a nice hashtable, which I iterate through
> later on. hashtables "size()" method gives the rowCount.
Tuomo, please tell us more about your XSP. I'm sure, someone will be
able to suggest a better solution than this. But we need to understand
the task.
> BUT: How to implement "Enumeration" interface in XSP in order to use the
> "keys()" method of the Hashtable object? (I'm pretty new to Java, so
> there's probably a convinient way of doing this..)
You need to put your inenr class before the <page> element.
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Andre Thenot <at...@thenot.net>.
On Wednesday, August 28, 2002, at 06:06 , Tuomo Lesonen wrote:
> This doesn't work: SELECT COUNT(*), id, name FROM names WHERE id = 1;
>
> I'm using MS SQL2000. The query above is probably not well
> formed SQL, or
> SQL2000 doesn't know what to do with it... :(
The count(*) syntax works in MySQL, but the documentation for
the latter indicates that it's not standard SQL. (It's sooo
convenient, though...:-))
A.
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
RE: Counting rows in a resultset
Posted by Koen Pellegrims <ko...@pandora.be>.
Damn, should proof-read...
Should have been SELECT COUNT(id) FROM names WHERE id=1;
... of course...
> -----Oorspronkelijk bericht-----
> Van: Koen Pellegrims [mailto:koen.pellegrims@pandora.be]
> Verzonden: woensdag 28 augustus 2002 12:35
> Aan: cocoon-users@xml.apache.org
> Onderwerp: RE: Counting rows in a resultset
>
>
> Try SELECT(id) FROM names WHERE id = 1;
>
> K.
>
> > -----Oorspronkelijk bericht-----
> > Van: Tuomo Lesonen [mailto:tlesonen@cc.hut.fi]
> > Verzonden: woensdag 28 augustus 2002 12:07
> > Aan: cocoon-users@xml.apache.org; haul@dvs1.informatik.tu-darmstadt.de
> > Onderwerp: Re: Counting rows in a resultset
> >
> >
> >
> >
> > On Wed, 28 Aug 2002, Christian Haul wrote:
> >
> > > On 28.Aug.2002 -- 10:54 AM, Leszek Gawron wrote:
> > > > try to do select count(*) as row_count from table;
> > > >
> > > > and then in <esql:row-results> do <esql:get-long
> column="row_count"/>
> > > >
> > > > I'm not 100% sure but this highly probable to work
> > >
> > > Yes, if it is an option to run the query twice this should work. The
> > > "as row_count" is not necessary if you access the count(*) by column
> > > number i.e. <esql:get-long column="1"/>
> >
> > I did try these both. But COUNT(*) is not working, because I have to
> > specify more columns in the query. My query is something like
> this: SELECT
> > id, name FROM names WHERE id = 1;
> >
> > This doesn't work: SELECT COUNT(*), id, name FROM names WHERE id = 1;
> >
> > I'm using MS SQL2000. The query above is probably not well
> formed SQL, or
> > SQL2000 doesn't know what to do with it... :(
> >
> > -Tuomo
> >
> >
> > >
> > > Chris.
> > > --
> > > C h r i s t i a n H a u l
> > > haul@informatik.tu-darmstadt.de
> > > fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
> > >
> > > ---------------------------------------------------------------------
> > > Please check that your question has not already been answered in the
> > > FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
> > >
> > > To unsubscribe, e-mail: <co...@xml.apache.org>
> > > For additional commands, e-mail: <co...@xml.apache.org>
> > >
> >
> >
> > ---------------------------------------------------------------------
> > Please check that your question has not already been answered in the
> > FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
> >
> > To unsubscribe, e-mail: <co...@xml.apache.org>
> > For additional commands, e-mail: <co...@xml.apache.org>
> >
> >
>
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
RE: Counting rows in a resultset
Posted by Koen Pellegrims <ko...@pandora.be>.
Try SELECT(id) FROM names WHERE id = 1;
K.
> -----Oorspronkelijk bericht-----
> Van: Tuomo Lesonen [mailto:tlesonen@cc.hut.fi]
> Verzonden: woensdag 28 augustus 2002 12:07
> Aan: cocoon-users@xml.apache.org; haul@dvs1.informatik.tu-darmstadt.de
> Onderwerp: Re: Counting rows in a resultset
>
>
>
>
> On Wed, 28 Aug 2002, Christian Haul wrote:
>
> > On 28.Aug.2002 -- 10:54 AM, Leszek Gawron wrote:
> > > try to do select count(*) as row_count from table;
> > >
> > > and then in <esql:row-results> do <esql:get-long column="row_count"/>
> > >
> > > I'm not 100% sure but this highly probable to work
> >
> > Yes, if it is an option to run the query twice this should work. The
> > "as row_count" is not necessary if you access the count(*) by column
> > number i.e. <esql:get-long column="1"/>
>
> I did try these both. But COUNT(*) is not working, because I have to
> specify more columns in the query. My query is something like this: SELECT
> id, name FROM names WHERE id = 1;
>
> This doesn't work: SELECT COUNT(*), id, name FROM names WHERE id = 1;
>
> I'm using MS SQL2000. The query above is probably not well formed SQL, or
> SQL2000 doesn't know what to do with it... :(
>
> -Tuomo
>
>
> >
> > Chris.
> > --
> > C h r i s t i a n H a u l
> > haul@informatik.tu-darmstadt.de
> > fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
> >
> > ---------------------------------------------------------------------
> > Please check that your question has not already been answered in the
> > FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
> >
> > To unsubscribe, e-mail: <co...@xml.apache.org>
> > For additional commands, e-mail: <co...@xml.apache.org>
> >
>
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Tuomo Lesonen <tl...@cc.hut.fi>.
On Wed, 28 Aug 2002, Christian Haul wrote:
> On 28.Aug.2002 -- 10:54 AM, Leszek Gawron wrote:
> > try to do select count(*) as row_count from table;
> >
> > and then in <esql:row-results> do <esql:get-long column="row_count"/>
> >
> > I'm not 100% sure but this highly probable to work
>
> Yes, if it is an option to run the query twice this should work. The
> "as row_count" is not necessary if you access the count(*) by column
> number i.e. <esql:get-long column="1"/>
I did try these both. But COUNT(*) is not working, because I have to
specify more columns in the query. My query is something like this: SELECT
id, name FROM names WHERE id = 1;
This doesn't work: SELECT COUNT(*), id, name FROM names WHERE id = 1;
I'm using MS SQL2000. The query above is probably not well formed SQL, or
SQL2000 doesn't know what to do with it... :(
-Tuomo
>
> Chris.
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Tuomo Lesonen <tl...@cc.hut.fi>.
Time to take out some heavier arms. Since I only need to
get "id" and "name"
from my table, I'm placing these pairs in a hashtable inside
<esql:row-results/>. Then I have a nice hashtable, which I iterate through
later on. hashtables "size()" method gives the rowCount.
BUT: How to implement "Enumeration" interface in XSP in order to use the
"keys()" method of the Hashtable object? (I'm pretty new to Java, so
there's probably a convinient way of doing this..)
-Tuomo
On Wed, 28 Aug 2002, Christian Haul wrote:
> On 28.Aug.2002 -- 10:54 AM, Leszek Gawron wrote:
> > try to do select count(*) as row_count from table;
> >
> > and then in <esql:row-results> do <esql:get-long column="row_count"/>
> >
> > I'm not 100% sure but this highly probable to work
>
> Yes, if it is an option to run the query twice this should work. The
> "as row_count" is not necessary if you access the count(*) by column
> number i.e. <esql:get-long column="1"/>
>
> Chris.
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 28.Aug.2002 -- 10:54 AM, Leszek Gawron wrote:
> try to do select count(*) as row_count from table;
>
> and then in <esql:row-results> do <esql:get-long column="row_count"/>
>
> I'm not 100% sure but this highly probable to work
Yes, if it is an option to run the query twice this should work. The
"as row_count" is not necessary if you access the count(*) by column
number i.e. <esql:get-long column="1"/>
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Leszek Gawron <ou...@vip.net.pl>.
> > ...
> > <xsp:logic>
> > int rowCount = 0;
> > ...
> > <esql:execute-query>
> > ...
> > <esql:row-results>
> > ...
> > <xsp:logic>
> > rowCount++;
try to do select count(*) as row_count from table;
and then in <esql:row-results> do <esql:get-long column="row_count"/>
I'm not 100% sure but this highly probable to work
ouzo
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Tuomo Lesonen <tl...@cc.hut.fi>.
On Wed, 28 Aug 2002, Christian Haul wrote:
> On 28.Aug.2002 -- 10:07 AM, Tuomo Lesonen wrote:
> > Hi,
> >
> > How can I count the rows in my resultset? I'm using esql, but
> > <esql:row-count/> doesn't seem to be implemented yet. SQL COUNT(*) doesn't
> > work in this case either. Can I do this with XSP?
>
> Yes, have a new var declared before the execute-query and ++ it in
> your row-results
>
Thank you Chris, but in this case I need to know the number of rows
_before_ I do <esql:row-results>, in order to produce the right data.
-Tuomo
> ...
> <xsp:logic>
> int rowCount = 0;
> ...
> <esql:execute-query>
> ...
> <esql:row-results>
> ...
> <xsp:logic>
> rowCount++;
>
>
> Chris.
> --
> C h r i s t i a n H a u l
> haul@informatik.tu-darmstadt.de
> fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
>
> ---------------------------------------------------------------------
> Please check that your question has not already been answered in the
> FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
>
> To unsubscribe, e-mail: <co...@xml.apache.org>
> For additional commands, e-mail: <co...@xml.apache.org>
>
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>
Re: Counting rows in a resultset
Posted by Christian Haul <ha...@dvs1.informatik.tu-darmstadt.de>.
On 28.Aug.2002 -- 10:07 AM, Tuomo Lesonen wrote:
> Hi,
>
> How can I count the rows in my resultset? I'm using esql, but
> <esql:row-count/> doesn't seem to be implemented yet. SQL COUNT(*) doesn't
> work in this case either. Can I do this with XSP?
Yes, have a new var declared before the execute-query and ++ it in
your row-results
...
<xsp:logic>
int rowCount = 0;
...
<esql:execute-query>
...
<esql:row-results>
...
<xsp:logic>
rowCount++;
Chris.
--
C h r i s t i a n H a u l
haul@informatik.tu-darmstadt.de
fingerprint: 99B0 1D9D 7919 644A 4837 7D73 FEF9 6856 335A 9E08
---------------------------------------------------------------------
Please check that your question has not already been answered in the
FAQ before posting. <http://xml.apache.org/cocoon/faq/index.html>
To unsubscribe, e-mail: <co...@xml.apache.org>
For additional commands, e-mail: <co...@xml.apache.org>