Direct Database Connection
The servlet form of the graphing software provides a method
by which the graphing function can bind directly to a database.
Method Outline
The graphing <IMG> tag is added to an html page but
rather than supplying a "data" parameter for the
servlet a "dataDB" parameter is specified. The
dataDB parameter points to a file which contains the database
connection details (DB name, Username and Password) and
an SQL Statement for each series of data.
At page view time the Servlet reads the database file
info, connects to the database, extracts the data, constructs
the graph image and incorporates into the HTML page - thus
a real-time graph is generated at page view time.
Note: This method may only be applied to the Servlet
form of the graphing software.
Implementation
To implement this method perform the following 5 steps,
- Construct
the Database info file
- Set Up the Configuration Data
- Construct
the HTML Page
-
Upload the Database Info file, HTML page to your web
server
- Add
the graphing class files to your Servlet Engine
Step
1 - Construct the Database info file
The database info file contains both the information necessary
to create a connection to the database and the SQL statements
to execute in order to retrieve the data values.
Database
Connection Info
There
are 4 pieces of info required to make the Database Connection
which are specified by the following parameters,
Database:
Driver:
User:
Password:
Database: - This is the database connection path
and name.
eg. If you have a MySQL database called Sales you may have,
Database: jdbc:mysql://localhost/Sales
Driver: This is the location of the JDBC Driver
for your database system. In order for the graphing servlet
to connect to your database it is vital that you have the
correct JDBC driver set up and configured for you system.
For further information on JDBC database drivers please
click here.
User: The username which the graphing servlet will
use to connect to the database
Password: The database password for the Username
specified.
Series SQL Statements
For each series of data an SQL Statement should be specified.
The graph servlet will execute each statement and take the
first data item of each record returned as the value for
that data item. For instance if you have a table which holds
monthly sales figures for 2 products X and Y then your statements
may look like,
series1: Select
Value from SalesBar where Year=2003 and Product='X' ORDER
BY Month
series2: Select Value from SalesBar where Year=2003 and
Product='Y' ORDER BY Month
If 6 records are returned for each record set this would
mean that there are 6 data points for each series of data.
For a template Database Info file please click
here.
^^
back to top
Step
2 - Set up the Configuration data.
The servlet will read all the configuration parameters
from a specified file.
The
"barprops.txt" file contained in the Documentation
directory is an example file containing the configuration
data ( click
here to view the example file ). As you will see from
the example file each property is specified on a name,
value basis. Note that comments may be added to this file
by placing "<!--" at the beginning of a line.
Adjust the property values to specify the characteristics
of the bar graph and then place this file in the same
directory as your web page.
For
a full description of each property please see the "Configuration
Options and Parameters".
^^
back to top
Step
3- Construct the HTML Page
The next step is to incorporate the graph into your
HTML page. This is done via the standard html <IMG>
tag.
<img
src="[ServletEngineURL]/VbarchartServlet?
config=[URLconfigFile]&
dataDB=[DBinfo file path]"
width="500" height="420">
Where,
[ServletEngineURL]
should be replaced
with the url for your servlet engine. If running ServletRunner
on your local machine this would be, http://localhost:8080/servlet/
[URLconfigFile]
should be replaced
with the URL to either a Configuration file or server side
process.
[DBinfo
file path] should
be replaced with the file path to the location Database
info file. The database info file should be placed in a
secure directory on your web server. ie. one which isn't
accessible by the general user population.
(Remember
this file contains your database logon details !)
^^
back to top
Step
4- Upload the Database Info file, config file and HTML Page
to your web server.
Upload both the HTML Page (step 3) and Config File (Step
2) to your web server.
Upload
the Database Info file (created in Step 1) to the directory
specified in the "dataDB" parameter of the <IMG>
tag created in step 2.
For
reasons of Database security it is important that the directory
in which you place the database info file is not a Web Directory
- but rather a secure directory of which only system administrators
etc have access.
(Remember this file contains your database logon details
!)
^^
back to top
Step
5 - Add the graphing class files to your Servlet Engine.
The final step is to install the graphing class files in
your Servlet Engine.
Place
the following class files and the 'encoder' directory in
your Servlet Engine's class file directory. For Tomcat this
is usually /WEB-INF/classes/ and for the default setup of
the JSDK environment this is the /examples/ directory.
vbarcharts.class
VbarchartServlet.class
encoder
(these
classes & directory can be found in the Servlet directory
).
For
further Information on this please see the following section
in the Documentation
Implementing
the Servlet
^^
back to top