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.
[EMAIL PROTECTED] wrote:
Do you have any code that might do a select to determine the value of thesequence 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 explainthe jump by 2 your seeing.There is a function to determine the value of the sequence withoutincrementing for postgres, but I don't remember right now what that is.I actually just did something like this myself. Creating an entry in atable and then needing to find out what the entry id was. In my case, Iactually ended up just doing a SELECT with a where clause including thedata I just inserted, reverse sorted by record creation date (timestampdefault now() kind of thing, not included in INSERT statement) with alimit of 1.I know I could have used the function
to get the sequence value, but Iwasn't absolutely sure what would happen. If two processes incremented thesequence at the same time, what would the function return, the valuefor that connection's insert, or the value of the other session?Especially when I added Apache::DBI to the mix where db connections appearto be pooled and shared. This way I know I'm getting the value I'm lookingfor.Gregwww.bluewolverine.com
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software