SQLTransformer modifications

[Irv Salisbury III <ir...@dotech.com>]

We'd like to use paging in our application from the result sets returned 
from the SQLTransformer.  However, with a number of our queries 
returning 20,000+ rows, it seems like the current Paginator will not cut 
it for us.  This is mainly because it looks like the Paginator expects 
all of the data to be there, which we don't want it to be.

What we'd like to do is to take advantage of the setFetchSize() stuff in 
the Statement object, and the fact that the drivers we are using 
implement this properly.  So, in essence, the driver will only go and 
get the number of rows we want, and when we go past that number of rows, 
it will go and fetch N number more.  To do this, we have to do our 
paginating by using the ResultSet directly.

I have been wrestling with this and looking at the code for 
SQLTransformer to see if I could extend it to do this.  Basically, set 
the fetch size on the Statement object before querying.  I also then 
need to put the ResultSet object into my session so I can retrieve it 
with my paginating transformer.  Upon looking at this, it doesn't seem 
like there is a good way to tap into the SQLTransformer to do this.  It 
seems like another alternative would be to just copy the source code for 
SQLTransformer and tap in where I need to.  Seems pretty nasty, but 
would get me where I want to go.

So, I'd love feedback on this, as well as any thoughts from people that 
might have tackled this before.

Irv



---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: SQLTransformer modifications

[Jeremy Quinn <je...@media.demon.co.uk>]

On Wednesday, March 5, 2003, at 05:15 PM, Irv Salisbury III wrote:

> We'd like to use paging in our application from the result sets 
> returned from the SQLTransformer.  However, with a number of our 
> queries returning 20,000+ rows, it seems like the current Paginator 
> will not cut it for us.  This is mainly because it looks like the 
> Paginator expects all of the data to be there, which we don't want it 
> to be.

I just use the LIMIT statement in my SQL Query:


<component xmlns="http://www.xmlmind.com/xmleditor/schema/iniva">
	<meta>
		<title>formats</title>
		<desc>gets the list of audiocd format</desc>
	</meta>
	<chapter type="publications">
		<execute-query xmlns="http://apache.org/cocoon/SQL/2.0" 
xmlns:sql="http://apache.org/cocoon/SQL/2.0">
			<query name="count-paging">
				SELECT COUNT(id) AS total
				FROM library_resource
				WHERE format = 15
			</query>
		</execute-query>
		<execute-query xmlns="http://apache.org/cocoon/SQL/2.0" 
xmlns:sql="http://apache.org/cocoon/SQL/2.0">
			<query name="publications">
				SELECT id AS 'libraryid', title, YEAR(pubyear) AS 'date', 
description AS 'desc'
				FROM library_resource
				WHERE format = 15
				ORDER BY r.title
				LIMIT <substitute-value sql:name="start"/>,<substitute-value 
sql:name="count"/>
			</query>
		</execute-query>
	</chapter>
</component>


Then some XSLT turns the 'count-paging' query into a bunch of links to 
each 'page' of results.

I use InputModules in the Sitemap for the parameters 'start' and 
'count', which have default values (0 and 25) that can be overridden by 
request parameters.


hope this helps

regards Jeremy


---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org

Re: SQLTransformer modifications

[Brian Johnson <jo...@yahoo.com>]

I have also started to look into this, but have not come up with 
anything yet. I'd be very interested in anything you come up with, and 
will certainly pass along any ideas I have.
Brian

On Wednesday, March 5, 2003, at 12:15  PM, Irv Salisbury III wrote:

> We'd like to use paging in our application from the result sets 
> returned from the SQLTransformer.  However, with a number of our 
> queries returning 20,000+ rows, it seems like the current Paginator 
> will not cut it for us.  This is mainly because it looks like the 
> Paginator expects all of the data to be there, which we don't want it 
> to be.
>
> What we'd like to do is to take advantage of the setFetchSize() stuff 
> in the Statement object, and the fact that the drivers we are using 
> implement this properly.  So, in essence, the driver will only go and 
> get the number of rows we want, and when we go past that number of 
> rows, it will go and fetch N number more.  To do this, we have to do 
> our paginating by using the ResultSet directly.
>
> I have been wrestling with this and looking at the code for 
> SQLTransformer to see if I could extend it to do this.  Basically, set 
> the fetch size on the Statement object before querying.  I also then 
> need to put the ResultSet object into my session so I can retrieve it 
> with my paginating transformer.  Upon looking at this, it doesn't seem 
> like there is a good way to tap into the SQLTransformer to do this.  
> It seems like another alternative would be to just copy the source 
> code for SQLTransformer and tap in where I need to.  Seems pretty 
> nasty, but would get me where I want to go.
>
> So, I'd love feedback on this, as well as any thoughts from people 
> that might have tackled this before.
>
> Irv
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
>


---------------------------------------------------------------------
To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
For additional commands, e-mail: cocoon-users-help@xml.apache.org