ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Hello everyone, wondering if someone could help me out with the script I am trying to write. I am still pretty new to perl but I can somewhat get myself through some simple stuff. I am however stuck with the script below. I am trying to write a script where I will feed it a file with about 100+ entries and want to create a select statement where the file defined can be set as a varible in the SQL query.
For example below I am playing around with the MYSQL db. I am doing a select * from user where user = 'root';(except when I feed it via the ARGV)
What I was doing just to test/until I get the script working is I have created a file called sqlquery.txt with one entry in the file that says root. Now I when I run the script along with the ARGV of the sqlquery.txt file I get no error nor do I get and results. (./db1.pl sqlquery.txt)
Wondering if someone can please help me with the following
1) not sure if I am supposed to loop through the my @q1 = <FILE> so that all 100+ entries get queried. If I do need this how/where should this be done at?
2) How would I implement passing a variable to the $query. my $query = 'SELECT * FROM user where user = $VARIABLE/ARGV-FILE
#!/usr/bin/perl
use DBI;
my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database";
open(FILE,$ARGV[0]) || die "This script requires a file to be defined along with the script";
my @q1 = <FILE>;
my $query = 'SELECT * FROM user where user = \'$q1\'';
my $query_prep = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;
$query_prep->execute || die "Error executing query" . $query_prep->errstr;
my @results;
while (@results = $query_prep->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}
This is another way I have tried it with no luck, not sure what the heck I am doing wrong. I am passing the $console variable to the query but I get no error or results. Again just to be clear what I am trying to do it send a list of entries from a file and feed it to the mysql query.
thanks again for the help
#!/usr/bin/perl
use strict;
use DBI;
my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database"; my $console = $ARGV[0];
my $query = 'SELECT * FROM user where user = \'$console\'';
my $query1 = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;
$query1->execute || die "Error executing query" . $query1->errstr;
print "$query";
my @results;
while (@results = $query1->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}
if ($query1->rows == 0 ) {
print "No Records", "\n";
}
If you've got a bunch of names to check, you can either loop through them doing one at a time, or you can quote the lot and use the SQL keyword 'IN' eg:
where user IN (<your csv list of quoted names>)
In normal SQL that'd look like:
WHERE user IN ('user1', 'user2')
Chrism thanks for the help/pointer. I have followed your advice but now I am running into another small problem. Below is the new code. While testing the output of the $query I see that the the actually file is being read currently but since I am using the $_ in the mysql_quote it is appending the \n to each query that is being executed. Is there anyway to resolve that, if so how? Also is there a better way to do this if so I am open to suggestions (with example please).
For example in the file I have user1, user2, user3...etc each user is separated by a newline. So when I execute the query the results of the $query are as follow.
SELECT * FROM user where user = 'user1\n' SELECT * FROM user where user = 'user2\n'
SELECT * FROM user where user = 'user3\n'
#!/usr/bin/perl
use DBI;
# Step1 - create a connection object this will grant you access to the DB
my $username = 'root';
my $dsn = 'DBI:mysql:mysql';
my $conn = DBI->connect($dsn,$username) || die "Could not connect to the database";
#my $console = $ARGV[0];
# Step 2 - define the query you want to perform.
open(han1, "dbquery.txt") || die "Could not open file";
my @console = <han1>;
foreach (@console) {
my $query = "SELECT * FROM user where user = ".$conn->quote("$_")." ";
my $query1 = $conn->prepare($query) || die "Error preparing query" . $conn->errstr;
print "$query";
$query1->execute || die "Error executing query" . $query1->errstr;
my @results;
while (@results = $query1->fetchrow_array()) {
foreach(@results) {
print "$_\n";
}
}
}
I'm not entirely sure on what you're trying to do. Anyway, here's my guess.
NOTE: This is untested code!
Code:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Slurp;
use DataDumper;
# 1st, slurp the file
my @lines = read_file('/path/to/file');
# 2nd, setup the connection do the DataBase
my $db_user = q|dbuser|;
my $db_pass = q|dbpassword|;
my $dbname = q|db_name|;
my $db_host = q|db_host|;
my $dbh = DBI->connect(
qq|DBI:mysql:database=$db_name;host=$db_host|,
$user, $pass
) or die(q|Ooopsss... something went wrong with the database connection.|);
$dbh->{RaiseError} = 1;
# 3rd, define the query
my $sql = q|SELECT * FROM tab_user WHERE user = ?|;
# Note the use of the placeholder (?), so, you don't need to escape evil characters
# 4th, prepare the query
my $sth = $dbh->prepare($sql);
# 5th, loop trought the lines and run the query
my @results; # This array will hold the results, if any
foreach my $line (@lines) {
# execute it...
$sth->execute($line);
# ... and fetch the records
push @results, $sth->fetchall_arrayref();
}
# 6th, dump the results in @results
print Dumper(\@results);
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 "$_";
}
}
}
# 6th, dump the results in @results
print Dumper(@sqlout);
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 that when running the query for example [B]SELECT * FROM user WHERE user = "DATA_COMING_FROM_FILE";[\B] the WHERE user part the list of users will come 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.
Some comments and modifications on your code:
I'm assuming that your file is something like:
Code:
user1
user2
user3
userN
Code:
# Moved these variables outside the loop
my $sql = q|SELECT * FROM user WHERE user = ?|;
my $sth = $dbh->prepare($sql);
# create an array to store all the results
my @results;
foreach my $line (@lines) {
# print "The line result is ", "$line\n";
print qq|Current line says: $line\n|;
# Moved the next line outside the loop. What's the point in
# re-defining a variable with the same value for each line?
# my $sql = q|SELECT * FROM user WHERE user = ?|;
# print "The sql_query is ", "$sql\n";
print q|The sql query is ";
# if you want to replace the ? with the user read from the file...
my $_sql_ = $sql;
$_sql_ =~s/\?/$line/gmx; # replace the ? with the username
print qq|$_sql_\n\n|;
# Moved outside the loop for the same reason
# my $query = $dbh->prepare($sql) || die "Error preparing query";
# $query->execute($lines);
$sth->execute($line);
# ------ Replace this with the line below ------- #
# my @sqlout;
# while (@sqlout = $query->fetchrow_array()) {
# foreach(@sqlout) {
# print "$_";
# }
# }
push @results, $sth->fetchrow_arrayref();
}
print Dumper(\@results);
---------------------------
Another way to do the same, perhaps faster (if I understand right what you're trying to do).
If you have a file with user names you want to check, and get info, against a MySQL db, you can do this in one go:
Code:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Slurp;
use DataDumper;
# 1st, slurp the file
my @lines = read_file('/path/to/file');
# 2nd, create and fill arrays to store the conditions
my @sql_qual;
my @sql_users;
foreach (@lines) {
push @sql_qual, q| user = ? |;
push @sql_bind, qq| $_ |;
}
# 3rd, setup the connection do the DataBase
my $db_user = q|dbuser|;
my $db_pass = q|dbpassword|;
my $dbname = q|db_name|;
my $db_host = q|db_host|;
my $dbh = DBI->connect(
qq|DBI:mysql:database=$db_name;host=$db_host|,
$user, $pass
) or die(q|Ooopsss... something went wrong with the database connection.|);
$dbh->{RaiseError} = 1;
# Define the SQL clause
my $sql_clause = join q| OR |, @sql_qual;
# 5th, prepare the query
my $sth = $dbh->prepare(
qq|SELECT * FROM user WHERE $sql_clause|;
);
# 6th, execute the query
$sth->execute(@sql_bind);
# 7th, fetch and add the results to @results
my @results = $sth->fetchall_arrayref;
# 8th, dump the results in @results
print Dumper(\@results);
Miguel thanks again for sticking with me. I think we are getting closer. The query does look correct when being printed by the print qq|$_sql_\n\n|; But the results are not being printed out. All that is being printed out is the stuff below, results show show user info like phone, email etc...
Not sure what the $VAR1 = ... is. Also do I need to declare the quotes for after the = sign in the query and also the trailing ; at the end of the query. I am guessing I don't since the qq is smart enough to know.
Here are the results after running the script.
Current line says: user01
SELECT * FROM user WHERE user = user01
Current line says: user02
SELECT * FROM user WHERE user = user02
Current line says: user03
SELECT * FROM user WHERE user = user03
$VAR1 = [
[],
[],
[]
];
Here is what the script looks like after the changes.
#!/usr/bin/perl
use strict;
use warnings;
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|;
1. pls use code tags when show ing your code
2. you shouldn't need to regex replace the usernames, that's what the arg to execute() does if you loop through your username list
3. not sure if the FILE::slurp->read_file() method does this for you, but if you slurp a file the traditional way:
Code:
open(han1, "dbquery.txt") || die "Could not open file";
my @console = <han1>;
close(han1);
you need to remove the invisible newline chars from each item before using them thus:
Code:
chomp(@console);
you may need to do this anyway, judging by your results...
Chris thanks for the tip. I've also tried it the way you mentioned and I get the same results. Below is the new code and also the results after running the script.
Code:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Slurp;
use Data::Dumper;
open(han1, "/root/perl/dbquery.txt") || die "Could not open file";
my @console = <han1>;
my $db_user = q|root|;
my $db_pass = q|password|;
my $dbname = q|mysql|;
my $db_host = q|localhost|;
#my $dbh = DBI->connect(qq|DBI:mysql:database=$dbname;host=$db_host,$db_user, $db_pass|) or die(q|Ooopsss... something went wrong with the database connection.|);
my $dbh = DBI->connect("DBI:mysql:database=$dbname;host=$db_host", "$db_user",
"$db_pass", {PrintError => 1, RaiseError => 1, AutoCommit => 1});
$dbh->{RaiseError} = 1;
my $sql = q|SELECT * FROM user WHERE user = ?|;
my $sth = $dbh->prepare($sql);
my @results;
foreach my $con (@console) {
chomp $con;
print qq|Current line says: $con\n|;
my $_sql_ = $sql;
print qq|$_sql_\n\n|;
$sth->execute($con);
push @results, $sth->fetchall_arrayref();
}
print Dumper(\@results);
RESULTS- As you can see if I dont run the regex code the user = gets left with a ? and the file being read does not populate into the query. But I end up with the same results as with the previous code, once again not sure what $VAR1 = [... is.
./copy.pl
Current line says: user01
SELECT * FROM user WHERE user = ?
Current line says: user02
SELECT * FROM user WHERE user = ?
Current line says: user03
SELECT * FROM user WHERE user = ?
$VAR1 = [
[],
[],
[]
];
Thanks guys I really appreciate your help with this as I've been banging my head for a few days.
AH! I warned you :-) I didn't tested the code! When you slurp the file, it comes with the \n at the end of each line. That's the reason you're getting empty records when dumping the results.
Now, let's do it in another way.
Code:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Data::Dumper;
my @users = get_users('/path/to/file');
my @results = fetch_data(
{
dbh => conn_db(
{
'username' => 'username',
'password' => 'password',
'host' => 'localhost',
'database' => 'database_name'
}
)
}
);
print Dumper(\@results);
sub get_users {
my $file = shift;
open my $FILE, '<', $file or die q|Error: can't open file!|;
my @lines;
while (<$FILE>) {chomp; push @lines, $_}
close $FILE;
return @lines;
}
sub fetch_data {
my $self = shift;
my @sql_qual;
my @sql_bind;
foreach (@users) {
push @sql_qual, q| user = ? |;
push @sql_bind, qq| $_ |;
}
my $sql_clause = join q| OR |, @sql_qual;
my $sth = $self->{'dbh'}->prepare(qq|SELECT * FROM user WHERE $sql_clause|);
$sth->execute(@sql_bind);
my @_results = $sth->fetchall_arrayref;
return \@_results;
}
sub conn_db {
my $self = shift;
my $db_user = $self->{'username'};
my $db_pass = $self->{'password'};
my $db_name = $self->{'database'};
my $db_host = $self->{'host'};
my $_dbh = DBI->connect(
qq|DBI:mysql:database=$db_name;host=$db_host|,
$db_user, $db_pass
) or die(q|Ooopsss... something went wrong with the database connection.|);
$_dbh->{RaiseError} = 1;
return $_dbh;
}
FYI, as per my last example, it is legal to chomp(@array), which will in fact individually chomp all elements in one go, so you don't need to resort to the 'push...' line.
BTW, it would be nice for future qnrs if you show the working version.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.