Re: SINSERT Key Generator

Re: SINSERT Key Generator

Re: SINSERT Key Generator

Hello Anthony,

Thanks for your reply, I did a thorough test with about 50,000 records
of data under a multi-threaded environment and it worked well and data
integrity was maintained.

I will have a look at the how best we can be able to push this
implementation to the SDriver class and will discuss my ideas with you
in the coming days.

Kind regards.

Abel

On 5/4/11, anthony berglas <anthony <at> berglas.org> wrote:
> Actually,
>
> http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
>
> Says it is threadsafe.  Plus has a few caveats.  Thanks Tim.
>
> Anthony
>
> On Wed, May 4, 2011 at 1:01 PM, anthony berglas <anthony <at> berglas.org> wrote:
>
>> Hello Abel,
>>
>> Sorry for the late reply, but your code looks basically OK.
>>
>> However, it would probably be better to use the new standard
>> JDBC getGeneratedKeys() method.  If that works for MySql and HSQL then it
>> could be pushed up into SDriver for all databases
>> that supportsInsertKeyGeneration().  If you would like to do that that
>> would
>> be great.  See
>>
>>
>> http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys
>>
>> I am suspicious of  the thread safety of LAST_INSERT_ID() (which
>> getGeneratedKeys probably uses beneath the covers).  To test, just set up
>> two manual  command lines sessions to a MySql database, Check AutoCommit
>> is
>> Off, insert a row in one session, then in the second session, then go back
>> to the first session and Select Last_Insert_Id.  It is important that it
>> be
>> the first record, not the second.
>>
>> This would be a valuable contribution to SimpleOrm.  If you get it working
>> just with MySql, we can push it up for you.
>>
>> You make a good point about the references.  Children assume parents
>> already have their keys.  I suppose there should be a lazy way to get
>> them.
>>  But for now just flush.
>>
>> Regards,
>>
>> Anthony
>>
>> On Wed, Apr 27, 2011 at 12:55 PM, Anthony & Melissa Berglas <
>> berglas <at> spreadsheetdetective.com> wrote:
>>
>>>
>>>
>>> ---------- Forwarded message ----------
>>> From: Abel Birya <abel.birya <at> gmail.com>
>>> Date: Mon, Apr 18, 2011 at 3:50 PM
>>> Subject: [SimpleORM] SINSERT Key Generator [1 Attachment]
>>> To: SimpleORM <at> yahoogroups.com
>>>
>>>
>>>
>>>
>>> [Attachment(s)<#12fb8f45faf831e3_12f94e2668a4f8ce_12f67290b26e68e3_TopText>from
>>> Abel Birya included below]
>>>
>>> Hi,
>>>
>>> I was wondering whether anyone has been able to do an implementation of
>>> this key generator mode for MySQL. I implemented the methods
>>> supportsInsertKeyGeneration() & retrieveInsertKey() of the
>>> SDriverMySQL.java
>>> class and so far I am able to retrieve my inserted key from the db when a
>>> commit() is done.
>>>
>>> One thing that I have noticed though is that in the event that an entity
>>> represents a foreign key in another table, I have to explicitly call the
>>> session.flush() method in order for the entity to bear its primary key
>>> field
>>> value before I call the setReference() method in the child record.
>>>
>>> I was wondering whether there is anyone who has time to assist me in
>>> testing this. I am currently working on a project that needs to work in a
>>> multi-threaded environment where there will be many connections to the DB
>>> at
>>> once and I wanted to find out how effective the retrieveInsertKey()
>>> method
>>> is going to be in such an environment and whether I will be able to
>>> maintain
>>> data integrity all across the different threads that are running.
>>>
>>> I have attached the SDriverMySQL.java class.
>>>
>>> Kind regards.
>>>
>>> Abel
>>>
>>>
>>>
>>>
>>>
>>> --
>>>
>>> *Spreadsheet Detective,
>>> *Southern Cross Software Queensland Pty Limited
>>> 54 Gerler Street, Bardon, Queensland 4065, Australia.
>>> www.SpreadsheetDetective.com
>>>  <http://www.spreadsheetdetective.com/>*"If the model seems correct only
>>> because the numbers look right,
>>> then why build the model in the first place?"
>>> *
>>>
>>>
>>
>>
>> --
>>
>> Dr Anthony Berglas, anthony <at> berglas.org       Mobile: +61 4 4838 8874
>> Just because it is possible to push twigs along the ground with ones nose
>> does not necessarily mean that that is the best way to collect firewood.
>>
>>
>
>
> --
>
> Dr Anthony Berglas, anthony <at> berglas.org       Mobile: +61 4 4838 8874
> Just because it is possible to push twigs along the ground with ones nose
> does not necessarily mean that that is the best way to collect firewood.
>

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

Re: SINSERT Key Generator

Hey Anthony,

I have been having a look at the way through which the key is assigned on the SINSERT generator and I saw that
the PreparedStatement where we can use the getLastInsertId() method is in the SessionHelper class.
There may be two approaches that I have thought of to retrieve and assign the generated id.

1. Change the postUpdateWithGeneratedKey method to include the prepared statement as a variable and
change its position in the SessionHelper class to come before the PreparedStatement.close(). This
would mean that after all validations have been done as to the integrity of the data being updated, the
SDriver.retrieveInsertedKey() will simply return statement.getLastInsertId();

2. Create a variable in SessionHelper before the PreparedStatement.close() that will be used to update
the primary key in the record instance. Subsequently, the postUpdateWithGeneratedKey will be updated
to reflect the extra variable that has to be passed on to the instance.

Kindly let me know what your thoughts are on this and then I can try to get it working as soon as possible.

Kind regards.

Abel

----- Original Message -----
From: "anthony berglas" <anthony <at> berglas.org>
To: SimpleORM <at> yahoogroups.com
Sent: Wednesday, 4 May, 2011 06:38:28 GMT +03:00 Iraq
Subject: Re: [SimpleORM] SINSERT Key Generator

Actually, 

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id 

Says it is threadsafe. Plus has a few caveats. Thanks Tim. 

Anthony 

On Wed, May 4, 2011 at 1:01 PM, anthony berglas < anthony <at> berglas.org > wrote: 

Hello Abel, 

Sorry for the late reply, but your code looks basically OK. 

However, it would probably be better to use the new standard JDBC getGeneratedKeys() method. If that works
for MySql and HSQL then it could be pushed up into SDriver for all databases that
supportsInsertKeyGeneration(). If you would like to do that that would be great. See

http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys 

I am suspicious of the thread safety of LAST_INSERT_ID() (which getGeneratedKeys probably uses beneath
the covers). To test, just set up two manual command lines sessions to a MySql database, Check AutoCommit
is Off, insert a row in one session, then in the second session, then go back to the first session and Select
Last_Insert_Id. It is important that it be the first record, not the second. 

This would be a valuable contribution to SimpleOrm. If you get it working just with MySql, we can push it up
for you. 

You make a good point about the references. Children assume parents already have their keys. I suppose
there should be a lazy way to get them. But for now just flush. 

Regards, 

Anthony 

On Wed, Apr 27, 2011 at 12:55 PM, Anthony & Melissa Berglas < berglas <at> spreadsheetdetective.com > wrote: 

---------- Forwarded message ---------- 
From: Abel Birya < abel.birya <at> gmail.com > 
Date: Mon, Apr 18, 2011 at 3:50 PM 
Subject: [SimpleORM] SINSERT Key Generator [1 Attachment] 
To: SimpleORM <at> yahoogroups.com 

[ Attachment(s) from Abel Birya included below] 

Hi, 

I was wondering whether anyone has been able to do an implementation of this key generator mode for MySQL. I
implemented the methods supportsInsertKeyGeneration() & retrieveInsertKey() of the
SDriverMySQL.java class and so far I am able to retrieve my inserted key from the db when a commit() is done. 

One thing that I have noticed though is that in the event that an entity represents a foreign key in another
table, I have to explicitly call the session.flush() method in order for the entity to bear its primary key
field value before I call the setReference() method in the child record. 

I was wondering whether there is anyone who has time to assist me in testing this. I am currently working on a
project that needs to work in a multi-threaded environment where there will be many connections to the DB
at once and I wanted to find out how effective the retrieveInsertKey() method is going to be in such an
environment and whether I will be able to maintain data integrity all across the different threads that
are running. 

I have attached the SDriverMySQL.java class. 

Kind regards. 

Abel 

-- 

Spreadsheet Detective, 
Southern Cross Software Queensland Pty Limited 
54 Gerler Street, Bardon, Queensland 4065, Australia. 
www.SpreadsheetDetective.com 
"If the model seems correct only because the numbers look right, 
then why build the model in the first place?" 

-- 

Dr Anthony Berglas, anthony <at> berglas.org Mobile: +61 4 4838 8874 
Just because it is possible to push twigs along the ground with ones nose 
does not necessarily mean that that is the best way to collect firewood. 

-- 

Dr Anthony Berglas, anthony <at> berglas.org Mobile: +61 4 4838 8874 
Just because it is possible to push twigs along the ground with ones nose 
does not necessarily mean that that is the best way to collect firewood. 

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/SimpleORM/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/SimpleORM/join
    (Yahoo! ID required)

<*> To change settings via email:
    SimpleORM-digest <at> yahoogroups.com 
    SimpleORM-fullfeatured <at> yahoogroups.com

<*> To unsubscribe from this group, send an email to:
    SimpleORM-unsubscribe <at> yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/