RE: Primary Key from Insert--FOR John M.

[Glenn Barnard <ba...@hotmail.com>]

John, you're needing to do pretty much exactly what I'm trying too. Just 
keep reading the thread. I won't quit till it's solved!

I see you're using DB2. Are you also letting the DB assign a PK? I'm trying 
to use the PK assigned as the value for a FK in another table. I notice that 
with Oracle you can get the PK BEFORE inserting a row (NextVal), so that why 
I'm asking....


>From: "McCaffrey, John G." <Jo...@kraft.com>
>Reply-To: "OJB Users List" <oj...@db.apache.org>
>To: 'OJB Users List' <oj...@db.apache.org>
>Subject: RE: Primary Key from Insert
>Date: Wed, 17 Mar 2004 13:52:25 -0600
>
>I have this same issue. I need to use the PlatformDB2Impl and use its
>getLastInsertIdentity() method to pick up the last Identity that was
>created.
>I noticed that the interface is defined as
>public String getLastInsertIdentityQuery(String tableName);
>and what I specifically need is access to the pb that was used to insert 
>the
>last row, and I need it before the commit is called (I need to be within my
>transaction).
>
>I don't know how to solve this, so I was told (by a team member) to break
>apart my OJB mapping (for my multi-joined object) and insert one table, get
>the identity, then insert the children objects (that are relying on that ID
>as a FK).
>What I would really like is to tell OJB to get the Identity, for the 
>parent,
>and insert it into the children, all on its own.
>
>Let me know if you solve your problem
>
>
>
>-----Original Message-----
>From: Glenn Barnard [mailto:barnardglenn@hotmail.com]
>Sent: Wednesday, March 17, 2004 12:57 PM
>To: ojb-user@db.apache.org
>Subject: Re: Primary Key from Insert
>
>
>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 <ar...@apache.org>
> >Reply-To: "OJB Users List" <oj...@db.apache.org>
> >To: OJB Users List <oj...@db.apache.org>
> >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 <ar...@apache.org>
> >>>Reply-To: "OJB Users List" <oj...@db.apache.org>
> >>>To: OJB Users List <oj...@db.apache.org>
> >>>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: ojb-user-unsubscribe@db.apache.org
> >>>>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>>
> >>>>
> >>>>
> >>>
> >>>---------------------------------------------------------------------
> >>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>>
> >>
> >>_________________________________________________________________
> >>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: ojb-user-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >>
> >>
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >For additional commands, e-mail: ojb-user-help@db.apache.org
> >
>
>_________________________________________________________________
>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: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>

_________________________________________________________________
Get reliable access on MSN 9 Dial-up. 3 months for the price of 1! 
(Limited-time offer) http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org