(You are
Anonymous)
Added | Removed | Changed | Unmodified
| Revision 5 | Current |
|---|---|
| = Database related tips | = Database related tips |
| === Sharing DB connection | === Sharing DB connection |
| An easy way to share your database handle is by by using the {pod:CGI::Application::Plugin::DBH} plugin, | An easy way to share your database handle is by by using the {pod:CGI::Application::Plugin::DBH} plugin, |
| which provides this feature. | which provides this feature. |
| === SQL Generation | === SQL Generation |
| /After being a long time {pod:SQL::Abstract} user, I now prefer | /After being a long time {pod:SQL::Abstract} user, I now prefer |
| {pod:SQL::Interpolate} for SQL generation. You can read my | {pod: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. --MarkStosberg/. |
| [http://cpanratings.perl.org/d/SQL-Interpolate 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. --MarkStosberg/ | |
| === Handling DBI errors | === Handling DBI errors |
| (MarkStosberg): I stopped using "$sth->do() or die" logic. | (MarkStosberg): 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. | * 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 "PrintError" attribute to get warnings for errors, or I can turn on the "RaiseError" 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 {... } ] ) | * DBI has excellent error handling built in that allows me to handle errors at a high level without additional abstraction. I can use the "PrintError" attribute to get warnings for errors, or I can turn on the "RaiseError" 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. | 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: | The DBI docs have example code for transaction error handling here: |
| http://search.cpan.org/~timb/DBI-1.45/DBI.pm#Transactions | http://search.cpan.org/~timb/DBI-1.45/DBI.pm#Transactions |
| === Fetching Hashref Via DBI | === Fetching Hashref Via DBI |
| Cory Trese asked: What is a forwards compatibly way to use fetchrow_hashref() ? I keep | 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>AoHref for H::T. | hearing that "this behavior might change". Trying to figure out the best way to make these <nop>AoHref for H::T. |
| (MarkStosberg): | (MarkStosberg): |
| I find there are generally 4 cases for the kinds of hashes I want to get back from DBI for use with | 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. | 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* | * 1. *A hashref based on a row of data* |
| my $href = $DBH->selectrow_hashref(""); | my $href = $DBH->selectrow_hashref(""); |
| * 2. *A hash based on two columns of data* | * 2. *A hash based on two columns of data* |
| (often an id and a name field for use in a popup menu) | (often an id and a name field for use in a popup menu) |
| my %h = @{ $DBH->selectcol_arrayref("",{Columns=>[1,2]}) }; | my %h = @{ $DBH->selectcol_arrayref("",{Columns=>[1,2]}) }; |
| * 3. *An array of hashrefs* | * 3. *An array of hashrefs* |
| my $LoH = $DBH->selectall_arrayref($sql,{Slice=>{}},@bind); | my $LoH = $DBH->selectall_arrayref($sql,{Slice=>{}},@bind); |
| $html_tmpl->param( my_loop_name => $LoH ); | $html_tmpl->param( my_loop_name => $LoH ); |
| * 4. *Tricky array of hashrefs* | * 4. *Tricky array of hashrefs* |
| where some keys also point to arrays of hashrefs (for nested loops in H::T). | 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 {pod:Data::Grouper} to transform the the data structure quickly into what I want: | For this I start out the same as above, and then use {pod:Data::Grouper} to transform the the data structure quickly into what I want: |
| require Data::Grouper; | require Data::Grouper; |
| my $g = Data::Grouper->new(DATA=>$LoH,SORTCOLS=>['category_name']); | my $g = Data::Grouper->new(DATA=>$LoH,SORTCOLS=>['category_name']); |
| my $cats = $g->get_data; | my $cats = $g->get_data; |
| ------ | ------ |
| Contributors: MarkStosberg, Cees Hek | Contributors: MarkStosberg, Cees Hek |
|
