This chapter contains the following sections:

When using the OLE DB provider, a common question is how to connect to the database. This section details the basics of connecting using the OLE DB provider.

Other common procedures are documented in Chapter 3, ADO/OLE DB Reference Information.

The provider connects to the database engine via a network protocol. The Server Engine processes the request, then sends the data back to the provider for processing and hand-off to the client. This is accomplished using the same protocol as the ODBC client.

You can specify the remote server using the Location= parameter in the connection string.

If you are currently using a path to the DDFs from your connection string, this method is still supported.

Unlike previous versions of PSQL, it is required that a DBName exist on the server to which you are connecting.

There is no performance difference between specifying a database name and a Location, or specifying a mapped drive. However, using a Location is the recommended way of connecting to a remote server. The OLE DB provider may have permissions issues when using a mapped drive or UNC path.

You use a pessimistic cursor when it is very important to your application that updates always succeed, even if this may increase the likelihood of other clients encountering record locks.

In past versions of the PSQL OLE DB provider, the ADO parameter that enables pessimistic cursors, adLockPessimistic, was not supported.

Now you can use this parameter, along with some PSQL connection string enhancements, to create applications with pessimistic cursors.

The OLE DB specification defines a pessimistic cursor as one that ensures that changes made to a single row from the most recent fetch will not fail because of a concurrency violation. After opening a recordset, some time may pass between that open and a deferred update. The use of pessimistic cursors is one way to mitigate concurrency problems that can arise because of this time gap.

The definition for pessimistic cursors in the OLE DB specification is intentionally ambiguous, as each provider may implement row-level locking differently. The definition alludes to two common implementations:

For example, consider the following sequence of events:

From a developer standpoint, the difference between the two is that algorithm (a) is using a read lock, and algorithm (b) is using an update lock. The following table summarizes the behavioral differences between the two algorithms:

A read lock gives the developer confidence that if the row was read, it can be updated without concern of a concurrency error. Unfortunately, it also locks the row. An update lock only locks a row if data is changed. However, it allows a larger time frame in which a concurrency error could occur.

For more information on the architecture of this change, see OLE DB Provider Architecture.

The PSQL implementation of pessimistic cursors allow for choice with regards to the behavior you want. The following shows the parameter related to pessimistic cursors in the connection string:

You use this connection string option in conjunction with specifying a pessimistic cursor with the ADO parameter adLockPessimistic during the Open method.

Using Pessimistic Read Lock=true in the connection string will perform a read lock, as shown in the following sample connection string:

Change the value for Pessimistic Read Lock to false if you want an update lock.

The following table summarizes the effects of using the pessimistic cursor options.

The following Visual Basic sample snippet opens the Course table of the DEMODATA sample database using a pessimistic cursor with read locks.

The following section lists notes about the OLE DB provider:

Updates made to a table with adCmdTableDirect will be visible to a Command-based recordset.

For example, consider the following sequence of events:

In this scenario, the updates made using the transactional method are visible to the SQL query.

This section describes a situation that occurs if you are using the OLE DB provider in conjunction with a mapped drive or UNC path in the Data Source portion of the connection string. For example:

In the previous version of the provider, this connection string would automatically resolve to the correct database name. Using the current provider, you may encounter permissions issues with this configuration. You will not have any permissions issues if you use the new Location parameter in your connection string. See Remote Connections for more information on the Location parameter.

If a client tries to connect to a server, and the client's data source is a UNC path, the provider must resolve the path to a database name and server name. In order for the OLE DB provider to obtain this information, the client's user account must have Administrative or Power User permissions on the server in order to resolve this path.

In many configurations, the client’s user account may not have these necessary permissions. If the client does not have sufficient permissions, the OLE DB provider displays a dialog similar to Figure 4, allowing the user to enter the database name and server name.

If you check Save above settings to registry, then the entries will be saved to the Windows registry and Figure 4 will no longer display for the referenced UNC or mapped drive location. Instead, the database name will be resolved using the registry, which offers a performance improvement. The registry location used is:

If you as a developer want to avoid the display of this dialog to your end-users, you can enter the necessary information in the Windows registry, in the following format under the preceding key:

For example:

You can have multiple UNC entries that point to the same database name if necessary.

The following shows examples of the ADO Refresh method using a parameterized query and a stored procedure.

To use seek on a static cursor, the index must be set before opening the result set. For example:

The OLE DB provider cannot create a remote connection. This means you cannot set a remote server within the connection string. However, there are alternatives for our provider to perform the same functionality.

ITableDefinition does not support creation of the following columns because they are not supported by the underlying PSQL engine.

If no LockType is specified, the cursor is set to immediate update mode. This has a couple of repercussions:

To programmatically determine when GetOriginalValue is available, you must use the "supports" method with adUpdateBatch as the argument. For example:

The following table lists the properties PSQL supports for initialization in OLE DB, and the corresponding connection string identifiers

You can also set the following properties:

This section describes how the OLE DB provider interacts with Microsoft’s COM+ Services.

COM+ services (Component Object Model) is a Microsoft specific technology used to create business objects in a multi-threaded context. COM+ is primarily designed for, but not limited to, Visual Basic programmers. COM+ allows for rapid creation of multi-tier applications and includes many benefits that would normally have to be implemented by the developer. The benefits of COM+ include

For further information about COM+, see Microsoft’s documentation.

COM+ services are an extension to the benefits provided by MTS (Microsoft Transaction Server). MTS is Microsoft’s previous business object server implementation. In general, references (in Microsoft and PSQL documentation) to MTS can be substituted with COM+ services.

The OLE DB provider is supported in COM+ services. ADO calls made within COM+ services behave like any ADO client call.

As a Visual Basic programmer, you must be aware of the MTSTransactionMode property. Setting this to anything other than NoTransactions will invoke Microsoft transactions. Please refer to COM+ services documentation for the complete reference of this feature.

The following example demonstrates use of Microsoft Transactions. In order for the calls to GetObjectContext to succeed, you must set the MTSTransactionMode property to something other than NoTransactions. Using Microsoft Transactions will allow Microsoft’s Transaction Coordinator to do a two-phase commit.

However, you could rewrite this business object using ADO transactions (with a connection object). This would allow you to set the MTSTransactionMode property to NoTransactions. Without Microsoft Transactions, you no longer have the overhead of the two-phase commit. Also, objects that do not support transactions are allowed to stay resident in memory, whereas those that do are constructed and destroyed on each reference.

When using the PSQL OLE DB Provider, the RecordsAffected parameter of the Execute Method on a command returns different results based on the type of operation.

When performing a SELECT statement, RecordsAffected returns a -1 (negative one), which means that this option is not supported. For example:

In this scenario RecordsAffected equals -1.

If you want to obtain the number of records returned by a SELECT query, use the RecordCount property, as shown in the following example:

RecordsAffected will return the correct number of records that the operation affected when performing a batch insert, update or delete.

In this scenario RecordsAffected equals 1.

In this scenario RecordsAffected equals x, which is all the records that have the value 13.