Author image Melissa VandenBrink
and 1 contributors

NAME

MySQL::RunSQL - Run SQL queries against a MySQL database and return the results to STDOUT or XLSX file.

SYNOPSIS

        use MySQL::RunSQL;
        
        my $runsql = MySQL::RunSQL->new(
                group_name => <mysql_group>, #[blockname] to look under in my.cnf file
                database => <database>, # Database to run query against
                my_cnf => <my_cnf_file>, # File the contains user/pass information
                host    => <mysql_server> # MySQL server to connect to
                );
                
        my $sqlfile =   "/tmp/employees.sql";
        
        $runsql->runsql("SELECT col1 as 'Col 1', col2 as 'Col 2'...");
        $runsql->runsql_fromfile($sqlfile);
        
        my $xlsx = $runsql_report("SELECT col1 as 'Col 1', col2 as 'Col 2'...");
        my $xlsx = $runsql_report_fromfile($sqlfile);
        
        

DESCRIPTION

        MySQL::RunSQL provides a simple object oriented interface to
        DBD::mysql for running a queries against a MySQL database. Data
        is returned to STDOUT as a comma delimited list or is written
        out to an XLSX file.
        

EXAMPLE

        #!/usr/bin/perl
        
        use strict;
        use MySQL::RunSQL;
        use Env qw/HOME/;
        
        my $db = "test";
        my $cnf = "$HOME/.my.cnf";
        my $group = "test";
        my $host = "localhost";
        
        # Create our MySQL::RunSQL Object
        my $rs = MySQL::RunSQL->new(
                database =>     $db,
                my_cnf  =>      $cnf,
                group_name => $group,
                host => $host
        );
        
        # The report will use the as "NAME" as the header for the column
        #if this is omitted then it will use the column identifier
        
        my $sql = <<"SQL";
        SELECT employee_id as 'ID',  CONCAT(first_name,' ', last_name) as 'Name',
        address as 'Address'
        FROM emloyeeDB
        WHERE start_date like "%2002%"
SQL
        
        # Run the report & write out to excel
        my $report = $rs->runsql_report->($sql);
        
        # Print the file location
        print "\n\tYour report is ready: $report\n\n";
        
        exit 0;

METHODS

MySQL::RunSQL->new( database => $db, my_cnf => $mycnf, group_name => $mygroup, host => $mysqlhost, port => $mysqlport );

Returns a MySQL::RunSQL object. The new method is invoked with the following options:

        host            => mysql_server
        database        => database      
        port            => mysql port   default=3306
        my_cnf          => mysql my.cnf file with both user & password set
        group_name      => under which [group] in the my.cnf file can the 
                                login information be found
                                        
        host, database, my_cnf, & group_name are all required.
        

MySQL::RunSQL->runsql("SELECT STATEMENT")

Takes a sql query and prints to STDOUT a comma delimited list of rows returned

MySQL::RunSQL->runsql_fromfile($sqlfile);

Takes a file containing a sql query and prints to STDOUT a comma delimited list of rows returned.

MySQL::RunSQL->runsql_report("SELECT STATEMENT...")

Takes a sql query and prints output to an xlsx file. Returns the file name and location.

MySQL::RunSQL->runsql_report_fromfile($sqlfile)

Takes a file containing a sql query and prints output to an xlsx file and returns the file location

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.10.0 or, at your option, any later version of Perl 5 you may have available.

AUTHOR

Melissa A. VandenBrink, <geeklady@cpan.org>

SEE ALSO

MySQL Database http://www.mysql.con/