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. **********************************************************************************

So, in order to exploit it as a SQL query result, I should address a statement such as this (please gimme your advice...): SELECT SQLCA.SQLERRD[2]; Is that right? Furthermore: is that concurrency-proof (is it isolated from concurrent connections)? Thanks, Keith Simmons, Keith wrote: > 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?