Re: Database Update or Insert
by VSarkiss (Monsignor) on Apr 26, 2002 at 18:46 UTC
|
The biggest problem with this is what jsprat has pointed out above: you're counting on only getting primary key violations. You should at least check the error condition coming back from the update before blindly trying to insert the row.
The second problem is that you're not taking advantage of one of DBI's best features: placeholders. You're taking the time to prepare the statement, but binding the values directly, which can be a problem if things need quoting. Here's how it would look:
my $sth = $dbh->prepare("UPDATE $table SET upc=? WHERE item_id=?");
# ...
if ($sth->execute($upc, $item_id)) {
# ...
Some style issues: you're re-declaring $sth in the inner block, masking the outer one, which is unnecessary and can cause confusion in a larger block of code. Finally -- this is just a matter of personal style -- but I find "die-if-not-command" puts the emphasis on the wrong thing. The important part is the command, not the error recovery, which comes through more clearly in this case with "command or die" style.
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: Database Update or Insert
by jsprat (Curate) on Apr 26, 2002 at 18:03 UTC
|
Normally, I wouldn't recommend this. It will work when the only cause of a failed update is that the data doesn't exist.What if the row (page, table) is locked? Do you still want to insert, or update? | [reply] [Watch: Dir/Any] |
Re: Database Update or Insert
by Mr. Muskrat (Canon) on Apr 26, 2002 at 21:45 UTC
|
Alright, I'm not going to say that it's finished but it's a lot better than it was...
foreach (<>) {
my ($item_id, $upc) = split(",",$_);
my $status = upsert($dbh, $table, $item_id, $upc);
print "Item #: $item_id, UPC: $upc, Status: $status\n";
}
sub upsert {
my ($rows, $rows2, $status);
my $true = "0E0";
my ($dbh, $table, $item_id, $upc) = @_;
my $sth = $dbh->prepare("UPDATE $table SET upc=? WHERE item_id=?;")
+or die $dbh->errstr;
my $sth2 = $dbh->prepare("INSERT INTO $table (item_id, upc) VALUES(?
+, ?);") or die $dbh->errstr;
$sth->execute($upc, $item_id);
$rows = $sth->rows;
($rows == 0) ? $true : $rows; # from DBI pod (using variable instea
+d of "0E0")
print "rows is $rows\n" if ($DEBUG);
if ($rows < 1) {
print "Didn't update so trying to insert...\n" if ($DEBUG);
$sth2->execute($item_id, $upc);
$rows = $sth2->rows;
($rows == 0) ? $true : $rows;
print "rows is $rows\n" if ($DEBUG);
if ($rows < 1) {
print "Didn't insert so giving up...\n" if ($DEBUG);
}else{
$status = "Inserted";
}
}else{
$status = "Updated";
}
return $status;
}
Comments, suggests?
Matthew Musgrove
Who says that programmers can't work in the Marketing Department?
Or is that who says that Marketing people can't program?
| [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
Re: Database Update or Insert
by asdfgroup (Beadle) on Apr 26, 2002 at 18:53 UTC
|
| [reply] [Watch: Dir/Any] [d/l] |
|
Perhaps someone should tell Jochen Wiedmann, Alligator Descartes, Gary Shea, Andreas Konig, and Tim Bunce. ;) Why? I got the original error checking code straight from the DBD::mysql pod. I was reading through it again this weekend and said "Hey! That's where I got it from!" :)
And yes, I do talk to myself. And it's perfectly normal. Just ask my shrink. :D
Matthew Musgrove
Who says that programmers can't work in the Marketing Department?
Or is that who says that Marketing people can't program?
| [reply] [Watch: Dir/Any] |
|
Pardon the newbie question - is there a DBD::Oracle pod, and if so, where might I find some example code for it...
| [reply] [Watch: Dir/Any] |
|
Re: Database Update or Insert
by Mr. Muskrat (Canon) on Apr 26, 2002 at 20:01 UTC
|
Okay, thanks for the comments. I'm still learning MySQL and the DBI module.
Matthew Musgrove
Who says that programmers can't work in the Marketing Department?
Or is that who says that Marketing people can't program?
| [reply] [Watch: Dir/Any] |
Why not Replace?
by powerman (Friar) on Apr 27, 2002 at 11:55 UTC
|
Hmm..
Why not use REPLACE instead of UPDATE+INSERT?
The only problem with REPLACE is when you UPDATE not all fields in table, but for your example REPLACE is good.
$dbh->do("REPLACE INTO $table SET item_id=?, upc=?",
undef, $item_id, $upc) or die $dbh->errstr;
| [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] |