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) or die $dbo->errstr;
      # 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() ...

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") or die $dbo->errstr;

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.

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.

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.

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