<%
/*
This is an example of a data-driven chart JSP, using a vertical
resultset.
*/
/*
Includes:
java.util has the Hashtable object and certain other methods
java.sql has the data access objects
netcharts.server has the chart building objects
*/
%>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="netcharts.server.api.*" %>
<%@ page import="netcharts.server.util.*" %>
<%
/*
GETTING THE DATA INTO THE NETCHARTS CHART OBJECT
================================================
The column names and data rows are first appended onto a string.
This string is then loaded into a hash table, where the key exactly
matches the key inside the CDX file. The resulting hash table can then
be passed to the NetCharts object. :)
*/
/*
name of the JSP on the netcharts server, in the testbed folder, in
the aaMMOCHARTSPre project
*/
String strFileName = "TEST_Chart_ByCode_DataMart.jsp";
String strSolution = "aaMMOChartsPre";
String strProject = "testbed";
String strCDX = "TEST_Chart_ByCode_DataMart.cdx";
// Connection attributes
String strDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String strURL = "jdbc:microsoft:sqlserver://";
String strServer = "DCACNT53";
String strPort = "1433";
String strDB = "ERP_613";
String strUID = "rentonmet";
String strPWD = "rainier";
String strCtype = "cursor";
// build the connection string from the above attributes
String strConn = strURL + strServer + ":" + strPort + ";databaseName=" + strDB + ";selectMethod=" + strCtype + ";";
// Other attributes
String strQuery = "select left(item, 1) as FirstLetter, " +
"count(item) as thecount from " +
"C613.ASCOpenOrderDetail " +
"Where left(item, 1) in ('A', 'B', 'C', 'D') " +
"group by left(item, 1) " +
"order by thecount desc";
/*
Try to open a connection to the data source
*/
Connection objConn = null;
try
{
Class.forName(strDriver);
objConn = java.sql.DriverManager.getConnection(strConn,strUID,strPWD);
}
catch(Exception e)
{
e.printStackTrace();
out.println("Couldn't connect: " + e.getMessage() + "");
}
/*
If we have an open connection, we can try to get the data and build
the chart. In Java, you have to create a Statement object first.
*/
Statement objStmt = objConn.createStatement();
try
{
// get the data and metadata
ResultSet objRS = objStmt.executeQuery(strQuery);
// initialize the local strings
String strColumns = "BarLabels = ";
String strRows = "DataSet1 = ";
String strTheData = "";
/*
load the local strings with data. We have to do it like this
because the SQL Server driver we're using doesn't support cursor
movement methods. :(
*/
while(objRS.next())
{
strColumns = strColumns + "'" + objRS.getObject(1) + "',";
strRows = strRows + objRS.getObject(2) + ",";
}
strColumns = strColumns.substring(0, strColumns.length() - 1) + ";";
strRows = strRows.substring(0, strRows.length() - 1) + ";";
strTheData = strColumns + "\n" + strRows;
// We have the data locally; release the objects for GC
objRS.close();
objConn.close();
/*
load the local string into the Hashtable. In the CDX file, the
BarLabels and DataSet1 parameters should be replaced with the key
shown in the first parameter of the put method - in this case,
THEDATA.
*/
Hashtable objchartParams = new Hashtable();
objchartParams.put("THEDATA", strTheData);
// Create a NetCharts object and load the Hashtable into it
NSWebToolKit objTK = new NSWebToolKit(strSolution + "\\" + strProject);
String strChart = objTK.getChartAsImageAndMap
(
strCDX,
objchartParams,
request,
true
);
// We're done; display the chart
out.println(strChart);
}
catch(Exception e)
{
e.printStackTrace();
out.println("
Query Failed." + "\n\n" +
e.getMessage() + "");
}
%>