/ 
MySQL-Easy-2.1019
River stage zero No dependents
/ MySQL::Easy

NAME

MySQL::Easy - Perl extension to handle various mundane DBI session related things specific to mysql.

SYNOPSIS

use MySQL::Easy;

my $dbo = MySQL::Easy->new("stocks");

my $symbols = $dbo->firstcol(
    "select symbol from ohlcv where symbol=?", "msft" );

my $q = $dbo->ready("select * from ohlcv where symbol=?");
for my $s (@$symbols) {
    my @a;

    $q->execute($s);
    # Although the regular DBI will reconnect to the server when it loses
    # it's connection, any queries you had previously prepared will fail
    # until it reconnects.  easy::mysql handles all that for you so this
    # execute() will always work, if it's syntactically correct, even if
    # your server connection was lost since the last ready();

    print "@a" while @a = $q->fetchrow_array;
}

DESCRIPTION

I don't remember how I used to live without this... I do like the way DBI and DBD::mysql work, but I wanted something slightly prettier... slightly handier.

Here are the functions MySQL::Easy provides:

new
$dbo = MySQL::Easy->new( $db_name, $trace )

$db_name is the name of the database you're connecting to... If you don't pick anything, it'll pick "test" for you. $trace is a 1 or false, ... it's the DBI->trace() ...

$dbo = MySQL::Easy->new( $db_name, {user=>"blarg", host=>"whatever"})

Alternatively, you can pass a hashref of options in place of the trace argument. Passing user=>"blarg" in this way is the same as calling $dbo->set_user() -- so this is really only good for avoiding calls to set_user, set_pass, and set_host; although, trace also works as an attribute here by calling the function of the same name.

do
$dbo->do("sql statement bind=? bind=?", $bind1, $bind2);

This immediately executes the SQL with the bind vars given. You can pas in a statement handle instead of the string... this is faster if you're going to use the SQL over and over. Returns a bool like you'd expect. Example:

$dbo->do("stuff");
lock
$dbo->lock("table1", "table2", "table3");

# code

$dbo->unlock;

MySQL::Easy uses only write locks. Those are the ones where nobody can read or write to the table except the locking thread. If you need a read lock, let me know. Most probably though, if you're using this, it's a smaller app, and it doesn't matter anyway.

ready
$sth = $dbo->ready("Sql Sql Sql=? and Sql=?");
$sth->execute($bindvar);
$sth->fetchrow_hashref; # etc...

ready() returns a DBI statement handle, mostly. The MySQL::Easy statement handles automatically recover from disconnects. At the time of this writing, DBI statement handles do not do this (although the database handle does).

firstcol
$arr = $dbo->firstcol("select col from tab where x=? and y=?", $x, $y)

returns an arrayref of values for the sql.

firstval
$val = $dbo->firstval("select blarg from table where unique_id=?", $id);

Returns the value asked for or undef (as a scalar) if the query didn't work out.

firstrow
$val = $dbo->firstrow("select * from table where unique_id=?", $id);

Returns the first row found as an array or as an arrayref.

last_insert_id
$id = $dbo->last_insert_id;

Fetches the last insert id (relating to auto_increment fields) from MySQL.

trace
$dbo->trace(1); $dbo->do("sql"); $dbo->trace(0);

Turns the DBI trace on and off.

errstr
$dbo->errstr

Returns an error string for the last error on the thread. Works roughly the same as a $sth->errstr and is described in detail there.

check_warnings

I'll just give this example:

$dbo->do("create temporary table cool( field enum('test1', 'test2') not null )");
$dbo->do("insert into cool set field='test3'");
$dbo->check_warnings 
    or die "SQL WARNING: $@\twhile inserting test field\n\t";
set_host set_user set_port set_pass
$dbo->set_host($h); $dbo->set_port($p); 
$dbo->set_user($U); $dbo->set_pass($p);

The first time you do a "do"/"ready"/"firstcol"/etc, MySQL::Easy connects to the database. You may use these set functions to override values found in your ~/.my.cnf for user and pass. They can also be passed during the call to new.

MySQL::Easy reads the user and pass from that file. The host name will default to localhost unless explicitly set.

bind_execute
my $table;
my $sth = $dbo->bind_execute("show tables", \( $table ) );

This was Josh's idea (see "THANKS").

die $dbo->errstr unless $sth;  
    # bind_execute returns undef if either the bind
    # or execute phases fail.

print "$table\n" while fetch $sth;

AUTOLOAD

Any functions from DBI will work with the $dbo.

AUTHENTICATION

MySQL::Easy actually checks a few places for the username and password (largely so it needn't be included in your script). These alternate locations are checked in the following order until something is found.

$ENV{ME_USER} and $ENV{ME_PASS}

Then environment variables ME_USER and ME_PASS override any file based guesses. This only works if both are set.

$ENV{ME_CNF}

Then environment variable ME_CNF may contain the location of a file. If it does, it's checked for username and password info. If only one is found, the value falls through to the next file.

$ENV{HOME}/.my.cnf

If the file exists, ~yourname/.my.cnf is checked for username and password info. If only one is found, the value falls through to the next file.

/etc/mysql-easy.cnf

If the file exists, it is checked for username and password info. If only one is found, the value falls through to the next file.

/etc/mysql/my.cnf

If the file exists, it is checked for username and password info.

The environment variable names and file locations are also all configurable (use the source for further information).

AUTHOR

Paul Miller <jettero@cpan.org>

I am using this software in my own projects. If you find bugs, please let me know. :) Actually, let me know if you find it handy at all. Half the fun of releasing this stuff is knowing that people use it.

THANKS

For bugs and ideas: Josh Rabinowitz <joshr-cpan@joshr.com>

COPYRIGHT

Copyright 2009 Paul Miller

Released under the LGPL

SEE ALSO

perl(1), DBI, DBD::mysql