Perl module to log in database and do a simple query?

Question

I can't seem to figure out the issue with my .pm file and script. I am fairly new to Perl.

I have a database with name "project" and there is table with name "mailing". mailing table has 7 entries, which I want to display using module.

So, I have this custom module to log in to database and do a query. This module is names as DB.pm

DB.pm is stored on my FEDORA 20 at /root/mysql/GUI/DB.pm.

DB.pm is defined as follows:

package GUI::DB;

use strict;
use DBI;

use vars qw(@ISA @EXPORT);
use Exporter;
@ISA = qw(Exporter);
@EXPORT = qw(dbConnect query);

#
# dbConnect - connect to the database, get the database handle
#
sub dbConnect {
        # Read database settings from config file:
        print "Works";
    my $dsn = "DBI:mysql:project";
    my   $dbh = DBI->connect( $dsn,
                'root',
                'mydatabasepassword',
                                { RaiseError => 1 } 
    );
        return $dbh;

}

#
# query - execute a query with parameters
#       query($dbh, $sql, @bindValues)
#
sub query {
        my $dbh = shift;
        my $sql ="SELECT * FROM mailing";
        my @bindValues = @_;            # 0 or serveral parameters

        my @returnData = ();

        # issue query
        my $sth = $dbh->prepare($sql);  //**line number 39 that is giving** error

        if ( @bindValues ) {
                $sth->execute(@bindValues);
        } else {
                $sth->execute();
        }

        if ( $sql =~ m/^select/i ) {
                while ( my $row = $sth->fetchrow_hashref ) {
                        push @returnData, $row;
                }
        }

        # finish the sql statement
        $sth->finish();

        return @returnData;
}
1;

Now I want to use this module inside my per script. This is what I tried:

#!/usr/bin/perl

use warnings;
use strict;
use lib '/root/mysql/';

use GUI::DB qw(dbConnect query);
dbConnect();
query();

This is the error I'm getting -->

Can't call method "prepare" on an undefined value at /root/mysql/GUI/DB.pm line 39.

Please help me with this. I am not sure how to proceed. I am guessing it has something to do with argument passing. Nothing is wrong with database. It works fine from CLI.

Thanks :)

_x_X_X_X_X_X_X_X_X_X_X_X_X_X_X_X__X_X_X_X_X__X

TILL HERE IT IS RESOLVED

_X_X_X_X_X_X_X_X_X_X_X_X_X_X_X_X_X__X_X_X

FURTHER ISSUE is SQL command is not working. In the mailing table of my database I have email id with different domains. For example, some id's are [email protected], [email protected] , [email protected] as so on and I am assuming new email ids will be added to mailing tables every day with different domains.

I am trying to write a scripts that updates another table which holds a daily count of email addresses by their domain name. This is what I tried:

#!/usr/bin/perl

use warnings;
use strict;
use lib '/root/mysql/';
use 5.016;
use Data::Dumper;

use GUI::DB qw(dbConnect query);

my $data = dbConnect();
my @domain = query($data, "SELECT substr(addr,locate('\@',addr)+1) as maildomain, count (*) as mailcount FROM mailing GROUP BY maildomain ORDER BY mailcount DESC");

for my $key (@domain){
        say Dumper ($key);
}

But I am getting an error,

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '*) as mailcount FROM mailing GROUP BY maildomain ORDER BY mailcount DESC' at line 1 at /root/mysql/GUI/DB.pm line 44.

Same SQL statement works from CLI with no issues. Any help would be appreciated. :)

If you don't want variable interpolation into a string, you can put single quotes around it. It's much easier to do that, than search through a string looking for problematic characters that need escaping. If you need to use single quotes in the interior of the string, then use q{} on the outside of the string, e.g. my $str = q{This isn't a problem}; (There's also qq{} for double quotes). Also, @domain is not a proper name for an array, which can contain may things. It should be @domains. — 7stud
– 7stud, Commented Jan 18, 2015 at 0:08
@7stud I am now using q with single quotes inside. How should I work around the error? — John F
– John F, Commented Jan 18, 2015 at 0:41
Post why your CLI command worked but the perl query didn't -- it might help someone else. — 7stud
– 7stud, Commented Jan 18, 2015 at 23:56
@7stud I just typed in the command shown in my post on CLI and it worked. But when I use same query through PERL it didn't work. I am confused why it didn't work. — John F
– John F, Commented Jan 18, 2015 at 23:58

7stud · Accepted Answer · 2015-01-19 09:18:58Z

6

1) Your error is saying that $dbh is undefined here:

