Database related tips

Sharing DB connection

An easy way to share your database handle is by by using the CGI::Application::Plugin::DBH plugin, which provides this feature.

SQL Generation

After being a long time SQL::Abstract user, I now prefer SQL::Interpolate for SQL generation. You can read my review of SQL::Interpolate for more details.

Beyond that I prefer to minimize abstraction by using the DBI module directly. I've found it has good documentation for error handling and returning data in various formats. –Mark Stosberg

Handling DBI errors

(Mark Stosberg): I stopped using "$sth->do() or die" logic.

• It seems to me it's better to handle the possible errors at higher level. With this code, to handle the error at a higher level, I have to be aware that it might cause my code to die (when I might not want it to, and then "eval" the code to prevent that, and possibly parse the error message if I want to do something different with it.

• DBI has excellent error handling built in that allows me to handle errors at a high level without additional abstraction. I can use the "Print Error" attribute to get warnings for errors, or I can turn on the "Raise Error" attribute if I do want bad SQL statements to die all the time. I can also check the value of "$DBI::errstr" from anywhere to see what the last error from DBI was. (Using eval {... } )

Without this extra abstraction in my code, I found it was easier to debug and gave me more flexibility in error handling.

The DBI docs have example code for transaction error handling here: http://search.cpan.org/~timb/DBI-1.45/DBI.pm#Transactions

Fetching Hashref Via DBI

Cory Trese asked: What is a forwards compatibly way to use fetchrow_hashref() ? I keep hearing that "this behavior might change". Trying to figure out the best way to make these <nop>Ao Href for H::T.

(Mark Stosberg): I find there are generally 4 cases for the kinds of hashes I want to get back from DBI for use with CGI::App/H::T. Here is what they are and how I handle them with DBI 1.30.

• 1. A hashref based on a row of data

my $href = $DBH->selectrow_hashref("");

• 2. A hash based on two columns of data

(often an id and a name field for use in a popup menu)

my %h = @{ $DBH->selectcol_arrayref("",{Columns=>[1,2]}) };

• 3. An array of hashrefs

my $LoH = $DBH->selectall_arrayref($sql,{Slice=>{}},@bind);
$html_tmpl->param( my_loop_name => $LoH );

• 4. Tricky array of hashrefs

where some keys also point to arrays of hashrefs (for nested loops in H::T).

For this I start out the same as above, and then use Data::Grouper to transform the the data structure quickly into what I want:

require Data::Grouper;
my $g = Data::Grouper->new(DATA=>$LoH,SORTCOLS=>['category_name']);
my $cats = $g->get_data;

Contributors: Mark Stosberg, Cees Hek