Tip

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.