Do you need to exchange data with your business partners? IBM's hidden treasure, Net.Data, comes through for you once again. Net.Data is bundled with the IBM HTTP Server (5769-DG1-Option 30 on your install CD). It is a simple easy to learn "macro" language that provides a bridge between HTML and your iSeries (AS/400) programs and database.
If you are a major company dealing with smaller partners, you will find that they are using Microsoft technology to run their business. Most likely you will find Windows 95/98 systems, which make use of the Microsoft Office product suite (Excel, Access, Word, etc.).
A common data exchange format easily used by Microsoft products is the Comma Separated Value file format (CSV). In the following discussion, I will show you how to build a simple SQL statement and create a CSV file dynamically on the fly as required. Lets first think about where we might use this and what type of applications we might create.
Suppose you have a customer who places many orders with you throughout the month and wants a statement summarizing all unpaid orders (one line item per invoice) so that they can reconcile the invoices to their system and pay you.
In this example, we will write a simple SQL query against our "INVOICE" file and format the output as a "Comma Separated Value" (CSV) file.
We will assume that the customer logs in to an authenticated Web site and you can obtain their customer number from their USER-ID.
You write a simple Net.Data DTW_SQL function such as:
%FUNCTION(DTW_SQL) getOpenInvoices() {
SELECT invnbr, invdte, duedte, amtdue
FROM mydb.invoice
WHERE custno = $(custno) and status = "OPEN"
Order by invnbr
%REPORT {
%ROW {
$(V_invnbr), "$(V_invdte)", "$(V_duedte)", $(V_amtdue)
%}
%}
%}
In this function, you select all of the "OPEN" invoices for the customer (identified by "custno"). Pay particular attention to the way the variables in the %ROW (detail line) block of the query are specified. $(V_invnbr) and $(V_amtdue) are numbers and therefore not surrounded by quotes. $(V_invdte) and $(V_duedte) are treated as character strings, and according to the rules for CSV files must be enclosed in double quotes. Each field is separated by a comma. The result of executing this function would be a row written to the STDOUT file that would look like this:
1234,"01-23-2001","02-01-2001",3540.23
Net.Data will automatically insert a carriage return line feed (CRLF) at the end of the line. Now for the magic: If you were to call this as a standard function, you would get an HTML page displayed on your browser with a bunch of rows of comma-separated value data that wouldn't be very useful to anyone.
If you know Net.Data, you know that there are sections called %HTML sections that have a name. You specify the name as part of the URL used to invoke the Net.Data macro beginning at that location. Here is the trick:
%HTML(openInvoice.csv) {
@getOpenInvocies()
%}
This HTML section calls the function ( @getOpenInvoices). The trick is in the name of the section. Your URL used to invoke this macro would look something like this:
http://www.mysite.com/cgi-bin/db2www/invoice.mac/openInvoice.csv
As you can see, the URL terminates with ".csv". This is mandatory for Microsoft Internet Explorer to determine based on the .csv extension that this is a "CSV" file and invoke Microsoft Excel!
This tip works great with Microsoft Internet Explorer. Stay tuned for how to use it with Netscape.
-------------------------
About the author: Bob Cancilla is managing director of IGNITe/400, an electronic iSeries 400 Internet users group. He is also author of the book Getting Down to e-business with AS/400.
This was first published in February 2001