Perl MySQL query help please

[learingperl01]

Hello everyone,

Hoping someone can help with what I've been trying to accomplish for a few days with no luck. To begin I am in the process of learning Perl.

Below is the script that I've been having problems with. 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 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. If there is a better way using Perl to do this I would love if someone could give me an example.

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($line);
my @sqlout;
while (@sqlout = $query->fetchrow_array()) {
foreach(@sqlout) {
print "$_";
}
}
}
print Dumper(@sqlout);
Report message to a moderator

Thanks for the help in advance.

 

[max1x]

untested few comments.

#!/usr/bin/perl -w
use strict;
use Data:umper; <-- is this Data:umper;
chomp the $line;
qq | SELECT ... = \?|;

 

[learingperl01]

Thanks for the quick reply...Yes the Dataumper was a typo on my end which is correctly defined on the script. I tried chomping the line then escaping the ? both with no luck and with the same results. For some reason when the file is being read it is not being appended to the query statement. Any other ideas would be REALLY helpful,

thanks everyone for the help!!!

 

[travs69]

my $sql = "select * from user where user = $line";

Just to ask.. you have a table called user with a column called user?

 

[stevexff]

Although it shouldn't make any difference in this instance, you only need to prepare the statement with the placeholder once, so you can move it outside of the while loop.

Note also, that the ? doesn't actually get 'replaced', so it will always print as ?. What really happens is that the value of $line gets bound to the ? placeholder during the execute() call, which is why you only need to prepare it once. Have you tried printing the results? Maybe it's a case-sensitivity issue or leading/trailing blanks in the WHERE clause?

Try

my [red]$[/red]sqlout;
[red]$[/red]sqlout = $query->fetch[red]all_arrayref[/red]();
print Dumper($sqlout);

to avoid the loop...


Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]