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 xyz@gmail.com, 12343@gmail.com , bae@yahoo.com 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. :)

Answer 1

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