04
Mon, Nov
5 New Articles

AS/400 Client/Server Programming with ADO, Microsoft Excel 2000, and OLE DB

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Let’s face it. In a world dominated by graphical PC interfaces, green-screen applications are things of the past. To effectively present AS/400 data, one needs to capitalize on the graphical strengths of PCs. With a basic knowledge of a PC language and SQL, you can easily learn to harness the combined power of the PC and DB2/400.

In this article, I will demonstrate PC-to-AS/400 connectivity using Microsoft’s Visual Basic for Applications (VBA) as the PC development language. I will use IBM’s SQL/400 to retrieve data from the AS/400. I will also use Microsoft’s ActiveX Data Objects
2.0 (ADO 2.0) to enable the two environments to communicate. This article will primarily serve as an introduction to ADO and its syntax. For those who need help getting started, there are a myriad of books that teach VBA. IBM’s SQL/400 manuals can be accessed at publib.boulder.ibm.com/html/as400/infocenter.html. An AS/400-specific ADO reference, A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support (SG24-5183-00), is also available at www.redbooks.ibm.com. This manual is a great reference tool.

For this exercise, Excel 2000 will be used as the demonstration environment. With little modification, developers using Office 95 and 97, 32-bit versions of Visual Basic (VB), Active Server Pages (ASP) Web programmers, and just about any other language that can accept a library extension can also use these examples.

What Is ADO and Why Use It?

Microsoft has declared ADO the successor to the ODBC interface. Basically, ODBC allows PCs to access data from multiple database platforms via a standard SQL interface. In addition to SQL database access, ADO provides record-level access and access to non- database items such as email or text. In AS/400 terms, these non-database items include executing commands and accessing data queues.

Prior to ADO, an AS/400 developer would have to learn ODBC for database access and would also have to make use of several APIs or ActiveX controls to access commands and data queues. Now, ADO provides access to all of these features in a single interface. In other words, ADO provides a single toolbox for many AS/400 objects.

ADO is an ActiveX control and, like other ActiveX controls, it has a set of properties and methods that give PC developers access to data on a remote server. As with ODBC, each database engine needs to have a special driver. In ADO terminology, these drivers are called OLE DB providers. Hence, SQL Server, Oracle, and the AS/400 will

each have its own OLE DB provider. Each provider translates the generic ADO functions into platform-specific instructions. For platforms that don’t yet have an ADO provider, a generic ODBC provider is available that allows ADO to be used with any ODBC data source.

Making a Clean Start of It

Before you start programming an Excel macro for AS/400 OLE DB access, check your AS/400 to make sure PTF levels are current and that the host servers are running. (Perform this task by running the AS/400 ‘STRHOSTSVR *ALL’ command.) On the PC, you will need to download IBM’s OLE DB provider (ADO driver) at www.as400.ibm.com/ clientaccess/OLEDB/. Currently IBM’s ADO 2.0 OLE DB provider is still in beta and is only available with the AS/400 Client Access Express for Windows (Express client) beta. If you do not want to bother downloading IBM’s ADO driver until it is released, you may use the standard ODBC driver that comes with ADO 2.0. If you will only be using the ODBC provider, then you must download ADO 2.0 from Microsoft’s Web site at www.microsoft.com/data/.

Let’s Demo This Puppy

What if your vice president of sales wants to know the sales figures for the products sold during a given date range? (See Figure 1.) A graphical representation of the data would be helpful. The PC spreadsheet offers a solution because it allows the user to view, sort, group, and manipulate the data after it is downloaded. The spreadsheet also places the data in an interchangeable format for sharing and can generate a graph. This demonstration shows the simplicity and power of programmatically retrieving AS/400 data into Excel.

For this example, I placed some data from Microsoft’s Northwind database on the AS/400. (Northwind is a sample database that comes with Microsoft Access and VB.) The sample data consists of three tables: Orders, Order Details, and Products. The Order Details table was renamed to OrderDtl to conform to AS/400 naming conventions. With the exceptions of the OrderDtl name change and the parameter definition of the date data type, this entire demonstration will work with the Northwind database using the Microsoft Access OLE DB driver.

Setting Up Your Spreadsheet Architecture

The spreadsheet will consist of one workbook containing two worksheets. Worksheet one (sheet1) will contain a summary of the products sold over a requested date range. Worksheet two (sheet2) will contain the graphical representation of the data in sheet1.

