Exporting data from database as HTML or CSV

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am looking for comments on an idea, and/or suggestions on how to improve it/replace it with something else. I have some pretty complex stand alone sql, plus some stored procedures, which I execute via perl+DBI. My concerns are regarding how to best handle the output. Most of the time, HTML+CSS reports/email will be produced, but some people will also need the output as comma delimited. My about-to-be-implemented idea consists of the following. Write a module, say SQLData, with subroutines for each query, which will take a parameter to output .csv or ready to be displayed html. i.e.

package @SQLData
...
sub css {
   if needed, define css here, else link to it.
   this will be mostly used for html emails, given
   that linking to it will be a pain if it goes outside
   the company
}
sub foo {
   my $bar = shift(@_);
   my @output;
   my $sql = ("sql code here")
   my @array $sql->execute;
   while (@array = $sql->fetchrow_array) {
     if ($bar=='csv') {
        push(@output,"$foo[0],$foo[1]"}
     } else {
        push(@output,"<tr><td>$foo[0]</td><td>$foo[1]</td></tr>");
     }
   }
   $sql->finish();
   return @output;
}
[download]

What do you think? Is this the best way to approach it? Is it even a good way? Thanks.

2005-08-17 Retitled by Arunbear, as per Monastery guidelines
Original title: 'How does this look?'

Comment on Exporting data from database as HTML or CSV Download Code

Replies are listed 'Best First'.
Re: Exporting data from database as HTML or CSV by dragonchild (Archbishop) on Aug 16, 2005 at 19:18 UTC
Separation of Concerns. You have data that you get from somewhere and you need to send it to someplace in someformat. So, you have 4 sections of your app: The code that controls how you get your data (model) The code that controls how you communicate with your user (view) The code that formats what gets sent to your user (view) The code that coordinates all of this (controller) This is also known as MVC, or Model-View-Controller. You can see where stuff falls. Now, the way this all works is that you have API boundaries. So, no matter where you get your data, the Model code will return back the exact same data structure. So, you could get your data from a database, a CSV, some RPC call, or any combination of options. But, as far as the rest of the code is concerned, the data has been retrieved in some known structure. And, the same goes for the other pieces. What does this mean for your question? It's very simple - you should have several Formatter objects that each accept a known data structure. They will then emit the appropriate stuff for the I/O section to send back to the user. In certain circumstances, the formatter may also provide metadata, such as the Content-Type (for webapps). Template Toolkit or HTML::Template are excellent templating modules. You can use Text::xSV to construct the CSV side of things. If you need, Excel::Template and PDF::Template can be used for other formats. And, other than Text::xSV, they can all take the exact same datastructure, which (coincendentally enough) is the datastructure provided when you do something like: `my $results = $sth->fetchall_arrayref({});` [download] My criteria for good software: Does it work? Can someone else come in, make a change, and be reasonably certain no bugs were introduced?	[reply] [d/l]
Re: Exporting data from database as HTML or CSV by jZed (Prior) on Aug 16, 2005 at 18:40 UTC
The DBI::Format module in the DBI::Shell distribution allows you to output in a variety of formats. The templating modules like Template::Toolkit and HTML::Template can be easily used to output many formats. The DBD::AnyData module can convert from any DBI accessible table to HTML or CSV or XML or Fixed-Length or others. I'd recommend that you not reinvent the wheel on this one. update - please retitle your node to reflect the subject matter of your question.	[reply]