Looking at DBI Part 18, Call me Ishmael,

In part 17 we had a look at DBD:Pg's ability to do Asynchronous Queries, now I think it is time I got a little back in line with the spec so we are going to look at DBI's ability to do callbacks.

How it happened that Mastro Cherry,

Callbacks are common attribute to all three of the the DBI handles and with them you can be come if you want the great and all powerful puppet master of DBI contorting everything from A to Z. Myself I have really only used them for one of two reasons, either as a quick and dirty logging system as a secondary way to control access.

Using them is quite straightforward one just set the 'Callbacks' attribute of the desired handle, to a hash ref where the keys are the methods you want to set a callback for and the values are the code you want to run.

So if you want your code to die if somebody tries to 'prepare' code that does not include a '?' param you could give this a try

$dbh->{Callbacks} = {
prepare => sub {
my ($dbh, $query, $attrs) = @_;
die if (index($query,"?") == -1);

Note how my callback sub will takes the same arguments as the original prepare statement
It is the same for any method you write a callback for.

You could also use it to disable the do method like this;

my $dbh = DBI->connect($dsn, $username, $auth, {
Callbacks => {
do => sub {
die "do is disabled";
} } });

Note here I added this when I connected so like all DBI attributes 'Callbacks' can be set directly or at creation time.

In a sense they are better described as pre-calls as the code you set up for callback fires before the actual method.

One thing I found it useful for was counting how many queries are actually run on a script that does a whole lot of process.

The problem is of course I would have to set the callback up for each $sth handle which would be a bit of a pain. DBI does have this little gem 'ChildCallbacks' which sets up callback in child handles and since all $sth are children of $dbh we need only do this

my $exec_count = 0; my $dbh = DBI->connect( $dsn, $username, $auth, { Callbacks => { ChildCallbacks => { execute => sub { $exec_count++; return; } }}});
You can also override DBI methods if you so desire. Lets say you want limit the data returned with the $dbh 'table' method you could do this

my $dbh = DBI->connect( $dsn, $username, $auth, {
Callbacks => {
table=> sub {
undef $_;
return some_other_function();
} } });

I used the 'undef $_' here to disable the table method and return what I want.

You can even play around with the arguments array if you need to make sure some setting this there. For example making sure that 'AutoCommit' is always off before you execute. Again we use the 'ChildCallbacks' like this

my $dbh = DBI->connect( $dsn, $username, $auth, { Callbacks => { ChildCallbacks => { execute => sub { my $dbh = $_[0]->{Database}; $dbh->{AutoCommit} = 0; }}} });

No limit to the amount of fun you can have with this.


> if (index($query,"?") eq -1)

Shouldn't that be == instead of eq?

Leave a comment

About byterock

user-pic Long time Perl guy, a few CPAN mods allot of work on DBD::Oracle and a few YAPC presentations