RE: UPDATE Statement Problem...

[NIPP, SCOTT V (SBCSI)]

        Following another suggestion, I tried using the 'qq' quoting method
and received the following error.

DBD::mysql::db do failed: Unknown column 'harry' in 'where clause' 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: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