OLE DB TechNote

OLE DB is a new option for database connections. It is available under the database options of the MicroStation V8 installer in the 08.00.05.xx version or higher. OLE DB provides open data access and uses a standard set of COM interfaces for accessing and manipulating many different types of data.

These interfaces are commonly referred to as Data Providers. The choices you have in the Data Link Properties dialog, Microsoft Jet 4.0 OLE DB Provider or Oracle Provider for OLE DB, are the Data Providers that you have installed on your computer. You must contact your database vendor to determine if one is available for your database. Software like MicroStation that accesses data through OLE DB is called a Data Consumer. OLE DB interfaces can be developed to access very simple types of data as well as true relational databases. Therefore, OLE DB interfaces can access data that cannot be accessed using the current ODBC technology.

Before you can access data using OLE DB, you must set up a UDL, or MicroSoft Data Link, file. This is similar to setting up an ODBC data source in that you must provide some information about the data you wish to access. You must know the following:

1. What data do you wish to access?
2. Where is the data located (server name)?
3. What is the database name?

A new database configuration file (..\Bentley\Program\MicroStation\config\database\oledb.cfg) has been added. In addition, a new database ID of 22528 has been added for database linkages created with an OLE DB connection. The MS_LINKTYPE value for OLE DB has been added to the oledb.cfg file.

#------ Database Linkages ------#
MS_LINKTYPE = OLEDB

To create a UDL file

1. In Windows Explorer, navigate to the directory in which you want to store the UDL files. Right click in the right-hand pane of Windows Explorer and select Microsoft Data Link from the New menu. (see Figure 1)

   Figure 1

   If the Microsoft Data Link option is not available on your operating system, select the Text Document option. Once the text document has been created, rename the extension to .udl. It will now be recognized as a Microsoft Data Link file.

2. You will now have created a file called New Microsoft Data Link.UDL (see Figure 2).

   Figure 2

   You may right-click on the file and select rename to give the file a more appropriate name.

3. Next double-click on the UDL file to open the Data Link Properties dialog.
   
4. Click the Provider tab, to see the list of OLE DB Providers found on your computer. If you do not see one for your database, contact the database vendor to see if one is available. For this example, we will set up a Microsoft Data Link to a Microsoft Access database by selecting the Microsoft Jet 4.0 OLE DB provider (see Figure 3).

   Figure 3

5. After the provider is selected, click the Next button to go to the Connection tab (see Figure 4).

   Figure 4

   You must enter the pertinent information for your database. This will differ based on the Provider selected. For this example, browse to the location of the delivered MicroStation Access database called GIS.mdb located in the ..\Bentley\Program\MicroStation\database\oledb\examples\access directory. You may also use the Test Connection button to verify the parameters that you have entered.

6. In the Advanced Tab, you may set other initialization parameters for the data you are accessing. You must consult the documentation for the OLE Provider. For this example, no further configuration is necessary.
   
7. Click OK to finish the MicroSoft Data Link setup.

For further information on creating a UDL connection, click here.

Connecting to an OLE DB data source

1. Start MicroStation V8 and go to the Settings > Database > Connect dialog. Use the pulldown menu to switch the Database Server to OLEDB (see Figure 5).

   Figure 5

2. If you have already created a Microsoft Data Link (UDL) file, click the Database Source Button and Click the Browse option. This will open the Select Data Link dialog (see Figure 6).

   Figure 6

3. Click on the UDL file you wish to use and click Open. You will be connected to the database. Future connections to the database can be initiated by clicking the down arrow on the Database Source and picking the UDL file from the history list (see Figure 7).

   Figure 7

Creating a UDL through MicroStation V8

1. It is not necessary to create a UDL file prior to starting MicroStation V8. To have MicroStation create this file for you, go to the Settings > Database > Connect dialog and select OLE DB as the Database Server and Database Source set to New (see Figure 8).

   Figure 8

2. Click the OK button and provide a file name for the UDL file (see Figure 9).

   Figure 9

   The UDL file will be created with this name and a UDL extension, in the directory defined by MS_UDLDIR. By default, this is set to ..\bentley\Program\MicroStation\database\oledb\udl.

3. The Data Link Properties Provider tab will be displayed. Continue with Step 4 from the "To create a UDL file" section above.

Editing a UDL file

1. If it becomes necessary to edit the UDL file due to a change in the database location or connection information, you must first navigate to the folder where the UDL file resides, using Windows Explorer.
2. Double-click on the UDL file and go to the Properties option in the list. You will be presented with the UDL properties dialog. Make the necessary changes to the file and click OK to save the file.

Note: Do not open the UDL file with a text editor to make changes. The UDL file may become corrupt.

Supported Data Providers

Following is a list of supported OLE DB providers:

• Microsoft Jet OLE DB Provider
• Oracle Provider for OLE DB
• Microsoft OLE DB Provider for SQL Server

While Microsoft also supplies an OLE DB provider for Oracle, we recommend the provider supplied by Oracle.

The following databases are supported with an OLE DB connection:

• Access 97
• Access 2000
• Oracle 8i
• Oracle 9i
• SQL Server 2000

New configuration variables for the OLE DB Server:

• MS_OLEDBMAXDATA: Limits the amount of data returned from database columns which have virtually unlimited space. These columns would be MEMO columns in Microsoft Access and LONG columns in Oracle. The default value of this variable is 1024.
• MS_UDLDIR: Points to the directory which contains the Universal Data Link files (.UDL). The .UDL files contain the connection strings to a particular data source. The default directory is ...\Bentley\program\microstation\database\oledb\udl
• MS_DBOLEDBDATEFORMAT: Format of date fields returned from the database. For example %B %d, %Y will return dates in the format of (September 14, 2002). Note that the comma "," was added and displayed as part of the format. Spaces also matter.

  %B - Full Month Name
  %d - Day of the month
  %Y - Year including the century
  

  See the Microsoft Run-Time Library Reference page for more information.

• MS_SESSION_DEBUG: Configuration variable used to start session debug prior to server.ma loading. This allows immediate responses from the consumer DLL. If this is set as a configuration variable, it will start the debug messages as soon as the database components load. If you set MS_SESSION_DEBUG=1 you will get debug information from the mdl code (server.ma) and the corresponding DLL (ORACLE, ODBC, OLEDB). If you set MS_SESSION_DEBUG=0 you only get output from the DLL.