Sending Dynamic Data to PopChart Server

PopChart Server User Guide 4.0.5

Importing Data Directly from Databases

Perhaps one of the most exciting new features of PopChart Server 4.0 is the ability to import data directly from a JDBC or ODBC driven database. All you have to do is specify a database driver, name, and SQL query, and PopChart Embedder will automatically execute the query and import the resulting data.

Note: This documentation assumes you are already familiar with your database and SQL queries. If you are not, you will probably need to brush up on these subjects before you can import database data into PopChart Server.

Importing From a DataBase in Java

Before you import data in Java, you should check to see that you have a JDBC driver for your database. Some installations of Java (for instance, the JRE that is installed automatically with PopChart Server on Microsoft Windows) already contain certain drivers. However, if your Java installation does not contain the appropriate driver, you must make sure that you include your JDBC driver in the classpath for your Java program, servlet, or web application server. You would do this in the exact same manner that you added the PopChartEmbedder.jar file to your classpath in "Including the PopChartEmbedder.jar File in Your Classpath" in Chapter 5.

Once you have done this, you can use the setDBQuery() method to import data. The setDBQuery() method accepts a number of parameters. The syntax is as follows:

myPopChart.setDBQuery(graphname,databasedriver,databaseURL,user,password,SQLquery);

The first parameter, graphname, is the name of the graph object (see "What is my Object Named?" in Chapter 6) into which you want to import the data. The second parameter is the full name of your JDBC driver. This is not the file or archive that contains the driver, but rather the Java class itself. In other words, com.oracle.jdbc.OracleDriver would be valid, while classes12.zip would not be valid.

The third parameter is the actual name of the database. Typically, your database URL will be in the form jbdc:driver:name (e.g. jdbc:odbc:mydata, jdbc:oracle:mydata). Be sure that this database is accessible from the computer running PopChart Embedder.

The fourth and fifth parameters are the user name and password for this database, respectively. If no user name or password is required, you should pass an empty String. The last parameter is your SQL query. This documentation assumes that you already know enough about SQL to build this query yourself.

Example 6.15 shows how you would use this method to import data into the PopChart image we created in Example 5.4. It assumes that your graph object is named graph, that you are using the sun.jdbc.odbc.JdbcOdbcDriver, that your user name and password are user and password, that your database is located at jdbc:odbc:bcdemos, and that your SQL query is "Select Description, OnHand from parts order by Description". If you have access to a database, you could easily replace these values with valid parameters and actually generate a PopChart image containing your data.

Note: Both Example 6.15 and Example 6.16 use Sun's JDBC-ODBC bridge, which is a database driver that is automatically installed with JRE for Windows. Thus, we do not have to worry about adding anything to our classpath.

Using SetResultSet()

If you'd like to query the data using your own method, you can take advantage of the Java PopChart Embedder's setResultSet(String,ResultSet) method, which allows you to import data from a resultSet object.

You pass setResultSet() two parameters. The first is a String containing the name of the graph object into which you want to import the data. The second is a resultSet object. Example 6.16 below shows how you might use this method. It assumes you have already imported the java.sql library (with the statement import java.sql.*). Note that it achieves the exact same results as the setDBQuery() method call in Example 6.15.

Importing from a DataBase with COM

New in PopChart Server 4.0.1

Using the COM version of the PopChart Embedder, you can import data from any ODBC data source that has been set up on the computer that is generating your web pages.

On Windows NT/2000/XP systems, you can view your ODBC data sources by selecting Administrative Tools > Data Sources from the Control Panel. This will bring up the ODBC Data Source Administrator. The PopChart Embedder can access any database listed under the User DSN, System DSN, and File DSN tabs. However, in most cases you will only want to use data sources listed under the System DSN tab, as other data sources will not be available when the current user logs off of the system.

Note: You can add a database to this list by clicking on the Add button. For this documentation, we assume that you have already successfully added the database you wish to access.

The screenshot below shows what you might see in your ODBC Data Source Administrator. In this particular example, the database that we want to use is bcdemo.

Once you have figured out which database you want to connect to, you can use the setDBQuery() method to import data. The setDBQuery() method accepts a number of parameters. The syntax is as follows:

myPopChart.setDBQuery(graphname,"",databaseName,user,password,SQLquery);

The first parameter, graphname, is the name of the graph object (see "What is my Object Named?" in Chapter 6) into which you want to import the data.

The second parameter is not used in the COM version of setDBQuery() and should be set to an empty string.

The third parameter is the actual name of the database, which you can identify in the ODBC Data Source Administrator (e.g. bcdemo). Be sure that this database is accessible from the computer running PopChart Embedder.

Note: You can also make a DSN-less connection to a database. In this case, in place of a database name, you would enter a string containing all of the parameters necessary to identify your database. The actual syntax will depend on the database, but may look something like this:

Provider=SQLOLEDB.1;Password=MyPassword;Persist Security Info=True;User ID=MyUserID;Initial Catalog=Northwind;Data Source=MyDataServer

The fourth and fifth parameters are the user name and password for this database, respectively. If no user name or password is required, you should pass an empty String. The last parameter is your SQL query. This documentation assumes that you already know enough about SQL to build this query yourself.

Example 6.17 shows how you would use this method to import data into the PopChart image we created in Example 5.4. It assumes that your graph object is named graph, that your user name and password are user and password, that your database is named bcdemo, and that your SQL query is "Select Description, OnHand from parts order by Description". If you have access to a database, you could easily replace these values with valid parameters and actually generate a PopChart image containing your data.