Utilizing Server Side Scripts to dynamically populate the Line Graph Applet or Servlet

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

Utilizing Server Side Scripts to dynamically populate the Line Graph Applet or Servlet

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 Web Server must be capable of running a Scripting Language (eg. ASP, PHP, JSP etc).

Overview

This method provides the ability to add Real-Time Dynamic Database Graphing to your web pages. This method is extremely powerful and allows the construction of graphs from virtually any database system running on virtually any platform.

The idea behind this method is that the graph function will make a request to a server side script for the data. This means that the script can be written to first read data from a database before returning the data values back to the graph function. Hence the result is real-time graphing capability.

Although this tutorial focuses on the Line Graph Applet, this method can be used for any chart style provided in the Advanced Graphs and Charts package (including bar, pie and area graphs).

Method

The graph applet is incorporated into a a static HTML page but rather than supplying the data with PARAM tags or flat file, the graph is set to acquire data from a server side script. The server side script accesses the database and returns the graph data to the applet. At page view time the graph function will request the data from the server side script. The server side script will then acquire the data in real time from a data source (eg. database) and return it to the graph function. Hence producing a Real-Time Dynamic Graph with data from a database within the web page.

Step 1 - Construct the Data Script
The dynamic script can be constructed in any language of your choice. Most Web Servers these days come set up with a dynamic scripting language which is configured to access your database system. For convenience we recommend using the language already configured on your web server.

(if you haven't used or are unsure about what's already available on your web server then you should in the first instance speak to your Web Server Administrator or ISP Hosting company ).

The following are the most common Scripting languages and cover over 90% of web servers in use today,

PHP
ASP (and ASP.NET)
JSP
Java Servlet

The following text boxes contain some example script written in the above languages. These are provided for you to copy and base your own script on. In each case the script is written to connect to MySQL database and retrieve data from a table. The data from the database is then formatted before being returned to the graph.

Template PHP Data Process:-

/* ---------------------------------------------------------------- */
/* The following script is a sample script designed to demonstrate  */
/* a method for creating real-time dynamic line graph from data     */
/* held within databases.                                           */
/*                                                                  */
/* This sample connects to a MySQL database and reads data from     */
/* a table "SalesLine". It then formats the data for use by the      */
/* Line Graph Applet.                                                */
/*                                                                  */
/* You may freely copy and modify this script to suite your own     */
/* requirements.                                                    */
/*                                                                  */
/* ---------------------------------------------------------------- */

<?php
    /* Connect to the MySQL Database Server */
    $link = mysql_connect("localhost", "[DB_USERNAME]", "[DB_PASSWORD]")
        or die("Could not connect : " . mysql_error());

    /* Select Database testDB2 */
    mysql_select_db('[DB_NAME]',$link) or die("Could not select database");

    /* Performing SQL query for Product X*/
    $query = "Select Value from SalesLine where Year=2004 and Product='X' ORDER BY Month";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());

    /* Construct the return data for Product X */
    print "<!-- Data for Product X -->\n";

    $data_num = 1;
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

        foreach ($line as $col_value) {
           print "data".$data_num."series1: ".$col_value."\n";
        }
        $data_num = $data_num +1;

    }


    /* Performing SQL query for Product Y*/
    $query = "Select Value from SalesLine where Year=2004 and Product='Y' ORDER BY Month";
    $result = mysql_query($query) or die("Query failed : " . mysql_error());

    /* Construct the return data for Product Y */
    print "<!-- Data for Product Y -->\n";

    $data_num = 1;
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {

        foreach ($line as $col_value) {
           print "data".$data_num."series2: ".$col_value."\n";
        }
        $data_num = $data_num +1;

    }



    /* Free resultset */
    mysql_free_result($result);

    /* Closing connection */
    mysql_close($link);


?>

Template ASP Data Process:-

<%
' ----------------------------------------------------------------
' The following script is a sample script designed to demonstrate
' a method for creating real-time dynamic line graph from data
' held within databases.
'
' This sample connects to a database via DSN and reads data from
' a table "SalesLine". It then formats the data for use by the
' Line Graph Applet.
'
' You may freely copy and modify this script to suite your own
' requirements.
'
' ----------------------------------------------------------------

<%
' -- Make Database Connection
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=myCONNECTION.dsn"
objConn.Open
%>

<%
' -- Perform SQL query for Product X
DIM mySQL1, objRS1
mySQL1 = "Select Value from SalesLine where Year=2004 and Product='X' ORDER BY Month"
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open mySQL1, objConn1

' -- Perform SQL query for Product Y
DIM mySQL2, objRS2
mySQL2 = "Select Value from SalesLine where Year=2004 and Product='Y' ORDER BY Month"
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open mySQL2, objConn2
%>

<!-- Chart Data -->
<%
DIM iDataCount

' Construct data tags for series 1
iDataCount = 0
DO WHILE NOT objRS1.EOF
    iDataCount = iDataCount + 1
%>

data<%=idataCount%>series1:  <%=objRS1("Value")%>

<%
    objRS1.MoveNext
Loop
objRS1.Close
%>

<%
' Construct data Param tags for series 2
iDataCount = 0
DO WHILE NOT objRS2.EOF
    iDataCount = iDataCount + 1
%>

data<%=idataCount%>series2:  <%=objRS2("Value")%>

<%
    objRS2.MoveNext
Loop
objRS2.Close
%>

<%
' Close Database Connection
objConn.Close()
Set objConn = Nothing
%>

Template JSP Data Process:-

<%@ page import="java.sql.*"  %>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*"   %>
<%
//-------------------------------------------------------------------
//
// This simple JSP is designed to demonstrate how a JSP script can be used
// to retrieve data from a database and construct a dynamic page
// containing the graphing applet.
//
// You may freely copy and modify this script to suite your own
// requirements.
//
//-------------------------------------------------------------------

    // Initialise and set variables
    String     url   = "jdbc:MySQL:///TESTDB";  // URL specifying the JDBC connection to a MySQL database TESTDB.
    Connection con   = null;                    // Database connection object
    Statement  stmt  = null;                    // Statement String
    String     query;                           // Query String
    int        datacount;
    ResultSet srs1 = null;
    ResultSet srs2 = null;

    // Establish the database connection
    try {
        // Connect to TESTDB
        Class.forName("org.gjt.mm.mysql.Driver");
        con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
        stmt = con.createStatement();

    } // End try


    // Error handling
    catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
    catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}



%>

<!-- Chart Data -->
<%
    try {


        // Performing SQL query for Product X
        query = "Select Value from SalesLine where Year=2004 and Product='X' ORDER BY Month";
        srs1 = stmt.executeQuery(query);

        // Write out the data for Series 1
        datacount = 1;
        while (srs1.next()) {
            out.println("data"+datacount+"series1: "+srs1.getString("Value")+"\n");
            datacount++;
        }

        // Performing SQL query for Product Y
        query = "Select Value from SalesLine where Year=2004 and Product='Y' ORDER BY Month";
        srs2 = stmt.executeQuery(query);

        // Write out the data for Series 2
        datacount = 1;
        while (srs2.next()) {
            out.println("data"+datacount+"series2: "+srs2.getString("Value")+"\n");
            datacount++;
        }

    }

    // Error handling
    catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}

    // All finished so close the database connection
    finally {
     try {if (con != null) con.close();}
     catch (SQLException e) {}
    }

%>

Template Java Servlet Data Process:-

import java.sql.*;
import java.lang.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class LineData extends HttpServlet {
//
// This simple servlet is designed to demonstrate how a servlet can be used
// to retrieve data from a database and return the data in the correct format
// to either the graphing applet or servlet.
//
// You may freely copy and modify this script to suite your own
// requirements.
//
//-----------------------------------------------------------------------------

    // Initialise and set variables
    String     url   = "jdbc:MySQL:///TESTDB";  // URL specifying the JDBC connection to a MySQL database TESTDB.
    Connection con   = null;                    // Database connection object
    Statement  stmt  = null;                    // Statement String
    String     query;                           // Query String
    int        datacount;

//-----------------------------------------------------------------------------
    public void doGet(HttpServletRequest req, HttpServletResponse res)
           throws ServletException, IOException {

        // Set the output characterics for the return data
        res.setContentType("text/html");
        ServletOutputStream out = res.getOutputStream();

        // Establish the database connection
        try {
            // Connect to TESTDB
            Class.forName("org.gjt.mm.mysql.Driver");
            con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
            stmt = con.createStatement();

            out.println("  <!-- Chart Data -->  \n");

            // Performing SQL query for Product X
            query = "Select Value from SalesLine where Year=2004 and Product='X' ORDER BY Month";
            ResultSet srs1 = stmt.executeQuery(query);

            // Write out the data for Series 1
            datacount = 1;
            while (srs1.next()) {
                out.println("data"+datacount+"series1: "+srs1.getString("Value")+"\n");
                datacount++;
            }

            // Performing SQL query for Product Y
            query = "Select Value from SalesLine where Year=2004 and Product='Y' ORDER BY Month";
            ResultSet srs2 = stmt.executeQuery(query);

            // Write out the data for Series 2
            datacount = 1;
            while (srs2.next()) {
                out.println("data"+datacount+"series2: "+srs2.getString("Value")+"\n");
                datacount++;
            }

        } // End try


        // Error handling
        catch(ClassNotFoundException e) {out.println("Could not load database driver: " + e.getMessage());}
        catch(SQLException e) {out.println("SQLException caught: " + e.getMessage());}

        // All finished so close the database connection
        finally {
                 try {if (con != null) con.close();}
                 catch (SQLException e) {}
        }




    } // End doGet
//-----------------------------------------------------------------------------
    public void doPost(HttpServletRequest request,HttpServletResponse response)
                throws ServletException, IOException {doGet(request, response);}
//-----------------------------------------------------------------------------
} // End class

Step 2 - Construct the HTML Page
Add the following to your HTML page replacing "[SCRIPT NAME HERE]" with the URL to the dynamic data script created in step 1 (eg. linedata.php )

<applet code="LineGraphApplet.class" archive="Linegraph.jar"
width="500" height="420">

<PARAM name="chartdata" value="[SCRIPT NAME HERE]">

<!-- Start Up Parameters -->
<PARAM name="LOADINGMESSAGE" value="Creating Chart - Please Wait.">
<PARAM name="STEXTCOLOR"     value="0,0,100">
<PARAM name="STARTUPCOLOR"   value="255,255,255">

<!-- Line Graph parameters, example values -->

<!-- General Properties -->
<PARAM name="BackgroundColor"      value="#FFFFFF">
<PARAM name="linkcursor"           value="hand">
<PARAM name="thousandseparator"    value=",">
<PARAM name="nSeries"              value="3">
<PARAM name="ndecplaces"           value="2">

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

<!-- Scale properties -->
<PARAM name="autoscale"            value="true">
<PARAM name="chartScale"           value="1">
<PARAM name="chartStartY"          value="0">

<!-- 3D properties -->
<PARAM name="3D"                   value="false">
<PARAM name="outline"              value="false">
<PARAM name="lineOutlineColor"     value="blue">
<PARAM name="depth3D"              value="0">

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

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

<!-- Y axis labels -->
<PARAM name="ylabels"              value="true">
<PARAM name="ylabel_font"          value="Arial,N,12">
<PARAM name="ylabel_pre"           value="$">
<PARAM name="ylabel_post"          value=" ">

<!-- Chart Titles -->
<PARAM name="title"                value="Graph Title">
<PARAM name="xtitle"               value="X axis title">
<PARAM name="ytitle"               value="Y axis title">

<!-- PopUp Display -->
<PARAM name="popupbgcolor"         value="#AAAAFF">
<PARAM name="popupfont"            value="Arial,N,12">
<PARAM name="popup_pre"            value="$">
<PARAM name="popup_post"           value=" ">

<!-- Series Data -->
<PARAM name="series1"              value="#F00000|6|16|false|Series 1">
<PARAM name="series2"              value="#F000F0|6|16|true|Series 2">
<PARAM name="series3"              value="#00F000|0|10|false|Series 3">

</applet>

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 - Upload the jar and script file to your web server.
The final step is to simply to upload the script to your web server and place the Linegraph.jar (contained in the ./AppletFiles/ files directory of the downloaded package) file in the same directory as your web page.

The Graphs and Charts Package can be downloaded here

The jar file contains the compiled Java code (software) which produces the graph image at page view time.

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

Utilizing Server Side Scripts to dynamically populate the Line Graph Applet or Servlet