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.

Also Read

The API Corner: Moving Jobs Within and Across Job Queues

The API Corner: What Time Zone Do You Want That Date and Time In?

The API Corner: How Do You Want That Date and Time?