cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Irving Salisbury III <>
Subject "Different" SQL Transformer implementation
Date Mon, 29 Mar 2004 14:25:48 GMT
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.


View raw message