Line Graph Servlet Direct Database Connection

	Graphs and Charts Applet Tutorial
	[ Home ] [ Products ] [ Downloads ] [ Purchase ] [ Support ] [ About Us ]

Connecting the Line Graph Servlet Directly to a Database

Prerequisites

Graphs and Charts Package - For this tutorial you will need the Advanced Graph and Chart package. If you haven't already done so then you should first download the software here

The Line Graph Servlet must have been installed on the Web Server. For further instructions on how to install the graphing servlets see:-
Implementing the Graphing Servlets

Overview

With this method the Graphing Servlet is set to connect directly to a database and issue SQL to the database for the data. The graph image is then dynamically constructed with the data returned from the database

For security reasons this method is only valid for the Servlet form of the Graphing software

Method

The graphing <IMG> tag is added to an HTML page but rather than supplying a "data" parameter for the servlet a "dataDB" parameter is specified. The dataDB parameter points to a file which contains the database connection details (DB name, Username and Password) and an SQL Statement for each series of data.

At page view time the Servlet will:-

read the database info file
connect to the database
extract the data
construct the graph image and dynamically add to the HTML page

Note: This method may only be applied to the Servlet form of the graphing software.

Step 1 - Construct the Database info file
The database info file should contain the information required to create a connection to the database and the SQL statements for each series of data.

Database Connection Info
There are 4 pieces of info required to make the Database Connection. These are specified with the following parameters:-

Database:
Driver:
User:
Password:

Database: - This is the database connection path and name.
eg. If you have a MySQL database called Sales you may have,

"Database: jdbc:mysql://localhost/Sales"

Driver: This is the location of the JDBC Driver for your database system. In order for the graphing servlet to connect to your database it is vital that you have the correct JDBC driver set up and configured for you system.

User: The username which the graphing servlet will use to connect to the database

Password: The database password for the Username specified.

Series SQL Statements

For each series of data an SQL Statement should be specified. The graph servlet will execute each statement and take the first data item of each record returned as the value for that data item. For instance if you have a table which holds monthly sales figures for 2 products X and Y then your statements may look like,

"series1: Select Value from SalesBar where Year=2004 and Product='X' ORDER BY Month"
"series2: Select Value from SalesBar where Year=2004 and Product='Y' ORDER BY Month"

If 6 records are returned for each record set this would mean that there are 6 data points for each series of data.

Here are the contents of an example dbinfo file:-

<!-- Database Connection Info -->
Database: jdbc:mysql://localhost/[DB NAME]
Driver:   [LOCATION OF DB JDBC DRIVER]
User:     [DB USERNAME]
Password: [DB PASSWORD]

<!-- Line Data -->
<!--         Example SQL Statement for Series 1 and 2 -->
series1: Select Value from SalesBar where Year=2004 and Product='X' ORDER BY Month
series2: Select Value from SalesBar where Year=2004 and Product='Y' ORDER BY Month

Step 2 - Set up the Configuration data.
The servlet will read all the configuration parameters from a specified file.

Create a text file "lineprops.txt" and add the following content:-

<!-- Example Line Graph properties file -->
<!-- All of the following properties are optional  -->
<!-- Any properties which are not supplied will be -->
<!-- automatically calculated.                     -->

<!-- General Properties -->
width:  500
height: 420
BackgroundColor: #FFFFFF
linkcursor: hand
thousandseparator: ,
nSeries: 3
ndecplaces: 2

<!-- Grid properties -->
grid: true
axis: true
nRows: 8
vSpace: 20
hSpace: 20
nPoints: 12
gridxpos: 30
gridypos: 270
gridstyle: 2
gridColor: #AAAAAA
axisColor: #888888
floorColor: #EEEEFF
gridbgimage:
gridbgcolor: #FFFF88

<!-- Scale properties -->
autoscale: true
chartScale: 1
chartStartY: 0

<!-- 3D properties -->
3D: false
outline: false
lineOutlineColor: blue
depth3D: 0

<!-- Legend properties -->
legend: true
LegendBackground: #9999FF
LegendBorder:  #5555AA
LegendtextColor: #FFFFFF
legendtitle:  Legend Title
legendposition:  -1,-1
legendfont:  Arial,N,12
LegendStyle: Horizontal

<!-- X axis labels -->
labelOrientation: Up Angle
labelsY: 300
xlabel_font: Arial,N,12
labelColor: #000000
label1: January
label2: February
label3: March
label4: April
label5: May
label6: June
label7: July
label8: August
label9: September
label10: October
label11: November
label12: December

<!-- Y axis labels -->
ylabels: true
ylabel_font: Arial,N,12
ylabel_pre: $
ylabel_post:

<!-- Chart Titles -->
title: Graph Title
xtitle: X axis title
ytitle: Y axis title

<!-- PopUp Display -->
popupbgcolor: #AAAAFF
popupfont: Arial,N,12
popup_pre: $
popup_post:

<!-- Series Data -->
series1: #F00000|6|16|false|Series 1
series2: #F000F0|6|16|true|Series 2

Adjust the values to create the line graph style you require.

For a full description of each property please see the following section in the Documentation,
"Line Graph Parameter Reference"

Step 3 - Construct the HTML Page
The next step is to incorporate the graph into your HTML page. This is done via the standard HTML <IMG> tag.

Where,

[ServletEngineURL] should be replaced with the URL for your servlet engine.

[URLconfigFile] should be replaced with the URL to either a Configuration file or server side process.

[DBinfo file path] should be replaced with the file path to the location of the Database info file. The database info file should be placed in a directory on your web server.

Note: The Width and Height values of the <IMG> should match the width and height specified in the configuration file created in step 2

Step 4 - Upload the Database Info file, config file and HTML Page to your web server.

Upload both the HTML Page (step 3) and Config File (Step 2) to your web server.

Upload the Database Info file (created in Step 1) to the directory specified in the "dataDB" parameter of the <IMG> tag created in step 2.

Further Help and Support

If it any stage you require help, advice or simply have a general enquiry then please feel free to contact us.

Contact Support here

Related Information

Documentation

Demos and Examples

Custom Solutions Center

If would like us to perform the set-up process, create custom built data scripts or help with any other part of your web application then we are pleased to offer you the following services:-

Product Set-Up, Installation and Configuration Service

Custom Software Development

Web Site Components Development Service

Web Application Development Service

Web Site Design Service

eCommerce Solutions and Web Development Service

Content Management Set Up and Implementation Service

« back to Tutorial index

« back to Getting Started

[ Home ] [ Products ] [ Downloads ] [ Purchase ] [ Support ] [ About Us ]

Graphs and Charts Applet Tutorial

Connecting the Line Graph Servlet Directly to a Database