Serials and Stored procedures
Paul Harman asked how, inside a stored procedure, to retrieve the SERIAL value generated by an INSERT he'd just done, worrying that a plain SELECT MAX could pick up another user's row. Answer: use DBINFO('sqlca.sqlerrd1') immediately after the singleton INSERT, either returned directly or assigned to a variable. Others confirmed sqlerrd is per-session, so concurrent inserts by other users can't corrupt the value; it must be read immediately because a later SQL statement overwrites the field. Informix's own online manuals were recommended as the SPL reference.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Firstly, can anyone recommend a good SPL reference manual, so I don't have to ask these silly questions? };*) What is the best way to acquire the serial value that the INSERT you just performed created, while inside a stored procedure? I can't believe you have to do the SELECT DBINFO nonsense, there are ways around it in all the other programming languages I've used - or is that just because they've done this call "behind my back"? Besides, what if someone else gets in there first with an insert before I do my select? Paul
Thomas Parsli <thomas.parsli@startsiden.no> wrote in message news:1z6bn9a2.fsf@localhost.localdomain... > "Paul Harman" <paul@kasterborus.demon.co.uk> writes: > > > Firstly, can anyone recommend a good SPL reference manual, so I don't have > > to ask these silly questions? };*) > > Informix' own are actully great... > > PDF: > http://www.informix.com/documentation/ > > HTML: > http://examples.informix.com/reference/sqlt/intro.fm1.html Thanks for that, I;m browsing through them right now };*) > > What is the best way to acquire the serial value that the INSERT you just > > performed created, while inside a stored procedure? I can't believe you have > > to do the SELECT DBINFO nonsense, > > Do you like Sybase's SELECT @@foobar nonsense better? Not be the looks of it };*) I've only got experience of Informix (and a little mySql). > > there are ways around it in all the other > > programming languages I've used - or is that just because they've done this > > call "behind my back"? Besides, what if someone else gets in there first > > with an insert before I do my select? > > You're interested in _your_ sessions serial from _your_ sessions > insert -right? Exactly. Paul
"Paul Harman" <paul@kasterborus.demon.co.uk> writes: > Firstly, can anyone recommend a good SPL reference manual, so I don't have > to ask these silly questions? };*) Informix' own are actully great... PDF: http://www.informix.com/documentation/ HTML: http://examples.informix.com/reference/sqlt/intro.fm1.html > What is the best way to acquire the serial value that the INSERT you just > performed created, while inside a stored procedure? I can't believe you have > to do the SELECT DBINFO nonsense, Do you like Sybase's SELECT @@foobar nonsense better? > there are ways around it in all the other > programming languages I've used - or is that just because they've done this > call "behind my back"? Besides, what if someone else gets in there first > with an insert before I do my select? You're interested in _your_ sessions serial from _your_ sessions insert -right? Thomas
"Paul Harman" <paul@kasterborus.demon.co.uk> writes: > Thomas Parsli <thomas.parsli@startsiden.no> wrote in message [SNIP] > > You're interested in _your_ sessions serial from _your_ sessions > > insert -right? > > Exactly. RTFM...what the f...Bloody hell! That's where Sybase got it right and Informix didn't obvious. You could lock the whole friggin' table on your inserts though... Better look what other (more competent than me;) have said about this. Thomas
RETURN DBINFO('sqlca.sqlerrd1'); /* Serial # of the inserted row */ immediately after your singleton INSERT statement will return the serial value. Or you could store it by assigning DBINFO('sqlca.sqlerrd1') to a variable. Rudy Paul Harman wrote: > Firstly, can anyone recommend a good SPL reference manual, so I don't have > to ask these silly questions? };*) > > What is the best way to acquire the serial value that the INSERT you just > performed created, while inside a stored procedure? I can't believe you have > to do the SELECT DBINFO nonsense, there are ways around it in all the other > programming languages I've used - or is that just because they've done this > call "behind my back"? Besides, what if someone else gets in there first > with an insert before I do my select? > > Paul
Thomas Parsli (thomas.parsli@startsiden.no) wrote: : "Paul Harman" <paul@kasterborus.demon.co.uk> writes: : > there are ways around it in all the other : > programming languages I've used - or is that just because they've done this : > call "behind my back"? Besides, what if someone else gets in there first : > with an insert before I do my select? : You're interested in _your_ sessions serial from _your_ sessions : insert -right? : Thomas When you use DBINFO, you will get the last serial value from _your_ session. Even if other people have made inserts after this session's, you will have the correct number. -- Rob Wilson rwilson@ntsource.com
Rob Wilson <rwilson@ntsource.com> wrote in message news:m7Vq4.886$Mn3.7118@newsfeed.slurp.net... > When you use DBINFO, you will get the last serial value from _your_ > session. Even if other people have made inserts after this session's, > you will have the correct number. Excellent, Just what I needed to know };*) Paul
rwilson@ntsource.com (Rob Wilson) writes: > Thomas Parsli (thomas.parsli@startsiden.no) wrote: > : "Paul Harman" <paul@kasterborus.demon.co.uk> writes: > > : > there are ways around it in all the other > : > programming languages I've used - or is that just because they've done this > : > call "behind my back"? Besides, what if someone else gets in there first > : > with an insert before I do my select? > > : You're interested in _your_ sessions serial from _your_ sessions > : insert -right? > > : Thomas > > When you use DBINFO, you will get the last serial value from _your_ > session. Even if other people have made inserts after this session's, > you will have the correct number. From the manual (Guide to SQL, Dec. 1999): The 'sqlca.sqlerrd1' option returns a single integer that provides the last serial value that is inserted into a table. To ensure valid results, use this option immediately following a singleton INSERT statement that inserts a single row with a serial value into a table. Why the _ensure_ and _immediately_ if it's session dependant? Thomas
Thomas Parsli (thomas.parsli@startsiden.no) wrote: : rwilson@ntsource.com (Rob Wilson) writes: : > When you use DBINFO, you will get the last serial value from _your_ : > session. Even if other people have made inserts after this session's, : > you will have the correct number. : From the manual (Guide to SQL, Dec. 1999): : The 'sqlca.sqlerrd1' option returns a single integer that provides the last : serial value that is inserted into a table. To ensure valid results, use this : option immediately following a singleton INSERT statement that inserts a single : row with a serial value into a table. : Why the _ensure_ and _immediately_ if it's session dependant? : Thomas According to the Guide to ESQL/C, 9.2 When SQLCODE contains an error code, this field contains either zero or an additional error code, called the ISAM error code, that explains the cause of the main error. After a successful insert operation of a single row, this field contains the value of any SERIAL value generated for that row. I would venture a guess that you must do it immediately because this will get set to a value (for sure when the next statement results in an error) if you execute another SQL statement. However, since the sqlerrd structure is unique to a session, it will contain the serial value you would expect immediately after running the insert. -- Rob Wilson rwilson@ntsource.com