MySQL Performance: MySQL => Perl Mysql

Perl Mysql [message #1358]

Sun, 03 June 2007 17:30

mysqluser81
Messages: 15
Registered: February 2007

Junior Member

Hello everyone not sure if this is the correct forum to ask this question if not I apologize in advance. Here is goes...I have been working on getting this script to work for a few days now changing things around with no luck.

For debugging purposes I am printing both the $line variable and the $sql query variables. When I run ./dbquery.pl I get the following as you can the variables are being read in correctly but not being applied to the query. I have tried a bunch of things but I am not getting anywhere hoping that someone can point me in the right direction. What I am trying to do is a SELECT query againt the MySQL db using a perl script. One of the things that I need to happen though is when executing the query for example [b]SELECT * FROM user WHERE user = "DATA_COMING_FROM_FILE";[\B] the "DATA_COMING_FROM part is a list of users which will be read from a file which contains a list of users.

Here are the results of running the script on the shell.

The line result is user01
The sql_query is SELECT * FROM user WHERE user = ?
The line result is user02
The sql_query is SELECT * FROM user WHERE user = ?
The line result is user03
The sql_query is SELECT * FROM user WHERE user = ?

As you can see the query is SELECT * FROM user WHERE user = ?. For some reason the ? is not being replaced with one of the user0x entries.

Thanks for the help.

#!/usr/bin/perl
use DBI;
use File::Slurp;
use Data:umper;

my @lines = read_file('/root/perl/dbquery.txt');
my $db_user = q|root|;
my $db_pass = q|password|;
my $dbname = q|mysql|;
my $db_host = q|localhost|;

my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$db_host", "$db_user",
"$db_pass", {PrintError => 1, RaiseError => 1, AutoCommit => 1});

foreach my $line (@lines) {
print "The line result is ", "$line\n";
my $sql = q|SELECT * FROM user WHERE user = ?|;
print "The sql_query is ", "$sql\n";
my $query = $dbh->prepare($sql) || die "Error prepraring query";
$query->execute($lines);
my @sqlout;
while (@sqlout = $query->fetchrow_array()) {
foreach(@sqlout) {
print "$_";
}
}
}
print Dumper(@sqlout);

Report message to a moderator