(Perl Programming) How to Generate Line Graph/Chart using DBD::Chart Module?

The DBD::Chart Module provides native SQL interface to render line graph. The default output format is PNG, JPEG or GIF Format. In this article we are going to see how we can use DBD::Chart module to produce Line Graph.

You can download DBD::Chart module from CPAN repository (http://search.cpan.org/dist/DBD-Chart/Chart.pm) or if you are using Active Perl just run “ppm install DBD-Chart” command.

Create Sales Table with two fields “sale_amount and sale_year”. (I am using MYSQL database)

CREATE TABLE Sales (sale_year INT NOT NULL , sale_amount FLOAT NOT NULL );

Insert Values into the table for example

INSERT INTO sales (sale_year, sale_amount) VALUES ('2003', '4000'), ('2004', '5000');

sale_year sale_amount

1996 5000

1997 7000

1998 6000

1999 8000

2000 5000

2001 4000

2002 3000

2003 4000

2004 5000

After creating a sales table, follow below mentioned code to generate Line Graph.

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

#create connection with data base, Connect function requires database name(test), user name(root) and password(xyz).

# if connection is not established die option display an error message (standard Error Message) and your script will die

my $DBH = DBI->connect('DBI:mysql:test', 'root', 'xyz') or die "Connection Error: $DBI::errstr\n";

my $i=0; # initialize Counter

my $buf;

my $Sth_Line;

# create connection with Char database (Chart is data source name)

my $dbh = DBI->connect('dbi:Chart:');

# Once connection is established, we want to create line graph so create line table with two fields year and Amount

$dbh->do('CREATE CHART line (Year INT, Amount INT )');

# create sql statment to fetch year and amount fields values from sales table

my $Query_Sales="SELECT sale_year, sale_amount from sales;";

# once connection is established prepare the Query_Sales SQL string to query the database

my $Sth = $DBH->prepare($Query_Sales);

$Sth->execute or die "SQL Error: $DBI::errstr\n"; # execute the prepared query or display error message

# fetch each row of the results from MYSQL database

while (my @row = $Sth->fetchrow_array) {

         my $Sale_Year=$row[0];

         my $Sale_Amount=$row[1];

        # we need to insert fetched year and sales values into line table

        $Sth_Line = $dbh->prepare('INSERT INTO line VALUES( ?, ?)');

        $Sth_Line->execute($Sale_Year, $Sale_Amount);

} # While loop end's here

$Sth->finish(); # free up resources

$Sth_Line->finish(); # free up resources

# after adding all values into the line table prepare SQL statment with line chart related fields to generate customized line graph/char

my $rsth = $dbh->prepare(

"SELECT LINEGRAPH FROM line WHERE WIDTH=? AND HEIGHT=? AND X_AXIS=? AND Y_AXIS=? AND TITLE = 'Sales Amount Statistics By Year' AND COLOR IN ('red') AND SHOWPOINTS=1 AND SHOWGRID=0 AND SHOWVALUES=1 AND SHAPE IN ('fillcircle') AND BACKGROUND='lgray' AND X_ORIENT='VERTICAL' AND SIGNATURE='Copyright(C) 2010, LampSys'");

# chart Width, Height

# chat x axis field name (X_AXIS) and y axis field name (Y_AXIS) (we have two fields year and amount)

# TITLE (Title of Chart)

# COLOR (represent Color of line chart), SHOWPOINTS (if 1 display points on Line chart)

#SHOWGRID=1 (if 1 generate line graph with grid background) SHOWVALUES=1 (if 1 display the value of points)

#SHAPE IN ('fillcircle') (display points as colored circles)

# BACKGROUND='lgray' (represent background color of line graph)

#X_ORIENT='VERTICAL' (display X axis values vertically)

#SIGNATURE='Copyright(C) 2010, LampSys'");

# specify width, hight , x axis and y axis fields

$rsth->execute(1000, 600, 'year', 'month');

#Binds a variable ($buf) with attributes (output columns year, amount) of a SELECT statement.

$rsth->bind_col(1, \$buf);

#Whenever a row is fetched from the database $buf

#appears to be automatically updated simply because it now refers to the same memory location as the corresponding column value.

$rsth->fetch;

$rsth->finish(); #free up resources

#open a file (as PNG, JPEG, GIF) in binary mode

open(OUTF, '>Sales.png');

binmode OUTF;

print OUTF $buf;

close(OUTF);

print "Sales.png Generated\n";
$DBH->disconnect;  #disconnect Database connection

#-------------------OutPut--------------------------------#