Start Excel and open a new workbook. On sheet1 of the workbook, enter the desired headings. (See Figure 1.) My example uses three columns: Qty, Product, and Amount. My example also requires two special cells to be reserved for entering the order date range. To have your macros work correctly, the order date range fields must reside in spreadsheet cells B3 and B4. If you place them anywhere else, you will get an improper date type error from your AS/400 query. Finally, a command button is utilized to activate the data retrieval from the AS/400. (A command button can be inserted via the Excel Forms toolbar.

Creating a command button should actually be the last step in the process after the VBA macro has been created.)

The Programming Interface

After the headings are set up, enter the VB Editor (Tools/Macro/Visual Basic Editor). In the Editor, add the Microsoft ActiveX Data Objects 2.0 Library to the list of references (Tools/References). Referencing the ADO 2.0 library expands Excel’s capabilities to include all of ADO’s features. Next, insert a module (Insert/Module). All of the code from this example can be placed in the default module (module1). Note also that Excel considers all subroutines created in VBA to be user-defined macros.

Establishing a Connection

The SetADOConn function (Figure 2) is a generic function that can be used in any VBA application. Establishing a connection to the AS/400 is simple. Define an ADO connection variable (gADOConn) and assign it a provider and a data source name. Again, the provider is the driver that lets ADO know what kind of backend database it is talking with. IBM’s OLE DB provider is IBMDA400. The data source is the AS/400 system name. User name and password information may also be supplied in the .Open method. If you’re using this code to program your own OLE DB interface, be sure to change the USER and PASSWORD literals in the .Open method to a valid AS/400 user and password for your system. You must also change the data source name (AS400, in this case) to be your AS/400 system name or IP address.

Keep in mind that making the initial connection is a relatively slow process. It is wise to establish a single, global connection so that it can be used by all subroutines in the project. Establishing a new connection for every server request will give new meaning to the word slow.

If you don’t have IBM’s OLE DB provider, you can use the ODBC provider by substituting a complete ODBC DSN string in the .Open method:

.Open
“DSN=AS400;UID=USER;PWD=+
PASSWORD;XDYNAMIC=1;DBQ=+
DATALIB;”

Regardless of whether you use OLE DB or ODBC, the variable gADOConn is defined as a global ADO connection variable and will store all of the necessary connection information. All subsequent requests to the AS/400 will be routed through this connection definition.

Submitting SQL Statements

Once the connection is established, the AS/400 is ready to receive instructions from the PC. The Retrieve Product Information subroutine (RtvProdInfo) in Figure 3 demonstrates how to submit an SQL SELECT statement with parameters, retrieve and copy the data into the spreadsheet cells, and format a data column for display.

There are three ADO variable types that need to be defined. The first type is the ADO command. This type is primarily used for executing AS/400 commands for submitting SQL SELECT queries with parameters and for action SQL statements such as UPDATE or DELETE. In this example, the ADO command is defined through the adoCMDProd definition.

The adoCMDProd definition is static in scope. Once it has been set, it will retain its definition in subsequent calls to the subroutine. Its .ActiveConnection property references the global connection variable gADOConn, telling it how to communicate to the AS/400. The .CommandText property contains a command or, as in this case, an SQL statement.

One disappointing note worth mentioning is that IBM’s provider currently doesn’t have a way to specify a default library name. If libraries are not specified in the SQL statement, ADO will attempt to use the user name as the library name.

The question marks (?) in the SQL statement are known as substitution markers. They will be replaced with parameters at the time of execution (in this case, the from and to order dates for data to be displayed on the spreadsheet).

The second ADO data type is the parameter (defined as adoParm and set in this example on the Set adoParm statements). As seen in the Figure 3, ADO parameters are defined with a name, data type, direction and length. The direction property is available for defining parameters as input, output, or both. Once defined, the parameters are appended to the command’s parameters collection. This step creates the association between the substitution markers in the SQL statement (the question marks) and the command. The

parameter definitions must match the order of the substitution markers in the statement,
i.e., the first substitution marker must match the first parameter definition, etc. The third ADO data type is the recordset. A recordset can be thought of as a two- dimensional array that contains the field and record contents for a given SELECT statement. In my example, the resulting recordset will have three elements or fields—Qty, Product, and Amount. In SQL terms, a recordset is the equivalent of a cursor.

The recordset is opened by referencing the .Execute method of the command variable, telling ADO to submit the SQL statement to the AS/400 for processing. The data will be returned via the recordset object. All of the recordset’s fields can then be referenced by field number or by name. Additionally, the .EOF property is provided to detect the end of file, and the .MoveNext, .MovePrevious methods, etc. are available to access the records. (A recordset may also be opened by specifying a nonparameter SELECT statement.)

In Figure 3, a loop is established to read all of the records and place the field data into the worksheet’s cells. When the loop is finished, the recordset is closed, column C is formatted as currency, and a subroutine is called to create the chart.

