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


%>

<!-- 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) {}
	}	
	
%>