Re: Retrieving SERIAL identifiers in a concurrent environment
If you are using Esqlc, then SQLERRD[2] is your friend, as someone pointed out. If you are using ADO, then the Refresh() method of the recordset object is your friend if the odbc driver supports it. Otherwise, get a guid, put it into a column before insert, find the row, get the serialid, update the row with correct data in the column you hosed. Nasty. ----- Original Message ----- From: "Simmons, Keith" <keith.simmons@office2office.biz> To: <informix-list@iiug.org> Sent: Sunday, January 01, 2006 11:25 AM Subject: RE: Retrieving SERIAL identifiers in a concurrent environment > Salvo > > Havn't got manuals available, but think the SQLCA record is your friend. > This contains the return code of any sql statement and item SQLERRD[2] > contains the serial of the last inserted record. > > Keith > >> -----Original Message----- >> From: Salvo Giubili [SMTP:colchicumNoSpmAbse@interfree.it] >> Sent: Sunday, January 01, 2006 3:54 PM >> To: informix-list@iiug.org >> Subject: Retrieving SERIAL identifiers in a concurrent environment >> >> Hello all >> >> Working on IDS 9.30, I need to retrieve the identifier (say: >> SERIAL-typed ID as primary key) of the latest row immediately after >> having inserted it, so that I'm able to refer it in related tables. >> >> Till now I've used the simple-though-awkward (brute force!) "SELECT >> FIRST 1 ID FROM myTable ORDER BY ID DESC" SQL statement just after the >> INSERT statement. Now I've to cope with concurrent users, and such an >> approach clearly isn't feasable. >> >> I'd prefer not to lock the entire table but, although I skimmed the web >> for documentation, I actually feel uncertain yet... >> >> What's the most reliable method for retrieving last-generated >> identifiers in an Informix concurrent environment? >> >> Your hints 'n' tips are much appreciated! >> Many thanks >> _______________________________________________ >> Informix-list mailing list >> Informix-list@iiug.org >> http://www.iiug.org/mailman/listinfo/informix-list > > ********************************************************************************** > This message is sent in strict confidence for the addressee only. It may > contain legally privileged information. The contents are not to be > disclosed > to anyone other than the addressee. Unauthorised recipients are requested > to preserve this confidentiality and to advise the sender immediately of > any > error in transmission. > This footnote also confirms that this email message has been swept for the > presence of computer viruses, however we cannot guarantee that this > message > is free from such problems. > ********************************************************************************** > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > __________ NOD32 1.1347 (20051230) Information __________ > > This message was checked by NOD32 antivirus system. > http://www.eset.com > >
Bill Hamilton wrote: > If you are using Esqlc, then SQLERRD[2] is your friend, as someone > pointed out. Excuse me, Bill: can I invoke SQLERRD[2] as a common SQL query, just as follows: SELECT SQLCA.SQLERRD[2]; > If you are using ADO, then the Refresh() method of the recordset object > is your friend if the odbc driver supports it. > Otherwise, get a guid, put it into a column before insert, find the row, > get the serialid, update the row with correct data in the column you > hosed. Nasty. Mmmh... weird (redundant...) but effective! Thanks, Bill. > ----- Original Message ----- From: "Simmons, Keith" > <keith.simmons@office2office.biz> > To: <informix-list@iiug.org> > Sent: Sunday, January 01, 2006 11:25 AM > Subject: RE: Retrieving SERIAL identifiers in a concurrent environment > >> Salvo >> >> Havn't got manuals available, but think the SQLCA record is your friend. >> This contains the return code of any sql statement and item SQLERRD[2] >> contains the serial of the last inserted record. >> >> Keith >> >>> -----Original Message----- >>> From: Salvo Giubili [SMTP:colchicumNoSpmAbse@interfree.it] >>> Sent: Sunday, January 01, 2006 3:54 PM >>> To: informix-list@iiug.org >>> Subject: Retrieving SERIAL identifiers in a concurrent environment >>> >>> Hello all >>> >>> Working on IDS 9.30, I need to retrieve the identifier (say: >>> SERIAL-typed ID as primary key) of the latest row immediately after >>> having inserted it, so that I'm able to refer it in related tables. >>> >>> Till now I've used the simple-though-awkward (brute force!) "SELECT >>> FIRST 1 ID FROM myTable ORDER BY ID DESC" SQL statement just after the >>> INSERT statement. Now I've to cope with concurrent users, and such an >>> approach clearly isn't feasable. >>> >>> I'd prefer not to lock the entire table but, although I skimmed the web >>> for documentation, I actually feel uncertain yet... >>> >>> What's the most reliable method for retrieving last-generated >>> identifiers in an Informix concurrent environment? >>> >>> Your hints 'n' tips are much appreciated! >>> Many thanks
Salvo Giubili <colchicumNoSpmAbse@interfree.it> schrieb: >Excuse me, Bill: can I invoke SQLERRD[2] as a common SQL query, just as >follows: > >SELECT SQLCA.SQLERRD[2]; That depends on your environment. If you are using ESQL/C or I4GL, then you can access the SQLCA structure directly. Do this immediately after the INSERT statement that generated the next serial value. BTW, in C Programs, the value is in SQLCA.SQLERRD[1], in 4GL, it is in SQLCA.SQLERRD[2] since 4GL uses 1 as the base for arrays while C is 0-based. Otherwise, use the built-in DBINFOfunction: SELECT DBINFO('sqlca.sqlerrd[1]') FROM systables WHERE tabname = 'systables' HTH, Richard
Richard Spitz wrote: > Salvo Giubili <colchicumNoSpmAbse@interfree.it> schrieb: > >>Excuse me, Bill: can I invoke SQLERRD[2] as a common SQL query, just as >>follows: >> >>SELECT SQLCA.SQLERRD[2]; > > > That depends on your environment. If you are using ESQL/C or I4GL, then you > can access the SQLCA structure directly. Do this immediately after the INSERT > statement that generated the next serial value. BTW, in C Programs, the value > is in SQLCA.SQLERRD[1], in 4GL, it is in SQLCA.SQLERRD[2] since 4GL uses 1 as > the base for arrays while C is 0-based. > > Otherwise, use the built-in DBINFOfunction: > SELECT DBINFO('sqlca.sqlerrd[1]') > FROM systables > WHERE tabname = 'systables' > > HTH, Richard I'll opt for the last one, as I'm working via ADO/ODBC. Mmmmany thanks, Richard and all the other nice c.d.i. guys!