Do you understand lightning? I don’t mean the kind in thunderstorms, but the IBM version that started life as Project Lightning and is now known as two pieces: the IBM AS/400 SDK for ActiveX and OLE DB. Where does this new technology fit into your organization? In this article, I’ll answer many common questions about the SDK and help you get started with it. I’ll discuss how to integrate the SDK into your application development, the components you’ll need for development and deployment, and how the OLE DB support compares to ODBC and IBM’s AS/400 Toolbox for Java. I’ll explore how to use the SDK, IBM’s AS/400 Client Access for Windows 95/NT (V3R2M0), and Microsoft Visual Basic (VB) 5.0 to create a Windows 95/NT client application front-end to retrieve and update data on an AS/400 database back-end. After reading this article, you should be comfortable enough to start coding your own Windows 95/NT OLE DB-to- AS/400 applications.
Why Does Lightning Exist?
IBM designed the SDK to make PC application development in environments as varied as VB, Inprise Delphi, and Sybase PowerBuilder as easy as possible. The SDK can also be used to code in many user productivity tools such as Lotus Notes, Lotus SmartSuite, and Microsoft Office. IBM’s OLE DB support function parallels that of IBM’s AS/400 Toolbox for Java. Where the AS/400 Toolbox for Java provides Java application integration with AS/400 services, the SDK provides the same application integration for the Windows 95/NT developer. The SDK, along with the Client Access OLE DB provider, creates an easy-to-use interface between Windows 95/NT tools and the AS/400.
What Is Lightning’s Microsoft Technology Made Of?
IBM’s OLE DB support is built upon Microsoft OLE DB and ActiveX technology for enterprise data access. Two pieces comprise this technology: OLE DB and ActiveX Data Objects (ADO).
OLE DB expands the data access paradigm and can easily be compared to ODBC. ODBC provides standardized access to SQL database data or an SQL database management system (DBMS). OLE DB removes the SQL-only restriction and provides common access
to data that can be represented in a tabular form. While tabular form data includes SQL, OLE DB can also access record-level database, spreadsheet, email, word processing, and multimedia data. ODBC doesn’t work with AS/400 data queues but OLE DB does. Using OLE DB, a developer who learns the OLE object model and concepts can use those techniques to access a wide variety of data types without learning a new interface. Microsoft is rewriting all of its products to use this technology. VB 6.0, for example, has its own OLE DB wizards, OLE DB data bound controls, and an ActiveX builder. Since many IBM customers use Microsoft tools under Windows 95/NT, IBM created an OLE DB middleware provider (or map) that implements the OLE DB specification for low-level DB2/400 access and other AS/400 services.
ADO is a high-level interface that simplifies PC programming. ADO concepts are very easy to understand, and you can quickly write working application with very few lines of code. ADO takes the high-level programming calls and converts them into OLE DB provider calls. It is a programming interface that sits on top of OLE DB, enhancing OLE DB without degrading its performance. ADO is not bulky, and Microsoft has streamlined it for Web application development.
There are three basic ADO objects: connections, commands, and recordsets. The connection object establishes a connection to your system. A connection object also retrieves all host and client errors as well as status information. The command object executes commands. Using the SDK, you can code command objects to open AS/400 database files and data queues, execute SQL statements and stored procedures, and call AS/400 programs. Command objects can also contain input and output parameters. Recordset objects hold the tabular results of an ADO command (e.g., an SQL result set, database file records, or data queue entries). ADO technology allows you to learn these simple objects and methods and to use them inside almost any Windows 95/NT tool for AS/400 data access.
What about Lightning’s Client Access Components?
Just as there are two pieces to the under-lying Microsoft technology, there are two pieces to IBM’s OLE DB and ADO support: the Client Access OLE DB runtime provider and the IBM AS/400 SDK for ActiveX and OLE DB itself. Keep in mind that these are two separate components with two different functions.
The Client Access OLE DB runtime provider comes with Client Access V3R1M3 and above. It is installed as part of the base support for Client Access, which means you do not need a XW1 license to use it. Just as IBM provides the Client Access ODBC driver, IBM provides the Client Access OLE DB Provider. The OLE DB provider supports PC-to- AS/400 record-level access database, SQL statements and stored procedures, data queues, programs, and CL commands.
The second component is the SDK itself. Just because Client Access provides an OLE DB Provider doesn’t mean developers will automatically know how to use it. IBM created the SDK to provide sample code, documentation, and even code-generating wizards that allow you to easily use the Client Access OLE DB Provider for AS/400 access. The SDK includes sample projects using Delphi, PowerBuilder, VB, Visual C++, Internet Explorer, and Lotus 1-2-3.
The difference between the two components is that the Client Access OLE DB provider is the engine that provides AS/400 access from PC applications, while the SDK is a set of development environment samples and plug-ins that helps you create OLE DB provider code in your PC applications.
Visual Basic Wizards Included with the SDK
When you install the SDK, a set of wizards is included as a plug-in for the VB Add-Ins menu. As you run each of the wizards for AS/400 tables, data queues, stored procedures, commands, and programs, it generates ADO code into your current project. In turn, this ADO code is set up to call the Client Access OLE DB Provider. By using these wizards, you can create a customized client/server application while knowing very little
about the AS/400, Client Access, OLE DB, ADO, or even VB for that matter. You can quickly develop a working application that also performs very well. The only drawback is that the wizards are only available in VB. For other tools, you need to rely on the sample projects and the SDK documentation.
Getting Client Access
How do you get a copy of the latest V3R2M0 Client Access and the OLE DB provider runtime? First, if you order or upgrade to OS/400 V4R3, you receive the Operations Navigator CD-ROM that can be used for OLE DB provider installation. This CD-ROM comes bundled with OS/400 at no extra charge. Second, if you order the Client Access XW1 product, you also receive the Client Access CD-ROM that can be used to install the OLE DB provider. There is a charge for this CD-ROM because it gives you a license to use Client Access features such as SNA communications, PC5250, and data transfer functions. The OLE DB provider is part of the base support on both CDs and does not require an XW1 license to use. The OLE DB provider is a fully supported piece of Client Access, and you should use your normal support line channels for technical support. If you are a V3R1M3 user, you already have the OLE DB provider on your machine, but you should install the most recent service pack in order for the latest SDK to work with it.
Getting the SDK
How do you get a copy of the latest SDK and VB wizards? First, you can continue to download the latest version of the SDK and the latest fixes at http://www. as400.ibm.com/clientaccess/oledb.
Second, the Client Access V3R2M0 XW1 CD-ROM contains the SDK install setup program in the Odbsdk folder. The Operations Navigator CD-ROM does not have it. Since the SDK is a set of “as is” tools and samples for helping the application developer, there is no formal support through the support line. The only way to report problems, ask questions, and provide feedback on the SDK is by sending an email to the development team at
Building a Record-Level Access Application
The AS/400 SDK menu item, shown in Figure 1, is automatically added to your VB 4.0 or 5.0 Integrated Development Environment when you install the SDK. You use the Link menu items to establish connections, open tables, access data queues, execute SQL statements, and run AS/400 commands and programs. The Create Form from Links option is used to create a user interface form for displaying or manipulating the data.
Specifying AS/400 Libraries in VB 4.0 through 5.0
[Editor’s Note: As we go to press with this article, IBM has discovered several important issues with running the OLE DB provider and the SDK with Microsoft VB 6.0. Currently, the SDK and the VB wizards are not compatible with VB 6.0 and you should use VB 5.0 for your wizards support. For current information on these incompatibilities and IBM’s advice and patches to achieve compatibility, please refer to IBM’s Client Access OLE DB support Web page at http://www.as400. ibm.com/clientaccess/oledb. The known issues, as of this writing, are presented in a sidebar to this article, “Trouble Installing or Using the SDK for ActiveX and OLE DB?” by Joe Hertvik on the AS/400 Network Expert Web page at http://www. midrangecomputing.com/ane/99/01.]
To create an application, you must first establish the AS/400 libraries your application will be working with. A list of your Client Access-configured systems is displayed when you select the Options menu item. This displays the IBM AS/400 OLE DB Provider Options panel. Select the OS/400 system you want to work with and click on the Properties button. This brings you to the Properties for System window shown in Figure
2. Updating the list of libraries that you work with for a particular system is simple. You can retrieve the system library list and add libraries to your current list or you can manually type in a library name in the Library to add box. Adding libraries to the current library list is necessary to have the library appear when you are using any of the Link menu items. This current library list has nothing to do with the libraries used at runtime. It is only used when
you are building a VB-to-AS/400 application. Click on OK from the Properties for System panel to save your current library list for that AS/400 connection.
Project Initialization
After building your library list, select Link Tables from the AS/400 SDK menu and you can begin to generate VB code to connect to your selected AS/400 system and open a database file. If this is the first time that the wizard has been run in this project, the wizard will add a new class file to your project called DA400Links.cls. This class file holds the wizard-generated code and common routines for accessing your AS/400. You will receive a pop-up message asking “Would you like to add this class file to your project?” Click Yes. The wizard will then ask you to specify the Windows directory on your hard drive to save the DA400Links.cls to. Save it to the default directory and continue.
Along with the execution code the wizard is generating, it will generate code into your current project form. (This form may be named Form1.frm if this is a new project.) The wizard will ask if you want to save your VB form at this time. Select Yes. This project initialization will only happen once for each VB project.
Linking to a Table
After saving your class file and form, the wizard will prompt you for the AS/400 database name (which it refers to as a table) you would like to open by using the Links to Tables screen shown in Figure 3. You can type the library and table name directly into the field or you can click on the Add button to browse your system for the table name by using the Add a Table Link window in Figure 4.
The Add a Table Link screen shows the list of AS/400 systems that you have configured to Client Access. Expanding a system name displays the list of libraries that you specified earlier using the Options panel. Expanding a library name displays all the physical and logical database files residing in that library. If you want to modify your table data inside VB, select the Additional Table Operations method desired by clicking on the Insert, Update, or Delete check boxes listed on the screen. If no Additional Table Operations are selected, the AS/400 file will be opened as read-only. Other options are available on this screen to specify whether you want to use commitment control and what type of control should be used. If you leave the Select Commitment Control check box blank, it will not enable any commitment control for this application. Click Finish when you are done.
Your link has now been added to the Links to Tables screen, as shown in Figure 5. More links can be added as necessary. When you are done, click OK to generate ActiveX code for this connection and table.
Link Tables Code
Figure 6 displays the sample code that was generated into the General Declarations and the Connect and OpenLinks procedures of the DA400Links.cls. You can see that the wizard has created ADO connection, command, and recordset objects according to the ADO specifications. The connect code opens the connection to the AS/400, and the OpenLinks code opens the file on the AS/400.
Creating a Form
Now that you have an open file and access to the file records, how can you display and update your AS/400 data on a form? Select the Create Form from Links option on the AS/400 SDK menu and the wizard will help you create a new form through the Create Form from Links panel, shown in Figure 7. This panel displays all the current AS/400 table links from which forms can be created. By clicking on the Control Type drop-down box, you can specify how the data should be shown in standard VB controls such as Grid, List, List Box, Combo Box, or Form. Select Form from the table and click Next. Each time you run the Create Form from Links wizard, a new form file will be created and added to your current project.
On the next form (Figure 8), the wizard has gone to the AS/400 and retrieved the file field description and listed all of the fields on the form, one per line, along with name, data type, and field description. For each field in the file, you can select whether it shows
up on the new form as a Text Box or Label by clicking on its Control Type drop-down box. You can also specify that a field should not be displayed by selecting None. When finished, click Next. The last wizard form allows you to specify the title for the new VB form that will be created. Enter a title on this screen and click Finish to generate the form using these parameters.
You should now see that a new form (similar to Figure 9) has been added to your project. You will always get the navigational buttons on created forms. The Add, Update, Delete, and Clear buttons are dependent on which options you selected when you specified the Link Table. If the file is an AS/400 keyed file, a Find button will also be generated so that you can search and position to a specific record by key value. When the wizard creates a form, it also generates VB code into the new form file—Form2.frm in this case. Due to space considerations, I cannot show the wizard-generated code for this form, but you can view it as you begin to create your VB-to-AS/400 ADO applications.
Completing the Application and Deployment
The last task is to add a command button to your Form1 from the VB toolbar, and add one line of code (Form2.Show) behind the button to show the new form when the command button is pressed. Then run your application and see how it works.
Once your VB project programming is complete, there are a few distribution issues to deal with. To create an installation setup, follow the VB online help instructions for information on how to create an executable program and a setup file containing the VB runtime objects you need. Every PC that runs your new application will also need to have Client Access V3R1M3 or V3R2M0 with the OLE DB provider installed. See the Client Access license agreement for details on any additional licensing considerations. Finally, the AS/400 system name that your application is connecting to will need to be configured on each PC in the AS/400 Connections screen.
Need More Information?
There are many excellent ADO and SDK informational resources available. To learn more about Microsoft’s Universal Data Access strategy, OLE DB, and ADO, see Microsoft’s Web page at http://www. microsoft.com/data. The SDK also installs an online Technical Reference manual and the Microsoft ADO help file on your PC. Demo scripts are included as part of the VB wizard’s online help. Just click on the Help menu item from within VB Add-Ins and the AS/400 SDK.
The IBM International Technical
Support Organization (ITSO) has written and released a new Redbook, A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support, SG24-5183-00. See the ITSO Web page at http://www.redbooks. ibm.com and enter OLE DB into the search field to find this manual online. The Client Access OLE DB development Web page at http://www.as400.ibm.com/clientaccess/oledb contains links to technical support information and companies that are offering technical education sessions.
IBM is also looking for project references to publish on or link to from its Web pages. If you have already written a project using the IBM AS/400 OLE DB support and don’t mind sharing some basic information about your experience, IBM would like to hear from you. You can email IBM’s development team at
Figure 1: The AS/400 SDK menu item is automatically added to your VB 4.0 or 5.0 Integrated Development Environment when you install the SDK.
Figure 2: Add AS/400 libraries to your current library working list by using the Properties for System window.
Figure 3: Define which AS/400 tables you want to open in your VB application by using the Links to Tables window.
Figure 4: The Add a Table Link window lets you browse your AS/400 libraries for VB access.
Figure 5: More links can be added as necessary to the Links to Tables screen.
General Declarations Code
Public cnSystem03 As New ADODB.Connection
Public cm_QGPL_TEST As New ADODB.Command
Public rs_QGPL_TEST As ADODB.Recordset
Connect Code
Public Sub Connect()
cnSystem03.Open “Provider=IBMDA400;Data Source=System03;”, “”, “”
End Sub
OpenLinks Code
Public Sub OpenLinks()
Dim Rcds As Variant
Dim Parms As Variant
Const DBPROPVAL_UP_CHANGE = 1
Const DBPROPVAL_UP_DELETE = 2
Const DBPROPVAL_UP_INSERT = 4
Set cm_QGPL_TEST.ActiveConnection = cnSystem03
cm_QGPL_TEST.Properties(“Updatability”) = DBPROPVAL_UP_INSERT + DBPROPVAL_UP_DELETE + DBPROPVAL_UP_CHANGE
cm_QGPL_TEST.CommandText = “/QSYS.LIB/QGPL.LIB/TEST.FILE(*FIRST, *NONE)”
cm_QGPL_TEST.Parameters.Append cm_QGPL_TEST.CreateParameter(“P1”, adChar, adParamInput, 1)
Set rs_QGPL_TEST = cm_QGPL_TEST.Execute(Rcds, Parms, adCmdTable)
End Sub
Figure 6: This is the sample ActiveX connectivity code that was generated by the VB wizard.
Figure 7: You can use the Create Form from Links option on the AS/400 SDK menu to display AS/400 data in a VB form.
Figure 8: The VB wizard will retrieve your AS/400 data fields and prompt you for how they should be displayed in your VB form.
Figure 9: This is the VB form that the wizard creates.
More 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.
TRY 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.
Forms 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.
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:
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.
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:
LATEST COMMENTS
MC Press Online