A common occurrence is a situation in which your application has free reign over most of the database, but needs to use a few stored procedure calls to get to certain pieces of data. In that case, you'll want to create a normal DBIC schema, and then add methods for accessing the unusual data.

As an example, let's look back at the AddressBook application and imagine that for some reason we couldn't use DBIx::Class to access the user table, and instead need to write the raw SQL to return an array containing everyone's username. In AddressBook::Model::AddressDB, we just need to write a subroutine to do our work as follows:

package AddressBook::Model::AddressDB;
// other code in the package
sub get_users {
my $self = shift;
my $storage = $self->storage;
return $storage->dbh_do(
sub {
my $self = shift;
my $dbh = shift;
my $sth = $dbh->prepare('SELECT username FROM user'),
$sth->execute();
my @rows = @{$sth->fetchall_arrayref()};
return map { $_->[0] } @rows;
});
}

Here's how the code works. On the first line, we get our DBIC::Schema object and then obtain the schema's storage object. The storage object is what DBIC uses to execute its generated SQL on the database, and is usually an instance of DBIx::Class::Storage::DBI. This class contains a method called dbh_do that will execute a piece of code, passed to dbh_do as a coderef (or "anonymous subroutine"), and provide the code with a standard DBI database handle (usually called $dbh). dbh_do will make sure that the database handle is valid before it calls your code, so you don't need to worry about things like the database connection timing out. DBIC will reconnect if necessary and will then call your code. dbh_do will also handle exceptions raised within your code in a standard way, so that errors can be caught normally.

The rest of the code deals with actually executing our query. When the database handle is ready, it's passed as the second argument to our coderef (the first is the storage object itself, in case you happen to need that). Once we have the database handle, the rest of the code is exactly the same as if we were using plain DBI instead of DBIx::Class. We first prepare our query (which need not be a SELECT; it could be EXEC or anything else), execute it and, finally, process the result. The map statement converts the returned data to the form we expect it in, a list of names (instead of a list of rows each containing a single name). Note that the return statement in the coderef returns to dbh_do, not to the caller of get_users. This means that you can execute dbh_do as many times as required and then further process the results before returning from the get_users subroutine.

Once you've written this subroutine, you can easily call it from elsewhere in your application as follows:

my @users = $c->model('AddressDB')->get_users;
$c->response->body('All Users' join ', ', @users);

sub get_users {
# version 2
my $self = shift;
my $users = $self->resultset('User'),
my @result;
while(my $user = $users->next){
push @result, $user->username;
}
return @result;
}

$users = $c->model('AddressDB::User'),
$users->search({}, { select => [ { COUNT => 'id' } ],
as => [ 'count' ],});
$count = $users->first->get_column('count'),

sub count_users_where {
my $self = shift;
my $condition = shift;
$self->search($condition,
{ select => [ { COUNT => 'id' } ], as => [ 'count' ], });
my $first = $users->first;
return $first->get_column('count') if $first;
return 0; # if there is no "first" row, return 0
}

$jons = $c->model('AddressDB::User')->
count_users_where([ username => {-like => '%jon%'}]);