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.
Connecting to OLE DB Provider
Connect String
"Provider=PervasiveOLEDB;Data Source=(DBName or Path to DataFiles)"
Sample connection and open
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'This should always be adUseServer unless you
'specifically want to use the Client Cursor Engine
The provider connects to the database engine via a network protocol. The server-side database engine processes the request, then sends the data back to the provider for processing and hand-off to the client, using the same protocol as the ODBC client.
New Syntax Supported
You can specify the remote server using the Location= parameter in the connection string.
Note The data source parameter refers to a database name (DBName), not a DSN. DSNs are only used in ODBC.
Backward Compatibility
If you are currently using a path to the DDFs from your connection string, this method is still supported.
Provider=PervasiveOLEDB;Data Source=f:\mydata
Unlike previous versions of Zen, 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 Zen OLE DB provider, the ADO parameter that enables pessimistic cursors, adLockPessimistic, was not supported.
Now you can use this parameter, along with some Zen connection string enhancements, to create applications with pessimistic cursors.
OLE DB Specification and 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 record set, 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:
•(a) lock the row on the read
•(b) lock the row at the start of the update.
For example, consider the following sequence of events:
1 Client A opens a record set
2 Client A reads row one (a)
3 Client A changes field 1 (b)
4 Client A changes field 2
5 Client A updates the row
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:
Table 1 Read vs. Update Locks
Lock Type
Behavior
Read Lock
Lock row on each read
Unlock the row on the first Move method
Lock the next row
Update Lock
Lock the row when data is changed
Unlock the row when the Update method is called
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.
The Zen 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:
•Read Locks or Update Locks (Pessimistic Read Lock=true/false)
You use this connection string option in conjunction with specifying a pessimistic cursor with the ADO parameter adLockPessimistic during the Open method.
Specifying Read Locks vs. Update Locks
Using Pessimistic Read Lock=true in the connection string will perform a read lock, as shown in the following sample connection string:
The following section lists notes about the OLE DB provider:
Visibility of Updates
Updates made to a table with adCmdTableDirect will be visible to a Command-based record set.
For example, consider the following sequence of events:
•A table is opened in the transactional (Btrieve) method using adCmdTableDirect
•Changes are made to the table and the Update method is called
•Next, a SQL query is executed that selects data that was just changed by the transactional table update.
In this scenario, the updates made using the transactional method are visible to the SQL query.
Using the OLE DB Provider with a UNC Path or Mapped Drive
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 3, allowing the user to enter the database name and server name.
Figure 3 Resolution Dialog for DBName and Server Name
If you check Save above settings to registry, then the entries will be saved to the Windows registry and Figure 3 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:
SOFTWARE\Actian\Zen\OLEDB\Connections
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:
Name
Value
UNC Path to data
Servername;DbName
For example:
Name
Value
\\myserver\c\pvsw\demodata
myserver;demodata
You can have multiple UNC entries that point to the same database name if necessary.
Support for ADO Refresh Method Examples
The following shows examples of the ADO Refresh method using a parameterized query and a stored procedure.
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.
•Use RDS, which has been tested with the Zen provider
•Develop a business object using COM+ services, and create the object using CreateObject. For example:
Dim m_busObj As projDLL.busObj
rs As New ADODB.Recordset
Set m_busObj = CreateObject("sampProj2.TwoPhaseSampleProduct", "RemoteServer")
Set rs = m_busObj.GetData()
Table Definitions
ITableDefinition does not support creation of the following columns because they are not supported by the underlying Zen engine.
•BSTR
•WCHAR
•VarWChar
•LongVarWChar
•UserDefined Types
•GUID
Default LockType
If no LockType is specified, the cursor is set to immediate update mode. This has a couple of repercussions:
•All changes are immediately transferred to the database (without the need to call update)
•Update and UpdateBatch really have no meaning (as the database is already updated). However, the Supports method will still return true of update, but will return false for UpdateBatch.
•GetOriginalValue is not usable.
To programmatically determine when GetOriginalValue is available, you must use the "supports" method with adUpdateBatch as the argument. For example:
if rs.Supports(adUpdateBatch) then
someValue = rs.fields(iCol).OriginalValue
end if
Initialization Properties
The following table lists the properties Zen supports for initialization in OLE DB, and the corresponding connection string identifiers
Table 3 Connection String Identifiers
Connection String Identifier
Property
Cache Authentication
DBPROP_AUTH_CACHE_AUTHINFO
Connect Timeout
DBPROP_INIT_TIMEOUT
Data Source
DBPROP_INIT_DATASOURCE
Encrypt Password
DBPROP_AUTH_ENCRYPT_PASSWORD
Locale Identifier
DBPROP_INIT_LCID
Location
DBPROP_INIT_LOCATION
Password
DBPROP_AUTH_PASSWORD
Persist Encrypted
DBPROP_AUTH_PERSIST_ENCRYPTED
Persist Security Info
DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO
User ID
DBPROP_AUTH_USERID
You can also set the following properties:
•DBPROP_INIT_HWND,
•DBPROP_INIT_PROMPT,
•DBPROP_INIT_ASYNCH,
•DBPROP_INIT_OLEDBSERVICES
COM+ Services Support
This section describes how the OLE DB provider interacts with Microsoft’s COM+ Services.
What is 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
•contexts
•concurrency
•added security
•object pooling
•just-in-time activation
•queued components
•events
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 Zen 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.
Example of COM+ Services for Visual Basic Programmers
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.
Public Function UpdatePayroll(employeeID As Integer, salary As Currency)
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.
Public Function UpdatePayroll(employeeID As Integer, salary As Currency)
When using the Zen OLE DB Provider, the RecordsAffected parameter of the Execute Method on a command returns different results based on the type of operation.
SELECT Operations
When performing a SELECT statement, RecordsAffected returns a -1 (negative one), which means that this option is not supported. For example: