Torsten Curdt wrote:
Hi!
The SQLTransformer lacks an important feature that is present in the ESQL logicsheet. ESQL allows limiting the number of rows returned from a database using <esql:max-rows> and skips some rows using the <esql:skip-rows> tag.
I have implemented something similar for the SQLTransformer. The patch is very simple and I think doesn't break any of the current funcionality.
The major feature of esql in this particular case is that it already limits the result set on the *database*! Your patch only skips the
> generation of SAX events. As a first steps this is nice but it is > probably a magnitute slower.
ESQL still uses JDBC for accessing the database, just as SQLTransformer does. I am not aware of the ability of limiting the results set returned by JDBC apart from using apropriate SQL. And all databases do not support limiting the number of rows returned by SQL SELECTs. All major databases, such as MySql, Postgresql and Oracle do, but some minor ones such as MS SQL Server do not. To my knowledge. JDBC fetches statement.getFetchSize() rows from the database, and gets more when needed, so I would't say I am _just_ limiting the generation of SAX events. Most rows in large tables are never fetched. I can probably use statement.setFetchSize(limit) to make it a little more efficient. (Just tried this and it does make a difference.)
That said, I first had my problem implemented with ESQL, and did not notice a big difference in performance after redoing it with SQLTRansformer. The transformer is slower, of course, but the the time dependence is the same O(N), where N is the numer of rows in the SQL querry! Regardless of <esql:max-rows>! This maybe isn't the case on most databses, but I cannot choose which one to use.
To share this feature with esql the SQLTransformer should better use the esql helper classes.
There was a discussion to merge the codebase of esql and the SQLTransformer into a new new sql logicsheet and a corresponding sql transformer (see [1]). Same syntax, same feature set. Or at least as close as possible.
Nice. But I need a solution soon.
That way we wouldn't have to maintain two tools for basically the same thing.
[1] http://marc.theaimsgroup.com/?l=xml-cocoon-dev&m=102642026918555&w=2
I agree that having two tools is a waste, but ESQL cannot do everything. In particular, I wanted to write a logicsheet, that processes a <browseable-table> tag. The logicsheet template takes parameters, such as <offset>, <limit> and <query>. The values for these parameters come from the request or from the session. I was not able to pass these parameters to the logicsheet _and_ use them in ESQL at the same time, even though I spent days on the problem and asked for help on cocoon-users. So I decided to break up the problem into two steps. My XSP logicsheet produces input for the SQLTransformer and so on... But this is off-topic here.
Realizing that SQLTransformer is an evolutionary dead end, I guess I will revisit my original problem. There go a few more days :-(
My first choice was ESQL. And I also noticed SQLTransformer is absent from Cocoon 2.1.
:-((
feri.
