13
Wed, Nov
5 New Articles

More AS/400 Client/Server Programming with ADO and VBA

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

ActiveX Data Objects (ADO) is a great aid for client/server programmers. It not only provides a great way to access data, but it also affords easy access to AS/400 commands, programs, and data queues.

In "AS/400 Client/Server Programming with ADO, Microsoft Excel 2000, and OLE DB," (AS/400 Network Expert, May/June 1999), I demonstrated some simple data retrieval techniques using an SQL parameter SELECT. Also, I presented a general introduction on using ADO connection, command, recordset, and parameter data types inside Excel 2000. However, in addition to data access, there are a few other ADO topics you should know about before delving into application development. These topics include calling stored procedures, executing commands, and retrieving server errors.

In this article, I'm going to move away from an application-specific implementation and demonstrate these additional ADO concepts by using generic Visual Basic for Applications (VBA) code. Programmers using Excel 95, Access 95, Office 97, Office 2000, and Visual Basic 4.0 (and above) may use these examples for their own ADO to AS/400 code. Likewise, Web developers should easily be able to modify these examples for VBScript.

 

Getting Started—Connecting to Your AS/400

 

As a quick review, Figure 1 contains the code necessary to connect to the AS/400. Remember that to use ADO for AS/400 connectivity, you have a choice of methods to use. You can use the ADO techniques I discuss here with the AS/400 SDK for ActiveX and OLE DB provider (the SDK) that comes with Client Access for Windows 95/NT V3R1M3 and above or the Express client toolkit that comes with AS/400 Client Access Express for Windows (Express client). The Express client toolkit now includes an upgraded version of the SDK or the Windows 95/NT client. If you don't want to use IBM's ADO drivers, you can also use these techniques with the standard ODBC driver that comes with ADO 2.0. If you are only using the ODBC driver, then you must download ADO 2.0 from Microsoft's Web site at www.microsoft.com/data.

If you are using Visual Basic with the Windows 95/NT SDK or the Express client toolkit, you need to be aware that there are some restrictions in how you use AS/400 ADO access with Visual Basic. For a list of restrictions on Visual Basic usage with the Client


Access family, see "Why IBM's AS/400 SDK for ActiveX and OLE DB Ran Afoul of Visual Basic 6.0 (and How to Correct It)," by Joe Hertvik (AS/400 Network Expert [Web Edition], January/February 1999, www.midrangecomputing.com/ane/99/01).

Also, don't forget to reference the ADO 2.0 library in your application. In Microsoft VBA environments, reference the library by selecting Tools/References from the drop-down menu and then selecting the Microsoft ActiveX Data Objects 2.0 library from the list (Visual Basic 6.0 and Access 2000 users may already have this library referenced by default.)

Recall that the ADO connection variable is defined with a global scope so that an application only has to connect once to your AS/400. The .OPEN method contains the OLE DB provider (in this case, IBMDA400), the data source (system name), and the user name and password. Additionally, an ODBC DSN string may be substituted in the .OPEN method when using the generic ODBC OLE DB provider. (Remember that the OLE DB provider communicates between the back-end database and ADO. As with ODBC, each back-end database has its own specific OLE DB provider.)

All of the following examples will assume that a connection to the AS/400 has been established via a connection variable called gADOConn.

 

Executing AS/400 Stored Procedures

 

One of the most useful features of any SQL interface is the ability to call stored procedures. A stored procedure is fancy SQL terminology for a program or script. On the AS/400, a stored procedure is nothing more than a program that is accessed by your client/server application.

Stored procedures are useful when SQL is incapable or inefficient at performing a task. They are also useful for AS/400 tasks that don't involve recordsets, such as when accessing non-database data (e.g., data areas), for passing parameters (e.g., system APIs) or for accessing a file by relative record number. The drawback of stored procedures is that they are usually platform-specific and therefore are not easily ported to other systems.

Figure 2 shows a stored procedure, written in CL, designed to retrieve basic AS/400 job information. Since the host server jobs that allow a PC to communicate to the AS/400 are run under user QUSER, it is difficult to determine the actual user of the job. It is also difficult to determine which AS/400 job is servicing PC requests, making it difficult to look for information when the PC application is having problems. This code uses the RTVJOBA command to identify the real user and the host job name.

In Figure 2, the ENDPGM command was issued. When designing a stored procedure that will be called many times, make sure that the program issues a return rather than a program end statement. The program will be left in memory so that it doesn't have to be loaded again each time it is called. In CL, the RETURN command is used to do achieve this result. In ILE RPG and RPG/400, issuing the RETURN (RETRN) op-code without having *INLR on will accomplish the same thing.

