New problem now. I appear have resolved my earlier problems. The
final issue appears to have been that the SQL statement was interpreting the
WHERE clause as having multiple arguments. Quoting the where clause seems
to have resolved the error, but the data still is not making it into the
database?!?! Below is the "working" UPDATE line:
$dbh->do(qq{UPDATE systems SET $set WHERE 'Name = $name'});
Not sure what is wrong now though. :(
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:46 AM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
I actually have that covered, I think. The variable $set actually
expands to something like ip='192.168.0.1' , speed='450' , etc. This
portion is working OK, I think. I can print the SQL statement and then
paste it into the MySQL Console and it UPDATEs OK. Below is the portion of
code I use to generate the $set:
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\'";
}
}
Thanks again.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:40 AM
To: NIPP, SCOTT V (SBCSI)
Subject: RE: UPDATE Statement Problem...
THe problem with the update statement is that you need a column name
before the value you are setting it to. EG. UPDATE systems SET col_set
= $set WHERE name = $name;
You are missing the col_set (or whatever the column name is).
Gordon
-----Original Message-----
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:28 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
A bit more information... Here is the error message from the
Perl
script on the UPDATE failure.
DBD::mysql::db do failed: You have an error in your SQL syntax near
'WHERE
Name ='$name' LIMIT 1' at line 1 at sys_db_update1.pl line 41, <INV>
line
81.
-----Original Message-----
From: NIPP, SCOTT V (SBCSI)
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:20 AM
To: 'Michael Ragsdale'; 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
Wow!!! This is one awesome mailing list. I really appreciate
all
of the responses. Several of your suggestions have helped me to narrow
in
on the problem. Here is what I have come up with so far, but still not
quite there.
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";
# print "$dbh->do(q{UPDATE systems SET $set WHERE Name = $name
LIMIT 1})";
$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";
}
}
I am now looking figuring out how to print out the error message
from the failed SQL statement (told you I am a newbie). Once I get that
part figured out and going, I think I can home in on the problem rather
quickly. Then I will just have to port this portion of code from my
laptop
to my Unix platform.
Thanks again for all of the helpful suggestions. Almost
everything
you guys have responded with so far has helped to push me along in the
correct direction.
-----Original Message-----
From: Michael Ragsdale [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 10:03 AM
To: NIPP, SCOTT V (SBCSI); 'Tielman J de Villiers'
Cc: '[EMAIL PROTECTED]'
Subject: RE: UPDATE Statement Problem...
At 10:50 AM 4/2/2002, NIPP, SCOTT V (SBCSI) wrote:
> 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});
q{} does not interpolate your scalars. Try qq{}
-Mike
