It's AMAZINGLY easy to create an Active Server Page to retrieve data from a file on the 400 and display those records in an HTML Table. ActiveX Data Objects (ADO) and IBM's OLE DB provider for the 400 make this possible.
There are a few requirements that need to be met to perform this. First off, you need to have a machine running Microsoft Internet Information Server (IIS). This machine must also have Client Access Express (V4R4M0 or higher) installed.
The attached code contains the source for an Active Server Page that uses a Server Side VB Script to read data and display it in the browser. Either select the text above and copy it to the clipboard and paste it into a new active server page document or click here to download the source in a text file. In this example, replace the SYSNAME value with the system name or IP address of your iSeries or AS/400. You will also need to use a valid user ID and password for your system. We first create the connection to the 400 using the IBMDA400 OLE DB provider. Next we use this connection along with a simple SQL SELECT statement to select the fields to be displayed on our page. This part is important because the example here will display all of the fields in the selected data source. This allows us to use this same peice of code over and over again. The only part that changes is the SELECT statement.
This example gives you the basic building blocks of using ADO and OLE DB from within an Active Server Page.
<HTML> <HEAD> <CENTER><H1>Data Set Results</H1> </HEAD> <BODY> <% Set conn = Server.CreateObject("ADODB.Connection") conn.open "Provider=IBMDA400;Data Source=SYSNAME;User ID=USER;Password=SECRET;" sql = "SELECT EMPNAM, EMPAD1, EMPAD2, EMPSTE, EMPZIP FROM QGPL.EMPMAST" Set rs = Server.CreateObject("ADODB.Recordset") set rs.ActiveConnection=conn rs.Open sql if conn.errors.count=0 Then Response.write "<table border='1' cellspacing='1' cellpadding='1' width='80%'><thead><caption><FONT SIZE=-1 FACE='arial, helvetica'><b>Query for File " & request.Form("LibFile") & "</caption><tr>" rs.MoveFirst if Not rs.EOF Then for x=0 to rs.fields.count-1 Response.write "<th BGCOLOR='#0066cc' WIDTH=60><FONT SIZE=-1 FACE='arial, helvetica'><b><font color='#ffffff'>" & rs.fields(x).name & "</th>" next End If do until rs1.eof Response.write "<tr>" For I=0 To rs1.Fields.Count-1 Response.write "<td><FONT SIZE=-1 FACE='arial, helvetica'>" & Trim(rs1.Fields(I).Value) & "</td>" Next Response.write "</tr>" rs.MoveNext loop rs.close conn.close Response.write "</TBODY></TABLE>" %> </BODY> </HTML>
This was first published in December 2001