From: "McCaffrey, John G." <[EMAIL PROTECTED]> To: 'Glenn Barnard' <[EMAIL PROTECTED]> Subject: RE: Primary Key from Insert Date: Thu, 25 Mar 2004 17:05:45 -0600
yeah, I upgraded to rc 5 to fix a bug. everything seems to work out ok, but i did have to break the object apart. I had the mapping for object A contains object B, but object A has the identity, and needs to be inserted first but OJB always wants to insert object B first, and it fails on the foriegn Key constraint.
How is it working for you?
-----Original Message----- From: Glenn Barnard [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 4:35 PM To: [EMAIL PROTECTED] Subject: RE: Primary Key from Insert
John, have you had any luck?
>From: "McCaffrey, John G." <[EMAIL PROTECTED]>
>To: 'Glenn Barnard' <[EMAIL PROTECTED]>
>Subject: RE: Primary Key from Insert
>Date: Wed, 17 Mar 2004 17:58:58 -0600
>
>Glenn,
>the query that I was told to use for DB2 is
>SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
>
>which I need to select after doing the insert, but before doing the commit.
>I looked into extending the PlatformDB2Impl but I didn't see how you could
>tell ojb to use it.
>
>-----Original Message-----
>From: Glenn Barnard [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, March 17, 2004 5:08 PM
>To: [EMAIL PROTECTED]
>Subject: RE: Primary Key from Insert
>
>
>
>John, sorry for going direct... Got a DB2 question...
>
>How does DB2 get the PK from the last insert? If you've read my thread, MS
>SQL Server provides the function SCOPE_IDENTITY() and Oracle has
>field.NextVal.
>
>
>
> >From: "McCaffrey, John G." <[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 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:[EMAIL PROTECTED]
> >Sent: Wednesday, March 17, 2004 12:57 PM
> >To: [EMAIL PROTECTED]
> >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 <[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]
> >
>
>_________________________________________________________________
>All the action. All the drama. Get NCAA hoops coverage at MSN Sports by
>ESPN. http://msn.espn.go.com/index.html?partnersite=espn
_________________________________________________________________
Get tax tips, tools and access to IRS forms - all in one place at MSN Money!
http://moneycentral.msn.com/tax/home.asp
_________________________________________________________________
Get reliable access on MSN 9 Dial-up. 3 months for the price of 1! (Limited-time offer) http://join.msn.com/?page=dept/dialup&pgmarket=en-us&ST=1/go/onm00200361ave/direct/01/
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
