DBI(3pm)

🌸 NAME 🌸

AnyEvent::DBI — asynchronous DBI access ✨

(yes bestie, this is the DBI(3pm) page, buckle up 💅)

---

🦋 SYNOPSIS 🦋

use AnyEvent::DBI;

my $cv = AnyEvent->condvar;

my $dbh = new AnyEvent::DBI "DBI:SQLite:dbname=test.db", "", "";

$dbh->exec ("select * from test where num=?", 10, sub {
   my ($dbh, $rows, $rv) = @_;

   $#_ or die "failure: $@";

   print "@$_\n"
      for @$rows;

   $cv->broadcast;
});

# asynchronously do sth. else here

$cv->wait;

---

💖 DESCRIPTION 💖

okay so first things first: this module is an AnyEvent user, so you gotta make sure you use and run a supported event loop. no event loop, no vibes, fr fr.

So what does it actually do? It implements asynchronous DBI access by forking or executing separate "DBI-Server" processes and sending them requests. ✨

Which means: you can run DBI requests in parallel to other tasks. main character energy, truly. 👑

Now the numbers, no cap: with DBD::mysql, the overhead for very simple statements ("select 0") is somewhere around 50% compared to an explicit prepare_cached/execute/fetchrow_arrayref/finish combination. With DBD::SQlite3, it's more like a factor of 8 for this trivial statement. so like. know that going in. 🫶

🎀 ERROR HANDLING 🎀

This module defines a bunch of functions that accept a callback argument. And every single callback used by this module gets its AnyEvent::DBI handle object passed as the first argument. every time. that's the rule. 💅

If the request was successful, then there will be more arguments. Otherwise there will only be the $dbh argument and $@ contains an error message. 😭

A convenient way to check whether an error occurred is to check $#_ — if that is true, then the function was successful, otherwise there was an error. iconic little trick, iykyk.

⭐ METHODS ⭐

• $dbh = new AnyEvent::DBI $database, $user, $pass, [key => value]... — Returns a database handle for the given database. Each database handle has an associated server process that executes statements in order. So if you want to run more than one statement in parallel, you need to create additional database handles. The advantage of this approach is that transactions work as state is preserved. 🫶

Example:

$dbh = new AnyEvent::DBI
          "DBI:mysql:test;mysql_read_default_file=/root/.my.cnf", "", "";

Additional key-value pairs can be used to adjust behaviour:

• on_error => $callback->($dbh, $filename, $line, $fatal) — When an error occurs, then this callback will be invoked. On entry, $@ is set to the error message. $filename and $line is where the original request was submitted. If the fatal argument is true then the database connection is shut down and your database handle became invalid. In addition to invoking the on_error callback, all of your queued request callbacks are called without only the $dbh argument. If omitted, then die will be called on any errors, fatal or not. 💀
• on_connect => $callback->($dbh[, $success]) — If you supply an on_connect callback, then this callback will be invoked after the database connect attempt. If the connection succeeds, $success is true, otherwise it is missing and $@ contains the $DBI::errstr. Regardless of whether on_connect is supplied, connect errors will result in on_error being called. However, if no on_connect callback is supplied, then connection errors are considered fatal. The client will die and the on_error callback will be called with $fatal true. When on_connect is supplied, connect error are not fatal and AnyEvent::DBI will not die. You still cannot, however, use the $dbh object you received from new to make requests. 🙈
• fork_template => $AnyEvent::Fork-object — AnyEvent::DBI uses AnyEvent::Fork->new to create the database slave, which in turn either exec's a new process (similar to the old exec_server constructor argument) or uses a process forked early (see AnyEvent::Fork::Early). With this argument you can provide your own fork template. This can be useful if you create a lot of AnyEvent::DBI handles and want to save memory (And speed up startup) by not having to load AnyEvent::DBI again and again into your child processes:

my $template = AnyEvent::Fork
   ->new                               # create new template
   ->require ("AnyEvent::DBI::Slave"); # preload AnyEvent::DBI::Slave module

