Need some MySQL query help...
From: Scott V Nipp (sn4265_at_sbc.com)
Date: 01/20/04
- Next message: Eric: "Installing DBD::mysql on MacOSX 10.3.2 (panther)"
- Previous message: Rob Saunders: "Apache2, perl, DBI, Informix - connect problems"
- Next in thread: Hardy Merrill: "Re: Need some MySQL query help..."
- Maybe reply: Hardy Merrill: "Re: Need some MySQL query help..."
- Maybe reply: Patricia Markiewicz: "Fw: Need some MySQL query help..."
- Maybe reply: Scott V Nipp: "RE: Need some MySQL query help..."
- Maybe reply: Ronald Kimball: "RE: Need some MySQL query help..."
- Maybe reply: Scott V Nipp: "RE: Need some MySQL query help..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
To: dbi-users@perl.org Date: Tue, 20 Jan 2004 09:48:54 -0600
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: sn4265@sbc.com
Web: http:\\ldsa.sbcld.sbc.com
- Next message: Eric: "Installing DBD::mysql on MacOSX 10.3.2 (panther)"
- Previous message: Rob Saunders: "Apache2, perl, DBI, Informix - connect problems"
- Next in thread: Hardy Merrill: "Re: Need some MySQL query help..."
- Maybe reply: Hardy Merrill: "Re: Need some MySQL query help..."
- Maybe reply: Patricia Markiewicz: "Fw: Need some MySQL query help..."
- Maybe reply: Scott V Nipp: "RE: Need some MySQL query help..."
- Maybe reply: Ronald Kimball: "RE: Need some MySQL query help..."
- Maybe reply: Scott V Nipp: "RE: Need some MySQL query help..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
