Perl Mysql

MySQL & MariaDB Other MySQL® Questions
1

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 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);

2

Some points:

Always use:

use strict;

in perl.
Then you will have spotted the fact that you are using a variable that is not defined.

The prepare should only be performed once outside the loop.
Then you just call the execute inside the loop, there is no need to prepare the same statement more than once.

Your print of the $sql is not meaningful since the replace is performed in the execute().

What you want to do when trouble shooting database queries is to use the DBI tracing mechanism.
Then you will be able to see what is sent to the DB and what is not when you execute the query.

Write the strings with single quotes instead, unless they actually contain single quotes then you can use the q{} notation (or as in your case q||).
It is more standard coding practice and makes it easier to read.

So all in all my suggestion to your script would be something like this:

#!/usr/bin/perluse DBI;use File::Slurp;use Data::Dumper;use strict; # Added since it makes a lot of sanity checks of your perl scriptmy @lines = (‘testing’,‘newtest’); #read_file(‘/root/perl/dbquery.txt’);my $db_user = ‘root’;my $db_pass = ‘password’;my $dbname = ‘mysql’;my $db_host = ‘localhost’;my $dbh = DBI->connect(“DBI:mysql:database=$dbname;host=$db_host”, $db_user,$db_pass, {PrintError => 1, RaiseError => 1, AutoCommit => 1});my $sql = ‘SELECT * FROM user WHERE user = ?’;print "The sql_query is ", “$sql\n”;my $query = $dbh->prepare($sql) || die “Error preparing query”;foreach my $line (@lines) { print “The line result is $line\n”; # example of adding trace for debug purpose $query->trace(1); # Execute # Error here since it said $lines previously which was undefined $query->execute($line); my @sqlout; while (@sqlout = $query->fetchrow_array()) { foreach(@sqlout) { print “$_”; } } # check for problems which may have terminated the fetch early die $query->errstr if $query->err;}# Commented out since it will not work since you# are defining @sqlout within the foreach loop# print Dumper(@sqlout);

3

Thanks so much for the tips and code. The only problem is now when I try to read the external file instead of using my @lines = (‘user01’,‘user02’); I get no results, but when I use the my @lines = (‘testing’,‘newtest’) with the users listed in the file I get the correct results.

I have tried both of the lines of code below with no luck.
my @lines = read_file(‘/root/perl/dbquery.txt’);
open(han1, “/root/perl/dbquery.txt”) || die “Could not open file”;

Is there a way that I need to have the file read into the sql query.

Below is the update code.
thanks so much for your help

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

use strict; # Added since it makes a lot of sanity checks of your perl script
#my @lines = read_file(‘/root/perl/dbquery.txt’);
open(han1, “/root/perl/dbquery.txt”) || die “Could not open file”;
my @lines = ;
my $db_user = ‘root’;
my $db_pass = ‘password’;
my $dbname = ‘mysql’;
my $db_host = ‘localhost’;

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

my $sql = ‘SELECT * FROM user WHERE user = ?’;
print "The sql_query is ", “$sql\n”;
my $query = $dbh->prepare($sql) || die “Error preparing query”;

foreach my $line (@lines) {
print “The line result is $line\n”;

example of adding trace for debug purpose

$query->trace(1);

Execute

Error here since it said $lines previously which was undefined

$query->execute($line);
my @sqlout;
while (@sqlout = $query->fetchrow_array()) {
foreach(@sqlout) {
print “$_”;
}
}

check for problems which may have terminated the fetch early

die $query->errstr if $query->err;
}

This is the output of running the script

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

DBI::st=HASH(0x919a250) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.52-ithread (pid 1832)
← execute('user01
')= ‘0E0’ at query.pl line 28
← fetchrow_array= ( ) [0 items] row-1 at query.pl line 30
← err= undef at query.pl line 36
← DESTROY(DBI::st=HASH(919a250))= undef

4

I think that your problem is that you are reading the rows from the file and that the newline is still left in the string.

Test this for reading the external file:

open(han1, “/root/perl/dbquery.txt”) || die “Could not open file”;my @lines = ;chomp(@lines);

The chomp() function removes newlines from the end of the file.

5

That did it you the MAN!!!

thanks a lot for all the help and detailed explaination I definitely learn a lot thanks to the examples and explanations.

Have a good one!!