SQL Transformer

[Irving Salisbury <ir...@dotech.com>]

At our company, we have now had two instances where we would have like 
to extend the SQLTransformer.  

For one of them, we wanted to do paging utilizing the ResultSet methods 
setFetchSize and absolute().  The current PagingTransformer loads the 
entire ResultSet in, which wouldn't work for our enromous ResultSets. 
 We succesfully wrote this, but the way we had to do it was to take the 
existing SQLTransformer and "hack" into it.  It was not very easy to get 
at the very reusable class Query that you guys wrote, nor to get at the 
ResultSet.  In our workspace, we pulled out the Query class and made 
some of its variables reusable.  We also tried to separate the Query 
class from its parent class, as there were some state variables that the 
SQLTransformer kept for the Query class.  We got this done, but giving 
it back to the community is not something we want to do because of the 
"hacking" we had to do.  We had to limit the multiple query feature of 
the SQLTransformer as well as assume our stored procedures returned a 
ResultSet.  Neither of these things we felt would be good to give back.

For the second one, we wanted to be able to do our own prepared 
statements and reuse that for multiple queries, and to also do batch 
updates.  

In both of these cases, a more layered SQLTransformer would have been a 
big help.  There are a couple of things that would have worked great for 
us and that I think could be implemented.  

1. The parsing and replacement features of the SQLTransformer could be 
separated.  Setting up an SQL statement with the substitute-value stuff 
and the parsing you guys do would be very reusable for others building 
SQL like transformers.  
2. The Query class as a seperate and not so tied in class.  It has some 
very reusable methods, and could be used by other SQL like transformers.
3. Possibly a more layered approach, with an AbstractSQLTransformer that 
does the parsing and sets up queries, but then you can subclass and 
actually execute the queries in the way your transformer needs?  The 
kind of pattern that the AbstractSAX transformer uses that makes it such 
a good class to subclass.

Thanks for listening.  This is such an important Transformer that I 
think a little bit more reusability would be a great thing!

Irv

Re: SQL Transformer

[Irving Salisbury <ir...@dotech.com>]

It is not proprietary code.  It is a little "ugly", but I am happy to 
share it.  We are using it on a reasonably large system, and it seems to 
be working well.  I'll just send it to you so I don't spam this whole 
list. (Unless others want it)  I should be able to bundle it up and send 
it in about 2 hours.

Irv

Boon Hian Tek wrote:

> Irving Salisbury wrote:
>
>> At our company, we have now had two instances where we would have 
>> like to extend the SQLTransformer. For one of them, we wanted to do 
>> paging utilizing the ResultSet methods setFetchSize and absolute().  
>> The current PagingTransformer loads the entire ResultSet in, which 
>> wouldn't work for our enromous ResultSets. We succesfully wrote this, 
>> but the way we had to do it was to take the existing SQLTransformer 
>> and "hack" into it.  It was not very easy to get at the very reusable 
>> class Query 
>
>
> I just realized that too. I almost wanted to go through what you did. 
> Any chance you can share the mods you made and
> is using? Or is it proprietary code?
>
> Boon

Re: SQL Transformer

[Boon Hian Tek <bt...@bhtek.dyndns.org>]

Irving Salisbury wrote:

> At our company, we have now had two instances where we would have like 
> to extend the SQLTransformer. 
> For one of them, we wanted to do paging utilizing the ResultSet 
> methods setFetchSize and absolute().  The current PagingTransformer 
> loads the entire ResultSet in, which wouldn't work for our enromous 
> ResultSets. We succesfully wrote this, but the way we had to do it was 
> to take the existing SQLTransformer and "hack" into it.  It was not 
> very easy to get at the very reusable class Query 

I just realized that too. I almost wanted to go through what you did. 
Any chance you can share the mods you made and
is using? Or is it proprietary code?

Boon