RE: Primary Key from Insert-For John

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

Yep, you're in the exact same boat as I am with MS SQL Server.

The platform class is loaded by PlatformFactory and it uses the following 
statement to name the class.:

        return "org.apache.ojb.broker.platforms.Platform" + pf.substring(0, 
1).toUpperCase() + pf.substring(1) + "Impl";

where the parameters come from the platform element in the 
<jdbc-connection-descriptor> tag in repository.xml. For me, that attribute 
is "MsSQLServer" and loads PlatformMsSQLServerImpl.

I made a copy of the source code for PlatformMsSQLServerImpl and added to my 
Eclipse project. Next, I implemented the getLastInsertIdentity() method that 
returns the SQL statement. For you it would return SELECT 
IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1. For a final implementation, I 
may rename the class or try to get the change into OJB.

However, the query needs to execute on the same connection as the insert, 
and that's where I'm having a problem with OJB.

I'm away for the weekend and will be looking at the problem in more depth. 
Will post my results Monday.

>From: "McCaffrey, John G." <Jo...@kraft.com>
>To: 'Glenn Barnard' <ba...@hotmail.com>
>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:barnardglenn@hotmail.com]
>Sent: Wednesday, March 17, 2004 5:08 PM
>To: John.McCaffrey@kraft.com
>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." <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
> >
>
>_________________________________________________________________
>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


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