Re: Last inserted id

> -----Original Message-----
> From: Geoffrey D. Bennett [mailto:g(at)netcraft(dot)com(dot)au]
> Sent: 12 November 2001 08:55
> To: Dave Page
> Cc: 'Simeo Reig'; 'pgsql-odbc(at)postgresql(dot)org'
> Subject: Re: [ODBC] Last inserted id
>
>
> On Mon, Nov 12, 2001 at 08:23:08AM -0000, Dave Page wrote:
> > > -----Original Message-----
> > > From: Simeo Reig [mailto:simreig(at)terra(dot)es]
> > > Sent: 11 November 2001 22:54
> > > To: Dave Page
> > > Subject: Re: [ODBC] Last inserted id
> > >
> > >
> > > I had think that the problem was this but I believed that was
> > > possible to make a mistake because I'm newer with ADO.
> What must I
> > > do ? I see two possibilities:
> > >
> > > A) Make a select NEXTVAL from sequence, and
> > > insert this value in the id
> > >
> > > B) Insert row and after make a CURRVAL
> > >
> > > What option Do you use ?
> >
> > Use option A), it's multi-user safe whereas the other method isn't.
>
> Unless I'm missing something, option B _is_ multi-user safe.
> currval() doesn't return the "current" value of the sequence
> (like "select * from my_seq" would) -- it returns the last value that
> nextval() gave that session (hence, it isn't defined until
> that session does a nextval()).

The problem is that Microsoft's ActiveX Data Objects (ADO) will not query
the database for additional values in a newly inserted row (in this case,
the id which is inserted as a default by the backend).

I suggest (and use) option A).

As I understand option B), Simeo is proposing to get the current value from
the sequence, insert the complete row client side (so ADO knows the ID),
then set the current value of the sequence (SELECT setval('sequence', 123)).
This is not multiuser safe as a second user may increment the sequence value
in the middle of the first users' 3 queries.

Regards, Dave.