The iSeries has been beaten up for years for its ugly green screens, but Web technologies can change all that. This tip will show how Web applications can easily create dynamic graphs using standard technologies such as SQL and HTML. There are many advanced and more sophisticated graphing techniques and package such as JFreeChart or integration with MS Excel, but if the following techniques can achieve your goal it can be done quickly and without cost.
Note: It is assumed the reader has an understanding of SQL/JDBC and HTML. If not, check out http://www.w3schools.com
The problem
Management wants to see data and an associated graph of unit sales by region.Getting the data is easily accomplished using standard SQL, but how do you get a graph on a Web site? The following is what we want to produce:
Step 1: Using a JPG image
Many graphing requirements can be achieved by showing images (JPG or GIF files) along with the data. An image can be created with any standard graphics software such as Paint Shop. Below is a JPG image that is a single pixel wide. (Look closely because it is just a sliver.)
Once you have an image, you can adjust the image width to produce the dynamic sizing. For example, this HTML produces the below graphic bar:
<img src="http://www.planetjavainc.com/wow63x/images/wowGraphRed.jpg" border=0 height = 25 width="416"/>
And this HTML produces the smaller graphic bar below:
<img src="http://www.planetjavainc.com/wow63x/images/wowGraphRed.jpg" border=0 height = 25 width="16"/>
In these examples, a single pixel graphic named "wowGraphRed.jpg" is referenced in an HTML image tag, but the width is set to 16 pixels wide for the small graphic and 416 for the long chart element (see the width parameter). Given this, charts can be produced from normal graphics by simply varying the width tag. Simple enough, eh?
Step 2: Dynamically varying the image width
The next job is to produce the HTML image tags with dynamic data. The standard way to access data is via SQL. We can use SQL to dynamically produce a String field that represents the HTML tags. Examine the following SQL:
SELECT region, sum(sales) as sales , '<img src="images/wowGraphRed.jpg" border=0 height = 25 width="' || char(sum(sales)) || '"/>' as graphDD FROM pjdata.sales group by region order by region
In the above SQL, we select the region and sum of the sales amounts and then use concatenation to produce a String that represents the HTML tag. Note: The || perform the concatenation of the String elements and the "char(sum(sales))" is SQL that produces an integer that is the sum of sales. This is the dynamic width value. The group by clause causes sales to be summed by the region.
Step 3: Putting it together
The SQL/JDBC above is executed by your Web application, and the results are extracted using standard JDBC Java programming. The field called "graphDD" is a string field and has the HTML image script needed to produce the chart. A servlet or JSP can simply produce the end result by creating an HTML table that includes the image tags produced dynamically by the SQL. As the data changes, the image length changes dynamically.
Run the following link and view the source from your browser to further analyze the results.
http://www.planetjavainc.com/wow63x/runApp?id=390&_pj_lib=wowsamp60
Summary
This tip showed you how to use a standard image, HTML and SQL to produce a simple but useful application with dynamic graphing from your iSeries data. If you're not familiar with JDBC, send an e-mail me at pholm@planetjavainc.com and I'll send you the code that performs that segment.
Happy graphing!
---------------------------
About the author: Paul Holm is an iSeries WebSphere and Java specialist at PlanetJ Corp. He worked for IBM-Rochester for over 10 years as a DB2/400 and Java/WebSphere developer.
