Re: last insert
En Martin A. Marques va escriure el dia 28 Jun 2000, a les 9:51: > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > >is there a way to get the value of the last inserted element of a > > >determined > > >column of a table? > > > > Depends on many things, but probably not. > > OK! Lets say I inserted a row, which has a SERIAL data column (and some other > columns), which is autoincremental, and I insert a 0! How do I get the number > that Informix put in that column to reference that row on another table? > > > -- > "And I'm happy, because you make me feel good, about me." - Melvin Udall > ----------------------------------------------------------------- > Mart'n Marqu's email: martin@math.unl.edu.ar > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > Administrador de sistemas en math.unl.edu.ar > ----------------------------------------------------------------- After the insert retrieve the value stored at sqlca.sqlerrd[2]. I suppose you work with 4gl. --------------------------------------- Isidre PONS ROCA BASE - Gesti' d'Ingressos Locals (Diputacio de Tarragona) Servei de Sistemes de Informacio Av President Lluis Companys 12-C 43005 - Tarragona SPAIN Tel # +34 977 236731 Fax # +34 977 227302 http://www.altanet.org ipons@dtgna.altanet.org ---------------------------------------
If table isn't fragmented IMHO the best way for any table is select max(rowid) from table_name if it's fragmented the common solution - using serial column as you all advise. If it's needed add this column with alter table. Isidre PONS ROCA wrote: > En Martin A. Marques va escriure el dia 28 Jun 2000, a les 9:51: > > > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > > > >is there a way to get the value of the last inserted element of a > > > >determined > > > >column of a table? > > > > > > Depends on many things, but probably not. > > > > OK! Lets say I inserted a row, which has a SERIAL data column (and some other > > columns), which is autoincremental, and I insert a 0! How do I get the number > > that Informix put in that column to reference that row on another table? > > > > > > -- > > "And I'm happy, because you make me feel good, about me." - Melvin Udall > > ----------------------------------------------------------------- > > MartМn MarquИs email: martin@math.unl.edu.ar > > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > > Administrador de sistemas en math.unl.edu.ar > > ----------------------------------------------------------------- > > After the insert retrieve the value stored at sqlca.sqlerrd[2]. > I suppose you work with 4gl. > > --------------------------------------- > Isidre PONS ROCA > BASE - GestiС d'Ingressos Locals > (Diputacio de Tarragona) > Servei de Sistemes de Informacio > Av President Lluis Companys 12-C > 43005 - Tarragona > SPAIN > Tel # +34 977 236731 > Fax # +34 977 227302 > http://www.altanet.org > ipons@dtgna.altanet.org > ---------------------------------------
Marat wrote: > > If table isn't fragmented IMHO the best way for any table is > > select max(rowid) from table_name This is NEVER good. If you are using that value as a serial in a master table it may already be that a row will live in that next slot by the time you get to add the row. If you are to use it as a foreign key in some related table are are trying to get the rowid of the just added row you may get another row's rowid that was added in the meantime. There is no value to this particular suggestion. I said it before. Just use SERIAL or SERIAL8 (if you got 'em) and live with the holes in the numbers. Or use the sequence table method and do not acquire the next number until the row is ready to commit and prevalidated, adding to the coding overhead, and live with single threading the inserts. Actually I just remembered a hybrid if you MUST have no wholes. Use a SERIAL for one unique key, but also have the table have a second column for 'customer number' or 'invoice number' which is the one that must not have holes. Then insert the row without the second id filled in, get the serial number, using the sequence table method get and update the sequence record for this table freeing the lock instantly and update your new row: UPDATE invoices SET invoice_number = :new_sequence WHERE serial_col = :new_serial_no; This will minimize the impact on concurrency and guarantee no wholes in the sequence not caused by deliberate deletions since you will know that the transaction is committed BEFORE acquiring the next number. Art S. Kagel > if it's fragmented the common solution - using serial column as you all advise. > If it's needed add this column with alter table. > > Isidre PONS ROCA wrote: > > > En Martin A. Marques va escriure el dia 28 Jun 2000, a les 9:51: > > > > > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > > > > > >is there a way to get the value of the last inserted element of a > > > > >determined > > > > >column of a table? > > > > > > > > Depends on many things, but probably not. > > > > > > OK! Lets say I inserted a row, which has a SERIAL data column (and some other > > > columns), which is autoincremental, and I insert a 0! How do I get the number > > > that Informix put in that column to reference that row on another table? > > > > > > > > > -- > > > "And I'm happy, because you make me feel good, about me." - Melvin Udall > > > ----------------------------------------------------------------- > > > Martín Marqués email: martin@math.unl.edu.ar > > > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > > > Administrador de sistemas en math.unl.edu.ar > > > ----------------------------------------------------------------- > > > > After the insert retrieve the value stored at sqlca.sqlerrd[2]. > > I suppose you work with 4gl. > > > > --------------------------------------- > > Isidre PONS ROCA > > BASE - Gestió d'Ingressos Locals > > (Diputacio de Tarragona) > > Servei de Sistemes de Informacio > > Av President Lluis Companys 12-C > > 43005 - Tarragona > > SPAIN > > Tel # +34 977 236731 > > Fax # +34 977 227302 > > http://www.altanet.org > > ipons@dtgna.altanet.org > > ---------------------------------------
"Art S. Kagel" wrote: > Marat wrote: > > > > If table isn't fragmented IMHO the best way for any table is > > > > select max(rowid) from table_name > > This is NEVER good. If you are using that value as a serial in a Surely you are right. But THERE ARE some purposes of rowid in this context and it works. > > master table it may already be that a row will live in that next slot > by the time you get to add the row. If you are to use it as a foreign > key in some related table are are trying to get the rowid of the just > added row you may get another row's rowid that was added in the > meantime. There is no value to this particular suggestion. > > I said it before. Just use SERIAL or SERIAL8 (if you got 'em) and > live with the holes in the numbers. Or use the sequence table method > and do not acquire the next number until the row is ready to commit > and prevalidated, adding to the coding overhead, and live with single > threading the inserts. > > Actually I just remembered a hybrid if you MUST have no wholes. Use > a SERIAL for one unique key, but also have the table have a second > column for 'customer number' or 'invoice number' which is the one > that must not have holes. Then insert the row without the second id > filled in, get the serial number, using the sequence table method > get and update the sequence record for this table freeing the lock > instantly and update your new row: > > UPDATE invoices > SET invoice_number = :new_sequence > WHERE serial_col = :new_serial_no; > > This will minimize the impact on concurrency and guarantee no wholes > in the sequence not caused by deliberate deletions since you will > know that the transaction is committed BEFORE acquiring the next > number. > > Art S. Kagel > > > if it's fragmented the common solution - using serial column as you all advise. > > If it's needed add this column with alter table. > > > > Isidre PONS ROCA wrote: > > > > > En Martin A. Marques va escriure el dia 28 Jun 2000, a les 9:51: > > > > > > > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > > > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > > > > > > > >is there a way to get the value of the last inserted element of a > > > > > >determined > > > > > >column of a table? > > > > > > > > > > Depends on many things, but probably not. > > > > > > > > OK! Lets say I inserted a row, which has a SERIAL data column (and some other > > > > columns), which is autoincremental, and I insert a 0! How do I get the number > > > > that Informix put in that column to reference that row on another table? > > > > > > > > > > > > -- > > > > "And I'm happy, because you make me feel good, about me." - Melvin Udall > > > > ----------------------------------------------------------------- > > > > MartМn MarquИs email: martin@math.unl.edu.ar > > > > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > > > > Administrador de sistemas en math.unl.edu.ar > > > > ----------------------------------------------------------------- > > > > > > After the insert retrieve the value stored at sqlca.sqlerrd[2]. > > > I suppose you work with 4gl. > > > > > > --------------------------------------- > > > Isidre PONS ROCA > > > BASE - GestiС d'Ingressos Locals > > > (Diputacio de Tarragona) > > > Servei de Sistemes de Informacio > > > Av President Lluis Companys 12-C > > > 43005 - Tarragona > > > SPAIN > > > Tel # +34 977 236731 > > > Fax # +34 977 227302 > > > http://www.altanet.org > > > ipons@dtgna.altanet.org > > > ---------------------------------------
Which language are you using? i4gl and esqlc both support sqlca structutres After the insert ... if sqlca.sqlcode = 0 then let serial field = sqlca.sqlerrd[ 2 ] (i4gl) or sqlca.sqlerrd[ 1 ] (esql c) # # sqlca # code # sqlerrd 1 # sqlerrd 2 Last Serial or ISAM code # sqlerrd 3 No. of rows processed # sqlerrd 4 Est CPU cost # sqlerrd 5 Offset of Error on SQL statement # sqlerrd 6 Rowid of last row processed # sqlwarn 1 - 8 I don't think this works for ansi SQL "Marat" <maratkotik@mail.ru> wrote in message news:397FE0F6.DE3A6657@mail.ru... > "Art S. Kagel" wrote: > > > Marat wrote: > > > > > > If table isn't fragmented IMHO the best way for any table is > > > > > > select max(rowid) from table_name > > > > This is NEVER good. If you are using that value as a serial in a > > Surely you are right. But THERE ARE some purposes of rowid in this context > and it works. > > > > > master table it may already be that a row will live in that next slot > > by the time you get to add the row. If you are to use it as a foreign > > key in some related table are are trying to get the rowid of the just > > added row you may get another row's rowid that was added in the > > meantime. There is no value to this particular suggestion. > > > > I said it before. Just use SERIAL or SERIAL8 (if you got 'em) and > > live with the holes in the numbers. Or use the sequence table method > > and do not acquire the next number until the row is ready to commit > > and prevalidated, adding to the coding overhead, and live with single > > threading the inserts. > > > > Actually I just remembered a hybrid if you MUST have no wholes. Use > > a SERIAL for one unique key, but also have the table have a second > > column for 'customer number' or 'invoice number' which is the one > > that must not have holes. Then insert the row without the second id > > filled in, get the serial number, using the sequence table method > > get and update the sequence record for this table freeing the lock > > instantly and update your new row: > > > > UPDATE invoices > > SET invoice_number = :new_sequence > > WHERE serial_col = :new_serial_no; > > > > This will minimize the impact on concurrency and guarantee no wholes > > in the sequence not caused by deliberate deletions since you will > > know that the transaction is committed BEFORE acquiring the next > > number. > > > > Art S. Kagel > > > > > if it's fragmented the common solution - using serial column as you all advise. > > > If it's needed add this column with alter table. > > > > > > Isidre PONS ROCA wrote: > > > > > > > En Martin A. Marques va escriure el dia 28 Jun 2000, a les 9:51: > > > > > > > > > On Wed, 28 Jun 2000, Obnoxio The Clown wrote: > > > > > > From: "Martin A. Marques" <martin@math.unl.edu.ar> > > > > > > > > > > > > > >is there a way to get the value of the last inserted element of a > > > > > > >determined > > > > > > >column of a table? > > > > > > > > > > > > Depends on many things, but probably not. > > > > > > > > > > OK! Lets say I inserted a row, which has a SERIAL data column (and some other > > > > > columns), which is autoincremental, and I insert a 0! How do I get the number > > > > > that Informix put in that column to reference that row on another table? > > > > > > > > > > > > > > > -- > > > > > "And I'm happy, because you make me feel good, about me." - Melvin Udall > > > > > ----------------------------------------------------------------- > > > > > Mart'n Marqu's email: martin@math.unl.edu.ar > > > > > Santa Fe - Argentina http://math.unl.edu.ar/~martin/ > > > > > Administrador de sistemas en math.unl.edu.ar > > > > > ----------------------------------------------------------------- > > > > > > > > After the insert retrieve the value stored at sqlca.sqlerrd[2]. > > > > I suppose you work with 4gl. > > > > > > > > --------------------------------------- > > > > Isidre PONS ROCA > > > > BASE - Gesti' d'Ingressos Locals > > > > (Diputacio de Tarragona) > > > > Servei de Sistemes de Informacio > > > > Av President Lluis Companys 12-C > > > > 43005 - Tarragona > > > > SPAIN > > > > Tel # +34 977 236731 > > > > Fax # +34 977 227302 > > > > http://www.altanet.org > > > > ipons@dtgna.altanet.org > > > > --------------------------------------- >