Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert.

One way to achieve database portability and avoid race conditions is to assign the id from a common source independent of the database, then insert the id along with your data. The example below (untested) of course is not OS portable and has some warts of its own.

my $episodeid = nextId();

my $query=qq(INSERT INTO MasterEpisode SET 
    episoideid   = "$episodeid",
    ShowId       = "$ShowID",
    title        = "$p->{'ep_title'}",
    description  = "$p->{'ep_description'}",
    uploaddate   = "@{[localtime]}"
    );

sub nextId {
    open my $seq, "+<", "sequence.dat";
    flock $seq, 2
    my $id = <$seq>;
    $id++;
    print $seq $id;
    close $seq;
    return $id;
}
[download]

Comment on Re: DBI insert: Need to retrieve an autoincremented column from a table after an insert. Download Code

Replies are listed 'Best First'.
Re^2: DBI insert: Need to retrieve an autoincremented column from a table after an insert. by runrig (Abbot) on Aug 09, 2008 at 22:06 UTC
assign the id from a common source independent of the database I would want to keep the sequence ids in the database...one way to do that in a portable way is with DBIx::Sequence.	[reply]
Re^3: DBI insert: Need to retrieve an autoincremented column from a table after an insert. by hangon (Deacon) on Aug 10, 2008 at 00:20 UTC
I would want to keep the sequence ids in the database I agree, its one of the warts from using a separate file. Like the OP, I've never completely trusted last_insert_id either, and getting unique row id's is just not an easy thing to do portably. I wasn't aware of DBIx::Sequence, just tried it out and it seems to work as advertized. Thanks for the pointer.	[reply]