A Perl DBI DBH MySQL example

Perl MySQL FAQ: Can you share a Perl MySQL example? (Or, can you share a Perl DBI/DBH MySQL example?)

I don't know if this Perl DBI DBH MySQL example code will help anyone, but I thought I'd put it out here in hopes that someone will gain something from it.

I wish I had something a little bit simpler to share, but this is all I have at this time. I used this Perl DBI code to read some cost-estimating data I received from an organization named ISBSG. I got their project data, which was available in a MySQL format, and then I used this Perl program to parse through their data.

Perl DBI DBH MySQL example - brief discussion

Okay, a few things I will say about this Perl DBI MySQL code:

• As mentioned, it uses the Perl DBI module.
• It is used to access a MySQL database.
• You can see the DSN references at the beginning of the code, so I know this was run on a Windows machine.
• There is a lot of setup code in there, and some long SQL statements.
• Finally at the bottom you'l see some DBI/DBH references.

Again, I hope this helps with your own Perl DBI/DBH database queries.

#/usr/local/bin/perl

##  Define Variables
##########################################################################

##  DSN Preferences
$dsn_connection_params = "DBI:mysql:database=ISBSG9;host=localhost";
$dsn_username          = "foo";
$dsn_password          = "bar";

##  Query Preferences
$query_sql_where       = "";
$query_sql_order_by    = "ORDER BY project_id ASC";

$query_sql_select = <<END;
SELECT project_id, data_quality_cd, ufp_rating_cd, count_approach, functional_size, project_pdr, 
       project_elapsed_time, implementation_date, development_type, architecture, client_server, 
       development_platform, language_type, used_methodology, 
       user_base_business_units, user_base_locations, average_team_size 
       FROM project_data
       WHERE data_quality_cd = 'A'
       AND ufp_rating_cd = 'A'
       AND functional_size > 100
       AND functional_size < 500
       AND language_type = '3GL'
       AND average_team_size < 10
       AND implementation_date LIKE '%200%'
       AND development_type = 'New Development'
END

##  Output Preferences
$output_template       = "output.xml";
$output_destination    = "output.xls";

##  Begin Program
##########################################################################

use DBI;
use Excel::Template;

do_main();

##  Subroutines
##########################################################################

sub do_main
{
  my ($dbh, $cursor, $key, $project_id, $data_quality_cd, $ufp_rating_cd);
  my ($count_approach, $functional_size, $project_pdr, $project_elapsed_time);
  my ($implementation_date, $development_type, $architecture, $client_server);
  my ($development_platform, $language_type, $primary_programming_language);
  my ($used_methodology, $user_base_business_units, $user_base_locations);
  my ($average_team_size, @results, $template);
  $dbh = DBI->connect($dsn_connection_params, $dsn_username, $dsn_password);
  $cursor = $dbh->prepare("$query_sql_select $query_sql_where $query_sql_order_by");
  $cursor->execute();
  while (($project_id, $data_quality_cd, $ufp_rating_cd, $count_approach, $functional_size, $project_pdr, $project_elapsed_time, $implementation_date, $development_type, $architecture, $client_server, $development_platform, $language_type, $primary_programming_language, $used_methodology, $user_base_business_units, $user_base_locations, $average_team_size) = $cursor->fetchrow_array())
  {
    push(@results, {'project_id' => $project_id,
                    'data_quality_cd' => $data_quality_cd,
                    'ufp_rating_cd' => $ufp_rating_cd,
                    'count_approach' => $count_approach,
                    'functional_size' => $functional_size,
                    'project_pdr' => $project_pdr,
                    'project_elapsed_time' => $project_elapsed_time,
                    'implementation_date' => $implementation_date,
                    'development_type' => $development_type,
                    'architecture' => $architecture,
                    'client_server' => $client_server,
                    'development_platform' => $development_platform,
                    'language_type' => $language_type,
                    'primary_programming_language' => $primary_programming_language,
                    'used_methodology' => $used_methodology,
                    'user_base_business_units' => $user_base_business_units,
                    'user_base_locations' => $user_base_locations,
                    'average_team_size' => $average_team_size});
  }
  $cursor->finish();
  $dbh->disconnect();
  print "Found $#results results...\n";
  print "Creating $output_destination...\n";
  $template = Excel::Template->new(filename => $output_template);
  $template->param(RESULTS => \@results);
  $template->write_file($output_destination);
}

I hope seeing this Perl DBI/DBH MySQL programming example has been helpful. Sorry I don't have much time for discussion today, but I hope this helps.