cocoon-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Andrew Stevens" <at...@hotmail.com>
Subject RE: SQL and CTemplate (was Re: [RT] Rules for adding blocks and functionality?)
Date Wed, 26 Oct 2005 22:54:33 GMT
>From: Upayavira <uv@odoko.co.uk>
>Date: Wed, 26 Oct 2005 21:44:23 +0100
>
>Whilst I'm not going to be the person implementing it, having seen the
>distinction made between SELECT and UPDATE in ESQL/SQLTransformer

That was one of the things that bugged me about the SQLTransformer too, that 
and the fact it didn't handle our Sybase-based stored procs (some of which 
returned a few update counts first from selecting into temporary tables and 
variables, and only then the result set).  Besides which, it's perfectly 
possible for a stored proc to return more than one result set, and that 
wasn't catered for either, nor were queries like "SET ROWCOUNT 3; SELECT foo 
FROM bar ORDER BY bub DESC; SET ROWCOUNT 0".

So, I modified the transformer to loop over all the returned results.  In 
the process I was able to remove the select/update distinction completely - 
an update is simply a query that returns an update count in its rowset 
instead of a bunch of rows (getNextResult can tell the difference without 
needing to guess it from the <query> contents).  If there's multiple results 
then you get multiple rowsets, finally followed by any output parameters if 
isstoredprocedure="true" (since the JDBC spec says you should process all 
results before accessing those anyway).

Another improvement was decent handling of <sql:in-parameter> (allowing the 
value to be specified in the element body as well as an attribute so you can 
combine it with sql:substitute-value to use a parameter passed from the 
sitemap instead of only static values, which is much more useful).

If the transformer in CVS hasn't changed too much since I adapted it, I 
could upload a patch if people think it's worthwhile?

>The worst thing about ESQL/SQLTransformer in my view is the embedded
>SQL. Horrible.

Nobody said it has to come from a static file, though.  You can always 
generate the query dynamically from an XML file representing your DB schema 
and a suitable XSL transform to convert it into the SQL query...


Andrew.



Mime
View raw message