Exchanging CSV data via Net.Data

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

Dig deeper on Web Tools

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close