Re: SQL Problem with serial

Posted in 2000

Octav Chiriac — Wed, 26 Jan 2000 11:33:09 +0200 — source: Informix-list mailing list archive (1991-1998)

I'm not sure what are you trying to do here, but I think that dbinfo('sqlca.sqlerrd1') will get you the last inserted serial value. Just put: let new_part_id = dbinfo('sqlca.sqlerrd1'); right after the insert. One question: don't you have problems with your zeitstempel (i mean the evaluation time of 'current year to second'??? I don't remember quite well, but there were some problems with this. If you do, you could put a default value for date column and not list it in the insert statement. Best Regards, Octav On Wed, Jan 26, 2000 at 09:28:31AM +0100, Harald Ruf wrote: > > --------------105BAD1B55984DB642001F77 > Content-Type: text/plain; charset=us-ascii > Content-Transfer-Encoding: 7bit > > Hello, > I have a problem with the following script: > create procedure insert_firma(.....> > define new_part_id integer; > define zeitstempel datetime year to second; > > let zeitstempel = current year to second; > > > insert into partner (part_id, number, date) values(0, 1,> zeitstempel); > > -- weil Firma -> part_in = 1 !! > select part_id into new_part_id from partner > where part_in = 1 and datum = zeitstempel; > .. > > The part_id is a serial(datatyp). I must know the new part_id > (automatically generated) after the insert. At the moment the select > (select part_id into ..) provide this, but the problem is, that if two > user call the procedure in one second, the select returns two part_ids. > Can anybody solve this problem? > > Thanks a lot. > > Harald > > > > > --------------105BAD1B55984DB642001F77 > Content-Type: text/html; charset=us-ascii > Content-Transfer-Encoding: 7bit > > <!doctype html public "-//w3c//dtd html 4.0 transitional//en"> > <html> > Hello, > I have a problem  with the following script: >     create procedure insert_firma(..... >     define new_part_id integer; >     define zeitstempel datetime year to second; >     let zeitstempel = current year to second; >   >     insert into partner (part_id, number, date)  > values(0, 1, zeitstempel); >     -- weil Firma -> part_in = 1 !! >     select part_id into new_part_id from partner >           where part_in > = 1 and datum = zeitstempel; > ... > The part_id is a serial(datatyp). I must know the new part_id (automatically > generated) after the insert. At the moment the select (select part_id into > .) provide this, but the problem is, that if two user call the procedure > in one second, the select returns two part_ids. > Can anybody solve this problem? > Thanks a lot. > Harald >   >   >  </html> > > --------------105BAD1B55984DB642001F77-- -- Octav Chiriac Phone: (373) 2 22 99 67 NetInfo S.R.L. Fax: (373) 2 21 36 59 Chisinau (373) 2 22 84 88 Moldova, Republic of mailto:com@netinfo-moldova.com