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" <[email protected]>
To: [email protected]
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 < [email protected] > 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 < [email protected] > wrote: 





---------- Forwarded message ---------- 
From: Abel Birya < [email protected] > 
Date: Mon, Apr 18, 2011 at 3:50 PM 
Subject: [SimpleORM] SINSERT Key Generator [1 Attachment] 
To: [email protected] 






[ 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, [email protected] 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, [email protected] 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:
    [email protected] 
    [email protected]

<*> To unsubscribe from this group, send an email to:
    [email protected]

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