My previous post has a pseudo source that I'm trying to do. For the overall picture, I'm trying to get the PK from an insert to populate the FK in another record.
The pseudo code again is below. Please note that the first statement is executed in OJB by JdbcAccessImpl.executeInsert() and the remaining statements in SequenceManagerNativeImpl.getLastInsert().
statement.executeUpdate("INSERT INTO <table> (<fields>) VALUES (<values>); ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY() AS newID"); while (rs.next()) { long pk = rs.getLong(1); System.out.("PK=" + pk); }
BTB, if you are an OJB developer, shouldn't getLastInsert close the resultset in the finally clause, not under the try?
That's the pseudo source that I'm trying to get OJB to perform. This is a code snippet from my application--but, I don't think it'll tell you much. I've annotated it to help.
PersistenceBroker broker = PersistenceBrokerFactory.defaultPersistenceBroker();
// Start broker transaction broker.beginTransaction();
// If the record is to be inserted
if (thisForm.isNew())
{
// Insert the new address
Addresses address = (Addresses)CommonDelegate.insertModel(address, false, broker);
// insertModel() stores the row and returns the updated model with
// the PK-Address ID-populated
// Create and insert the 1-to-many reference row
EntityToAddress entityAddress = new EntityToAddress();
entityAddress.setEntityId(thisForm.getEntity().getEntityId()); // the 1
entityAddress.setAddressId(addressExisting.getAddressId()); // the many
entityAddress = (EntityToAddress)CommonDelegate.insertModel(entityAddress, false, broker);
}
broker.commitTransaction();
From: Armin Waibel <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: Primary Key from Insert Date: Wed, 17 Mar 2004 20:25:42 +0100
Hi Glenn,
in OJB test suite you can find a test case called ...NativeIdentifierTest. This test use database based identity columns for PK assignment and pass against hsql and mysql.
Can post more detailed what you are doing? Source or pseudo code?
regards, Armin
Glenn Barnard wrote:Interesting exercise, but it didn't work.
I extended PlatformMsSQLServerImpl() and have it's getLastInsertIdentity() method return "SELECT SCOPE_IDENTITY() AS newID". It tries to process, but the resultset contains a null.
Which means that the connection/statement used to executeUpdate is not the same as the one performing the executeQuery. I know this because I went to a rudimentary JDBC application I wrote and implemented the following statements:
statement.executeUpdate("INSERT INTO <table> (<fields>) VALUES (<values>); ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY() AS newID"); while (rs.next()) { long pk = rs.getLong(1); System.out.("PK=" + pk); }
This returns the correct value. If you create another statement object (hence connection) between the executeUpdate and executeQuery, the result returns 0 (null). Which is as expected.
Any suggestions?
From: Armin Waibel <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: Primary Key from Insert Date: Wed, 17 Mar 2004 17:43:02 +0100
Glenn Barnard wrote:
Armin, thanks for the direction. I've switched over to the NativeSequenceManager. It calls lastInsertSelect() which in turn invokes the supported platforms getLastInsertIdentity(). The platform it's loading is the PlatformMsSQLServerImpl and it doesn't overload PlatformDefaultImpl's stub for getLastInsertIdentity which throws a not implemented exception.
I'm back on version 1.4 and will upgrade immediately to 1.5. If that version does not have the correct getLastInsertIdentity, then I will have to look into implementing it elsewhere, perhaps by extending PlatformMsSQLServerImpl with a custom getLastInsertIdentity method that simply returns "SELECT SCOPE_IDENTITY() AS newID".
In rc5 method was not implemented.
You can also use CVS version of OJB - it's stable (98%, to check you can run OJB junit test suite) and include identity column support in PlatformMsSQLServerImpl.
regards, Armin
Thanks again for your help.
From: Armin Waibel <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: Primary Key from Insert Date: Wed, 17 Mar 2004 15:49:44 +0100
Hi Glenn,
why don't you use a identity based sequence manager implementation?
http://db.apache.org/ojb/sequencemanager.html#nativeSequenceManager
(seems that one part of this section was "corrupted" by Maven, with "ant htmldoc" you can generate the local documentation)
or your persistent capable objects can implement PersistenceBrokerAware interface and you assign the PK by hand in the afterInsert method (obtain connection from PB instance and perform sql query by your own).
regards, Armin
Glenn Barnard wrote:
I'm using SQL Server 2000 and need to get the primary key returned in the data model after an insert. My research shows that the query "SELECT SCOPE_IDENTITY() AS newID" needs to be executed. What do I need to do to get OJB to automatically populate the PK field in the data model it returns?
_________________________________________________________________
Get tax tips, tools and access to IRS forms � all in one place at MSN Money! http://moneycentral.msn.com/tax/home.asp
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
Free up your inbox with MSN Hotmail Extra Storage. Multiple plans available. http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
All the action. All the drama. Get NCAA hoops coverage at MSN Sports by ESPN. http://msn.espn.go.com/index.html?partnersite=espn
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
Find a broadband plan that fits. Great local deals on high-speed Internet access. http://click.atdmt.com/AVE/go/onm00200360ave/direct/01/
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