Use the CRTBNDCL (for ILE programs) or CRTCLPGM (standard CL) command to create PGM001CL into library QGPL. (Make sure to put the program in QGPL or the user may not be able to access it in the next step.) Once the program is created, the next step is to define the procedure for SQL. This task is done using SQL's CREATE PROCEDURE command inside an AS/400 SQL Interactive session, which is started by running the Start SQL Interactive Session (STRSQL) as follows:

STRSQL NAMING(*SQL)

(If you don’t have the SQL development kit installed on your AS/400, then you will have to use another SQL utility to execute the CREATE PROCEDURE statement. You may also use ADO to submit the SQL statement using code.) CREATE PROCEDURE stores the program name, language, parameter list, etc. in an SQL system catalog. You can view all


of the your stored procedure definitions by querying the catalog table QSYS2/SYSPROCS. The definitions in this table serve mostly as a time-saving feature for SQL. When a program is called from a PC application, the SQL interpreter doesn't have to figure out how many and what type of parameters are needed. SQL can look up the program in the SYSPROCS table and find out what type of program is being called (which is important because different languages have different data type conversion rules) and what parameters are being passed. A friendly name can also be assigned to the program. For this example, the statement to define PGM001CL is given in Figure 3.

This command only needs to be executed once to define the procedure for SQL. If for some reason the procedure definition changes, then the procedure must be dropped (using the DROP PROCEDURE statement) and recreated. Program logic changes do not require the procedure definition to be dropped and recreated. Only things such as a change in the number of parameters, parameter types, etc. will require procedure recreation.

Once defined, the stored procedure is ready to be accessed by the ADO client (Figure 4). This code creates an ADO command to define the stored procedure and an ADO parameter to view the results of the procedure. The command's text property is surrounded by brackets to signify that it is a stored procedure. Remember, the question marks indicate that four parameters are required to call the program.

The command type should be set to adCmdText. Although it seems counterintuitive, do not assign the adCmdStoredProc constant to the command type. For some reason, IBM decided not to implement this command type when it designed the AS/400's OLE DB provider. The .Refresh method is executed against the command's parameters collection, which will automatically generate the parameter definitions based on the CREATE PROCEDURE definition.

The .Execute method of the command performs the call to the procedure. When the command is completed, the parameters collection is examined with the results being displayed in the debug window. (In most Microsoft environments, the debug or "immediate" window can be activated by using Control+G.) The job information and current user name should appear.

You may be wondering why the SQL examples contain a period instead of a slash when referring to a system/object combination. The answer is that the AS/400's SQL engine can accept SQL statements according to two naming conventions. The system (*SYS) convention follows the standard AS/400 naming convention of separating the library and object name with a slash (DATALIB/MYTABLE). The SQL (*SQL) naming convention uses a period as the separator (DATALIB.MYTABLE). All of the examples given here use the *SQL convention.

Be aware of which naming convention your favorite SQL environments use. For example, if you're planning to use the STRSQL command to issue the CREATE PROCEDURE statement, be aware that (by default) STRSQL uses the *SYS naming convention. Therefore, you need to change the naming convention parameter to NAMING(*SQL) when issuing the STRSQL command (as shown above). Or, you must substitute a slash in place of a period for the library/object separator.

Figure 5 lists some popular SQL environments and their corresponding default naming conventions. In general, native AS/400 SQL commands make use of *SYS, and PC milieus make use of *SQL.

 

Executing Remote Commands

 

Executing remote commands are even easier. Just slap the command name between two sets of brackets. For example, the next code block (Figure 6) demonstrates how to execute commands to help debug an ADO job.

 

Error Retrieval

 


As a final note on ADO programming, sometimes figuring out why something won't work is a nightmare because the PC application doesn't have direct communication with the back- end database server. For instance, when developing in Microsoft Access, I often get a generic Access error message when I encounter a server error. Needless to say, Access's message doesn't provide any help.

Fortunately, the ADO connection object has an errors collection that will return error messages from the host database server. The ADO connection object contains a pointer to all error messages. Therefore, if an ADO statement fails, just check the connection object's error collection for more information on the problem. Figure 7 shows some easy-to-input code that you can use for error checking.

As an AS/400-specific side note, I've noticed that the first two errors in the collection are usually the same error. For example, if a call to a stored procedure fails, the
.Count property of the errors collection may indicate that there are three error messages. The first two messages in the collection will be generic, duplicate messages indicating that there was a problem with the SQL statement. The last error in the collection is usually the one that describes the problem.

 

Put It into Action

 

Now that you have samples to work with, think of what ADO can be used to accomplish in your PC programs. ADO adds a rich functionality to any 400 programmer's toolbox. With the ability to query data, execute remote commands, call stored procedures and analyze errors, ADO enables the development of great Client/Server applications, but that's not all. As the popularity of ADO and VBA continues to soar, you will have a skillset that will not only prove useful in the AS/400 world, but in other ADO-capable environments as well

 

Related Materials

 

"AS/400 Client/Server Programming with ADO, Microsoft Excel 2000, and OLE DB," Michael Sansoterra, AS/400 Network Expert, May/June 1999

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

"Debugging Client/Server SQL Applications," Howard F. Arner Jr., Midrange Computing, July 1999

"Slam-dunking AS/400 Data into Microsoft Excel with OLE DB," Brant Klepel, Midrange Computing, May 1999

"Making PC Application-to-AS/400 Programming a Snap with IBM's SDK for ActiveX and OLE DB," Brant Klepel, AS/400 Network Expert, January/February 1999

"Turbocharging ODBC for Client/Server Performance," Howard F. Arner, Jr., Midrange Computing, December 1998

"Programming in Visual Basic Using the IBM AS/400 SDK for ActiveX and OLE DB," Chris Peters, Midrange Computing, October 1998 (Also available on the Web at www.midrangecomputing.com/mc/98/10)

'

' Define global variables
'

Public gADOConn As ADODB.Connection 'Global 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 AS/400 OLE DB provider and system name (Ex. S1024200)

'

gadoConn.Open "Provider=IBMDA400;Data Source=S1024200;" _

, "USER","PASSWORD" 'Connect

If Err Then

MsgBox "Could not connect to ADO data source"

Set gadoConn = Nothing

SetADOConn = True

End If
End If

End Function

Figure 1: This is the code to establish a connection with the AS/400.

PGM PARM(&JOBNAME &USER &NUMBER &CURUSER)
/* PGM001CL */

DCL &JOBNAME *CHAR 10 /* JOB NAME */
DCL &USER *CHAR 10 /* USER */
DCL &NUMBER *CHAR 6 /* JOB NUMBER */
DCL &CURUSER *CHAR 10 /* NAME OF ACTUAL USER */

/* RETURN CURRENT JOB INFO TO CLIENT SERVER PROGRAM */

RTVJOBA JOB(&JOBNAME) USER(&USER) +

NBR(&NUMBER) CURUSER(&CURUSER)

ENDPGM

Figure 2: This stored procedure written in CL is designed to retrieve host job information.

CREATE PROCEDURE QGPL.$GET_JOB_INFO
(JOBNAME OUT CHAR(10), USER OUT CHAR(10),

JOBNO OUT CHAR(6), CURUSER OUT CHAR(10))
(EXTERNAL NAME QGPL.PGM001CL LANGUAGE CL SIMPLE CALL)

Figure 3: The CREATE PROCEDURE command defines program information for SQL.

Dim ADOCmd As ADODB.Command
Dim ADOPrm As ADODB.Parameter

Set ADOCmd = New ADODB.Command

With ADOCmd

.ActiveConnection = gADOConn

.CommandText = "{ CALL QGPL.$GET_JOB_INFO (?,?,?,?) }"

.CommandType = adCmdText 'Don't use adCmdStoredProc

.Parameters.Refresh 'Create Parameter Defs

.Execute 'Call Stored Procedure

For Each ADOPrm In .Parameters 'Display Parms

Debug.Print ADOPrm.Value

Next
End With

Figure 4: This code calls the stored procedure and displays the results.


SQL Environment Default Naming Convention

STRSQL *SYS STRQMQRY *SYS RUNSQLSTM *SYS ODBC *SQL ADO *SQL

Figure 5: Here's a list of some popular SQL environments and their default naming conventions.

Dim ADOCmd As ADODB.Command

With adoCMD

.ActiveConnection = gadoConn

.CommandText = "{{CHGJOB LOG(4 00 *SECLVL) LOGCLPGM(*YES) }}"

.Execute adCmdText

. . . Additional ADO Code Goes Here

if err_flag then

.CommandText = "{{OVRPRTF QPJOBLOG OUTQ(PRT01) OVRSCOPE(*JOB) }}"

.Execute adCmdText

.CommandText = "{{DSPJOBLOG OUTPUT(*PRINT) }}"

.Execute adCmdText

end if
End With

Figure 6: The command data type provides an easy way to execute AS/400 commands

On Error Resume Next

Dim ADOErr As ADODB.Error
'

' Insert ADO Code Here
'

If gADOConn.Errors.Count>0 Then

For Each ADOErr In gadoConn.Errors

MsgBox ADOErr.Description

Next
End If

Figure 7: When an error occurs, check the ADO error collection to retrieve messages from the server.


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: