Thread: Last inserted id
Hi, I have this simple code that uses ADO to insert a row in a test table
with a serial id and a varchar,
after insert I can obtain varchar's value but I *can't* obtain id's value
of
this record. What I'm doing wrong ??
Thanks!
*Table definition:
mydatabase-# \d societats;
idsocietat | integer | not null default
nextval('"societats_idsocietat_seq"'::text)
nomsocietat | character varying(50) |
capitalsocial | double precision | default 0
*Postgres 7.1.3 under FreeBSD 4.4
*VB code
Private Sub Command3_Click()
' Dims omited
Set Cnxn = New ADODB.Connection
strCnxn = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=mysource"
Cnxn.Open strCnxn
Set rstEmployees = New ADODB.Recordset
strSQL = "societats"
rstEmployees.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic,
adCmdTable
strFirstName = Trim(InputBox("Put name:"))
rstEmployees.AddNew
rstEmployees!nomsocietat = strFirstName
rstEmployees.Update
' Show the newly added data
MsgBox "New record: " & rstEmployees!idsocietat & " " &
rstEmployees!nomsocietat
'''''rstEmployees!idsocietat returns nothing !!!!!!!!!!!!!!
rstEmployees.Close
Cnxn.Close
Set rstEmployees = Nothing
Set Cnxn = Nothing
End Sub
> -----Original Message-----
> From: Simeo Reig [mailto:simreig@terra.es]
> Sent: 10 November 2001 20:56
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Last inserted id
>
>
> Hi, I have this simple code that uses ADO to insert a row in
> a test table with a serial id and a varchar, after insert I
> can obtain varchar's value but I *can't* obtain id's value of
> this record. What I'm doing wrong ?? Thanks!
>
>
> *Table definition:
>
> mydatabase-# \d societats;
> idsocietat | integer | not null default
> nextval('"societats_idsocietat_seq"'::text)
> nomsocietat | character varying(50) |
> capitalsocial | double precision | default 0
>
> *Postgres 7.1.3 under FreeBSD 4.4
>
>
> *VB code
>
> Private Sub Command3_Click()
>
> ' Dims omited
> Set Cnxn = New ADODB.Connection
> strCnxn = "Provider=MSDASQL.1;Persist Security
> Info=False;Data Source=mysource"
> Cnxn.Open strCnxn
> Set rstEmployees = New ADODB.Recordset
> strSQL = "societats"
>
> rstEmployees.Open strSQL, strCnxn, adOpenKeyset,
> adLockOptimistic, adCmdTable
>
> strFirstName = Trim(InputBox("Put name:"))
> rstEmployees.AddNew
> rstEmployees!nomsocietat = strFirstName
> rstEmployees.Update
>
> ' Show the newly added data
> MsgBox "New record: " & rstEmployees!idsocietat & " " &
> rstEmployees!nomsocietat
>
> '''''rstEmployees!idsocietat returns nothing !!!!!!!!!!!!!!
>
> rstEmployees.Close
> Cnxn.Close
> Set rstEmployees = Nothing
> Set Cnxn = Nothing
>
> End Sub
>
ADO doesn't re-query the database to get the true representation of the new
row. The common way around this problem is to manually get the new ID first
in a seperate query, and then insert that value as well. The other
alternative is to refresh the recordset after the insert, however this often
isn't desirable.
Regards, Dave.
> -----Original Message----- > From: Simeo Reig [mailto:simreig@terra.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. > > Another question please : Are you pleased with Postgres ? > I have to make a project with almost 50 tables (five with > arround 100.000 rows), and 20 concurrent users. Can postgres > make a good job? I'm project lead for pgAdmin II (pgadmin.postgresql.org) so I'm probably a bit biased :-) but the main server I run at work hosts about 12 databases for interactive (VB) applications, PHP apps and also logs data from 3 PBXs. Most of the databases have at least 10 tables in them (a couple have about 40) and I have some tables over 1.7 million rows. In short, PostgreSQL does a great job for me and I use it wherever I can (incidently, I also have Informix, MS-SQL & Unidata servers here which I could have used instead of PostgreSQL). Regards, Dave. > Thanks Dave > > Simeó Reig > Barcelona (Spain) > > > ADO doesn't re-query the database to get the true > >representation of > > the > new > > row. The common way around this problem is to manually >get > the new ID > first > > in a seperate query, and then insert that value as well. The >other > > alternative is to refresh the recordset after the insert, >however > > this > often > > isn't desirable. > > > > Regards, Dave. > > >
On Mon, Nov 12, 2001 at 08:23:08AM -0000, Dave Page wrote: > > -----Original Message----- > > From: Simeo Reig [mailto:simreig@terra.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()). I have no idea what ADO is :-), so there may be other issues here, but I don't think multi-user access is one of them. -- Geoffrey D. Bennett, RHCE, RHCX geoffrey@netcraft.com.au Senior Systems Engineer http://www.netcraft.com.au/geoffrey/ NetCraft Australia Pty Ltd http://www.netcraft.com.au/linux/
> -----Original Message-----
> From: Geoffrey D. Bennett [mailto:g@netcraft.com.au]
> Sent: 12 November 2001 08:55
> To: Dave Page
> Cc: 'Simeo Reig'; 'pgsql-odbc@postgresql.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@terra.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.
Him
> 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()).
>
Actually the issue is b) is multi-user safe
*if* you have an exclusive lock on the table. If you don't it is quite
possible for a user to insert an other record between your insertion and the
currval() call
David
--
****************************************************
David Horwitz University of Cape Town
IT Officer Private Bag
Multimedia Education Group Rondebosch
7701
dhorwitz@ched.uct.ac.za SOUTH AFRICA
Tel:+27 21 650 3841 Fax:+27 21 650 5045
http://www.meg.uct.ac.za
PGP key: http://www.meg.uct.ac.za/dhorwitz/david_horwitz.acs
*************************************************
Hello, Why don't you add a timestamp field in the table? Insert a value with current time, then query to get the corresponding row. Best regards, Jean-Michel POURE
> -----Original Message----- > From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr] > Sent: 12 November 2001 09:36 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Last inserted id > > > Hello, > > Why don't you add a timestamp field in the table? > Insert a value with current time, then query to get the > corresponding row. 'Cos that's not safe either - you might get multiple rows inserted at the same time - unlikely, but certainly possible. /Dave.
At 09:55 12/11/01 +0000, you wrote: >'Cos that's not safe either - you might get multiple rows inserted at the >same time - unlikely, but certainly possible. In most cases a simple timestamp is enough because you add some other parameter to you query (ex:company name, or whatever field you inserted). For example, if you insert values with First_name=Dave, Second_name=Page, Timestamp=current time and query the resulting row, there is *very little* chance to get rows from other users. I guess this is the recommended way (see Access howto). Cheers, Jean-Michel
> -----Original Message-----
> From: Jean-Michel POURE [mailto:jm.poure@freesurf.fr]
> Sent: 12 November 2001 10:06
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Last inserted id
>
>
> At 09:55 12/11/01 +0000, you wrote:
> >'Cos that's not safe either - you might get multiple rows
> inserted at
> >the same time - unlikely, but certainly possible.
>
> In most cases a simple timestamp is enough because you add some other
> parameter to you query (ex:company name, or whatever field
> you inserted).
> For example, if you insert values with First_name=Dave,
> Second_name=Page,
> Timestamp=current time and query the resulting row, there is
> *very little*
> chance to get rows from other users.
>
> I guess this is the recommended way (see Access howto).
There is *very little* chance, but very little is not *no chance* (bear in
mind that at this point in time in the example we're discussing no primary
key value is yet known).
I would always suggest that people do a select nextval('seq') followed by a
suitable insert. It requires no locking, is definitely multi user safe and
will *always* correctly identify the row. The only downside is that it
requires a quick select before the insert, but if this minor speed sacrifice
was that much of an issue then VB+ADO+ODBC is possibly not the technology to
use in such a project anyway IMHO.
Regards, Dave.
David Horwitz <Dhorwitz@ched.uct.ac.za> writes:
> Actually the issue is b) is multi-user safe
> *if* you have an exclusive lock on the table. If you don't it is quite
> possible for a user to insert an other record between your insertion and the
> currval() call
False. Option B is multi-user safe, period. The reason is that currval
returns the value last obtained by nextval *in your own session*,
independently of what anyone else has done meanwhile.
I tend to prefer option A (select nextval and insert) myself, just
because it seems more intuitive. But if that's not convenient for
some reason, option B works fine too.
regards, tom lane
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 12 November 2001 15:30
> To: Dhorwitz@ched.uct.ac.za
> Cc: 'pgsql-odbc@postgresql.org'
> Subject: Re: [ODBC] Last inserted id
>
>
> David Horwitz <Dhorwitz@ched.uct.ac.za> writes:
> > Actually the issue is b) is multi-user safe
> > *if* you have an exclusive lock on the table. If you don't it is
> > quite possible for a user to insert an other record between your
> > insertion and the
> > currval() call
>
> False. Option B is multi-user safe, period. The reason is
> that currval returns the value last obtained by nextval *in
> your own session*, independently of what anyone else has done
> meanwhile.
>
> I tend to prefer option A (select nextval and insert) myself,
> just because it seems more intuitive. But if that's not
> convenient for some reason, option B works fine too.
Ahh, now I see where the (== my) confusion has occurred. Option B) being:
- do insert
- select current val.
Whereas I originally was arguing against my interpretation of the question
which was:
- Select current val
- Do insert
- Select setval('seq', current_val + 1)
Which isn't safe.
Oh well.
My bad.
Regards, Dave.
> False. Option B is multi-user safe, period. The reason is that currval > returns the value last obtained by nextval *in your own session*, > independently of what anyone else has done meanwhile. What do you understand for *session* ? Same postgres connection ? Same ADO connection ? (ADO sometimes make more than one connection for recordset) How Can I know how many connections are active ? (for ADO debugging) Thanks Simeó Reig
"Simeo Reig" <simreig@terra.es> writes:
>> False. Option B is multi-user safe, period. The reason is that currval
>> returns the value last obtained by nextval *in your own session*,
>> independently of what anyone else has done meanwhile.
> What do you understand for *session* ? Same postgres connection ?
Same postgres connection. If you are working in an application
environment that does connection pooling, or some such, then this
is unsafe and you'd better go back to option A.
regards, tom lane
> -----Original Message----- > From: Simeo Reig [mailto:simreig@terra.es] > Sent: 12 November 2001 20:43 > To: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] Last inserted id > > > > False. Option B is multi-user safe, period. The reason is that > > currval returns the value last obtained by nextval *in your own > > session*, independently of what anyone else has done meanwhile. > > What do you understand for *session* ? Same postgres > connection ? Same ADO connection ? (ADO sometimes make more > than one connection for recordset) > > How Can I know how many connections are active ? > (for ADO debugging) I don't think you can easily find out what connections are open (at least from the ADO end). In pgAdmin II there is a long running bug that I can't resolve that prevents dropping a database because I can't persuade *all* connections to the specified database to close. It's for this reason that I'd still use option A - option B may be multi user safe (now that I understand it properly :-) ) but you never know how ADO is going to handle it. In theory it should only use one connection but the bug I mention above (knowing the time, effort and experimentation I've put into fixing it) makes me wonder. Regards, Dave.
At 08:35 13/11/01 +0000, you wrote: >In pgAdmin II there is a long running bug that I can't >resolve that prevents dropping a database because I can't persuade *all* >connections to the specified database to close. Dear all, The same problem arises when working in psql after the closing of Php socket connections. I have to do a 'service postgresql restart' server-side, and then psql template1 < drop database xxxx; Does anyone know a simpler solution? Cheers, Jean-Michel
On Mon, 12 Nov 2001, Jean-Michel POURE wrote: > At 09:55 12/11/01 +0000, you wrote: > >'Cos that's not safe either - you might get multiple rows inserted at the > >same time - unlikely, but certainly possible. > > In most cases a simple timestamp is enough because you add some other > parameter to you query (ex:company name, or whatever field you inserted). > For example, if you insert values with First_name=Dave, Second_name=Page, > Timestamp=current time and query the resulting row, there is *very little* > chance to get rows from other users. > > I guess this is the recommended way (see Access howto). Yes, but the query for search the row is more expensive than select from an id that is normally indexed. I have making tests and 'option A' seems be the best. Thanks Simeo Reig
I believe that in the release notes for the most recent version, it states that this problem is known and can't really be worked around. Chris > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Jean-Michel > POURE > Sent: Tuesday, 13 November 2001 5:33 PM > To: pgsql-odbc@postgresql.org > Cc: pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] [ODBC] Last inserted id > > > At 08:35 13/11/01 +0000, you wrote: > >In pgAdmin II there is a long running bug that I can't > >resolve that prevents dropping a database because I can't persuade *all* > >connections to the specified database to close. > > Dear all, > > The same problem arises when working in psql after the closing of Php > socket connections. > I have to do a 'service postgresql restart' server-side, and then psql > template1 < drop database xxxx; > > Does anyone know a simpler solution? > > Cheers, > Jean-Michel > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >