Thomas, I've been struggling with a similar problem with legacy updates to SQL server using IDENTITY and using instance callbacks to requery the PK. The advice you gave will not because of a problem I brought up in a previous post about a bug in the PB that will not store a row if an Identity is not specified (either explicitly or by a sequence manager).
Thanks for all your hard work. Any advice? Wally Gelhar University of Wisconsin - Eau Claire Facilities Planning & Management -----Original Message----- From: Mahler Thomas [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 29, 2002 2:19 AM To: 'OJB Users List' Subject: RE: OJB & SQLServer & IDENTITY COLUMNS Hi > OJB [ SQL Server ] how-to request: > > First off... Im a newbie to OJB.. and just wanted to say how > slick I think OJB > is :-) My hats off Thomas and the OJB team! thanks ! Please have a look at the last section of tutorial3.html. It covers the instance callback stuff. The example given is a mechanism to get along with IDENTITY COLUMNS, that are automatically filled on inserting new rows. The idea is to: - store the object - the db will automatically assign the value of the identity column - after inserterng lookup the row from the db and reflect the value from the identity column back into the actual object. cheers, Thomas > (sorry for the length of this post in advance and I hope I'm > not asking the > obvious) > > Im attempting to use OJB on a project that will deal with > legacy tables using > both ORACLE and SQL Server. > The tables will be accessed by both older applications and > newer applications > that utilize OJB and therefore I need them > to interoperate. The 'issue' is with generating foreign keys. > > [ Background information ] > The old system uses STORED procedures for inserts on tables. > On the ORACLE > side, it uses an ORACLE sequence (per table) > to generate a unique ID. On the SQL Server side however, it > uses the SQL > Server's IDENTITY COLUMN 'feature'. > It was very simple to implement the ORACLE solution. I > simply implemented a new > sequence manager that calls a stored procedure (creating it > on the fly if necessary) that gets the next value from the > oracle sequence > associated with the table. > > [Problem statement] > However in trying to implement the SQLServer solution I have > run into problems. > SQL Server does not like you to specify the > IDENTITY COLUMN on the INSERT statement for a new object. It > will throw the > following exception > > java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for > JDBC][SQLServer] > Cannot insert explicit value for identity column in table > 'CfTextQuestion' > when IDENTITY_INSERT is set to OFF. > > [ Approaches attempted ] > My first cut was to try and obtain the information from the > 'identity' from a > SequenceManager that called a stored procedure. > > CREATE PROCEDURE PSG_CFTEXTQUESTION(@aOutId int output) as BEGIN > SELECT @aOutId=IDENT_CURRENT( 'CfTextQuestion' ) + IDENT_INCR( > 'CfTextQuestion' ) > END > > However, this was inherently unsafe because it would be > possible to have the > same ID returned (if the legacy code inserted something into the > table after you executed the stored procedure - and other > issues). And... as > stated above... when OJB attempted to execute the insert > statement... I got the > aforementioned exception. I did not see a facility where > you could 'increment' > the IDENTITY column - counter (gee that would have been to easy) > > [ 2nd approach ] > So.. then I investigated SETTING the IDENTITY INSERT to off > on SQL Server... > via making the Persistent Class > implement the PersistenceBrokerAware class... and then in the > > BEFORE STORE... turning ON the IDENTITY INSERT > AFTER STORE ... turning OFF the IDENTITY INSERT > > There was a statement in the SQL Server docs that IDENTY > INSERT ON can only be > active on ONE TABLE at a time (arghhhh). > > However.. this solution isnt elegant / valid because (a) I > still have the issue > with the stored procedure being 'unsafe' and (b) it would mean that > special code would need to be put in to check which platform > you are on before > executing the 'BEFORE STORE' & 'AFTER STORE' > > [ Questions ] < Keeping in mind that I have been using OJB > for approximately a > week... > > > 1. It would appear that it is not possible to have a COLUMN > that is specified > as an IDENTITY COLUMN -- AND -- in the OJB repository > because one would always get the above exception for an > INSERT statement. > In my case.. since the primary key is the > IDENTITY column I need to have the field specified in > the OJB repository > > Unfortunately for me... I have to deal with legacy > tables and hence 'the > legacy way' of generating unique id's. I like the OJB way > much better! > > Has anyone run into this problem out there? I would > assume this is a > common way under SQL Server to generate primary keys.. and > since SQL Server is very similar to Sybase... they may > have the same > facility (but im not familiar with it.. and therefore cant > say for sure) > Oracle's approach to sequence generators seems much more sane. > > If so... how did you solve the problem? > > [ Possible solutions -- IFF there isnt a nicer way to solve > the problem] > > [ I realize this may seem specific to one database however.. > im trying to avoid > having different repository files based on different database > platforms ] > > I was thinking on playing around and attempting to put in a > facility that on a > field you could specify the 'identity' ness or > something similar so that on an insert statement... it would > basically build an > INSERT statement without the fields specified > as identity-column="true" ... then after generating the > statement automatically > re-read the row and update the object's state with the > value(s) of identity columns... Since Im a newbie with OJB > however.. i have no > idea yet how 'sane' / 'doable' this approach is... or ramifications > in regard to other more complex O/R mappings. > > Or am I just missing the boat and there is a simpler solution? > > <class-descriptor class="com.mactiveinc.po.TextQuestion" > table="CfTextQuestion"> > > <field-descriptor id="1" > name="id" > column="ID" > jdbc-type="INTEGER" > primarykey="true" > autoincrement="true" > identity-column="true" /> > . > > </class-descriptor> > > Thanks in advance for any information... > Jeff > > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
