best way to keep track of unique id numbers on inserts

I'm making inserts into a table and I wish to be able to keep track of the unqiue id number for new records as efficiently as possible. One way we tried was to make an insert into the table and have the id field be of serial type so the database would select the next available number. But then we had to do a select statement immediately afterwards in order to find out what number the database chose for the id. The problem here is we have many simultaneous users and there is a possibility that before the select statement gets called, someone else inserts a record, and so the wrong id number is pulled out. Is there any way out of this dilema. I would think that this is a very common thing and there should already be an elegant solution. Any help would be greatly appreciated. In addition: We did have a clever solution at one point: We created a class that's sole purpose was to use a static variable to keep track of the next available id number. We used this class to get the next number when making an insert and the class worried about incrementing its static variable and keeping it unique. However, we have recently added load balancing which has distributed our application on two seperate servers. The class now has two seperate instantiations which have no way of synchronizing with eachother. We could use some distributed computing code in order to get these instances to talk to one another, but I'm thinking that that is way more complicated than what we're trying to accomplish. Thanks in advance for your help! Kevin

since you need to check against multiple users it sounds like you need a user field, and a time stamp field ( both with default USER , TODAY or current... and after an insert select (serial key ) with user = thisuser and max(timestamp) Kevin MacClay wrote: > I'm making inserts into a table and I wish to be able to keep track of the > unqiue id number for new records as efficiently as possible. One way we > tried was to make an insert into the table and have the id field be of > serial type so the database would select the next available number. But > then we had to do a select statement immediately afterwards in order to find > out what number the database chose for the id. The problem here is we have > many simultaneous users and there is a possibility that before the select > statement gets called, someone else inserts a record, and so the wrong id > number is pulled out. Is there any way out of this dilema. I would think > that this is a very common thing and there should already be an elegant > solution. Any help would be greatly appreciated. > > In addition: We did have a clever solution at one point: We created a > class that's sole purpose was to use a static variable to keep track of the > next available id number. We used this class to get the next number when > making an insert and the class worried about incrementing its static > variable and keeping it unique. However, we have recently added load > balancing which has distributed our application on two seperate servers. > The class now has two seperate instantiations which have no way of > synchronizing with eachother. We could use some distributed computing code > in order to get these instances to talk to one another, but I'm thinking > that that is way more complicated than what we're trying to accomplish. > > Thanks in advance for your help! > > Kevin

You should check the sqlca.sqlerrd internal variable. It will give you the 'serial' value that was assigned. Use this instead of a 'select' of the table. rick