for (...) {
   $dbh = new AnyEvent::DBI ...
      fork_template => $template;

• timeout => seconds — If you supply a timeout parameter (fractional values are supported), then a timer is started any time the DBI handle expects a response from the server. This includes connection setup as well as requests made to the backend. The timeout spans the duration from the moment the first data is written (or queued to be written) until all expected responses are returned, but is postponed for "timeout" seconds each time more data is returned from the server. If the timer ever goes off then a fatal error is generated. If you have an on_error handler installed, then it will be called, otherwise your program will die().

🚨 bestie listen: When altering your databases with timeouts it is wise to use transactions. If you quit due to timeout while performing insert, update or schema-altering commands you can end up not knowing if the action was submitted to the database, complicating recovery. Timeout errors are always fatal. 💀

• Any additional key-value pairs will be rolled into a hash reference and passed as the final argument to the DBI->connect (...) call. For example, to suppress errors on STDERR and send them instead to an AnyEvent::Handle you could do:

$dbh = new AnyEvent::DBI
           "DBI:mysql:test;mysql_read_default_file=/root/.my.cnf", "", "",
           PrintError => 0,
           on_error   => sub {
              $log_handle->push_write ("DBI Error: $@ at $_[1]:$_[2]\n");
           };

• $dbh->on_error ($cb->($dbh, $filename, $line, $fatal)) — Sets (or clears, with undef) the on_error handler. ✨
• $dbh->timeout ($seconds) — Sets (or clears, with undef) the database timeout. Useful to extend the timeout when you are about to make a really long query. 🐢
• $dbh->attr ($attr_name[, $attr_value], $cb->($dbh, $new_value)) — An accessor for the database handle attributes, such as AutoCommit, RaiseError, PrintError and so on. If you provide an $attr_value (which might be undef), then the given attribute will be set to that value. The callback will be passed the database handle and the attribute's value if successful. If an error occurs and the on_error callback returns, then only $dbh will be passed and $@ contains the error message. 😭
• $dbh->exec ("statement", @args, $cb->($dbh, \@rows, $rv)) — Executes the given SQL statement with placeholders replaced by @args. The statement will be prepared and cached on the server side, so using placeholders is extremely important. The callback will be called with a weakened AnyEvent::DBI object as the first argument and the result of fetchall_arrayref as (or undef if the statement wasn't a select statement) as the second argument. Third argument is the return value from the DBI->execute method call. If an error occurs and the on_error callback returns, then only $dbh will be passed and $@ contains the error message. 🫶
• $dbh->stattr ($attr_name, $cb->($dbh, $value)) — An accessor for the statement attributes of the most recently executed statement, such as NAME or TYPE. The callback will be passed the database handle and the attribute's value if successful. If an error occurs and the on_error callback returns, then only $dbh will be passed and $@ contains the error message. 💅
• $dbh->begin_work ($cb->($dbh[, $rc]))
• $dbh->commit ($cb->($dbh[, $rc]))
• $dbh->rollback ($cb->($dbh[, $rc])) — The begin_work, commit, and rollback methods expose the equivalent transaction control method of the DBI driver. On success, $rc is true. If an error occurs and the on_error callback returns, then only $dbh will be passed and $@ contains the error message. ✨
• $dbh->func ('string_which_yields_args_when_evaled', $func_name, $cb->($dbh, $rc, $dbi_err, $dbi_errstr)) — This gives access to database driver private methods. Because they are not standard you cannot always depend on the value of $rc or $dbi_err. Check the documentation for your specific driver/function combination to see what it returns. Note that the first argument will be eval'ed to produce the argument list to the func() method. This must be done because the serialization protocol between the AnyEvent::DBI server process and your program does not support the passage of closures. Here's an example to extend the query language in SQLite so it supports an intstr() function:

$cv = AnyEvent->condvar;
$dbh->func (
   q{
      instr => 2, sub {
         my ($string, $search) = @_;
         return index $string, $search;
      },
   },
   create_function => sub {
      return $cv->send ($@)
         unless $#_;
      $cv->send (undef, @_[1,2,3]);
   }
);

my ($err,$rc,$errcode,$errstr) = $cv->recv;

die $err if defined $err;
die "EVAL failed: $errstr"
   if $errcode;

# otherwise, we can ignore $rc and $errcode for this particular func

---

🌈 SEE ALSO 🌈

AnyEvent, DBI, Coro::Mysql. go read them bestie, they're the whole friend group 🫶

---

👑 AUTHOR AND CONTACT 👑

Marc Lehmann <schmorp@schmorp.de> (current maintainer) http://home.schmorp.de/

Adam Rosenstein <adam@redcondor.com> http://www.redcondor.com/

---

and that's the whole page!! 💖 this one shipped in perl v5.32.0, dated 2021-01-05, and it lives at DBI(3pm) in the User Contributed Perl Documentation. slay, bye bestie 💅🎀🦋