$code or die has asked for the wisdom of the Perl Monks concerning the following question:
Hi guys. I need some help! I've been at work now for 36 hours (seen the same people come and go twice already!) and I've been stumped on a problem for a bit.
I'm trying to insert a row into a database and pull out the ID (primary key) that the database assigned automatically. I'm using DBI, but I tried the AddNew() func in ADODB.Recordset but that was no joy. Back to DBI now.
My original thought was insert the data and do a "Select MAX" on the primary key, but could end up worse than the race conditions you get in bad file-locking practices.
I am sure that there must be a way of doing this in DBI, but I can't see how - execute() returns 1 always (almost always)
It's starting to do my head in! I read through the DBI docs again but couldn't find anything - neither on usenet nor on PM Super Search. Although this could be due to my almost delirious hyper-caffeinated state. The answer is probably staring me in the face. Oh, I tried DBIx::Recordset and didn't like it much although I suspect it "might" be able to do what I want (sorry Terrence).
I appreciate any help available. Here's a bit of code I've munged over the past few hours... (I know its not terribly efficient at this stage but I'll fix that once I figure out how to do this primary key thing):
$ perldoc perldoc
I'm trying to insert a row into a database and pull out the ID (primary key) that the database assigned automatically. I'm using DBI, but I tried the AddNew() func in ADODB.Recordset but that was no joy. Back to DBI now.
My original thought was insert the data and do a "Select MAX" on the primary key, but could end up worse than the race conditions you get in bad file-locking practices.
I am sure that there must be a way of doing this in DBI, but I can't see how - execute() returns 1 always (almost always)
It's starting to do my head in! I read through the DBI docs again but couldn't find anything - neither on usenet nor on PM Super Search. Although this could be due to my almost delirious hyper-caffeinated state. The answer is probably staring me in the face. Oh, I tried DBIx::Recordset and didn't like it much although I suspect it "might" be able to do what I want (sorry Terrence).
I appreciate any help available. Here's a bit of code I've munged over the past few hours... (I know its not terribly efficient at this stage but I'll fix that once I figure out how to do this primary key thing):
Thank you guysuse DBI; # declared variables - dsn, etc... my @columns = keys %$input; my $columns = join ', ', @columns; my @values = map {$input->{$_} } @columns; my $vars = join ',', map { "?" } @columns; my $conn = DBI->connect($dsn, $user, $pass); my $sql = "Insert Into $table ($columns) values ($vars)"; my $sth = $conn->prepare($sql); $sth->execute(@values); # return $sth->{int_id} ?
$ perldoc perldoc
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBI and primary keys
by lachoy (Parson) on Mar 28, 2001 at 00:21 UTC | |
by BigJoe (Curate) on Mar 28, 2001 at 02:01 UTC | |
Re: DBI and primary keys
by busunsl (Vicar) on Mar 28, 2001 at 00:17 UTC | |
(boo) Re: DBI and primary keys
by boo_radley (Parson) on Mar 28, 2001 at 00:19 UTC | |
by $code or die (Deacon) on Mar 28, 2001 at 00:34 UTC | |
by Yohimbe (Pilgrim) on Mar 28, 2001 at 01:09 UTC | |
Re: DBI and primary keys
by mr.nick (Chaplain) on Mar 28, 2001 at 00:14 UTC | |
by tadman (Prior) on Mar 28, 2001 at 00:17 UTC | |
by $code or die (Deacon) on Mar 28, 2001 at 00:20 UTC | |
by chromatic (Archbishop) on Mar 28, 2001 at 00:25 UTC | |
by busunsl (Vicar) on Mar 28, 2001 at 00:30 UTC | |
by busunsl (Vicar) on Mar 28, 2001 at 00:24 UTC | |
by boo_radley (Parson) on Mar 28, 2001 at 00:44 UTC | |
by $code or die (Deacon) on Mar 28, 2001 at 00:59 UTC | |
by sutch (Curate) on Mar 28, 2001 at 00:18 UTC | |
Re: DBI and primary keys
by wardk (Deacon) on Mar 28, 2001 at 03:09 UTC |
Back to
Seekers of Perl Wisdom