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
<?xml version="1.0" encoding="UTF-8" ?> <page xmlns:sql="http://dotech.com/SQL/1.0";> <title>Membership List</title> <content> <sql:query name="objects" is-update="true"> delete from SEC_OBJECT where OBJECT_NAME like 'Test SQL%' </sql:query> <sql:execute /> <sql:query name="objects1" is-update="true" > insert into SEC_OBJECT ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL 1', 1 ) </sql:query> <sql:query name="users" count-rows="true"> select * from SEC_USER </sql:query> <sql:query name="get-inserted-key-test"> select SEC_OBJECT_SEQ.CURRVAL from DUAL <sql:store-param name="sec_object_id" field-pos="1"/> </sql:query> <sql:query name="test-use-later" show-meta-data="true"> select * from SEC_USER where SEC_USER_ID = <sql:use-param name="sec_object_id"/> </sql:query> <sql:query name="objects1" is-update="true" is-batch="true"> insert into SEC_OBJECT ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL 2', 1 ) </sql:query> <sql:query name="objects2" is-update="true" is-batch="true"> insert into SEC_OBJECT_FOO ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL 3', 1 ) </sql:query> <sql:execute use-transaction="true" use-connection="myconnection"/> <sql:query name="users" count-rows="true"> select * from SEC_USER <sql:paging page-size="10" display-pages="3" /> </sql:query> <sql:query name="objects" is-update="true"> delete from SEC_OBJECT where OBJECT_NAME like 'Test SQL%' </sql:query> <sql:execute use-transaction="true"/> <sql:query name="roles"> select * from SEC_ROLE </sql:query> <sql:query name="objects1" is-update="true"> insert into SEC_OBJECT ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL 3', 1 ) </sql:query> <sql:query name="get-inserted-key"> select SEC_OBJECT_SEQ.CURRVAL from DUAL <sql:store-param name="sec_object_id" field-pos="1"/> </sql:query> <sql:query name="get-inserted-key-test"> select SEC_OBJECT_SEQ.CURRVAL from DUAL <sql:store-param name="sec_object_id_doobie" field-pos="1"/> </sql:query> <sql:query name="test-use-later" show-meta-data="true"> select * from SEC_USER where SEC_USER_ID = <sql:use-param name="sec_object_id_doobie"/> </sql:query> <sql:query name="objects2" is-update="true"> insert into SEC_OBJECT ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL', 1 ) </sql:query> <sql:query name="objects3" count-rows="true"> select * from SEC_OBJECT </sql:query> <sql:query name="objects5" count-rows="true"> select * from SEC_OBJECT </sql:query> <sql:execute use-transaction="true"/> <sql:query name="objects7" count-rows="true"> select * from SEC_OBJECT </sql:query> <sql:query name="objects6" is-update="true" > insert into SEC_OBJECT ( SEC_OBJECT_TYPE_ID, OBJECT_NAME, LAST_UPDATE_USER_ID ) values ( 2, 'Test SQL 4', 1 ) </sql:query> <sql:execute use-transaction="true"/> </content> </page>
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
