Thanks for the feedback, this did the trick for me.
The next question I have is regarding a good way to archive changes.
I have already created a separate archive table. I am mainly curious as to
if there is a standard method of moving a row from one table to another. If
I were to script this manually, I would assume that I select the data and
capture the fields as variable and then have an insert statement to put this
data into the archive table. At this point, I would then delete the
existing row from the current table and then insert the new data into the
current table. I am just curious as to if there is a better way than doing
all of this.
The archive table has two additional fields. Not sure if this makes
any difference or not. The first new field is the Primary Key, which is a
simple autoincrement field. The other extra field is a date field for when
this change was archived off. I know that this is actually redundant,
however, I think it will make some of the other tools easier to write later.
Thanks again for the help.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED]
Web: http:\\ldsa.sbcld.sbc.com
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 20, 2004 10:39 AM
To: [EMAIL PROTECTED]
Subject: Fw: Need some MySQL query help...
You can try a != instead of the <> for not equal.
----- Forwarded by Patricia A Markiewicz/US/DNY on 01/20/2004 10:38 AM
-----
"NIPP, SCOTT V
(SBCSI)"
<[EMAIL PROTECTED]> To
Hardy Merrill
01/20/2004 10:23 <[EMAIL PROTECTED]>,
AM [EMAIL PROTECTED]
cc
Subject
RE: Need some MySQL query help...
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
