I found different ways for SQL in cocoon. On the one hand there is <esql:.../>, on the other <sql:...>. So consider this a work in progress -- Scherler



The main difference that I see is that <sql:.../> is based on a transformer and <esql:...> on a generator.


Mailinglist says: {{{The major feature of esql in this particular case is that it already limits the result set on the *database*}}} {{{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.}}} {{{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. That way we wouldn't have to maintain two tools for basically the same thing. }}} Proposal to Remove SQLTransformer in 2.1 {{{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.) }}}

> 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, 
are you talking about use-limit-clause parameter? Similar to this should work:
<esql:use-limit-clause><xsp-session:get-attribute name="limit"/></esql:use-limit-clause>

SQLdifferenceESQL (last edited 2009-09-20 23:41:50 by localhost)