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::Interp for SQL generation. The most pleasant way to use it is via DBIx::Simple. A plugin to integrate DBIx::Simple with CGI::Application is forthcoming. –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