my $enroll = Y; my $cpaid = 12345;
my $sth = $dbh->prepare(q{ insert into war.rfrl_assn (CPA2BIZ_ENROLLED,CPAID) values (?,?) }) or die $dbh->errstr;
$sth->execute($enroll,$cpaid) or die $dbh->errstr;
of course, when i want to insert a new record with different values for enroll or cpaid, i can just call the execute statement again.
HTH
-- ------------------------------- Just Your Friendly Neighborhood _SPIDEY_
NIPP, SCOTT V (SBCSI) wrote:
OK. I am working on converting this to use placeholders and the "qq" quoting option. I am obviously very new to placeholders, so this is probably a stupid question, but here goes. I execute the script and receive the following error:
[EMAIL PROTECTED]:/home/sadmin/sn4265/perl> timex ./passwd2db.pl Name "main::passwd" used only once: possible typo at ./passwd2db.pl line 25. Password file for argon found. Now processing... Undefined subroutine &main::NOW called at ./passwd2db.pl line 33, <FILE> line 11.
The following is the section of code that includes the "NOW" on line 33. Obviously there is a problem with the way I am doing the placeholders here.
$dbh->do(qq{ INSERT INTO acct_db VALUES(?,?,?,?,?,?,?)}, undef,$key1,$uid,$gid,$gcos,$home,$shell,NOW()) or print "Error updating database: ", $dbh->errstr, "\n";
Thanks again for the help.
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: Thursday, January 22, 2004 12:46 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: RE: Strange matching problem...
This time HM### - I hate Groupwise! Can't properly quote anything.
"NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:32PM >>>
OK... First of all, today, this afternoon, I am going to learn how to use placeholders and get that fixed. I promise. :)
HM### Your "non-placeholder" code is not wrong - it can just be done better with placeholders :)
HM>> I don't see any matching logic in your code below, but here's a general thought - instead of a string equality check ( if ($acct1 eq $acct2) ), you could use a regular expression with the "i" (ignore case flag) something like ( if ($acct1 =~ /$acct2/i )
The matching logic is actually a part of the MySQL query. This
part
works great, but it was case sensitivity that was giving me the fits. I
discovered that VARCHAR and CHAR data types in MySQL are not case
sensitive
by default. You have to set the column to have a BINARY column
modifier to
make these data types case sensitive. This seems to have fixed at
least
that problem.
HM### Sorry, I missed that the 1st time around. Here is one of your selects:
my $test = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1'");
HM### you should be able to use something like this (I'm not sure exactly what MySQL's syntax is for this): my $test = $dbh->prepare(qq{ SELECT * FROM acct_db WHERE UPPER(key1) = UPPER('$key1') });
or, which placeholders, like this:
my $sth = $dbh->prepare(qq{ SELECT * FROM acct_db WHERE UPPER(key1) = UPPER(?) }); $sth->execute($key1);
the idea is to make sure the case on both sides is the same. MySQL may also have some sort of regular expression matching where you can ignore case, but I'm not familiar enough with it to know definitely.
HTH.
Hardy Merrill
Now, I'll get to work on the placeholders, and see about the other issue. If the BINARY column modifier doesn't fix this, hopefully I can come back with some more useful information. 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: Thursday, January 22, 2004 12:22 PM
To: [EMAIL PROTECTED]; NIPP, SCOTT V (SBCSI)
Subject: Re: Strange matching problem...
My comments below as HM>>. I'll repeat one general suggestion made previously - use placeholders :)
I am working on a script to gather data about Unix user"NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 01/22/04 01:05PM >>>
accounts.
This is going well so far and has gotten us some initial data such as
almost
27000 user accounts across about 80 servers. I have built into the
script
and database some checking to track changes basically capture these
changes
in a historical table. I am running into at least two problems I am
currently unable to explain...
The first problem appears to be a matching problem with case
sensitivity. There are several instances on a couple of systems that
have
an account twice, once with the account name in all upper case and the
other
in all lower case. I am not sure exactly what/why this is occurring. Any
help on this would be appreciated.
HM>> I don't see any matching logic in your code below, but here's a general thought - instead of a string equality check ( if ($acct1 eq $acct2) ), you could use a regular expression with the "i" (ignore case flag) something like ( if ($acct1 =~ /$acct2/i )
The other problem is an account with the same name on two servers that is being captured as changed every time. This is strange as I am simply running the script repeatedly on the same dataset. Any ideas on this would be most appreciated also.
HM>> Can't help you with this one - not enough info.
HTH.
Hardy Merrill
Below is the relevant sections of code...
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'"); $test->execute (); $rows = $test->rows; #print "Return value: $rows\n"; 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) { my $test1 = $dbh->prepare("SELECT * FROM acct_db WHERE key1 = '$key1' AND (uid != '$uid' OR gid != '$gid' OR gcos != '$gcos' OR home != '$home' OR shell != '$shell')"); $test1->execute(); @old = $test1->fetchrow_array (); if ($old[0]) { print "$key1 requires updating in database. Updating entry now.\n"; # Insert existing data into acct_hist. $dbh->do("INSERT INTO acct_hist (key1, uid, gid, gcos, home, shell, ent_time, arc_time) VALUES ('$old[0]','$old[1]','$old[2]','$old[3]','$old[4]','$old[5]','$old[6]',NOW() )"); # Delete existing data from acct_db. $dbh->do("DELETE FROM acct_db WHERE key1 = '$key1'"); # Insert new entry into acct_db. $dbh->do("INSERT INTO acct_db VALUES('$key1','$uid','$gid','$gcos','$home','$shell',NOW())") or print "Error updating database: ", $dbh->errstr, "\n"; } else { #print "$key1 is up to date in database. No update necessary.\n"; } } else { print "Error. \n"; } } }
The basic functionality of this script is working great. Data is definitely making it into the database, and testing changes works as intended. The problems mentioned above are what I am trying to rectify at this point. Any other suggestions on improvements or better methods for doing some of this stuff are most welcome too. Thanks in advance.
Scott Nipp
Phone: (214) 858-1289
E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com
-----------------------------------------
The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or any employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.
Thank you. Paychex, Inc.