Thread: Re: Retrieving the new nextval...

Hi all,
I'm in trouble with the same problem, but in PHP..
With your solution, I cannot be totally sure that last
inserted raw was mine...
Because I'm on a web page, it could be that, as soon
as I've inserted my record, another one do an
insertion, so I would get the wrong ID...
does transactions resolve this, in Psql???
I thought to solve it with a similiar solution,
working in transactions inserting a raw and
immedialtly after read from DB last raw, but who
assure me that all will go right??
If I was on a server app., I (and you, if it is your
case) would insert a timestamp, and then I'd select
from table where timestamp = mysavedtime;
But in my case there could be two or more equals
timestamp, cause there's not only one application
working with DB...
I'm still reading, searching, trying...
ciao
danilo


--- Kevin Brannen <kevinb@nurseamerica.net> ha
scritto: > Greg Patnude wrote:
> > I am using postgreSQL with Perl::CGI and
> Perl::DBI::Pg... I would like to be
> > able to insert a row from my Perl script
> [$SQL->exec();] and have postgreSQL
> > return the id of the newly inserted record
> (new.id) directly to the Perl
> > script for further processing... Anyone with a
> solution / idea ???
> > 
> > Nearly EVERY table I create in postgreSQL (7.2)
> has the following minimum
> > structure:
> > 
> > create table "tblName" (
> > 
> >     id int4 primary key nextval
> ("tblName_id_seq"),
> > 
> >     ..field...
> > )
> 
> You can either do it in 2 statements, something
> like:
> 
> $dbh->do("insert into tblName ...");
> my ($id) = $dbh->selectrow_array("select
> currval('tblName_id_seq')");
> 
> Or you could create a function which takes the
> insert statement, and 
> ends with doing a select on the currval (as above)
> and returning that. 
> As I do the 2 statement approach above, I haven't
> done a function, but 
> it doesn't look like it would be that hard to do.
> 
> HTH,
> Kevin
R

______________________________________________________________________
Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/

--- friedrich nietzsche <nietzsche_psql@yahoo.it>
wrote:
> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that
> last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one do an
> insertion, so I would get the wrong ID...

"currval" will return the last value used _for the
current connection_.  But if you want to be absolutely
sure, instead call "nextval" before doing your insert,
and use the returned value explicitly.


> does transactions resolve this, in Psql???
> I thought to solve it with a similiar solution,
> working in transactions inserting a raw and
> immedialtly after read from DB last raw, but who
> assure me that all will go right??
> If I was on a server app., I (and you, if it is your
> case) would insert a timestamp, and then I'd select
> from table where timestamp = mysavedtime;
> But in my case there could be two or more equals
> timestamp, cause there's not only one application
> working with DB...
> I'm still reading, searching, trying...
> ciao
> danilo
> 
> 
> 
>  --- Kevin Brannen <kevinb@nurseamerica.net> ha
> scritto: > Greg Patnude wrote:
> > > I am using postgreSQL with Perl::CGI and
> > Perl::DBI::Pg... I would like to be
> > > able to insert a row from my Perl script
> > [$SQL->exec();] and have postgreSQL
> > > return the id of the newly inserted record
> > (new.id) directly to the Perl
> > > script for further processing... Anyone with a
> > solution / idea ???
> > > 
> > > Nearly EVERY table I create in postgreSQL (7.2)
> > has the following minimum
> > > structure:
> > > 
> > > create table "tblName" (
> > > 
> > >     id int4 primary key nextval
> > ("tblName_id_seq"),
> > > 
> > >     ..field...
> > > )
> > 
> > You can either do it in 2 statements, something
> > like:
> > 
> > $dbh->do("insert into tblName ...");
> > my ($id) = $dbh->selectrow_array("select
> > currval('tblName_id_seq')");
> > 
> > Or you could create a function which takes the
> > insert statement, and 
> > ends with doing a select on the currval (as above)
> > and returning that. 
> > As I do the 2 statement approach above, I haven't
> > done a function, but 
> > it doesn't look like it would be that hard to do.
> > 
> > HTH,
> > Kevin
> R
> 
>
______________________________________________________________________
> Yahoo! Musica: notizie, recensioni, classifiche,
> speciali multimediali
>
http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org


__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com

On Wed, 28 Aug 2002 18:32:45 +0200 (CEST)
friedrich nietzsche <nietzsche_psql@yahoo.it> wrote:

> Hi all,
> I'm in trouble with the same problem, but in PHP..
> With your solution, I cannot be totally sure that last
> inserted raw was mine...
> Because I'm on a web page, it could be that, as soon
> as I've inserted my record, another one do an
> insertion, so I would get the wrong ID...
> does transactions resolve this, in Psql???
> I thought to solve it with a similiar solution,
> working in transactions inserting a raw and
> immedialtly after read from DB last raw, but who
> assure me that all will go right??
> If I was on a server app., I (and you, if it is your
> case) would insert a timestamp, and then I'd select
> from table where timestamp = mysavedtime;
> But in my case there could be two or more equals
> timestamp, cause there's not only one application
> working with DB...
> I'm still reading, searching, trying...
> ciao
> danilo

If your on the same connection, then currval()/nextval() will do the
correct thing.  i.e. if this is the same php page, then it should be the same
connection.  If not, I would use php's session support to pass the sequence number
onto the next page.

currval()/nextval() are connection safe.  As far as I know the php connection system works correctly 
(just don't try to use a DBM file at the same time as a pgsql connection... PHP does NOT like that!)

GB

> 
> 
>  --- Kevin Brannen <kevinb@nurseamerica.net> ha
> scritto: > Greg Patnude wrote:
> > > I am using postgreSQL with Perl::CGI and
> > Perl::DBI::Pg... I would like to be
> > > able to insert a row from my Perl script
> > [$SQL->exec();] and have postgreSQL
> > > return the id of the newly inserted record
> > (new.id) directly to the Perl
> > > script for further processing... Anyone with a
> > solution / idea ???
> > > 
> > > Nearly EVERY table I create in postgreSQL (7.2)
> > has the following minimum
> > > structure:
> > > 
> > > create table "tblName" (
> > > 
> > >     id int4 primary key nextval
> > ("tblName_id_seq"),
> > > 
> > >     ..field...
> > > )
> > 
> > You can either do it in 2 statements, something
> > like:
> > 
> > $dbh->do("insert into tblName ...");
> > my ($id) = $dbh->selectrow_array("select
> > currval('tblName_id_seq')");
> > 
> > Or you could create a function which takes the
> > insert statement, and 
> > ends with doing a select on the currval (as above)
> > and returning that. 
> > As I do the 2 statement approach above, I haven't
> > done a function, but 
> > it doesn't look like it would be that hard to do.
> > 
> > HTH,
> > Kevin
> R
> 
> ______________________________________________________________________
> Yahoo! Musica: notizie, recensioni, classifiche, speciali multimediali
> http://it.yahoo.com/mail_it/foot/?http://it.music.yahoo.com/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 


-- 
GB Clark II             | Roaming FreeBSD Admin
gclarkii@VSServices.COM | General Geek           CTHULU for President - Why choose the lesser of two evils?