perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bruce Tennant <bluewl...@yahoo.com>
Subject Re: (maybe offtopic) mod_perl/DBI/PostgreSQL
Date Fri, 15 Aug 2003 00:54:54 GMT
Yeah, actually here is the code snippets I use.
 
Table def:
 
CREATE SEQUENCE s_league_lid minvalue 1;
 
CREATE TABLE t_League (
        /*      lid      integer DEFAULT nextval('s_league_lid') PRIMARY KEY,*/
        lid      SERIAL PRIMARY KEY NOT NULL,
        name     VARCHAR(20) UNIQUE NOT NULL,
        numdiv   integer, 
        draft    integer,
        last_wvr integer 
);
 
As you can see above, I've tried the manual sequence method and the new convenience method.
 
in module "new" function:
 
   $self->{sth}->{League_by_lid} =
       $self->{dbh}->prepare("select * from t_League where lid = ?");
   $self->{sth}->{League_by_name} =
       $self->{dbh}->prepare("select * from t_League where name = ?");
    $self->{ith}->{League} =
        $self->{dbh}->prepare("insert into t_League (name,numdiv,draft,last_wvr) VALUES(?,?,?,?);");
 
Then in a member function, called later:
 
call execute on the by_name handle to see if new name already exists
if it doesn't
  call execute on the insert handle with the new row data
then call the select by name handle to get the id that was used to create new row.
 
all of my execute statements have "or die $DBI::errstr;" tacked on the end to catch any errors.
 


greg@mail.netio.org wrote:
Do you have any code that might do a select to determine the value of the
sequence used?

Example:

INSERT blah blah blah;
SELECT currval(sequence_name);

or somethign like that?

If so, the SELECT may be incrementing the counter and that would explain
the jump by 2 your seeing.

There is a function to determine the value of the sequence without
incrementing for postgres, but I don't remember right now what that is.

I actually just did something like this myself. Creating an entry in a
table and then needing to find out what the entry id was. In my case, I
actually ended up just doing a SELECT with a where clause including the
data I just inserted, reverse sorted by record creation date (timestamp
default now() kind of thing, not included in INSERT statement) with a
limit of 1.

I know I could have used the function to get the sequence value, but I
wasn't absolutely sure what would happen. If two processes incremented the
sequence at the same time, what would the function return, the value
for that connection's insert, or the value of the other session?
Especially when I added Apache::DBI to the mix where db connections appear
to be pooled and shared. This way I know I'm getting the value I'm looking
for.

Greg




www.bluewolverine.com

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
Mime
View raw message