<%@ page import="java.sql.*"  %>
<%@ page import="java.util.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*"   %>
<%
/** 
 * The following JSP script is a sample script designed to demonstrate
 * a method for creating real-time dynamic graphs from data
 * held within databases.
 *
 * This sample connects to a database and reads data from
 * a table "SalesLine". It then formats the data for use by the
 * Active Graph software.
 *
 * You may freely copy and modify this script to suite your own
 * requirements.
 *
 * 
 */

/*
 * Initialise and set variables
 */
String     url   = "jdbc:MySQL:///graphtestdb";  // URL specifying the JDBC connection to a MySQL database graphtestdb.
Connection con   = null;                         
Statement  stmt  = null;                         
String     query;                                
int        datacount;
ResultSet  rs    = null;

/*
 * Establish the database connection
 */
try 
{
    Class.forName("org.gjt.mm.mysql.Driver");
    con = DriverManager.getConnection (url,"[DB Username]","[DB Password]");
    stmt = con.createStatement();
} 
catch(ClassNotFoundException e) 
{
    out.println("Could not load database driver: " + e.getMessage());
}
catch(SQLException e) 
{
    out.println("SQLException caught: " + e.getMessage());
}

/*
 * Get the data from the database
 */
try 
{
    query = "SELECT productx,producty,productz FROM sales ORDER BY month";
    rs    = stmt.executeQuery(query);
    
    /*
     * Output the data to the graph
     */
    datacount = 1;
    while (rs.next()) 
        {
        out.println("series1data" + datacount + " = " + rs.getString("productx")+"\n");
        out.println("series2data" + datacount + " = " + rs.getString("producty")+"\n");
        out.println("series3data" + datacount + " = " + rs.getString("productz")+"\n");          
        datacount++;
    }
	
}
catch(SQLException e) 
{
    out.println("SQLException caught: " + e.getMessage());
}
finally 
{
    try 
    {
        if (con != null) con.close();
    }
    catch (SQLException e) 
    {
        out.println("Errot trying to close database connection: " + e.getMessage());
    }
}	
	
%>