From: Upayavira <[EMAIL PROTECTED]>
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.
