rickphil has asked for the wisdom of the Perl Monks concerning the following question:

Hey all, I am in a bit of spot where someone wants data that's in a mysql db. The script I am trying to cobble up needs to read a flat file with 2 columns of data Column1= and 11 digit number and Column2 = date number appears in the deletions file. Once the file gets read the script loops thru a query to the db, using the enteries in each line. Anyone have a sec to take a look and offer some help ? 
# This script reads a file and creates 2 variables with it by using a 
+hash.
# the data is in 2 columns. Column1=msisdn Column2=DeletionDate
# This script may be helpful if expanded to a loop and db query for fi
+guring
# last possible active date.
#
#  Sample of the file read by script
#  19992507638 deletions.20060723
#  19993017551 deletions.20060723


#!/usr/bin/perl
use DBI;
$database = "chai";
$hostname = "localhost";
$port = "3306";
$username = "bonezer";
$password = 'monezer';

$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $username, $password) or die("Could not conn
+ect!");
$sql = "select to_days(substring($dday,11)) - to_days(lastactive)  fro
+m user where number = '$msdn' into OUTFILE '/tmp/sp'";

open DATA, "/tmp/SEOUL_NUMBERS";
my %hash;
while( <DATA> ) {

        @elements = split / /, $_;

        {
                $hash{ $elements[0] } = $elements[1];
                $hash{ $elements[1] } = $elements[1];
        $msdn="$elements[0]\n";
        $dday=$elements[1];



$sth = $dbh->prepare($sql);
$sth->execute;


}
}
$dbh->disconnect;

[download]

Replies are listed 'Best First'.
Re: Looping an SQL query
by GrandFather (Saint) on Apr 30, 2007 at 23:48 UTC

    I strongly recommend that you use strictures (use strict; use warnings;). In this case your attention would have been drawn to the assignment to $sql which interpolates two undeclared variables ($dday, $msdn). It is interesting to note that these variables are given values inside a loop, but too late to affect the contents of $sql.

    You need to construct $sql inside to loop after the variables have been initialized.

    DWIM is Perl's answer to Gödel
[reply]
[d/l]
[select]
Re: Looping an SQL query
by eric256 (Parson) on Apr 30, 2007 at 23:56 UTC 

    use strict;
use warnings;

    [download]
    and use placeholders in your sql. Put a ? in your sql where you would liek to put a value, then when you call execute pass the values. 

    #!/usr/bin/perl

# This script reads a file and creates 2 variables with it by using a 
+hash.
# the data is in 2 columns. Column1=msisdn Column2=DeletionDate
# This script may be helpful if expanded to a loop and db query for fi
+guring
# last possible active date.
#
#  Sample of the file read by script
#  19992507638 deletions.20060723
#  19993017551 deletions.20060723



use strict;
use warnings;
use DBI;

my $database = "chai";
my $hostname = "localhost";
my $port = "3306";
my $username = "bonezer";
my $password = 'monezer';

my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
my $dbh = DBI->connect($dsn, $username, $password) or die("Could not c
+onnect!");

my $sql = "select to_days(substring( ?,11)) - to_days(lastactive)  fro
+m user where number = ? into OUTFILE '/tmp/sp'";
my $sth = $dbh->prepare($sql);

open(DATA, "/tmp/SEOUL_NUMBERS") or die "Failed to open /tmp/SEUL_NUMB
+ERS file: $!";
my %hash;
while( <DATA> ) {
    @elements = split / /, $_;
    $hash{ $elements[0] } = $elements[1];
    $hash{ $elements[1] } = $elements[1];
    $sth->execute($elements[1], "$elements[0]\n");
}
$dbh->disconnect;

    [download]

    It's untested, but hopefully you can see the use of strict, warnings, and placeholders as well as prepared SQL statements. Cheers!


    ___________
    Eric Hodges
[reply]
[d/l]
[select]
Re: Looping an SQL query
by shigetsu (Hermit) on Apr 30, 2007 at 23:59 UTC
[reply]

Back to Seekers of Perl Wisdom