Thread: get last oid
hello, i try get the last oid after INSERT command but i have no idea about command/function .anyone know or is there any better method? Thanks Ricky
PHP uses "pg_last_oid() ": a reliable way because it work's at connection
level (i.e. it will not be affected
by inserts made from other tasks or connections .
I'd try searching starting from there for a solution without php: every
other language i've seen has an equivalent solution.
With "psql" you have:
postgres=# insert into cars(name) values('lamborghini');
INSERT 2237168 1
where 2237168 is the oid...
I hope it willl help...
Roberto
----- Original Message -----
From: "Ricky" <ricky@babonmultimedia.com>
To: "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 1:05 AM
Subject: [ODBC] get last oid
> hello,
>
> i try get the last oid after INSERT command but i have no idea about
> command/function
> .anyone know or is there any better method?
>
> Thanks
> Ricky
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
thanks Roberto,
i'm new in vb and also in postgresql,
well, if there'no function to get the last oid, how to catch the message
INSERT 2237168 1 in visual basic ?
if no, maybe i have to try to select the bigest oid to get it but i'm
affraid while somebody insert in the same time, the oid will not match.
you have suggestion about this?
BR
Ricky
----- Original Message -----
From: "Ropel" <ropel@ropel.it>
To: "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 3:20 PM
Subject: Re: [ODBC] get last oid
> PHP uses "pg_last_oid() ": a reliable way because it work's at connection
> level (i.e. it will not be affected
> by inserts made from other tasks or connections .
> I'd try searching starting from there for a solution without php: every
> other language i've seen has an equivalent solution.
>
> With "psql" you have:
>
> postgres=# insert into cars(name) values('lamborghini');
> INSERT 2237168 1
>
> where 2237168 is the oid...
>
> I hope it willl help...
>
> Roberto
>
>
> ----- Original Message -----
> From: "Ricky" <ricky@babonmultimedia.com>
> To: "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 1:05 AM
> Subject: [ODBC] get last oid
>
>
> > hello,
> >
> > i try get the last oid after INSERT command but i have no idea about
> > command/function
> > .anyone know or is there any better method?
> >
> > Thanks
> > Ricky
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
... In a well designed relational Db, every table has a (unique) primary
key: if so, you don't even need to
know the oid, because the record just inserted is directly reachable using
the PK.
If your PK uses sequences, you have to get the next seq value "select
nextval() from seq_xxxxx..." and use the
value obtained for inserting your new row instead of using it as a default
value; this way you are protected from
concurrent writing to the DB.
If you REALLY need the oid from the ODBC connection, I can't help... sorry !
Bye
Roberto
----- Original Message -----
From: "Ricky" <ricky@babonmultimedia.com>
To: "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 9:25 AM
Subject: Re: [ODBC] get last oid
> thanks Roberto,
>
> i'm new in vb and also in postgresql,
> well, if there'no function to get the last oid, how to catch the message
> INSERT 2237168 1 in visual basic ?
>
> if no, maybe i have to try to select the bigest oid to get it but i'm
> affraid while somebody insert in the same time, the oid will not match.
>
> you have suggestion about this?
>
> BR
>
> Ricky
>
>
> ----- Original Message -----
> From: "Ropel" <ropel@ropel.it>
> To: "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 3:20 PM
> Subject: Re: [ODBC] get last oid
>
>
> > PHP uses "pg_last_oid() ": a reliable way because it work's at
connection
> > level (i.e. it will not be affected
> > by inserts made from other tasks or connections .
> > I'd try searching starting from there for a solution without php: every
> > other language i've seen has an equivalent solution.
> >
> > With "psql" you have:
> >
> > postgres=# insert into cars(name) values('lamborghini');
> > INSERT 2237168 1
> >
> > where 2237168 is the oid...
> >
> > I hope it willl help...
> >
> > Roberto
> >
> >
> > ----- Original Message -----
> > From: "Ricky" <ricky@babonmultimedia.com>
> > To: "postgre" <pgsql-odbc@postgresql.org>
> > Sent: Friday, December 19, 2003 1:05 AM
> > Subject: [ODBC] get last oid
> >
> >
> > > hello,
> > >
> > > i try get the last oid after INSERT command but i have no idea about
> > > command/function
> > > .anyone know or is there any better method?
> > >
> > > Thanks
> > > Ricky
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Ropel wrote: >... In a well designed relational Db, every table has a (unique) primary >key: if so, you don't even need to >know the oid, because the record just inserted is directly reachable using >the PK. >If your PK uses sequences, you have to get the next seq value "select >nextval() from seq_xxxxx..." and use the >value obtained for inserting your new row instead of using it as a default >value; this way you are protected from >concurrent writing to the DB. > >If you REALLY need the oid from the ODBC connection, I can't help... sorry ! > >Bye > >Roberto > > > Actually, if you created the sequence implicitly by using the "serial" type, you don't even have to do that. You can simply do this: creating the table: create table foo (index serial not null, a type, b type.....); inserting into the table: insert into foo (a,b,....).... I.e. - leave the serial field out of the insert, and it will automatically be set to the next sequence from the table. -- Shachar Shemesh Open Source integration & consulting Home page & resume - http://www.shemesh.biz/
Hi Ricky,
There is a work around for this...
1. Create a LOCAL TEMPORARY table.
2. On the MAIN table, put a trigger to store NEW.oid in that TEMPORARY
table.
3. On your VB application, Fetch OID from that TEMP table.
Since, the local temporary table's data is visible to specific to session,
each session will get their OID even if "n" numbers of users are inserting
data in that table at a time..
HTH
Thanx
Denis
> ----- Original Message -----
> From: "Ropel" <ropel@ropel.it>
> To: "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 2:32 PM
> Subject: Re: [ODBC] get last oid
>
>
> > ... In a well designed relational Db, every table has a (unique) primary
> > key: if so, you don't even need to
> > know the oid, because the record just inserted is directly reachable
using
> > the PK.
> > If your PK uses sequences, you have to get the next seq value "select
> > nextval() from seq_xxxxx..." and use the
> > value obtained for inserting your new row instead of using it as a
default
> > value; this way you are protected from
> > concurrent writing to the DB.
> >
> > If you REALLY need the oid from the ODBC connection, I can't help...
sorry
> !
> >
> > Bye
> >
> > Roberto
> >
> > ----- Original Message -----
> > From: "Ricky" <ricky@babonmultimedia.com>
> > To: "postgre" <pgsql-odbc@postgresql.org>
> > Sent: Friday, December 19, 2003 9:25 AM
> > Subject: Re: [ODBC] get last oid
> >
> >
> > > thanks Roberto,
> > >
> > > i'm new in vb and also in postgresql,
> > > well, if there'no function to get the last oid, how to catch the
> message
> > > INSERT 2237168 1 in visual basic ?
> > >
> > > if no, maybe i have to try to select the bigest oid to get it but i'm
> > > affraid while somebody insert in the same time, the oid will not
match.
> > >
> > > you have suggestion about this?
> > >
> > > BR
> > >
> > > Ricky
> > >
> > >
> > > ----- Original Message -----
> > > From: "Ropel" <ropel@ropel.it>
> > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > Sent: Friday, December 19, 2003 3:20 PM
> > > Subject: Re: [ODBC] get last oid
> > >
> > >
> > > > PHP uses "pg_last_oid() ": a reliable way because it work's at
> > connection
> > > > level (i.e. it will not be affected
> > > > by inserts made from other tasks or connections .
> > > > I'd try searching starting from there for a solution without php:
> every
> > > > other language i've seen has an equivalent solution.
> > > >
> > > > With "psql" you have:
> > > >
> > > > postgres=# insert into cars(name) values('lamborghini');
> > > > INSERT 2237168 1
> > > >
> > > > where 2237168 is the oid...
> > > >
> > > > I hope it willl help...
> > > >
> > > > Roberto
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "Ricky" <ricky@babonmultimedia.com>
> > > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > > Sent: Friday, December 19, 2003 1:05 AM
> > > > Subject: [ODBC] get last oid
> > > >
> > > >
> > > > > hello,
> > > > >
> > > > > i try get the last oid after INSERT command but i have no idea
about
> > > > > command/function
> > > > > .anyone know or is there any better method?
> > > > >
> > > > > Thanks
> > > > > Ricky
> > > > >
> > > > >
> > > > > ---------------------------(end of
> > broadcast)---------------------------
> > > > > TIP 4: Don't 'kill -9' the postmaster
> > > >
>
>
Yes of course, but doing so you have the same old problem: which serial did you get ?? ----- Original Message ----- From: "Shachar Shemesh" <psql@shemesh.biz> To: "Ropel" <ropel@ropel.it> Cc: "postgre" <pgsql-odbc@postgresql.org> Sent: Friday, December 19, 2003 12:38 PM Subject: Re: [ODBC] get last oid > Ropel wrote: > > >... In a well designed relational Db, every table has a (unique) primary > >key: if so, you don't even need to > >know the oid, because the record just inserted is directly reachable using > >the PK. > >If your PK uses sequences, you have to get the next seq value "select > >nextval() from seq_xxxxx..." and use the > >value obtained for inserting your new row instead of using it as a default > >value; this way you are protected from > >concurrent writing to the DB. > > > >If you REALLY need the oid from the ODBC connection, I can't help... sorry ! > > > >Bye > > > >Roberto > > > > > > > Actually, if you created the sequence implicitly by using the "serial" > type, you don't even have to do that. You can simply do this: > creating the table: > create table foo (index serial not null, a type, b type.....); > inserting into the table: > insert into foo (a,b,....).... > > I.e. - leave the serial field out of the insert, and it will > automatically be set to the next sequence from the table. > > -- > Shachar Shemesh > Open Source integration & consulting > Home page & resume - http://www.shemesh.biz/ > > >
Wanderful!!! I never thought about this !!
Roberto
----- Original Message -----
From: "sqllist" <sqllist@coralindia.com>
To: "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 12:22 PM
Subject: Re: [ODBC] get last oid
> Hi Ricky,
>
> There is a work around for this...
>
> 1. Create a LOCAL TEMPORARY table.
> 2. On the MAIN table, put a trigger to store NEW.oid in that TEMPORARY
> table.
> 3. On your VB application, Fetch OID from that TEMP table.
>
> Since, the local temporary table's data is visible to specific to session,
> each session will get their OID even if "n" numbers of users are inserting
> data in that table at a time..
>
> HTH
>
> Thanx
>
> Denis
>
>
> > ----- Original Message -----
> > From: "Ropel" <ropel@ropel.it>
> > To: "postgre" <pgsql-odbc@postgresql.org>
> > Sent: Friday, December 19, 2003 2:32 PM
> > Subject: Re: [ODBC] get last oid
> >
> >
> > > ... In a well designed relational Db, every table has a (unique)
primary
> > > key: if so, you don't even need to
> > > know the oid, because the record just inserted is directly reachable
> using
> > > the PK.
> > > If your PK uses sequences, you have to get the next seq value "select
> > > nextval() from seq_xxxxx..." and use the
> > > value obtained for inserting your new row instead of using it as a
> default
> > > value; this way you are protected from
> > > concurrent writing to the DB.
> > >
> > > If you REALLY need the oid from the ODBC connection, I can't help...
> sorry
> > !
> > >
> > > Bye
> > >
> > > Roberto
> > >
> > > ----- Original Message -----
> > > From: "Ricky" <ricky@babonmultimedia.com>
> > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > Sent: Friday, December 19, 2003 9:25 AM
> > > Subject: Re: [ODBC] get last oid
> > >
> > >
> > > > thanks Roberto,
> > > >
> > > > i'm new in vb and also in postgresql,
> > > > well, if there'no function to get the last oid, how to catch the
> > message
> > > > INSERT 2237168 1 in visual basic ?
> > > >
> > > > if no, maybe i have to try to select the bigest oid to get it but
i'm
> > > > affraid while somebody insert in the same time, the oid will not
> match.
> > > >
> > > > you have suggestion about this?
> > > >
> > > > BR
> > > >
> > > > Ricky
> > > >
> > > >
> > > > ----- Original Message -----
> > > > From: "Ropel" <ropel@ropel.it>
> > > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > > Sent: Friday, December 19, 2003 3:20 PM
> > > > Subject: Re: [ODBC] get last oid
> > > >
> > > >
> > > > > PHP uses "pg_last_oid() ": a reliable way because it work's at
> > > connection
> > > > > level (i.e. it will not be affected
> > > > > by inserts made from other tasks or connections .
> > > > > I'd try searching starting from there for a solution without php:
> > every
> > > > > other language i've seen has an equivalent solution.
> > > > >
> > > > > With "psql" you have:
> > > > >
> > > > > postgres=# insert into cars(name) values('lamborghini');
> > > > > INSERT 2237168 1
> > > > >
> > > > > where 2237168 is the oid...
> > > > >
> > > > > I hope it willl help...
> > > > >
> > > > > Roberto
> > > > >
> > > > >
> > > > > ----- Original Message -----
> > > > > From: "Ricky" <ricky@babonmultimedia.com>
> > > > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > > > Sent: Friday, December 19, 2003 1:05 AM
> > > > > Subject: [ODBC] get last oid
> > > > >
> > > > >
> > > > > > hello,
> > > > > >
> > > > > > i try get the last oid after INSERT command but i have no idea
> about
> > > > > > command/function
> > > > > > .anyone know or is there any better method?
> > > > > >
> > > > > > Thanks
> > > > > > Ricky
> > > > > >
> > > > > >
> > > > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
Hiya, You get the serial as a result of your select from sequence... --Tim -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel Sent: vrijdag 19 december 2003 13:07 To: postgre Subject: Re: [ODBC] get last oid Yes of course, but doing so you have the same old problem: which serial did you get ?? ----- Original Message ----- From: "Shachar Shemesh" <psql@shemesh.biz> To: "Ropel" <ropel@ropel.it> Cc: "postgre" <pgsql-odbc@postgresql.org> Sent: Friday, December 19, 2003 12:38 PM Subject: Re: [ODBC] get last oid > Ropel wrote: > > >... In a well designed relational Db, every table has a (unique) primary > >key: if so, you don't even need to > >know the oid, because the record just inserted is directly reachable using > >the PK. > >If your PK uses sequences, you have to get the next seq value "select > >nextval() from seq_xxxxx..." and use the > >value obtained for inserting your new row instead of using it as a default > >value; this way you are protected from > >concurrent writing to the DB. > > > >If you REALLY need the oid from the ODBC connection, I can't help... sorry ! > > > >Bye > > > >Roberto > > > > > > > Actually, if you created the sequence implicitly by using the "serial" > type, you don't even have to do that. You can simply do this: > creating the table: > create table foo (index serial not null, a type, b type.....); > inserting into the table: > insert into foo (a,b,....).... > > I.e. - leave the serial field out of the insert, and it will > automatically be set to the next sequence from the table. > > -- > Shachar Shemesh > Open Source integration & consulting > Home page & resume - http://www.shemesh.biz/ > > > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
... You can't: some other task can do another insert between your insert and your select, so you get the serial of the other record, not yours ! ----- Original Message ----- From: "Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com> To: "'Ropel'" <ropel@ropel.it>; "postgre" <pgsql-odbc@postgresql.org> Sent: Friday, December 19, 2003 1:21 PM Subject: RE: [ODBC] get last oid > Hiya, > > You get the serial as a result of your select from sequence... > > --Tim > > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel > Sent: vrijdag 19 december 2003 13:07 > To: postgre > Subject: Re: [ODBC] get last oid > > > Yes of course, but doing so you have the same old problem: which serial did > you get ?? > > ----- Original Message ----- > From: "Shachar Shemesh" <psql@shemesh.biz> > To: "Ropel" <ropel@ropel.it> > Cc: "postgre" <pgsql-odbc@postgresql.org> > Sent: Friday, December 19, 2003 12:38 PM > Subject: Re: [ODBC] get last oid > > > > Ropel wrote: > > > > >... In a well designed relational Db, every table has a (unique) primary > > >key: if so, you don't even need to > > >know the oid, because the record just inserted is directly reachable > using > > >the PK. > > >If your PK uses sequences, you have to get the next seq value "select > > >nextval() from seq_xxxxx..." and use the > > >value obtained for inserting your new row instead of using it as a > default > > >value; this way you are protected from > > >concurrent writing to the DB. > > > > > >If you REALLY need the oid from the ODBC connection, I can't help... > sorry ! > > > > > >Bye > > > > > >Roberto > > > > > > > > > > > Actually, if you created the sequence implicitly by using the "serial" > > type, you don't even have to do that. You can simply do this: > > creating the table: > > create table foo (index serial not null, a type, b type.....); > > inserting into the table: > > insert into foo (a,b,....).... > > > > I.e. - leave the serial field out of the insert, and it will > > automatically be set to the next sequence from the table. > > > > -- > > Shachar Shemesh > > Open Source integration & consulting > > Home page & resume - http://www.shemesh.biz/ > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
The pattern which I mean is given in the manual pages for create-sequence: http://www.postgresql.org/docs/current/interactive/sql-createsequence.html To know the sequence number, do first SELECT nextval(<sequence-name>); And store the result somewhere in a variable of your program. Then do the insert with that variable-nr as the key-value. regards, --Tim -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel Sent: vrijdag 19 december 2003 14:58 To: postgre Subject: Re: [ODBC] get last oid ... You can't: some other task can do another insert between your insert and your select, so you get the serial of the other record, not yours ! ----- Original Message ----- From: "Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com> To: "'Ropel'" <ropel@ropel.it>; "postgre" <pgsql-odbc@postgresql.org> Sent: Friday, December 19, 2003 1:21 PM Subject: RE: [ODBC] get last oid > Hiya, > > You get the serial as a result of your select from sequence... > > --Tim > > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel > Sent: vrijdag 19 december 2003 13:07 > To: postgre > Subject: Re: [ODBC] get last oid > > > Yes of course, but doing so you have the same old problem: which serial did > you get ?? > > ----- Original Message ----- > From: "Shachar Shemesh" <psql@shemesh.biz> > To: "Ropel" <ropel@ropel.it> > Cc: "postgre" <pgsql-odbc@postgresql.org> > Sent: Friday, December 19, 2003 12:38 PM > Subject: Re: [ODBC] get last oid > > > > Ropel wrote: > > > > >... In a well designed relational Db, every table has a (unique) primary > > >key: if so, you don't even need to > > >know the oid, because the record just inserted is directly reachable > using > > >the PK. > > >If your PK uses sequences, you have to get the next seq value "select > > >nextval() from seq_xxxxx..." and use the > > >value obtained for inserting your new row instead of using it as a > default > > >value; this way you are protected from > > >concurrent writing to the DB. > > > > > >If you REALLY need the oid from the ODBC connection, I can't help... > sorry ! > > > > > >Bye > > > > > >Roberto > > > > > > > > > > > Actually, if you created the sequence implicitly by using the "serial" > > type, you don't even have to do that. You can simply do this: > > creating the table: > > create table foo (index serial not null, a type, b type.....); > > inserting into the table: > > insert into foo (a,b,....).... > > > > I.e. - leave the serial field out of the insert, and it will > > automatically be set to the next sequence from the table. > > > > -- > > Shachar Shemesh > > Open Source integration & consulting > > Home page & resume - http://www.shemesh.biz/ > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Yor're right, I forgot that "currval" is not influenced by other sessions...
Regards
Roberto
----- Original Message -----
From: "Miguel Juan" <mjuan@cibal.es>
To: "Ropel" <ropel@ropel.it>; "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 3:15 PM
Subject: Re: [ODBC] get last oid
> Hello,
>
> if you make an Insert on a table with a serial, you can get the last
value
> of the serial inserted by your session using:
> SELECT currval('Sequence_name');
>
> This is better and faster than any other solution. This ensures you to get
> the last sequence number asigned to your session. Doesn't matter the
number
> of concurrent INSERTS made by other clients.
>
> Regards
>
> Miguel Juan
>
>
>
> ----- Original Message -----
> From: Ropel
> To: postgre
> Sent: Friday, December 19, 2003 2:57 PM
> Subject: Re: [ODBC] get last oid
>
>
> ... You can't: some other task can do another insert between your insert
and
> your select, so you get the serial
> of the other record, not yours !
>
> ----- Original Message -----
> From: "Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com>
> To: "'Ropel'" <ropel@ropel.it>; "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 1:21 PM
> Subject: RE: [ODBC] get last oid
>
>
> > Hiya,
> >
> > You get the serial as a result of your select from sequence...
> >
> > --Tim
> >
> > -----Original Message-----
> > From: pgsql-odbc-owner@postgresql.org
> > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel
> > Sent: vrijdag 19 december 2003 13:07
> > To: postgre
> > Subject: Re: [ODBC] get last oid
> >
> >
> > Yes of course, but doing so you have the same old problem: which serial
> did
> > you get ??
> >
> > ----- Original Message -----
> > From: "Shachar Shemesh" <psql@shemesh.biz>
> > To: "Ropel" <ropel@ropel.it>
> > Cc: "postgre" <pgsql-odbc@postgresql.org>
> > Sent: Friday, December 19, 2003 12:38 PM
> > Subject: Re: [ODBC] get last oid
> >
> >
> > > Ropel wrote:
> > >
> > > >... In a well designed relational Db, every table has a (unique)
> primary
> > > >key: if so, you don't even need to
> > > >know the oid, because the record just inserted is directly reachable
> > using
> > > >the PK.
> > > >If your PK uses sequences, you have to get the next seq value "select
> > > >nextval() from seq_xxxxx..." and use the
> > > >value obtained for inserting your new row instead of using it as a
> > default
> > > >value; this way you are protected from
> > > >concurrent writing to the DB.
> > > >
> > > >If you REALLY need the oid from the ODBC connection, I can't help...
> > sorry !
> > > >
> > > >Bye
> > > >
> > > >Roberto
> > > >
> > > >
> > > >
> > > Actually, if you created the sequence implicitly by using the "serial"
> > > type, you don't even have to do that. You can simply do this:
> > > creating the table:
> > > create table foo (index serial not null, a type, b type.....);
> > > inserting into the table:
> > > insert into foo (a,b,....)....
> > >
> > > I.e. - leave the serial field out of the insert, and it will
> > > automatically be set to the next sequence from the table.
> > >
> > > --
> > > Shachar Shemesh
> > > Open Source integration & consulting
> > > Home page & resume - http://www.shemesh.biz/
> > >
> > >
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
Hi Ricky,
There is a work around for this...
1. Create a LOCAL TEMPORARY table.
2. On the MAIN table, put a trigger to store NEW.oid in that TEMPORARY
table.
3. On your VB application, Fetch OID from that TEMP table.
Since, the local temporary table's data is visible to specific to session,
each session will get their OID even if "n" numbers of users are inserting
data in that table at a time..
HTH
Thanx
Denis
----- Original Message -----
From: "Ropel" <ropel@ropel.it>
To: "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 2:32 PM
Subject: Re: [ODBC] get last oid
> ... In a well designed relational Db, every table has a (unique) primary
> key: if so, you don't even need to
> know the oid, because the record just inserted is directly reachable using
> the PK.
> If your PK uses sequences, you have to get the next seq value "select
> nextval() from seq_xxxxx..." and use the
> value obtained for inserting your new row instead of using it as a default
> value; this way you are protected from
> concurrent writing to the DB.
>
> If you REALLY need the oid from the ODBC connection, I can't help... sorry
!
>
> Bye
>
> Roberto
>
> ----- Original Message -----
> From: "Ricky" <ricky@babonmultimedia.com>
> To: "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 9:25 AM
> Subject: Re: [ODBC] get last oid
>
>
> > thanks Roberto,
> >
> > i'm new in vb and also in postgresql,
> > well, if there'no function to get the last oid, how to catch the
message
> > INSERT 2237168 1 in visual basic ?
> >
> > if no, maybe i have to try to select the bigest oid to get it but i'm
> > affraid while somebody insert in the same time, the oid will not match.
> >
> > you have suggestion about this?
> >
> > BR
> >
> > Ricky
> >
> >
> > ----- Original Message -----
> > From: "Ropel" <ropel@ropel.it>
> > To: "postgre" <pgsql-odbc@postgresql.org>
> > Sent: Friday, December 19, 2003 3:20 PM
> > Subject: Re: [ODBC] get last oid
> >
> >
> > > PHP uses "pg_last_oid() ": a reliable way because it work's at
> connection
> > > level (i.e. it will not be affected
> > > by inserts made from other tasks or connections .
> > > I'd try searching starting from there for a solution without php:
every
> > > other language i've seen has an equivalent solution.
> > >
> > > With "psql" you have:
> > >
> > > postgres=# insert into cars(name) values('lamborghini');
> > > INSERT 2237168 1
> > >
> > > where 2237168 is the oid...
> > >
> > > I hope it willl help...
> > >
> > > Roberto
> > >
> > >
> > > ----- Original Message -----
> > > From: "Ricky" <ricky@babonmultimedia.com>
> > > To: "postgre" <pgsql-odbc@postgresql.org>
> > > Sent: Friday, December 19, 2003 1:05 AM
> > > Subject: [ODBC] get last oid
> > >
> > >
> > > > hello,
> > > >
> > > > i try get the last oid after INSERT command but i have no idea about
> > > > command/function
> > > > .anyone know or is there any better method?
> > > >
> > > > Thanks
> > > > Ricky
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 4: Don't 'kill -9' the postmaster
> > >
Hello,
if you make an Insert on a table with a serial, you can get the last value
of the serial inserted by your session using:
SELECT currval('Sequence_name');
This is better and faster than any other solution. This ensures you to get
the last sequence number asigned to your session. Doesn't matter the number
of concurrent INSERTS made by other clients.
Regards
Miguel Juan
----- Original Message -----
From: Ropel
To: postgre
Sent: Friday, December 19, 2003 2:57 PM
Subject: Re: [ODBC] get last oid
... You can't: some other task can do another insert between your insert and
your select, so you get the serial
of the other record, not yours !
----- Original Message -----
From: "Leeuw van der, Tim" <tim.leeuwvander@nl.unisys.com>
To: "'Ropel'" <ropel@ropel.it>; "postgre" <pgsql-odbc@postgresql.org>
Sent: Friday, December 19, 2003 1:21 PM
Subject: RE: [ODBC] get last oid
> Hiya,
>
> You get the serial as a result of your select from sequence...
>
> --Tim
>
> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Ropel
> Sent: vrijdag 19 december 2003 13:07
> To: postgre
> Subject: Re: [ODBC] get last oid
>
>
> Yes of course, but doing so you have the same old problem: which serial
did
> you get ??
>
> ----- Original Message -----
> From: "Shachar Shemesh" <psql@shemesh.biz>
> To: "Ropel" <ropel@ropel.it>
> Cc: "postgre" <pgsql-odbc@postgresql.org>
> Sent: Friday, December 19, 2003 12:38 PM
> Subject: Re: [ODBC] get last oid
>
>
> > Ropel wrote:
> >
> > >... In a well designed relational Db, every table has a (unique)
primary
> > >key: if so, you don't even need to
> > >know the oid, because the record just inserted is directly reachable
> using
> > >the PK.
> > >If your PK uses sequences, you have to get the next seq value "select
> > >nextval() from seq_xxxxx..." and use the
> > >value obtained for inserting your new row instead of using it as a
> default
> > >value; this way you are protected from
> > >concurrent writing to the DB.
> > >
> > >If you REALLY need the oid from the ODBC connection, I can't help...
> sorry !
> > >
> > >Bye
> > >
> > >Roberto
> > >
> > >
> > >
> > Actually, if you created the sequence implicitly by using the "serial"
> > type, you don't even have to do that. You can simply do this:
> > creating the table:
> > create table foo (index serial not null, a type, b type.....);
> > inserting into the table:
> > insert into foo (a,b,....)....
> >
> > I.e. - leave the serial field out of the insert, and it will
> > automatically be set to the next sequence from the table.
> >
> > --
> > Shachar Shemesh
> > Open Source integration & consulting
> > Home page & resume - http://www.shemesh.biz/
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html