Direct Database Access

In this example, we will demonstrate how to create a web page to show the monthly revenue for a given year. The user will select a year from a HTML form and press OK. The web server will query a database to obtain the necessary data, and return a web page containing the bar chart for the selected year.

The code for producing the HTML form is listed below. It outputs a drop down select list to allow the user to select a year. Based on the selected year, it uses an <IMG> tag with a ChartDirector script as the URL and the selected year as query parameter. The ChartDirector script will generate the chart image based on the selected year and deliver it to the browser.

[CGI Version] perldemo_cgi\dbdemo1.pl

#!/usr/bin/perl

# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;

use perlchartdir;

# Get HTTP query parameters
use CGI;
my $query = new CGI;

# The currently selected year
my $selectedYear = ($query->param("year") or 2001);

#
# The following code generates the <option> tags for the HTML select box, with the <option> tag
# representing the currently selected year marked as selected.
#

my $optionTags = [(undef) x (2001 - 1990 + 1)];
for(my $i = 1990; $i < 2001 + 1; ++$i) {
    if ($i == $selectedYear) {
        $optionTags->[$i - 1990] = "<option value='$i' selected>$i</option>";
    } else {
        $optionTags->[$i - 1990] = "<option value='$i'>$i</option>";
    }
}
my $selectYearOptions = join("", @$optionTags);

print "Content-type: text/html\n\n";
print <<EndOfHTML
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Database Integration Demo (1)
</div>
<hr style="border:solid 1px #000080" />
<div style="font-size:10pt; font-family:verdana; margin-bottom:20px">
&#8226; <a href="viewsource.pl?file=$ENV{"SCRIPT_NAME"}">
    View containing HTML page source code
</a>
<br />
&#8226; <a href="viewsource.pl?file=dbdemo1a.pl">
    View chart generation page source code
</a>
<br />
<br />
<form>
    I want to obtain the revenue data for the year
    <select name="year">
        $selectYearOptions
    </select>
    <input type="submit" value="OK">
</form>
</div>

<img src="dbdemo1a.pl?year=$selectedYear">

</body>
</html>
EndOfHTML
;

As seen from the code above, the chart is created by the URL in the <IMG> tag, which is "dbdemo1a.pl". The source code of "dbdemo1a.pl" is as follows.

[CGI Version] perldemo_cgi\dbdemo1a.pl

#!/usr/bin/perl

# In the sample code, the ChartDirector for Perl module is assumed to be in "../lib"
use File::Basename;
use lib (dirname($0)."/../lib") =~ /(.*)/;

use perlchartdir;

# Get HTTP query parameters
use CGI;
my $query = new CGI;

#
# Displays the monthly revenue for the selected year. The selected year should be passed in as a
# query parameter called "year"
#
my $selectedYear = ($query->param("year") or 2001);

# SQL statement to get the monthly revenues for the selected year.
my $SQL =
    "Select Software, Hardware, Services From revenue Where Year(TimeStamp) = $selectedYear Order ".
    "By TimeStamp";

#
# Connect to database and read the query result into arrays
#

use DBI;
my $dbh = DBI->connect('dbi:mysql:sample', 'test', 'test');
my $sth = $dbh->prepare($SQL);
$sth->execute();

my $software = [];
my $hardware = [];
my $services = [];
while (my @row = $sth->fetchrow_array) {
    push @$software, $row[0];
    push @$hardware, $row[1];
    push @$services, $row[2];
}
$dbh->disconnect;

#
# Now we have read data into arrays, we can draw the chart using ChartDirector
#

# Create a XYChart object of size 600 x 300 pixels, with a light grey (eeeeee) background, black
# border, 1 pixel 3D border effect and rounded corners.
my $c = new XYChart(600, 300, 0xeeeeee, 0x000000, 1);
$c->setRoundedFrame();

# Set the plotarea at (60, 60) and of size 520 x 200 pixels. Set background color to white (ffffff)
# and border and grid colors to grey (cccccc)
$c->setPlotArea(60, 60, 520, 200, 0xffffff, -1, 0xcccccc, 0xccccccc);

# Add a title to the chart using 15pt Times Bold Italic font, with a light blue (ccccff) background
# and with glass lighting effects.
$c->addTitle("Global Revenue for Year $selectedYear", "timesbi.ttf", 15)->setBackground(0xccccff,
    0x000000, perlchartdir::glassEffect());

# Add a legend box at (70, 32) (top of the plotarea) with 9pt Arial Bold font
$c->addLegend(70, 32, 0, "arialbd.ttf", 9)->setBackground($perlchartdir::Transparent);

# Add a stacked bar chart layer using the supplied data
my $layer = $c->addBarLayer2($perlchartdir::Stack);
$layer->addDataSet($software, 0xff0000, "Software");
$layer->addDataSet($hardware, 0x00ff00, "Hardware");
$layer->addDataSet($services, 0xffaa00, "Services");

# Use soft lighting effect with light direction from the left
$layer->setBorderColor($perlchartdir::Transparent, perlchartdir::softLighting($perlchartdir::Left));

# Set the x axis labels. In this example, the labels must be Jan - Dec.
my $labels = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sept", "Oct", "Nov", "Dec"];
$c->xAxis()->setLabels($labels);

# Draw the ticks between label positions (instead of at label positions)
$c->xAxis()->setTickOffset(0.5);

# Set the y axis title
$c->yAxis()->setTitle("USD (Millions)");

# Set axes width to 2 pixels
$c->xAxis()->setWidth(2);
$c->yAxis()->setWidth(2);

# Output the chart in PNG format
binmode(STDOUT);
print "Content-type: image/png\n\n";
print $c->makeChart2($perlchartdir::PNG);

The first part of the above code is a database query using MySQL. The SQL statement is:

my $SQL =
    "Select Software, Hardware, Services From revenue Where Year(TimeStamp) = ".
    "$selectedYear Order By TimeStamp";

The code then executes the SQL query and read the data into an array using a loop. In the loop, the data are read using:

use DBI;
my $dbh = DBI->connect('dbi:mysql:sample;host=localhost','test','test');
my $sth = $dbh->prepare($SQL);
$sth->execute();

my $software = [];
my $hardware = [];
my $services = [];
while (my @row = $sth->fetchrow_array) {
    push @$software, $row[0];
    push @$hardware, $row[1];
    push @$services, $row[2];
}
$dbh->disconnect;

After we read the data into arrays, the second part of the code is to create a stacked bar chart using the given data. This is very similar to the examples in other parts of this documentation, so it will not be explained further.