V5R4 Improvements to the i5/OS OLE DB Providers
May 3, 2006 Michael Sansoterra
The code for this article is available for download.
I’ve used the feature rich and reliable AS/400 ODBC driver for quite a while in my applications. When creating new applications, I would often switch to the alternative OLE DB providers to see if performance and features were comparable. However, I would always run into a limitation of some sort with the OLE DB provider (e.g., not being able to use the system naming convention) that would put me back to using ODBC. I’m happy to report as of V5R4 it looks like many of these limitations are gone.
In case you’re not familiar with ActiveX Data Objects and OLE DB, these terms refer to Microsoft technology designed to allow Windows applications to connect to other systems.
IBM offers the OS/400 community three OLE DB providers: IBMDA400, IBMDASQL, and IBMDARLA. As a review, IBMDA400 is a general purpose, all encompassing provider that supports SQL, command and data queue interfaces. IBMDARLA is the record level access provider and is primarily for applications that work with single records instead of sets of records. The IBMDASQL provider was released with iSeries Access V5R3 and is an SQL only provider.
Two long-awaited features have finally arrived in iSeries Access V5R4: the ability to specify the system naming convention and library list in the connection string.
To specify the library list, specify Naming Convention=x in the connection string. The values for x are 0 for the SQL naming convention (the default) and 1 for the system naming convention. The advantage of the system naming convention is that SQL uses the job library list to resolve unqualified object names and hence reduces hard coding of library (or schema) names.
To specify a job library list, specify a comma delimited list of libraries in the following format Library List=lib1,lib2;. (That is a period after the semicolon in that statement, not a part of the statement.) Note that the special value *USRLIBL is allowed to appear in the list, along with other hard coded library names. When *USRLIBL is specified, the libraries from the connecting user profile’s job description will be substituted in the list. Setting the job library list in the connection allows an easy way to allow traditional RPG and CL programs to execute correctly without making the programs themselves set it.
Here are a few more things to note:
A few other notable enhancements that have occurred since V5R3:
You can also read an IBM document that has a list of all of the OLE DB provider connection string enhancements since V5R1 along with some sample VBA code. Be sure that you spell the connection property names correctly. Unlike the ODBC driver that is gracious with many connection string errors, the OLE DB providers will choke. However, this does have the benefit of verifying that a connection string is accurate.
The downloadable code for this story contains a sample VBA subroutine that uses ActiveX Data Objects with the IBMDASQL provider to establish a connection. As opposed to IBM’s code example, I prefer to specify the new connection properties within the connection string itself because many third party applications that make use of OLE DB providers (such as SQL Server Linked Servers and DTS) only allow the user to override the connection string.
If you haven’t tried them, try out the maturing OLE DB providers. For applications that use ActiveX Data Objects, the OLE DB providers:
The next time I have a data integration project involving Windows COM objects, I will definitely give the OLE DB providers a try.
Correction: This story has been corrected since it originally ran. It originally said that for the new naming convention and library list connection string settings, the host must also be running i5/OS V5R4. This is not true. The host is not required to be running i5/OS V5R4. IT Jungle regrets the error. [Corrected 05/03/2006]
Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.