Mailing-List: contact cocoon-dev-help@xml.apache.org; run by ezmlm
Date: Fri, 19 May 2000 14:03:56 -0400 (EDT)
From: Donald Ball <balld@webslingerZ.com>
To: Cocoon <cocoon-dev@xml.apache.org>
Subject: Re: COUNT in sql taglib
In-Reply-To: <39254E59.7CAF32F3@credit-suisse.ch>
Message-ID: <Pine.LNX.4.10.10005191355530.31495-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 19 May 2000, Giacomo Pati wrote:

> I've thought almost the hole day of a solution to the count problem in
> the sql taglib and came to the conclusion that there is no reliable
> solution. I've take a sample query from my good old DB2 manuals and
> extended a little bit:
> 
> select count(dep),job as work,min(salary),max(salary)from emp 
> where(salary>20000)group by job 
> having count(*)>1
> order by 1
> 
> Yes, it might look ugly but it is still legal SQL (tested with mysql,
> postgres and db2).
> 
> With some sample input the query above lists 3 records. The nearest
> possible count statement is:
> 
> select count(job) from emp where(salary>20000)
> 
> and this results in the number 11!

Man, that's tricky. I hadn't thought that it would be this complicated.
Guess my SQL knowledge has more holes in it than I thought. I'm sending
this on to the cocoon developers list in hopes of expanding the noosphere.

> Now we have several opportunities:
> 1. we don't offer the count attribute anymore
> 2. we insert the count attribute after the result set is produced (and
> counted) [could/should not be portable to cocoon 2 because of SAX]
> 3. we analyse the sql statement and decide if a count could be reliable
> produced depending on some keywords like HAVING and maybe more.

count attribute is very very helpful when you want to generate pages that
iterate through a resultset. maybe we should make it an element instead so
that we don't have to worry about problems when porting to SAX and
cocoon2? Hmm...

I'd say we should analyze the statement first to see if we can generate a
reliable count query and do that if possible. If not, we'll iterate
through the whole resultset (if the author wants a count attribute).
Although... it just occured to me - I've never used cursors, but do they
provide any special functionality we could use to help overcome this
problem?

Any thoughts from interested users?

- donald