| Note: | The DB2 SDK for Windows 32-bit operating systems does not supply a precompiler for Microsoft Visual Basic. |
This section covers the following topics:
ActiveX Data Objects (ADO) allow you to write an application to access and manipulate data in a database server through an OLE DB provider. The primary benefits of ADO are high speed, ease of use, low memory overhead, and a small disk footprint.
To use ADO with Microsoft Visual Basic, you need to establish a reference to the ADO type library. Do the following:
where <version_number> is the current version the ADO library.
Once this is done, ADO objects, methods, and properties will be accessible through the VBA Object Browser and the IDE Editor.
A full Visual Basic program includes forms and other graphical elements, and you need to view it inside the Visual Basic environment. Here are Visual Basic commands as part of a program to access the DB2 sample database, cataloged in ODBC:
Establish a connection:
Dim db As Connection Set db = New Connection
Set client-side cursors supplied by the local cursor library:
db.CursorLocation = adUseClient
Set the provider so ADO will use the Microsoft ODBC Driver, and open database "sample" with no user id/password; that is, use the current user:
db.Open "SAMPLE"
Create a record set:
Set adoPrimaryRS = New Recordset
Use a select statement to fill the record set:
adoPrimaryRS.Open "select EMPNO,LASTNAME,FIRSTNME,MIDINIT,EDLEVEL,JOB from EMPLOYEE Order by EMPNO", db
From this point, the programmer can use the ADO methods to access the data such as moving to the next record set:
adoPrimaryRS.MoveNext
Deleting the current record in the record set:
adoPrimaryRS.Delete
As well, the programmer can do the following to access an individual field:
Dim Text1 as String Text1 = adoPrimaryRS!LASTNAME
DB2 provides Visual Basic ADO sample programs in the %DB2PATH%\samples\ADO\VB directory.
Remote Data Objects (RDO) provide an information model for accessing remote data sources through ODBC. RDO offers a set of objects that make it easy to connect to a database, execute queries and stored procedures, manipulate results, and commit changes to the server. It is specifically designed to access remote ODBC relational data sources, and makes it easier to use ODBC without complex application code, and is a primary means of accessing a relational database that is exposed with an ODBC driver. RDO implements a thin code layer over the Open Database Connectivity (ODBC) API and driver manager that establishes connections, creates result sets and cursors, and executes complex procedures using minimal workstation resources.
To use RDO with Microsoft Visual Basic, you need to establish a reference to your Visual Basic project. Do the following:
where <version_number> is the current RDO version.
A full Visual Basic program includes forms and other graphical elements, and you need to view it inside the Visual Basic environment. Here are Visual Basic commands as part of a DB2 program that connects to the sample database, opens a record set that selects all the columns from the EMPLOYEE table, and then displays the employee names to a message window, one by one:
Dim rdoEn As rdoEngine Dim rdoEv As rdoEnvironment Dim rdoCn As rdoConnection Dim Cnct$ Dim rdoRS As rdoResultset Dim SQLQueryDB As String
Assign the connection string:
Cnct$ = "DSN=SAMPLE;UID=;PWD=;"
Set the RDO environment:
Set rdoEn = rdoEngine Set rdoEv = rdoEn.rdoEnvironments(0)
Connect to the database:
Set rdoCn = rdoEv.OpenConnection("", , , Cnct$)
Assign the SELECT statement for the record set:
SQLQueryDB = "SELECT * FROM EMPLOYEE"
Open the record set and execute the query:
Set rdoRS = rdoCn.OpenResultset(SQLQueryDB)
While not at the end of the record set, display Message Box with LastName, Firstname from table, one employee at a time:
While Not rdoRS.EOF MsgBox rdoRS!LASTNAME & ", " & rdoRS!FIRSTNME
Move to the next row in the record set:
rdoRS.MoveNext Wend
Close the program:
rdoRS.Close rdoCn.Close rdoEv.Close
DB2 provides Visual Basic RDO sample programs in the %DB2PATH%\samples\RDO directory.
This section describes Object Linking and Embedding (OLE) automation UDFs in Microsoft Visual Basic as well as accessing a sample OLE automation controller for stored procedures.
You can implement OLE automation UDFs and stored procedures in any language, as OLE is language independent, by exposing methods of OLE automation servers, and registering the methods as UDFs with DB2. Application development environments which support the development of OLE automation servers include certain versions of the following: Microsoft Visual Basic, Microsoft Visual C++, Microsoft Visual J++, Microsoft FoxPro, Borland Delphi, Powersoft PowerBuilder, and Micro Focus COBOL. Also, Java beans objects that are wrapped properly for OLE, for example with Microsoft Visual J++, can be accessed via OLE automation.
You need to refer to the documentation of the appropriate application development environment for further information on developing OLE automation servers. For more detailed information on DB2 programming using OLE automation, see the Application Development Guide.
Microsoft Visual Basic supports the creation of OLE automation servers. A new kind of object is created in Visual Basic by adding a class module to the Visual Basic project. Methods are created by adding public sub-procedures to the class module. These public procedures can be registered to DB2 as OLE automation UDFs. Refer to the Microsoft Visual Basic manual, Creating OLE Servers, Microsoft Corporation, 1995, and to the OLE samples provided by Microsoft Visual Basic, for further documentation on creating and building OLE servers.
DB2 provides self-containing samples of OLE automation UDFs in Microsoft Visual Basic, located in the directory %DB2PATH%\samples\ole\msvb. For information on building and running the OLE automation UDF samples, please see the readme.txt file in %DB2PATH%\samples\ole.
Directory %DB2PATH%\samples\ole\stpcntr contains a sample OLE automation controller implemented in Microsoft Visual C++ as a stored procedure. The automation controller can be used to invoke stored procedures through OLE automation. The first SQLVAR in the SQLDA provides the OLE programmable identifier, progID, and the name of the method which should be invoked. OLE automation stored procedures must be implemented as in-process OLE automation servers.
The directory %DB2PATH%\samples\ole\msvb contains a Visual Basic project, salarysvr, with a "median" stored procedure which calculates the median salary in the STAFF table of the DB2 samples database. The stored procedure is implemented in Microsoft Visual Basic and DB2 CLI. The Visual Basic project, salaryclt, shows a DB2 client implemented in Visual Basic, which invokes the "median" stored procedure.
For information on setting up and running the automation controller and the projects using it, please see the readme.txt file in %DB2PATH%\samples\ole.