Re: [Springframework-developer] JDBC Insert functionality for HSQL

[Ken K. <kk...@kk...>]

Thomas,

tri...@tr... wrote:

>Ken,
>
>  
>
>>I have been using the incrementers for Mysql and Hsql for the petclinic 
>>demo. The Mysql version of the incrementer was changed to use an  UPDATE 
>>statement on the sequence. The Hsql version uses an INSERT statement as 
>>the Mysql version used to do. I notice that the Hsql version  leaves 
>>extra rows in the sequence table because of this. There also is a 
>>different way  of caching blocks of keys. I don't understand why this is 
>>so. Is this something that just never got changed after the Mysql 
>>version got reworked ???
>>
>>    
>>
>
>I don't think HSQL has the feature, of bumping up the id, that I used for MySQL.
> The most reliable way of retrieving new ids was actually retrieving them from a
>sequence table and caching the ids.  We could clean up the extra rows after we
>are done however, and just leave the last one.  I'll make that change today or
>tomorrow. 
>

I have pretty much finished my proposed refactoring of the incrementer 
class hierarchy. As I don't have commit rights, could you make your 
changes to the files I've done and commit them ?


> 
>
>A more generic approach would be the following:
>
>"select current_id from sequence_table;"
>store current_id in x
>"update sequence_table set current_id = current_id + 10 where current_id = x"
>check update count - if it is 0 then repeat the whole process again (someone
>else incremented the sequence)
>
>This approach does not work if you are using transactions with isolation level
>serializable.  If someone else increments the sequence while your transaction is
>running, but before you try to increment the sequnce, then you will never be
>able to retrieve the new current_id and your whole transaction will fail.
>
>HSQL does not support any isolation levels other than READ_UNCOMMITTED, so this
>approach would work.  I'll try it when I am making the change.
>

Thanks for the enlightenment ;=}.


>
>Unless we can come up with a general solution that will always work in all
>databases, I would prefer to provide a few specific solutions and let anybody
>that needs a different solution provide their own implementation.
>

I think a generalized solution that would work in most full-featured 
db's would be useful.


>
>Do you have a new download available - I would like to set up a datasource and
>deploy the demo app on the website.
>

I'm getting close. I've got one more problem to solve (see my recent 
posting about this) and a few more little things to do. The demo needs 
to use the refactored incrementers. I should have a new petclinic 
available in a couple of days. If you could check it out on JBoss that 
would be great.


Ken