well, it seems like everything you are doing is correct. Here is what I am doing: version = rc5 seq = SequenceManagerNativeImpl platform = PlatformCustDb2Impl (extends PlatformDb2Impl) override getLastInsertIdentityQuery() qry = "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1" (the query for DB2 that is in rcd didn't work for me)
primary key setup = name="evntRqstId" column="EVNT_RQST_ID" jdbc-type="INTEGER" primarykey="true" autoincrement="true" access="readonly" the access is read-only because I have to tell OJB not to check if its Null (cuz it is Null before its inserted, bc DB2 will assign the value after its inserted) the data type is Integer, so that it can be Null. (if I used int, the default value of 0 would get set, I think) what is your java type? I don't really know what else could be going on. Originally I thought that the connection was a new connection so that the call to get the identity would not happen within the same tx that the row was inserted with, but now I see that that isn't the case.(though I haven't run any multithreaded tests) -----Original Message----- From: Glenn Barnard [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 12:22 PM To: [EMAIL PROTECTED] Subject: RE: Primary Key from Insert So you are getting the PK populated now with your DB2 application? VERY GOOD! I hope my posts were helpful. John, I won't hold it against you if you can't help me on MS SQL Server. But, somebody out there should be able to shed some light on the problem. In summary, I'm using autoincrement=true to let the DB assign the PK, and then expect OJB to return the PK value in the data object after an insert. >OJB version: rc5 >Sequence manager? org.apache.ojb.broker.util.sequence.SequenceManagerNativeImpl >platformImpl ? MsSQLServer >What is your query? Am returning "SELECT SCOPE_IDENTITY()" in an overloaded PlatformMsSqlImpl.getLastInsertIdentityQuery() method. >exceptions? None-Query returns nothing. >debug info? I've traced the code and everything appears to process OK, except the ID's value is 0. In order to highlight and isolate the problem, I've modified JdbcAccessImpl.executeInsert() method as follows. I know the query is correct by running test code not using OJB. Note that this problem is reproduceable in my test program IF the Statement object is not closed first. public void executeInsert(ClassDescriptor cld, Object obj) throws PersistenceBrokerException { if(logger.isDebugEnabled()) logger.safeDebug("executeInsert", obj); PreparedStatement stmt = null; try { stmt = broker.serviceStatementManager().getInsertStatement(cld); if (stmt == null) { logger.error("getInsertStatement returned a null statement"); throw new PersistenceBrokerException("getInsertStatement returned a null statement"); } broker.serviceStatementManager().bindInsert(stmt, cld, obj); if (logger.isDebugEnabled()) logger.debug("executeInsert: " + stmt); stmt.executeUpdate(); // Harvest any return values. this.harvestReturnValues(cld.getInsertProcedure(), obj, stmt); // This is test code I've inserted to isolate my problem.... Connection connection = stmt.getConnection(); stmt.close(); Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY()"); while (rs.next()) { long val = rs.getLong(1); System.out.println("PK=" + val); // IS ALWAYS 0. rs.get(1) is NULL } } >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: Fri, 26 Mar 2004 10:37:28 -0600 > >Glenn, >well, I am no expert at this, but I would like to try to help. >can you give me a quick run down of your situation again (I know its in >previous posts, just give me a summary) > >details: >OJB version >Sequence manager? >platformImpl ? (what is your query?) >debug info? >exceptions? > > >-----Original Message----- >From: Glenn Barnard [mailto:[EMAIL PROTECTED] >Sent: Thursday, March 25, 2004 9:05 PM >To: [EMAIL PROTECTED] >Cc: [EMAIL PROTECTED] >Subject: RE: Primary Key from Insert > > >I'm dead in the water. Can't get the PK query to return anything. Am at >wits > >end as all my development efforts are dependent on this working. No further >responses from the OJB guys leading me to try something else. > > > > >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] > >--------------------------------------------------------------------- >To unsubscribe, e-mail: [EMAIL PROTECTED] >For additional commands, e-mail: [EMAIL PROTECTED] > _________________________________________________________________ FREE pop-up blocking with the new MSN Toolbar - get it now! http://toolbar.msn.com/go/onm00200415ave/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]
