RE: Need some MySQL query help...

[NIPP, SCOTT V (SBCSI)]

        Thanks for the feedback.  I am getting an error during the compile.
I think my problem is in the structure of the SELECT statement.  I was
taking a guess on how to test for inequality.  My guess now is that the "<>"
is simply invalid, and this is where my problem is coming from.  Here again
is the SELECT statement that I think is the problem, along with output from
attempting to run the script:

my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1' AND
(uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <> '$home' OR
shell <> '$shell')");
$test->execute ();

***  OUTPUT  ***
syntax error at ./passwd2db.pl line 34, near ""Adding $key1 to password
database
. \n";"
Execution of ./passwd2db.pl aborted due to compilation errors.

        In the mean time, I'll start reading up on using placeholders too.
Thanks again.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED]
Web:  http:\\ldsa.sbcld.sbc.com



-----Original Message-----
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, January 20, 2004 10:13 AM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Need some MySQL query help...


You're saying that you have a problem and that you think the problem is
in the matching logic in the SELECT, but you don't say exactly _what_ is
happening.  Put some prints in so you can see the values of the
variables you are plugging into the sql - make sure those values match
what you are expecting.  If that doesn't tell you enough, you can try
using DBI's "trace" method to help you see exactly what values are being
passed to the database.

I also suggest using placeholders - if you have quoting issues,
placeholders will solve those for you.  And they'll make things run
faster.  Read about placeholders by doing

   perldoc DBI

at a command prompt and searching(using the forward slash "/") for
"Placeholder".  Those perldocs also describe using "trace".

HTH.

Hardy Merrill

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/20/04 10:48AM >>>
        I am working on a set of Perl scripts, along with some PHP web
pages, to help organize and automate user account creation in a large
HP-UX
environment.  I am currently writing a few scripts to gather all of
the
existing user account data from every system and populate a couple of
database tables.  One feature I am working on is to have a history
table to
track all changes to user accounts.  
        I am currently working on the logic of the script that populates
the
database tables and what I am attempting to do is compare the existing
passwd entry to the current database entry.  The Primary Key on the
current
table (acct_db) is a combination of userID and hostname.  I want the
query
to match the Primary Key, and compare all of the data in the passwd
file to
the data in this table.  Assuming the data is all a match, nothing
happens,
the script simply proceeds to the next entry.  If however there is a
difference, the script should delete the entry and populate this same
entry
into the history table (acct_hist) and also insert the new data into
the
current table.
        This is where I am currently running into problems.  I am not
sure
exactly how to test for inequality of all the variables in the passwd
file.
I was hoping to have a single MySQL query do the test in order to use
it to
help simplify the Perl code.  The SELECT statement is where I think I
am
going wrong here.  Here is the code loop that processes the passwd
file
data:

while ($file = readdir(DATA)) {
  if ($file =~ /passwd/) {
    ($host) = split /\./, $file, 2;
    print "Password file for $host found.  Now processing...\n";
    open(FILE, "/usr/local/mysql/tmp_data/$file");
    while ($entry = <FILE>) {
      ($name, $passwd, $uid, $gid, $gcos, $home, $shell) =
split(/:/,$entry);
      if ($uid > 100) {
        $key1 = "$name"."-"."$host";
        my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 =
'$key1'
AND (uid <> '$uid' OR gid <> '$gid' OR gcos <> '$gcos' OR home <>
'$home' OR
shell <> '$shell')");
        $test->execute ();
        $rows = $test->rows;
        if ($rows == 0) {
          $dbh->do("INSERT INTO acct_db
VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())")
or print "Error updating database:  ", $dbh->errstr, "\n";(
          print "Adding $key1 to password database. \n";
        } elsif ($rows == 1) {
          print "$key1 already in database.  Updating entry now.\n";
        }  else {
          print "Error. \n";
        }
      }
    }
  }
}

        Thanks in advance for any help.  

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com