As the final step in making the spreadsheet functional, go back to sheet1 and create a Retrieve Data button. To create a command button, display the forms toolbar (View/Toolbars/Forms) and click the button icon. Assign the macro RtvProdInfo() to the button when prompted.

Creating the Chart

See the code in Figure 4 for creating the data chart. While the code may seem long, don’t be intimidated because Excel created most of it. To have Excel create the code, I used the Record Macro feature (Tools/Macro/Record New Macro). When the macro recorder is on, almost everything the user does (e.g., menu options and data entry) is recorded. When the macro recorder is stopped, everything that was recorded is converted to a VBA macro. Go to the VB Editor to view the macro.

With the macro recorder on, I used the chart wizard to create a chart on sheet2 (Figure 5). I then resized the chart, toyed with a few of its properties, and clicked the stop record button. To my delight, Excel had created a new VBA macro that provided the necessary code to create the chart.

When I started this project, I had no idea how to create a chart using VBA, so I let Excel show me how. One of the great features of Excel is that it can be used to create VBA code automatically! In fact, just about anything that can be done via menus in Excel can also be done using a VBA macro. The moral of this story is don’t spend hours combing through manuals to figure how to code something in Excel. Let Microsoft do it for you.

Users with Excel 2000 and a Web server can take this demonstration a step further by dynamically publishing the results to a Web page. Or users of an email client such as Outlook 98 can automatically email the spreadsheet to a predefined distribution list. Get out of the rut of doing things manually!

Forging Ahead with OLE DB

I’ve only scratched the surface of ADO’s capabilities in this article. There are a lot of easy-to-program possibilities once you learn the techniques.

ADO is a great benefit for the AS/400 developer because it provides a single interface to a variety of AS/400 features including access to database files, commands, and data queues. With ADO, there is no more need to learn a separate ActiveX control or API for each of these functions. In my opinion, ADO also requires less maintenance than ODBC.

ADO is a useful tool for combining the graphical capability and versatility of PC applications with the power of AS/400 applications. It behooves all AS/400 client-server developers to take advantage of this technology.

References

A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support, Redbook (SG24-5183-00), available online at www.redbooks.ibm.com

IBM AS/400e Information Center: publib.boulder.ibm.com/html/as400/infocenter.html

Related Reading

“Slam-dunking AS/400 Data into Microsoft Excel with OLE DB,” Midrange Computing, May 1999

“Microsoft Office 2000: Another Fish in the Groupware Sea,” Midrange Computing, April 1999

“Making PC Application-to-AS/400 Programming a Snap with IBM’s AS/400 SDK for ActiveX and OLE DB,” AS/400 Network Expert, January/February 1999

“Why IBM’s AS/400 SDK for ActiveX and OLE DB Ran Afoul of Visual Basic
6.0 (and How to Correct It),” AS/400 Network Expert (Web Edition), January/February 1999, www. midrangecomputing.com/ane/99/01

“Programming in Visual Basic Using the IBM AS/400 SDK for ActiveX and OLE DB,” Midrange Computing, October 1998

“Configuring 32-Bit Client Access/400 ODBC, Part 1” Client Access/400 Expert, September/October 1998

“Configuring 32-Bit Client Access/400 ODBC, Part 2,” Client Access/400 Expert, November/December 1998

Figure 1: Here’s a graph of sales figure I created in Excel 2000 by using an ADO interface to my AS/400.

AS-_400_Client-_Server_Programming_with_ADO-05-00.jpg 600x448

'

' Define global variables
'

Public gADOConn As ADODB.Connection

Function SetADOConn() As Boolean
'

' Function:
' Set global connection to ADO data source
'

' Returns:
' False - if successful
' True - if failed
'

On Error Resume Next

If gadoConn Is Nothing Then

Set gadoConn = New ADODB.Connection

'

' Specify name of AS/400 OLE DB provider

' and system name (Ex. S1024200)

'

gadoConn.Open "Provider=IBMDA400;_

Data Source=S1024200;"

, "USER","PASSWORD"

If Err Then

MsgBox "Could not connect to ADO data source"

Set gadoConn = Nothing

SetADOConn = True

End If
End If

End Function

Figure 2: Use this code to establish an ADO connection to an AS/400.

Sub RtvProdInfo()
' Retrieve Product Information
'

' This macro will retrieve summary info
' for all products for the given
' date range.

'

' It will then graph the resulting info
' on Sheet2.

'

