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

Check out DBINFO and sqlca structure Salvo Giubili wrote: > 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 said: > > 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? Is it April the 1st already? :o? -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien ' dire qu'il faut fermer sa gueule" - Coluche did i mention i like nulls? heck, i even go so far as to say that all columns in a table except the primary key could/should be nullable. this has certain advantages, for example, if you need to insert a child record and you don't have a parent row for it, just do an insert into the parent table with the primary key value (everything else null), and voila, relational integrity is preserved. but this is, admittedly, a bit controversial among modellers. --r937, dbforums.com