NetCharts® Applets and JSP with JDBC/ODBC
NetCharts Applets can be used in combination with Java Server Pages (JSP) technology to dynamically produce chart-enabled HTML pages.
This example uses JSP to extract data from an ODBC data source (via the JDBC/ODBC bridge) called regionalsales, populate variables with the data, and build an HTML page that contains a NetCharts Applets applet that uses this data.
Configuring the ODBC data source
Running the example
Examining the JSPcode
The complete JSPsource code
When run, the example will generate a page containing the following barchart applet.
Configuring the ODBC Data Source
This example extracts data from a small Access database called regionalsales.mdb. This sample database is provided as part of the NetCharts Applets distribution and can be be registered as a System DSN called regionalsales as follows:
- Launch the Control Panel.
- Select Data Sources (ODBC).
- Select the System DSN tab.
- Add a "Microsoft Access Driver (*.mdb)" data source
- Name the data source regionalsales.
- Select the regionalsales.mdb file. Its default location is
c:\program files\visual mining\netcharts4.5\Netcharts\examples\dataimport.
Running the Example
The example JSP script can be configured and run using the following steps:
- Make sure that regionalsales.mdb is available as a System DSN (see above).
- Copy the getSalesDataChart.jsp file to any subdirectory under the Cold Fusion-enabled webserver. For instance, with IIS or PWS, it could be in the \wwwroot directory or any subdirectory under \wwwroot.
- Make a copy of the NetCharts Applets 4.5 \classes directory under \wwwroot, or make a \classes virtual directory that points to the \classes directory under the NetCharts Applets 4.5 installation. The default installation directory for NetCharts Applets 4.5 is
c:\program files\visual mining\netcharts 4.5\netcharts\. - Start a web browser and load the JSP page. For example, if the JSP page was installed in the wwwroot/examples directory, the URL would look like http://localhost/examples/getSalesDataChart.jsp
When you load the page, you should see some a page the regional sales chart shown above.
Examining the Code
This JSP script performs the following steps to create chart enabled web page.
- Define the variables
- Connect to the database and pass in the SQL statement
- Open the database and extract the data
- Temporarily pass the data into holding arrays
- Populate the variables with the data from the arrays
- Instantiate the applet and pass the variables in through the NFParamScript parameter.
The first section of code defines several variables. These variables are used to connect to the ODBC datasource, pass in the SQL statement that defines the data we want to extract, and hold this data for passing to the NetCharts Applets applet:
// variables for connecting to the ODBC data source
String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
String serverURLBase = "jdbc:odbc:";
String dbName = "regionalsales";
String sql = "Select region, quarter1, quarter2, quarter3, quarter4 from Sales"; // variables for holding the data from the data source
String ds1 = "25,50,75,100";
String ds2 = "100,200,300,400";
String ds3 = "100,125,150,200";
String ds4 = "100,75,50,25";
String labels = "\"North\", \"South\", \"East\", \"West\"";Connection connection = null;
Statement statement;
boolean isdata;
ResultSet rs;
int updateCount = 0;
The next section of code loads the JDBC driver, opens a connection to the database, and prepares and executes the SQL statement.
try {
Class.forName(driverName);
} catch (Exception e){
}try {
// Open connection to db
connection = DriverManager.getConnection(serverURLBase+dbName);// create statement to handle SQL
statement = connection.createStatement();
isdata = statement.execute(sql);
Next, the code loops through the ResultSets extracted from the database. The resulting data is placed into strings. These strings contain the data in a comma-separated format, which NetCharts Applets can understand. Ultimately, the data will look something like "5,10,15,20". After each row of data is extracted, the ResultSet is closed. After all of the data is extracted, the Statement and Connection are closed.
int loopCount=0;
// This loop handles multiple sql statements or stored procedures
// that return multiple result sets.
do {
loopCount++;
if (loopCount > 50){
// This prevents errors, like with the Symantec
// driver, always returning an update count of 0
break;
}rs = statement.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int numColumns = rsmd.getColumnCount();boolean isdatainrow;
ds1 = ds2 = ds3 = ds4 = labels = "";
for (int rowNum=0; rs.next(); rowNum++){
Vector row = new Vector();if (rowNum > 0)
{
ds1 += ",";
ds2 += ",";
ds3 += ",";
ds4 += ",";
labels += ",";
}Object o = null;
for (int i=1; i <= numColumns; i++){
// we would return the actual object, but
// the JDBC-ODBC bridge crashes. :-(
o = rs.getString(i);
row.addElement(o);
}labels += (String) row.elementAt(0);
ds1 += (String) row.elementAt(1);
ds2 += (String) row.elementAt(2);
ds3 += (String) row.elementAt(3);
ds4 += (String) row.elementAt(4);
}
rs.close();
System.gc();
} while (statement.getMoreResults() == true);// close the statement to clean up cursors.
statement.close();
if (!connection.isClosed())
connection.close();
The applet tag can now be constructed. NetCharts Applets uses an applet parameter called NFParamScript to pass in chart definition strings. A simple name=value format is used to construct complete chart definitions that can be processed by the NetCharts Applets applet.
Note: The "code=" attribute of the applet is pointing to the netcharts45.apps.* package. This is done to avoid any classpath conflicts that may occur on the system running NetCharts Applets. If, for instance, an older version of NetCharts Applets is installed on the system, and the CLASSPATH environment variable is pointing to that package, then a conflict could occur that might keep this example from running. By pointing specifically to the netcharts45.apps.* package, no conflict will occur. Note that a change will need to be made to the "code=" attribute in all of the applet tags with every major new version upgrade of NetCharts Applets. (NetCharts Applets 5.0, 6.0, etc.)
If this potential classpath conflict is not a concern, then set "code=netcharts.apps.*". This will allow the applets to run across multiple versions of NetCharts Applets, without any changes.
<applet name=Quarterly Sales
code=netcharts4.apps.NFBarchartApp
codebase=/classes
archive=netcharts4.jar
width=600 height=400>
<param name=NFParamScript value='
#Populate the chart with all of the static template information;
ChartName = "Basic Grouped Barchart";
DebugSet = ALL;
ChartWidth = 600;
ChartHeight = 400;
Background = (white,NONE,3,null,TILE,black);
...
The relevant parameters for this chart are DataSet1, DataSet2, DataSet3, DataSet4, and BarLabels:
#Now populate the chart with the dynamic data extracted from the database via JSP;
DataSet1 = <%=ds1%>;
DataSet2 = <%=ds2%>;
DataSet3 = <%=ds3%>;
DataSet4 = <%=ds4%>;
BarLabels = <%=labels%>;
When this JSP page runs, it will convert the variables into the strings created earlier. These strings will then be passed in to the applet, and the chart will be created.
