springframework-developer

[Springframework-developer] JDBC Insert functionality for HSQL

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

Thomas,

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 have pretty much completed and tested (with the exception of testing 
on the Oracle version) most of my proposed refactoring of this class 
hierarchy. Should the Hsql version be changed to use a similar 
implementation as Mysql ???

It seems like they could both use the virtually the same code with the 
exception of the SQL used to get the last inserted value.

Regards,

Ken Krebs

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

[<tri...@tr...>]

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.  

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.

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.

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

Thomas

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