19
Fri, Apr
5 New Articles
  1. You are here:  
  2. Home
  3. Programming
  4. Programming - Other
  5. Microsoft
  6. Microsoft Computing: Microsoft's OLE DB/ADO Technology

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..

Chris Peters has 32 years of experience with 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 i5/OS and Microsoft Office Integration Handbook, AS/400 TCP/IP Handbook, AS/400 Client/Server Programming with Visual Basic, and Peer Networking on the AS/400. He is also a nationally recognized seminar instructor and a lecturer in the Computer Science department at Eastern Washington University. Chris can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it..

MC Press books written by Chris Peters available now on the MC Press Bookstore.

i5/OS and Microsoft Office Integration Handbook i5/OS and Microsoft Office Integration Handbook
Harness the power of Office while exploiting the i5/iSeries database.
List Price $79.95

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$0.00 Raised:
$

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: