Embedding the Line Graph Applet within a Dynamic Page

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

Embedding the Line Graph Applet within a Dynamic Page

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 all the data and configuration information is supplied by to the graph via standard HTML PARAM tags. The values of the PARAM tags may then be dynamically generated by the script in which the graph resides. This means that the script can be written to first read data from a database before populating the PARAM tag values. 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 server side dynamic page script (eg. PHP, ASP, JSP or Perl). Within the script the data values of the Applets PARAM tags are filled in by the Web Server at page view time.

At page view time the script will read data from the database and construct the Applet PARAM tags - hence producing a Real-Time Dynamic Graph with data direct from a database within the web page.

Step 1 - Construct the Dynamic 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 used to dynamically populate the data parameters for the graph.

Template PHP dynamic graph page:-

<?php
/* ---------------------------------------------------------------- */
/* 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 "SalesBar". It then constructs the HTML page dynamically */
/* inserting the values from the result set into the parameters of  */
/* the Line Graph Applet.                                           */
/*                                                                  */
/* You may freely copy and modify this script to suite your own     */
/* requirements.                                                    */
/*                                                                  */
/* ---------------------------------------------------------------- */


    /* 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 SalesBar where Year=2004 and Product='X' ORDER BY Month";
    $result1 = mysql_query($query) or die("Query failed : " . mysql_error());

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

    /* Construct HTML page containing Line Graph Applet*/
    print "<html>\n";
    print "<head>\n";
    print "<title>3D Line Graph</title>\n";
    print "</head>\n";
    print "<body>\n";
    print "<applet code='LineGraphApplet.class' archive='Linegraph.jar' width='500' height='420'>\n";
    print "   <!-- Start Up Parameters -->\n";
    print "  <PARAM name='LOADINGMESSAGE' value='Line Graph Loading - Please Wait.'>     <!-- Message to be displayed on Startup -->\n";
    print "  <PARAM name='STEXTCOLOR' value='0,0,100'>                                  <!-- Message Text Color-->\n";
    print "  <PARAM name='STARTUPCOLOR' value='255,255,255'>                            <!-- Applet Background color -->\n";
    print " \n";
    print "  <!-- Property file -->\n";
    print "  <PARAM name='chartproperties' value='linepropsdb.txt'>\n";
    print "  \n";
    print "  <!-- Chart Data -->  \n";

    $data_num = 1;
    while ($line = mysql_fetch_array($result1, MYSQL_ASSOC)) {
        foreach ($line as $col_value) {
           print "<param name='data".$data_num."series1' value='".$col_value."'>\n";
        }
        $data_num = $data_num +1;
    }


    $data_num = 1;
    while ($line = mysql_fetch_array($result2, MYSQL_ASSOC)) {
        foreach ($line as $col_value) {
           print "<param name='data".$data_num."series2' value='".$col_value."'>\n";
        }
        $data_num = $data_num +1;
    }

    print " \n";
    print "</applet>\n";
    print " \n";
    print "</body>\n";
    print "</html>\n";

    /* Free resultset */
    mysql_free_result($result1);
    mysql_free_result($result2);

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


?>

Template ASP dynamic graph page:-

<html>
<head>
<title>Line Graph</title>
</head>
<body>

<!-- ----------------------------------------------------------------  -->
<!-- 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 constructs the html page dynamically -->
<!-- inserting the values from the result set into the parameters of   -->
<!-- the Pie Chart 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, objConn


' -- 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, objConn
%>



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

  <!-- Start Up Parameters -->
  <PARAM name='LOADINGMESSAGE' value='Line Graph Loading - Please Wait.'>     <!-- Message to be displayed on Startup -->
  <PARAM name='STEXTCOLOR' value='0,0,100'>                                  <!-- Message Text Color-->
  <PARAM name='STARTUPCOLOR' value='255,255,255'>                            <!-- Applet Background color -->

  <!-- Property file -->
  <PARAM name='chartproperties' value='linepropsdb.txt'>

  <!-- Chart Data -->

<%
DIM iDataCount

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

<param name='data<%=iDataCount%>series1' value='<%=objRS1("Value")%>'>

<%
    objRS1.MoveNext
Loop
objRS1.Close
%>


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

<param name='data<%=iDataCount%>series2' value='<%=objRS2("Value")%>'>

<%
    objRS2.MoveNext
Loop
objRS2.Close
%>

</applet>


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

</body>
</html>

Template JSP dynamic graph page:-

<%@ 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.
//
// For further information visit,
// http://www.jpowered.com/line_graph/index.htm
//
//-------------------------------------------------------------------

    // 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());}



%>


<html>

<head>
    <title>Line Graph</title>
</head>

<body>
    <applet code='LineGraphApplet.class' archive='Linegraph.jar' width='500' height='420'>
       <!-- Start Up Parameters -->
      <PARAM name='LOADINGMESSAGE' value='Line Graph Loading - Please Wait.'>     <!-- Message to be displayed on Startup -->
      <PARAM name='STEXTCOLOR' value='0,0,100'>                                  <!-- Message Text Color-->
      <PARAM name='STARTUPCOLOR' value='255,255,255'>                            <!-- Applet Background color -->

      <!-- Property file -->
      <PARAM name='chartproperties' value='linepropsdb.txt'>

      <!-- Chart Data -->
<%
    try {
        // Performing SQL query for Product X
        query = "Select Value from SalesBar 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("<param name='data"+datacount+"series1' value='"+srs1.getString("Value")+"'>\n");
            datacount++;
        }

        // Performing SQL query for Product Y
        query = "Select Value from SalesBar 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("<param name='data"+datacount+"series2' value='"+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) {}
    }

%>


    </applet>


</body>
</html>

Template Java Servlet dynamic graph page:-

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

public class LineAppletDB 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 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;

//-----------------------------------------------------------------------------
    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();

            // Construct HTML page containing Line Graph Applet
            out.println("<html>\n");
            out.println("<head>\n");
            out.println("<title>Line Graph</title>\n");
            out.println("</head>\n");
            out.println("<body>\n");
            out.println("<applet code='LineGraphApplet.class' archive='Linegraph.jar' codebase='http://www.yourdomain.com/path/' width='500' height='420'>\n");
            out.println("   <!-- Start Up Parameters -->\n");
            out.println("  <PARAM name='LOADINGMESSAGE' value='Line Graph Loading - Please Wait.'>     <!-- Message to be displayed on Startup -->\n");
            out.println("  <PARAM name='STEXTCOLOR' value='0,0,100'>                                  <!-- Message Text Color-->\n");
            out.println("  <PARAM name='STARTUPCOLOR' value='255,255,255'>                            <!-- Applet Background color -->\n");
            out.println(" \n");
            out.println("  <!-- Property file -->\n");
            out.println("  <PARAM name='chartproperties' value='http://www.yourdomain.com/path/linepropsdb.txt'>\n");
            out.println("  \n");
            out.println("  <!-- Chart Data -->  \n");

            // Performing SQL query for Product X
            query = "Select Value from SalesBar 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("<param name='data"+datacount+"series1' value='"+srs1.getString("Value")+"'>\n");
                datacount++;
            }

            // Performing SQL query for Product Y
            query = "Select Value from SalesBar 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("<param name='data"+datacount+"series2' value='"+srs2.getString("Value")+"'>\n");
                datacount++;
            }

            // write out the end of the HTML page
            out.println(" \n");
            out.println("</applet>\n");
            out.println(" \n");
            out.println("</body>\n");
            out.println("</html>\n");

        } // 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

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 2 - 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 script.

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

Embedding the Line Graph Applet within a Dynamic Page