sub query {
    my $dbh = shift;
    ...
    # issue query
    my $sth = $dbh->prepare($sql);  #<***LOOK HERE***

...which means $dbh must be undefined here:

sub query {
    my $dbh = shift;  #<***LOOK HERE***
    ...
    # issue query
    my $sth = $dbh->prepare($sql);

2) Let's see why. Your dbConnect() method returns $dbh:

sub dbConnect {
    # Read database settings from config file:
    print "Works";
    my $dsn = "DBI:mysql:project";

    my   $dbh = DBI->connect( 
        $dsn,
        'root',
        'mydatabasepassword',
        { RaiseError => 1 } 
    );

    return $dbh;  #<***LOOK HERE*****
}

3) But, you call dbConnect() like this:

dbConnect();

Because you never save the return value anywhere, $dbh is discarded.

4) Furthermore, you call query() like this:

query();

Yet, you defined query() like this:

sub query {
        my $dbh = shift;

The query() sub believes that the first argument will be the database handle--but you didn't call query() with any arguments.

You need to do this:

my $data_base_handle = dbConnect();

my @results = query($data_base_handle);

#do something with @results

Response to comment:

I printed @results, this is what I see HASH(0x1d05be8) HASH(0x1d05ba0) HASH(0x1d05b58) HASH(0x1d05b10) HASH(0x1d05ac8) HASH(0x1d05a80) HASH(0x1d05a38)

You wrote:

my $row = $sth->fetchrow_hashref;

...which asks DBI to return each row as a reference to a hash. Then you wrote:

push @returnData, $row;

...which pushed each hash reference into an array. So query() returns an array of hash references. The notation HASH(0x1d05be8) is what perl outputs when you print a hash reference.

If you want to see what's in those hashes, then do this:

use 5.016;  #enable say()
use Data::Dumper;
...
...

for my $href (@results) {
    say Dumper($href);
}

To access the data in a hash reference, you can do this:

use strict;
use warnings;
use 5.016;
use Data::Dumper;

my $href = {
    c => 3,
    a => 1,
    b => 2,
};

my %hash = %{$href};  #dereference, {}, the reference into a hash, %

for my $key ( keys %hash ) {
    say "$key $hash{$key}";
}

--output:--
c 3
a 1
b 2

Response to next comment: (Answer posted in comments under op.)

By the way, perl is pretty good at text processing, so if you couldn't figure out the problem with your query, you could process the email addresses with perl:

use strict; 
use warnings; 
use 5.012;
use Data::Dumper;

use DBI;
use DBD::mysql;

# CONFIG VARIABLES
my $db_type = "mysql";
my $database = "my_db";
my $host = "localhost";
my $port = "3306";
my $user = "root";
my $pword = "";

# DATA SOURCE NAME
my $dsn = "dbi:$db_type:$database:$host:$port";

# PERL DBI CONNECT
my $dbh = DBI->connect($dsn, $user, $pword);

# PREPARE THE QUERY
my $tablename = "mailing";

my $select =<<"END_OF_SELECT";
    select addr from $tablename
END_OF_SELECT

my $addr_aref = $dbh->selectcol_arrayref($select);  #Returns a reference to a flat array containing all the email addresses
$dbh->disconnect;

my %count_for;

for my $addr (@{$addr_aref}) {
    $addr =~ s/.*@//;
    $count_for{$addr}++;
}

say Dumper(\%count_for);

--output:--
$VAR1 = {
          'google.com' => 2,
          'gorilla.com' => 1,
          'yahoo.com' => 3
        };

edited Jan 19, 2015 at 9:18

answered Jan 17, 2015 at 22:07

7stud

48.8k14 gold badges107 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John F Over a year ago

I used $data_base_handle and when I printed @results, this is what I see HASH(0x1d05be8) HASH(0x1d05ba0) HASH(0x1d05b58) HASH(0x1d05b10) HASH(0x1d05ac8) HASH(0x1d05a80) HASH(0x1d05a38) SO instead of 7 entries in my database table , I see these 7 hashed values. Any idea why that is happening?

7stud Over a year ago

@JohnF, See my response in my answer.

John F Over a year ago

I tried this

my $data = dbConnect();   my %result = query($data);    for my $key (keys %result){           print "$key $result{$key}";   }

Still gives me hash values? @7stud

7stud Over a year ago

Why did you try that? query() returns an array. Then you assigned the array to a hash. Start a new example program, create an array, then assign it to a hash. Then output the hash like this: say Dumper(\%hash_name); You have to use Data::Dumper; and use 5.016; #for say() to be able to do that. You ABSOLUTELY, NO QUESTIONS ASKED, have to learn to use Data::Dumper like that. In fact, EVERY program you write, should have use Data::Dumper; at the top....along with use strict;, and use warnings; and use 5.016; #enable say().

John F Over a year ago

Yes, you are right. Dumper shows entries. Thanks your answer cleared many of my doubts :)

|

Collectives™ on Stack Overflow

Perl module to log in database and do a simple query?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related