Dim ws1 As Worksheet 'Worksheet 1 (Product Info)
Dim ws2 As Worksheet 'Worksheet 2 (Chart)
Dim adoRSprod As ADODB.Recordset 'ADO Recordset-Product Info
Dim adoParm As ADODB.Parameter 'Parameter
Static adoCMDprod As ADODB.Command 'ADO Command
Dim iRow As Integer

If SetADOConn() Then Exit Sub
'

' Set Reference to Worksheet 1
'

Set ws1 = Worksheets!Sheet1 'Data Sheet
ws1.Range("A7", "C100").Clear 'Clear Cells
iRow = 7 'Start row

If adoCMDprod Is Nothing Then _

Set adoCMDprod = New ADODB.Command

With adoCMDprod

If adoParm Is Nothing Then

.ActiveConnection = gadoConn

.CommandType = adCmdText

.CommandText = " SELECT Sum(B.Quantity) AS Total_qty, " & _

" C.ProductName, " & _

" Sum(B.UnitPrice*B.Quantity) AS Amount " & _

" FROM MDRNGCOMP.Orders A, " & _

" MDRNGCOMP.OrderDtl B, " & _

" MDRNGCOMP.Products C " & _

" WHERE A.OrderDate Between ? AND ? " & _

" AND A.OrderID=B.OrderID " & _

" AND B.ProductID=C.ProductID" & _

" GROUP BY C.ProductName"

' Define Date Parms

' NOTE: Date data types are defined as char on the AS/400

Set adoParm = .CreateParameter("FromDate", adChar, adParamInput, 10)

.Parameters.Append adoParm

Set adoParm = .CreateParameter("ToDate", adChar, adParamInput, 10)

.Parameters.Append adoParm

End If

'

' Set Date Parameters

' Must be in YYYY-MM-DD format for the AS/400

'

.Parameters(0) = Format(ws1.Cells(3, 2), "yyyy-mm-dd")

.Parameters(1) = Format(ws1.Cells(4, 2), "yyyy-mm-dd")

Set adoRSprod = .Execute

With adoRSprod

'

' Loop through all records & place data in

' worksheet 1.

'

Do Until .EOF

ws1.Cells(iRow, 1) = !Total_Qty

ws1.Cells(iRow, 2) = !ProductName

ws1.Cells(iRow, 3) = Format(!Amount, "########")

iRow = iRow + 1

.MoveNext

Loop

.Close

End With
End With
'

' Format Column for Currency
'

ws1.Columns("C").NumberFormat = "$#,##0.00_);[Red]($#,##0.00"
'

' Create Chart on Sheet 2
'

Set ws2 = Worksheets!Sheet2

Dim c As ChartObject
'

' Delete Old Chart(s) first
'

For Each c In ws2.ChartObjects

c.Delete
Next

CreateChart ("B7:C" & Trim(Str(iRow - 1))) 'Create Chart

End Sub

Figure 3: Use this code to create the RtvProdinfo macro.

Sub CreateChart(sDataRange As String)
'

' CreateChart Macro
' Receive Parm sDataRange - Data range to graph
' ex. B7:C15
Sheets("Sheet2").Select
Charts.Add

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(sDataRange)
ActiveChart.Location WHERE:=xlLocationAsObject, Name:="Sheet2"

With ActiveSheet.Shapes(1)

.ScaleWidth 1.48, msoFalse, _

msoScaleFromBottomRight

.ScaleHeight 1.35, msoFalse, _

msoScaleFromBottomRight

.ScaleWidth 1.25, msoFalse, msoScaleFromTopLeft

.ScaleHeight 1.25, msoFalse, msoScaleFromTopLeft
End With

With ActiveChart

.Legend.Delete

' Format Chart Title

.HasTitle = True

With .ChartTitle

.Characters.Text = "Product Summary"

.Font.Size = 12

End With

' Format X Axis

With .Axes(xlCategory, xlPrimary)

.HasTitle = True

With .AxisTitle

.Characters.Text = "Product"

.Font.Size = 10

End With

End With

' Format Y Axis

With .Axes(xlValue, xlPrimary)

.HasTitle = True

With .AxisTitle

.Characters.Text = "Amount"

.Font.Size = 10

End With

End With
End With
'

' Set Font Size for Axes
'

With ActiveChart.Axes(xlCategory).TickLabels

.AutoScaleFont = True

.Font.Size = 8
End With
With ActiveChart.Axes(xlValue).TickLabels .AutoScaleFont = True

.Font.Size = 8
End With

End Sub

Figure 4: The Create Chart macro code can also be generated with Excel’s macro recorder.

Figure 5: The final product! I created this chart by using the code and techniques in this article.

AS-_400_Client-_Server_Programming_with_ADO..08-00.jpg 600x449

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: