Mark Hedges


Apache2::Controller::DBI::Connector - connects DBI to $r->pnotes->{a2c}{dbh} or the key that you select.


Version 1.001.001



 sub some_a2c_controller_method {
     my ($self, @path_args) = @_;
     my $dbh = $self->pnotes->{a2c}{dbh};



 # virtualhost.conf:
 PerlLoadModule Apache::DBI
 PerlLoadModule Apache2::Controller::Directives
 <Location '/'>
     A2C_DBI_DSN        DBI:mysql:database=foobar;host=localhost
     A2C_DBI_User       heebee
     A2C_DBI_Password   jeebee
     A2C_DBI_Options    RaiseError  1
     A2C_DBI_Options    AutoCommit  0

     # this boolean pushes a PerlLogHandler to run rollback if in_txn
     A2C_DBI_Cleanup    1

     SetHandler                 modperl
     PerlInitHandler            MyApp::Dispatch
     PerlHeaderParserHandler    Apache2::Controller::DBI::Connector


If you need to make your life more complicated, subclass this module and implement your own <dbi_connect_args()> subroutine, which returns argument list for <DBI-connect()>>.

 PerlLoadModule Apache::DBI
 <Location '/'>
     SetHandler                 modperl

     PerlInitHandler            MyApp::Dispatch
     PerlHeaderParserHandler    MyApp::DBIConnect

 package MyApp::DBIConnect;
 use base qw( Apache2::Controller::DBI::Connector );
 sub dbi_connect_args {
     my ($self) = @_;
     return (
         'heebee', 'jeebee',
         { RaiseError => 1, AutoCommit => 0 }
 sub dbi_cleanup { 1 }
 sub dbi_pnotes_name { 'dbh' }


You also have to use overloaded subs in a subclass if you want to set up multiple DBH handles by specifying the name for the key in pnotes using A2C_DBI_PNOTES_NAME or dbi_pnotes_name().


Connects a package-space DBI handle to $r->pnotes->{a2c}{dbh}.

You only need this where you need a database handle for every request, for example to connect to a session database regardless of whether the user does anything.

You can load it only for certain locations, so the handle will get connected only there.

Otherwise you probably just want to use Apache::DBI and connect your database handles on an ad-hoc basis from your controllers.

If directive A2C_DBI_Cleanup is set, a PerlLogHandler gets pushed which will roll back any open transactions. So if your controller does some inserts and then screws up, you don't have to worry about trapping these in eval if you want the DBI errors to bubble up. They will be automatically rolled back since commit() was never called.

(This used to be a PerlCleanupHandler, but it appears that Apache hands this off to a thread even if running under prefork, and cleanup doesn't always get processed before the child handles the next request. At least, this is true under Apache::Test. Wacky. So, it's a PerlLogHandler to make sure the commit or rollback gets done before the connection dies.)

If you subclass, you can set up multiple dbh handles with different params:

 <Location '/busy/database/page'>
     SetHandler modperl

     PerlInitHandler         MyApp::Dispatch
     PerlHeaderParserHandler MyApp::DBI::Writer MyApp::DBI::Read

If you use a tiered database structure with one master record and many replicated nodes, you can do it this way. Then you overload dbi_pnotes_name to provide the pnotes key, say "dbh_write" and "dbh_read". In the controller get them with $self->pnotes->{a2c}{dbh_write} and $self->pnotes->{a2c}{dbh_read}, etc.

If you subclass DBI, specify your DBI subclass name with the directive A2C_DBI_Class. Note that this has to be connected using a string eval() instead of the block eval() used for normal DBI if you do not specify this directive.

Accessing $dbh from controller

In your Apache2::Controller module for the URI, access the database handle with $self->pnotes->{a2c}{dbh}, or instead of "dbh", whatever you set in directive A2C_DBI_PNOTES_NAME or return from your overloaded dbi_pnotes_name() method.


Because a reference persists in package space, the database handle will remain connected after a request ends.

Usually Apache will rotate requests through child processes.

This means that on a lightly-loaded server with a lot of spare child processes, you will quickly get a large number of idle database connections, one per child.

To solve this you need to set your database handle idle timeout to some small number of seconds, say 5 or 10. Then you load Apache::DBI in your Apache config file so they automatically get reconnected if needed.

Then when you get a load increase, handles are connected that persist across requests long enough to handle the next request, but during idle times, your database server conserves resources.

There are various formulas for determining how much memory is needed for the maximum number of connections your database server provides. MySQL has a formula in their docs somewhere to calculate memory needed for InnoDB handles. It is weird.

When using persistent database connections, it's a good idea to limit the max number of Apache children to the max number of database connections that your server can provide. Find a formula from your vendor's documentation, if one exists, or wing it.



Gets DBI connect arguments by calling $self->dbi_connect_args(), then connects $dbh and stashes it in $r->pnotes->{a2c}{dbh} or the name you select.

The $dbh has a reference in package space, so controllers using it should always call commit or rollback. It's good practice to use eval anyway and throw an Apache2::Controller::X or your subclass of it (using a2cx(), so you can see the function path trace in the logs when the error occurs.

The package-space $dbh for the child persists across requests, so it is never destroyed. However, it is assigned with DBI->connect() on every request, so that Apache::DBI will cache the database handle and actually connect it only if it cannot be pinged.


Default interprets directives. Apache2::Controller::Directives. You can override this in a subclass to provide your own connect args.


Default interprets directive. "A2C_DBI_Cleanup" in Apache2::Controller::Directives. You can override this in a subclass.


Maybe it would be useful to you to overload this. But you'd probably better use the directive "A2C_DBI_Pnotes_Name" in Apache2::Controller::Directives in case other modules (like session) depend on it.








Mark Hedges, hedges +(a t)-


Copyright 2008-2010 Mark Hedges. CPAN: markle

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

This software is provided as-is, with no warranty and no guarantee of fitness for any particular purpose.