Microsoft Computing: Microsoft's OLE DB/ADO Technology

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

In spite of Microsoft's best efforts, more than half of the world's data is stored in non-Microsoft databases such as Oracle and IBM's DB2 (the iSeries' native database management system, or DBMS). This month's article provides an overview of Microsoft's follow-on technology to ODBC: UDA (Universal Data Access).

Microsoft Universal Data Access

Naturally, Microsoft wants its Windows-based applications to be able to access data kept in any popular DBMS; that's why it developed ODBC. ODBC technology has reached the end of its development life, however, because of a long list of shortcomings, among them a "nonstandard" programming model and an inability to interface fully with more complex database products. To rectify these inadequacies, Microsoft developed UDA.

UDA provides high-performance access to a variety of data formats, both relational and nonrelational, on multiple platforms. It gives you a simple programming interface that can be used with almost all of today's programming languages and tools. It's based on open industry specifications, so it doesn't require the use of one particular vendor's products or solutions, and it works with all major database platforms.

Developers can take advantage of UDA through Microsoft Data Access Components (MDAC), including ActiveX Data Objects (ADO), Remote Data Services (RDS), OLE DB, and ODBC.

OLE DB and ADO

Within UDA is the OLE DB data-access architecture. OLE DB allows Windows-based applications to work with non-Microsoft databases. It is much more capable than ODBC, allowing Windows applications to interface with complex database functions like field-level security and constraints.

The price for this increased functionality, however, is increased complexity for the programmer. Programming directly to the OLE DB API is not for the faint of heart. Again, though, Microsoft comes to the rescue. (Anything to get you to use its products...) To insulate the programmer or data-access user from the complexities inherent in OLE DB, Microsoft developed ADO as another layer of user interface structure built on top of OLE DB.

ADO

To conform to current Windows programming standards (Component Object Model, ActiveX programming, or .NET), Microsoft developed ADO to provide data-access capability within an easy-to-use object interface.

ADO is a set of program routines that acquire and process iSeries data. An ADO session is started by creating (loading) an ADO object (program). Once created, the ADO object is manipulated by a Windows program or MS Office macro, and the iSeries database, in turn, responds. The framework for this arrangement comes from Microsoft, while the specifications for building a conduit to iSeries data, which fits into Microsoft's framework, comes from IBM.

The Two Parts of OLE DB/ADO

Microsoft developed the outer framework within which OLE DB/ADO technology operates, but that is only part of the picture. Dovetailed into Microsoft's framework for OLE DB/ADO is the information that OLE DB/ADO needs to interface with a specific database. That information is supplied by the individual database developers. For example, into Microsoft's OLE DB/ADO piece must be fitted one of the "iSeries Data Access Providers"--the piece that tells OLE DB/ADO how to interface with data stored within the iSeries database. Both parts must be installed on each PC that is to access iSeries data through ADO services.

The iSeries Data Access Providers

To use OLE DB/ADO with iSeries data, you must do two things:

  1. Acquire and install the Microsoft framework for OLE DB/ADO support. This is contained within Microsoft Data Access Components (MDAC) and is normally installed with Windows 2000 or XP.
  2. Acquire and install the iSeries Data Access Provider(s) for OLE DB/ADO. Normally, this is also accomplished automatically when iSeries Access is installed on a PC.

Depending on your version of iSeries Access and i5/OS or OS/400, your options for OLE DB/ADO processing will vary. With V5R3 of i5/OS, there are three OLE DB/ADO data access providers:

  • IBMDA400--The standard OLE DB data access provider allows command execution or servicing of SQL statements. This provider is present on most installations of iSeries Access or Express Client.
  • IBMDASQL and IBMDARLA were created as SQL-only and record-level-access-only providers, respectively. IBM created these specific providers to enable certain functions that it didn't want to put into IBMDA400 for compatibility reasons. For instance, transactions/commitment control and support for MTS for SQL is available only through IBMDASQL, and forward-only record-level access cursors are available only through IBMDARLA. You can still get dynamic cursors with record-level access using IBMDA400.

OLE DB/ADO data access can be used with any Windows-based programming language, including C++, C#, Java, and Visual Basic. The basic approach is the same, regardless of the language used, and revolves around the ADO object model. Here are the steps to provide iSeries data access through OLE DB/ADO when using a Microsoft Office macro or Visual Basic 6:

  1. Set a reference to OLE DB/ADO data-access support (Microsoft OLE DB ActiveX Data Objects x.x Type Library).
  2. Define an object variable of type ADODB.CONNECTION.
  3. Define an object variable of type ADODB.RECORDSET.
  4. Create (instantiate) the connection and recordset objects.
  5. Execute the OPEN method of the connection object to establish a link to the iSeries. The name of the IBM iSeries Data Access Provider (IBMDA400, IBMDASQL, or IBMDARLA) and the iSeries' system name are passed as parameters to the CONNECTION.OPEN method.
  6. Execute an SQL statement or file data command to render a recordset.
  7. Use the data made available by the recordset in some manner.
  8. Close the recordset and connection objects.
  9. Disassociate (unload) the recordset and connection objects.

OLE DB/ADO Programming with Visual Basic for Applications

The simple Visual Basic for Applications code shown in Figure 1 uses OLE DB/ADO and SQL to access iSeries data. (Note that a list box named List1 is assumed to exist on form Form1.)

