cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
Subject Re: "Different" SQL Transformer implementation
Date Mon, 29 Mar 2004 16:20:38 GMT
I've used the vanilla SQLTransformer and the new one Irv talks about. I can say
that this new one is very powerful! The feature that saved the most time is the
ability to pass data from one query to the next! This is stuff we would have
done in the sitemap with multiple query/xslt steps. This way, more generic
pipelines can include several dependent query steps within one element in the
I would have used batching if it ws available at the time. I was inserting
thousands of rows into a databse from an excel file and batching would have
sped this up remarkbly.

+1  (as a user, not a committer)


Quoting Irving Salisbury III <>:

> Unfortunately, we are stuck at cocoon-2.0.4, so I have not been tracking 
> SQLTransformer changes in the latest cocoon release.
> However, I wanted to see if anyone was interested in some of the work we 
> have done with a different implementation of the SQLTransformer.  Here 
> is what we needed:
> 1. Batch updates for our large inserts, which we had to support up to 
> 10,000 inserts at once
> 2. Transactions as we needed to be able to roll back
> 3. Paging data for viewing all of these records
> 4. Reusing results from previous queries as parameters in later queries  
> (like getting the last entered id)
> So, attached is an example XML file that drives our SQLTransformer.  As 
> you can see, it is somewhat different from the cocoon version.  First, 
> you will notice it is very sequential and flat. (No nesting)  It is 
> meant to be almost a "script" to our SQLTransformer.  It basically 
> "records" queries until it hits an sql:execute, and then it takes 
> action.  Anything labeled with is-batch will be held until the end and 
> then use the JDBC batching calls.  If use-transaction is true, it will 
> do the all the queries before the execute element as a transaction and 
> roll back.  The XML result is similar to the existing SQLTransformer, 
> except providing more information about the transaction, and the fact 
> that we have multiple result sets for a given execute.
> Anyway, we dont' have a lot of time to develop a lot of documentation on 
> it, but I'd be happy to give it to anyone who wants it and/or donate it 
> if there is interest.  I can tell you we are using it on a number of 
> apps at our main customer, doing transactions of 10,000 rows or more for 
> every upload.  (managing a 5TB database)
> Some issues we have uncovered:
> 1. To get the number of rows of a query, we wrap the SQL with a select 
> count( * ) from (    <your query> ).  This is because getting this 
> information by using last() and getRow() was horrible when you are 
> retrieving 50K rows.  This does break mysql before 4.0 as it doesn't 
> allow suboridnate queries.  However, the code is in there to do the 
> last() and getRow on the JDBC result set if someone wants to put it back
> 2. We did most of our testing with Oracle and MySQL 4.1 (and oracle got 
> the lion's share of testing)  So, I can't speak to the true cross 
> platform nature of it :-)
> Anyway, let me know if anyone is interested or how I might go about 
> giving code if desired to cocoon.  Like I said, I haven't looked at the 
> latest SQLTransformer in the 2.1 cocoon branch, so you might already 
> have a lot of these things.
> Irv

To unsubscribe, e-mail:
For additional commands, e-mail:

View raw message