I use the "Returning" SQL clause. I know this works in primebase, and should work in others. If it doesn't, any SQL server worth its salt will have a method for returning the value of an automatic counter upon insert.
It is my opinion (someone smarter may prove me wrong) that this is the best method for a couple of reasons. First, every table should have an integer counter as it's primary key. Your database actions will be much more efficient when you do this. Even if you need to have another unique identifier in the table, you should still use the integer counter as the primary key, and use the other UID as a "candidate" or secondary key. Also, the primary key should be used whenever possible to identify/retrieve/update/delete a record. Secondly, by using "Returning" or whatever method your dbms uses for retrieving the value of the automatic counter on insert, you are accomplishing the task in one action, not two or more, which is more efficient. Another tip: If you must use a unique identifier as a key, create a CHAR(32) field, and do not make it case insensitive (unless your dbms is only case insensitive like Butler). Then do a hash of something to create the UID. A hash will generate a precise 32 character string that you can then insert. I use a combination of a timestamp and a random number. Here is the code from my "createUID32" method in a tcf. It takes no parameters, just returns the UID: <@assign method$tempValue "<@currenttimestamp format=datetime:%m%d%Y%H%M%S> _<@random low=1 high=9999>"> <@assign method$returnValue <@cipher str="@@method$tempValue" action=hash>> This takes a timestamp to the second, adds a random number between 1 and 9999, and converts it to a precise 32 char string that will look something like: dbf4d89fb2dad8f9c1f2b0bde3ee34e9 A hash will always produce a 32 character alphanumeric string with no spaces. I have found this to be very efficient, and I have never gotten a duplicate. I learned some of these things by picking the brains of some guys that wrote the PrimeBase code. Hopefully it is helpful. Maybe someone has picked the brains of smarter folks, and can add to this, but this has worked for me. Robert Garcia. On Tuesday, August 27, 2002, at 03:05 PM, Thomas Ferguson wrote: > 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 > -- Robert Garcia BigHead Technology 2781 N Carlmont Pl Simi Valley, CA 93065 Phone 805.522.8577 http://www.bighead.net/ [EMAIL PROTECTED] ________________________________________________________________________ TO UNSUBSCRIBE: send a plain text/US ASCII email to [EMAIL PROTECTED] with unsubscribe witango-talk in the message body