Option Explicit

Public objConnection As ADODB.Connection
Public objRecordSet As ADODB.Recordset

'SQL example - using the IBM OLE DB/ADO data access provider

'Important note:  You must set a reference to
' "Microsoft OLE DB ActiveX Data Objects x.x Library" in this VB program

'This example assumes that a list box named "List1" is present on Form1...


Private Sub Form_Load()

Dim varParms As Variant

  Set objConnection = New ADODB.Connection
  objConnection.Open _
"Provider=IBMDA400;Data Source=S10xxxxx;", "", "" 
  
  Set objRecordSet = objConnection.Execute("SELECT SRCSEQ, SRCDTA FROM
  QGPL.QDDSSRC", varParms, adCmdText)

  Do While Not objRecordSet.EOF

    List1.AddItem objRecordSet.Fields("SRCSEQ") & " " & _
       objRecordSet.Fields("SRCDTA")

    objRecordSet.MoveNext 
  Loop
    
  objRecordSet.Close
  objConnection.Close
  
  Set objRecordSet = Nothing
  Set objConnection = Nothing

End Sub

Figure 1: This simple Visual Basic example accesses iSeries data with OLE DB/ADO.
 

The example in Figure 1 creates the ADO connection and recordset objects and specifies the IBMDA400 data access provider and the name of the iSeries where the data resides. A loop is run to fill the list box from the records being read one at a time, and then the connection is closed and the objects destroyed.

Using OLE DB/ADO with an ODBC Data Source

To allow you to use ADO technology with existing ODBC data source definitions, Microsoft supplies a special OLE DB data-access provider named MSDASQL.

If you already have ODBC data sources configured on your client PCs, you may still use them with OLE DB technology and enjoy the benefits of the ADO interface. Instead of specifying IBMDA400 as the data access provider, substitute the name MSDASQL, together with the existing data source name.

  
  'Use an existing ODBC data source definition as accessed 
  '  through the special OLE DB data access provider "MSDASQL"...
  objConnection.Open "Provider=MSDASQL;DSN=QGPL;", "", ""            '(A)
  

Figure 2: The Microsoft data access provider for ODBC is substituted and a data source name is added.

In the line of code at letter A, note the special OLE DB/ADO data-access provider, MSDASQL. This provider comes from Microsoft and accepts the name of an existing ODBC data source name to be used (DSN=QGPL.) The data source must include a specification for the iSeries library name(s) that will be referenced in subsequent SQL statements.

OLE DB/ADO and .NET Access to the iSeries

With V5R3 of i5/OS, a new ADO.NET managed provider is part of iSeries Access for Windows. The new .NET provider is named IBM.Data.DB2.iSeries, and it allows applications using Microsoft's .NET framework to access DB2 UDB for iSeries databases.

The .NET data access provider developed by IBM, together with the iSeries Access Programmer's Toolkit, makes it possible to create a Windows .NET application that can interact with iSeries data within the confines of managed code. (Managed code is a Microsoft .NET technology that is very similar to Java's virtual machine scheme with automatic garbage collection.)

ADO.NET is a standardized collection of classes and methods that provides a consistent interface to a variety of databases. Figure 3 from Microsoft is a short example of an ADO.NET application written in VB.NET. The application accesses a local example database that comes with MS Office (NorthWind on localhost).

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class Sample

  Public Shared Sub Main()
 
    //  Create connection object...
    Dim nwindConn As SqlConnection = _
       New SqlConnection("Data Source=localhost;" & _
       "Integrated Security=SSPI;Initial Catalog=northwind")

    //  Initialize a command string object...
    Dim catCMD As SqlCommand = nwindConn.CreateCommand()
    catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"

    //  Open the database...
    nwindConn.Open()

    //  Create the record reader from the command execution...
    Dim myReader As SqlDataReader = catCMD.ExecuteReader()

    //  Read through the records...
    Do While myReader.Read()
      Console.WriteLine(vbTab & "{0}" & vbTab & "{1}",_
         myReader.GetInt32(0), myReader.GetString(1))
    Loop

    //  Release the data resources...
    myReader.Close()
    nwindConn.Close()

  End Sub

End Class

Figure 3: This ADO.NET example was written in VB.NET for the NorthWind sample Access database.

For more information regarding the ADO.NET architecture see Microsoft MSDN and supply "ADO.NET" in the MSDN search.

IBM has made it clear that the future of the Microsoft-iSeries data relationship lies in ADO data access technology. IBM's development of C/C++ "Optimized SQL APIs" (also known as the DB APIs) has come to a halt, and IBM has made it clear that support for the DB APIs will come to an end as well, leaving ADO and ODBC to carry the service. At present, however, native ADO.NET is not supported within Microsoft Office's macro language (VBA), leaving that transition to a future release. ADO (and ODBC) are proven technologies that will be with us for a while, so an understanding of what they have to offer is merited.

Chris Peters has 26 years of experience in the IBM midrange and PC platforms. Chris is president of Evergreen Interactive Systems, a software development firm and creators of the iSeries Report Downloader. Chris is the author of The OS/400 and Microsoft Office 2000 Integration Handbook, The AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400 (MC Press). He is also a nationally recognized seminar instructor. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

BLOG COMMENTS POWERED BY DISQUS