MySQL::Easy - Perl extension to handle various mundane DBI session related things specific to mysql.
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;
}
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:
$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->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");
$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.
$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).
$arr = $dbo->firstcol("select col from tab where x=? and y=?", $x, $y)
returns an arrayref of values for the sql.
$id = $dbo->last_insert_id;
Fetches the last insert id (relating to auto_increment fields) from MySQL.
$dbo->trace(1); $dbo->do("sql"); $dbo->trace(0);
Turns the DBI trace on and off.
$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.
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";
$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.
MySQL::Easy reads the user and pass from that file. The host name will default to localhost unless explicitly set.
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;
Any functions from DBI will work with the $dbo.
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.
For bugs and ideas: Josh Rabinowitz <joshr-cpan@joshr.com>
Copyright 2009 Paul Miller
Released under the LGPL
perl(1), DBI, DBD::mysql
