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

In article <8geqp8$f2o$1@news.xmission.com>, kmacclay@vantage.com says... >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. You didn't mention the environment you're working under; Informix can give you the last serial number inserted via SELECT DBINFO('sqlca.sqlerrd1'). If you're in an environment where you have little control over the actual SQL that gets sent to the engine, this may be a bit difficult to use. The older Visual Basic database controls were downright nasty about this, but the newer controls should allow you enough control over the database connection to allow you to use DBINFO select. -- William Harris william@carsinfo.com

NO NO NO. You do not have to do a SELECT to get the SERIAL value that you just inserted. Informix returns it as part of the error reporting structure when the INSERT it executed. If you are using ESQL/C just do: EXEC SQL INSERT INTO ...... if (sqlca.sqlcode == 0) last_inserted_serial = sqlca.sqlerrd[1]; ... In 4GL: INSERT INTO ..... IF (sqlca.sqlcode = 0) LET last_inserted_serial = sqlca.sqlerrd[2] ... In any other environment where the sqlca structure is not visible you can use the DBINFO function for the same purpose: INSERT INTO ...... SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1 Art S. Kagel 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

Two ways: Generate a callable program which app calls for the unique key. 1. Implement a scheme with datetime (long int secs since 1980) , process id, and seq (short) from 0 to 65000. 1st call gets time of day, and sets seq to 0, then each subsequent call increments the seq by 1. When you roll over, refresh datetime. or Implement a message queue with the unique sequence of your definition. A generate program populates the message queue. Say 500 messages, when the queue is full the generator goes to sleep, and is awakened when the queue needs more messages. The apps get their next number of the message queue. The generater needs a file to keep track of unique ids used. Kevin Macclay <kmacclay@vantage.com> wrote in message news:8geqp8$f2o$1@news.xmission.com... > > 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 > >

Informix provides several mechanisms for retrieving the generated serial value following an insert: 1) If you are using ESQL/C, the serial number assigned to the record is made available via the sqlca.sqlerrd[1] field. This is documented in the Informix-ESQL/C Programmer's Manual in Chapter 8. 2) If you use the ODBC API, the following function will retrieve the serial value if called immediately following the insertion: rc = SQLGetStmtAttr(hstmt_, SQL_GET_SERIAL_VALUE, &serial, SQL_IS_INTEGER, NULL); 3) The following query will also do the trick: SELECT UNIQUE DBINFO("sqlca.sqlerrd1") FROM systables WHERE tabid=1; Hope this helps! ...Jake >>>>> "KM" == Kevin Macclay <kmacclay@vantage.com> writes: KM> I'm making inserts into a table and I wish to be able to keep track KM> of the unqiue id number for new records as efficiently as possible. KM> One way we tried was to make an insert into the table and have the id KM> field be of serial type so the database would select the next KM> available number. But then we had to do a select statement KM> immediately afterwards in order to find out what number the database KM> chose for the id. The problem here is we have many simultaneous KM> users and there is a possibility that before the select statement KM> gets called, someone else inserts a record, and so the wrong id KM> number is pulled out. Is there any way out of this dilema. I would KM> think that this is a very common thing and there should already be an KM> elegant solution. Any help would be greatly appreciated. KM> In addition: We did have a clever solution at one point: We created a KM> class that's sole purpose was to use a static variable to keep track KM> of the next available id number. We used this class to get the next KM> number when making an insert and the class worried about incrementing KM> its static variable and keeping it unique. However, we have recently KM> added load balancing which has distributed our application on two KM> seperate servers. The class now has two seperate instantiations KM> which have no way of synchronizing with eachother. We could use some KM> distributed computing code in order to get these instances to talk to KM> one another, but I'm thinking that that is way more complicated than KM> what we're trying to accomplish. KM> Thanks in advance for your help! KM> Kevin -- Jake Colman Principia Partners LLC Phone: (201) 946-0300 Harborside Financial Center Fax: (201) 946-0320 902 Plaza II Beeper: (800) 928-4640 Jersey City, NJ 07311 E-mail: colman@ppllc.com E-mail: jcolman@jnc.com web: http://www.ppllc.com microsoft: "where do you want to go today?" linux: "where do you want to go tomorrow?" BSD: "are you guys coming, or what?"

Use a SERIAL plus a SMALLINT or small CHAR column indicating the machine or location where the record originated. This is the best solution. Art S. Kagel cdlvj wrote: > > Two ways: > Generate a callable program which app calls for the unique key. > 1. Implement a scheme with datetime (long int secs since 1980) , process id, > and seq (short) from 0 to 65000. 1st call gets time of day, and sets seq to > 0, > then each subsequent call increments the seq by 1. When you roll over, > refresh datetime. > > or > Implement a message queue with the unique sequence of your definition. A > generate program populates the message queue. Say 500 messages, when the > queue is full the generator goes to sleep, and is awakened when the queue > needs more messages. The apps get their next number of the message queue. > The generater needs a file to keep track of unique ids used. > > Kevin Macclay <kmacclay@vantage.com> wrote in message > news:8geqp8$f2o$1@news.xmission.com... > > > > 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 > > > >

"Art S. Kagel" <kagel@bloomberg.net> writes: > Use a SERIAL plus a SMALLINT or small CHAR column indicating the machine or > location where the record originated. This is the best solution. > > Art S. Kagel [SNIP] > > Kevin Macclay <kmacclay@vantage.com> wrote in message > > news:8geqp8$f2o$1@news.xmission.com... > > > > > > 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. Does this _really_ (REALLY) mean that it (dbinfo-whatever) is _not_ connection -orientated? Boy, even Sybase does that right... Thomas

NO, of course DBINFO is connection specific in it returning of SERIAL values. I was answering another post where the user was looking for a way to differentiate rows from various sources AND get a unique id AND replicate on multiple peers. Similar subject as one I'd seen the other day and did not have time to answer then and I did not read the whole post. OOPS. Art S. Kagel Thomas Parsli wrote: > > "Art S. Kagel" <kagel@bloomberg.net> writes: > > > Use a SERIAL plus a SMALLINT or small CHAR column indicating the machine or > > location where the record originated. This is the best solution. > > > > Art S. Kagel > > [SNIP] > > > > Kevin Macclay <kmacclay@vantage.com> wrote in message > > > news:8geqp8$f2o$1@news.xmission.com... > > > > > > > > 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. > > Does this _really_ (REALLY) mean that it (dbinfo-whatever) is _not_ connection > -orientated? > > Boy, even Sybase does that right... > > Thomas

Thomas Parsli wrote: [SNIP] > [SNIP] > > > > Kevin Macclay <kmacclay@vantage.com> wrote in message > > > news:8geqp8$f2o$1@news.xmission.com... > > > > > > > > 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. > > Does this _really_ (REALLY) mean that it (dbinfo-whatever) is _not_ connection > -orientated? > > Boy, even Sybase does that right... > > Thomas Don't worry. It's not the case. The original poster was not talking about dbinfo. The (dbinfo-whatever) method is definitely connection orientated. of course it can be reset by something that your connection has done, but it works fine for simultaneous users. We use it with JBuilder 3 and Online 7.23 (I know, I know!) with no problems. -- Andrew Pearson: "exactly what the web needs less of".

"Art S. Kagel" <kagel@bloomberg.net> writes: > NO, of course DBINFO is connection specific in it returning of SERIAL > values. I was answering another post where the user was looking for a > way to differentiate rows from various sources AND get a unique id AND > replicate on multiple peers. Similar subject as one I'd seen the other > day and did not have time to answer then and I did not read the whole post. > OOPS. Thank god;) -I was seeing Oracle in the horizon... Thomas