I added a COMMIT immediately after the UPDATE, and still have the
same problem. Below is exactly what I added, with the lines immediately
before and after.
$dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT 1});
$dbh->do("COMMIT"); #Just added!!!
} else {
-----Original Message-----
From: Tielman J de Villiers [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 9:39 AM
To: NIPP, SCOTT V (SBCSI)
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Check how you connect -- if Autocommit is not on, then you need to commit
after you update
Tielman J de Villiers
BondNet Pty Ltd
-----Original Message-----
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 5:40 PM
To: '[EMAIL PROTECTED]'
Subject: UPDATE Statement Problem...
Hey guys. I am still quite new to the database world and obviously
in need of help. Not sure if I am just stupid, or if there is really very
little information to be found concerning UPDATE statements. I am working
on a MySQL database with Perl 5.6.0 and the latest DBI version. I am doing
fine with connectivity, in that I have INSERT and SELECT statements working
fine. My problem is that an UPDATE statement that I have been working with
is NOT working. Here is, I hope, the relevant portion of the code.
my $test = $dbh->prepare("SELECT * FROM systems WHERE Name ='$name'");
$test->execute ();
if ($test) {
while (my $ref = $test->fetchrow_hashref ('NAME_lc')) {
undef $set;
foreach my $key (keys %$ref) {
unless ($$key eq $ref->{$key}) {
$set .= " , " if $set;
$set .= $key . "=$$key";
}
}
if ($set) {
print "$name found in database. Updating information for $name.
\n";
print "$set \n";
$dbh->do(q{UPDATE systems SET = $set WHERE Name = $name LIMIT 1});
} else {
print "$name found in database to be current. No update
necessary. \n";
}
}
} else {
print "$name was NOT found in database. Adding database entry for
$name. \n";
$dbh->do("INSERT INTO systems
VALUES('$name','$id','$ip','$model','$cpunum','$speed','$os_ver','$mem','$sc
si','$fibre','$disks','$size','$tapes','$sa')")
or print "Error updating database: ", $dbh->errstr, "\n";
}
}
This database stores system information. I have written Perl
scripts to collect all of this information from the systems, format the
output, and FTP it over to the database server. I have no problem INSERTing
new systems into the database, the problem I have is UPDATEing existing
systems. I know that I could simply DELETE and then INSERT the system again
with all of the new information, but this seems a very inelegant way of
handling this. Any help would be GREATLY appreciated.
Scott Nipp
Systems Analyst
SBC Long Distance
(214) 858-1289
