RE: UPDATE Statement Problem...

[Gordon.Rhea]

Now that I read this more carefully I have some more thoughts on this.
You need to do some error checking when you call execute.  Simply
checking the value of $test won't do it as $test is set to a statement
handle by prepare.  The line "if($test)" should always be true as $test
will always be non-zero.  I'm not sure what you're trying to check for
with that unless it's just to check to see if the statement was prepared
correctly.  This will not check to see if any data was returned.  The
prepare statement only returns a statement handle.  If there is no data
associated with the handle it will still have a non-zero value.  You
need to check the value(s) returned by the query to determine whether an
insert statement is necessary.  

Another good idea is to use error checking at every possible place.  Try
using the line "$test->execute() or die print "ERROR updating table" .
$DBH->errstr();" to trap any errors and get some return error message
from the database.  You also need to trap errors on the other SQL
statements in a similar fashion in case there is a problem you can tell
there was a problem in the SQL and get some sort of usable error message
from the database.  I think your update problem comes from the fact that
there is a syntax error in the update, but there are other problems in
the script as well.

Hope this helps,
Gordon

-----Original Message-----
From: NIPP, SCOTT V (SBCSI) [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 02, 2002 9:40 AM
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