what is your java type? is your access readonly? thats the only other thing I can think of
-----Original Message----- From: Glenn Barnard [mailto:[EMAIL PROTECTED] Sent: Friday, March 26, 2004 1:21 PM To: [EMAIL PROTECTED] Subject: RE: Primary Key from Insert Thank you for the affirmation.... There's a couple of things that are different. First, your query invokes a function against a DB2 system table (by appearances anyway. This is similar to Oracle, but not MS SQL Server. Secondly, you created a new platform. So you must have changed the DTD. The only other thing I can think of is that the MS JDBC classes don't work right. I've been using the same ones for a few years now, so this weekend I'll upgrade. I'm not holding out much hope since these are the same JDBC files I used for my test code. Thanks again! >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 12:57:23 -0600 > >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] > _________________________________________________________________ Is your PC infected? Get a FREE online computer virus scan from McAfee� Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 --------------------------------------------------------------------- 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]
