You are correct - each DB tends to have something that you can use (I use a stored procedure written specifically for my DB), but I find that storing (as Ben suggested) a unique number, such as the userreference, with the new ID and then retrieving the ID using the same unique number the surest way.
I do think though that there should have been a SQL standard methodology for this process since it is an obvious need. Garth At 08:39 27/08/02 -0400, you wrote: >-----BEGIN PGP SIGNED MESSAGE----- >Hash: SHA1 > >Actually Ben, there ARE lots of way to get the ID, the unfortunate part >is that they're all different, and therefore engineers are left holding >the dirty laundry bag. If you were writing code for one DB, invariably >there is a 'clean' way to get the ID back. Oracle uses sequences, so >does Postgresql but the syntax is a bit different, MySQL has a call >called LAST_INSERT_ID() (or something like that) where you do SELECT >LAST_INSERT_ID() right after your insert, and you'll get the ID back. >MS uses @@identity... etc... you get my point. There is always a way >to get it, it's just never the same, so when you write code for a >database agnostic app... you're pretty much stuck using your own ID >generation methodology. > >R > > >On Tuesday, August 27, 2002, at 08:13 PM, Ben Johansen wrote: > >>Hi, >> >>You would think that after all this time of people needing to get the >>unique auto generated id, the database creators would have said, >>ah-hah, >>this is something that would be useful. >> >>The way I do it is, kinda convoluted but works without fail >>I insert the <@USERREFERENCE> value in a field and table that has the >>auto-increment. There is a trigger on the table that reads the ID and >>gets the ReferenceArg and places them into another table where the app >>goes and gets it. >> >>What would really be cool is to do an insert and have the ID returned, >>naah! Makes to much sense and beside there is all these workarounds >>that >>people have written over the years and we don't want to upset their >>ingenuity >> >>Ok, got that off my chest, carry on ;-) >> >>Ben Johansen - http://www.pcforge.com >>Authorized Witango Reseller http://www.pcforge.com/WitangoGoodies.htm >>Latest downloads & List Archives @ http://www.witango.ws >> >> >>-----Original Message----- >>From: [EMAIL PROTECTED] >>[mailto:[EMAIL PROTECTED]] On Behalf Of Robert Sfeir >>Sent: Tuesday, August 27, 2002 4:37 PM >>To: Multiple recipients of list witango-talk >>Subject: Re: Witango-Talk: Key of Record Just Written >> >>-----BEGIN PGP SIGNED MESSAGE----- >>Hash: SHA1 >> >>yes but unfortunately, the transaction blocks in Tango 4 don't work >>right. Not WithEnterprises' fault, just something that has lingered >>for a long time in T3/T4, and depending on the DB you're still not >>assured that you get the right ID since you could be inserting from >>another taf to the same table, and since you're obviously not table >>locking, you're not guaranteed an ID. >> >>R >> >>On Tuesday, August 27, 2002, at 07:02 PM, Robert Shubert wrote: >> >>>Tom, >>> >>>Typically we: >>> >>>Begin Transaction >>>Insert Record >>>Retrieve Highest Identity >>>Close Transaction >>> >>>Robert Shubert >>>Tronics >>>Thomas Ferguson wrote: >>>> >>>>nevermind... since it was an autonum, I just retreived the last row >> >>>>and >>>>extracted the key. >>>> >>>>Any idea on how to do this without an autonum and no date/time stamp? >>>> >>>>Just curious. >>>> >>>>TIA >>>> >>>>-----Original Message----- >>>>From: [EMAIL PROTECTED] >>>>[mailto:[EMAIL PROTECTED]]On Behalf Of Thomas Ferguson >>>>Sent: Tuesday, August 27, 2002 6:06 PM >>>>To: Multiple recipients of list witango-talk >>>>Subject: Witango-Talk: Key of Record Just Written >>>> >>>>I know I've seen this before, but I can't find it in the archives... >>>> >>>>How do I get the value of the key of the record I just wrote? I'm >>>>using MS >>>>SQL with the key defined as an AutoNum (Identity). >>>> >>>>Thanks in advance >>>> >>>>Top Ferguson >>>>ARiSAR, Inc. >>>> >>>>727.577.7474 - Office >>>>727.577.7494 - Fax >>>>727.510-9863 - Mobile >>>> >>>>www.arisar.com >>>> >>______________________________________________________________________ >>>>__ >>>>TO UNSUBSCRIBE: send a plain text/US ASCII email to >>>>[EMAIL PROTECTED] >>>> with unsubscribe witango-talk in the message body >>>> >>______________________________________________________________________ >>>>__ >>>>TO UNSUBSCRIBE: send a plain text/US ASCII email to >>>>[EMAIL PROTECTED] >>>> with unsubscribe witango-talk in the message body >>_______________________________________________________________________ >>>_ >>>TO UNSUBSCRIBE: send a plain text/US ASCII email to >>>[EMAIL PROTECTED] >>> with unsubscribe witango-talk in the message body >>Robert S. Sfeir >>Senior Java Engineer >>PGP Key available at: pgpkeys.mit.edu >>KeyID: 128C88C7 >>-----BEGIN PGP SIGNATURE----- >>Version: GnuPG v1.0.7 (Darwin) >> >>iD8DBQE9bA07AP6i6RKMiMcRAqIbAKCvvgEoEFMe/UA3LwCuiwB1CVHPrQCglLtF >>oDUO4/MuFoQKnGCQDBx8hzQ= >>=B91s >>-----END PGP SIGNATURE----- >> >>_______________________________________________________________________ _ >>TO UNSUBSCRIBE: send a plain text/US ASCII email to >>[EMAIL PROTECTED] >> with unsubscribe witango-talk in the message body >> >>_______________________________________________________________________ _ >>TO UNSUBSCRIBE: send a plain text/US ASCII email to >>[EMAIL PROTECTED] >> with unsubscribe witango-talk in the message body >Robert S. Sfeir >Senior Java Engineer >PGP Key available at: pgpkeys.mit.edu >KeyID: 128C88C7 >-----BEGIN PGP SIGNATURE----- >Version: GnuPG v1.0.7 (Darwin) > >iD8DBQE9bBurAP6i6RKMiMcRAs3eAKC1sg26Y4OjQLi62xkOFR9x4EPehACeJgPD >aUCc7WTcS7hHvi86FhMcqM8= >=VYs5 >-----END PGP SIGNATURE----- > >________________________________________________________________________ >TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] > with unsubscribe witango-talk in the message body ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
