> From: Jeffrey Oelschlegel [mailto:[EMAIL PROTECTED]] > Sent: 28 August 2002 20:37 [...]
> 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. [...] <DISCLAIMER> I know bugger all about IDENTITY COLUMNS and TRIGGERS; all the above info comes from quickly scanning the MSSQL docs, and quickly experimenting. Take it all with a pinch of salt. </DISCLAIMER> There is no easy way to do what you want to do. As you have found out (and for others that haven't), instance callbacks on your persistent object will allow you to retrieve an id assigned by the database (http://jakarta.apache.org/ojb/tutorial3.html#instance%20callbacks) The main issue, as you have correctly ascertained, is that MS SQL will not allow you to specify any value for an IDENTITY column on an insert - even a null. Your possible workaround would probably work; however, I suggest that it would be a great deal of work, and not a very elegant solution. You already have a cross-db solution (in that in Oracle you use a stored procedure to gain the primary key value). My approach would be to do something similar in MSSQL : if at all possible (and I admit it may not be, due to already-deployed apps), remove the IDENTITY columns e.g. remove the IDENTITY from the column defitinition for the PK). Create a stored procedure to get/update the next PK for a named table. Create an insert trigger (or maybe an INSTEAD OF INSERT trigger, depending on your version of MSSQL) that uses the stored procedure to get the value of pk, and uses that instead of the supplied on. In fact, an INSTEAD OF INSERT trigger may be usable with IDENTITY columns - maybe worth a bit more research. Hope that helps a little bit, Cheers, Charles. This email and any attachments are strictly confidential and are intended solely for the addressee. If you are not the intended recipient you must not disclose, forward, copy or take any action in reliance on this message or its attachments. If you have received this email in error please notify the sender as soon as possible and delete it from your computer systems. Any views or opinions presented are solely those of the author and do not necessarily reflect those of HPD Software Limited or its affiliates. At present the integrity of email across the internet cannot be guaranteed and messages sent via this medium are potentially at risk. All liability is excluded to the extent permitted by law for any claims arising as a re- sult of the use of this medium to transmit information by or to HPD Software Limited or its affiliates. -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
