Introducing PSQL OLE DB Provider

This documentation does not attempt to describe OLE DB and ADO, which are covered in depth in the Microsoft specifications. Instead, it gives specifics about the PSQL provider and helps you get started as a new user.

This provider supports only PSQL engines in the current release. You cannot use this OLE DB provider to connect to machines that use previous versions of PSQL.

PSQL OLE DB is the name of Actian Corporation’s implementation of the OLE DB Provider Specification written by Microsoft.

For relational access, 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. The provider uses the same protocol as the ODBC client interface.

The following shows the architecture of the OLE DB provider:

The PSQL OLE DB Provider uses an architecture similar to that of the ODBC and JDBC drivers to communicate to the remote server. By using the relational engine on the server-side, you should see increased performance in most SQL-based use of the provider, including stored procedures and complex queries.

Use the Location parameter in the connection string to specify a remote server.

For additional information, see Remote Connections.

There are certain situations in database programming where great importance is attached to the success of updates, even if that focus increases the chances that other clients will encounter record locks.

When you obtain a recordset and specify deferred update, you might encounter a concurrency violation when trying to perform an update because another client has updated a row that you also intend to update. Setting a pessimistic cursor can help in these situations.

For additional information, see Pessimistic Cursors.

The PSQL OLE DB Provider supports the Refresh method of a Command object’s Parameters collection so that you can retrieve parameter information from a stored procedure or parameterized query.

For additional information, see Support for ADO Refresh Method Examples.

For a comparison with the current version, this section provides a brief overview of past providers. PSQL first shipped an OLE DB provider with Service Pack 2 of the Pervasive.SQL 2000i release. This release is the third generation provider.

The first provider was transactional (Btrieve) only. For relational access, you had to use Microsoft’s ODBC to OLE DB bridge provider. The following figure shows the architecture of the first OLE DB provider.

With Pervasive.SQL 2000i, PSQL released an updated provider that allowed relational access. It allowed developers access to relational and transactional access using the same API. Updates to the data could even be encapsulated within transactions, even between relational and transactional access. Further, the provider incorporated some ADOX features, so in addition to the capabilities of SQL, database creation was also available.

This second version of the provider did have some drawbacks. It was considered a thick client because the relational engine was encapsulated in the provider. Therefore, the provider had to cross the process boundary for each row, which led to performance issues for applications in a client-server setting.

This version of the provider resolves this performance issue.

This section provides an overview of the changes made to the PSQL OLE DB driver in the Pervasive.SQL 2000i SP3 release.

The OLE DB provider that shipped with Pervasive.SQL 2000i prior to SP3 did not include support for SQL statements. This meant that Command objects were not supported and that result sets required table names to open successfully. This version includes support for SQL commands and conforms to 2.5 specifications. Unlike the SQL Server provider or the ODBC bridge, the provider can open result sets that are either command based or purely navigational. Additionally, server-side cursors on either can be forward-only, static, or dynamic. Both command-based result sets and navigational (table-based) result sets can be open and operated on at the same time.

Command-based recordsets provide the power and flexibility of the SQL engine, but server-side navigational result sets provide direct access to indexes; this feature is not available with command-based result sets (indirect access is provided by the query optimizer). With the index capability comes the ability to perform Seek operations. A routine that uses Seek can significantly outperform a similar routine that performs the same functions via a SQL statement. When used properly, server-side navigational recordsets can improve application performance by allowing rapid positioning on records that contain specific values.

PSQL supports ADO Extensions for Data Definition Language and Security (ADOX). ADOX is used to create tables, modify schema definitions, and view the contents of database tables. Currently, catalog, table, column, and index objects are supported. The creation of tables and indexes is supported; database creation is not supported at this time.

In order to open a navigational result set, adCmdTableDirect must be used in the options of the Open method. In the previous version, adCmdTable would successfully open a navigational result set. However, ADO will turn this into a "SELECT * FROM" SQL statement. This will make the result set command-based, and indexes will be unavailable.

Note Since ADO treated this differently when command support was unavailable, existing applications that take advantage of index capabilities will no longer function unless the Open method calls use adCmdTableDirect.

ISequentialStream support has been added to the OLE DB provider. In ADO, this translates into AppendChunk/GetChunk functionality of the recordset object. It also allows complex data binding to transfer BLOB data to and from visual controls.

This section outlines issues you should be aware of when using PSQL OLE DB Provider with Visual Studio.NET.

The wizards in Visual Studio.NET that generate ADO.NET code are designed for use only with Microsoft providers. Therefore, it is not recommended that you use these wizards with PSQL. However, the function of these wizards is only to generate code, so everything that the wizards can do can be accomplished with your own code.

To work with ASP.NET, you need to ensure that both ASP.NET and the user account used by IIS (IUSR_machinename) have read and write access to the following files and directories:

For a reference of supported objects in Visual Studio.NET, see Visual Studio.NET Implementation Reference.

This section identifies performance issues related to OLE DB.

The PSQL cache engine can impact performance of the OLE DB provider when used in a client/server environment. You may want to disable the cache engine using PSQL Control Center depending on your configuration. Static cursors are most affected by this issue.

Server-side navigational recordsets will have a significant performance advantage over command-based recordsets for tasks that require frequent positioning on records that contain specific values.

Static cursors will create a temporary table behind the scenes whenever one would not have been created by the relational engine (for more information about temporary tables, see SQL Engine Reference). This will be the case for both command-based and navigational tables. When bandwidth is not a significant consideration, dynamic cursors can provide higher performance, since they do not always involve temporary tables. However, in low bandwidth scenarios, round-trips can be too expensive to justify dynamic cursors; in this case RDS is often a good solution. The drawback to RDS is that Microsoft has implemented it as a command-based only solution, which means that index functionality (using Seek) is unavailable. Performance can be maintained regardless of deployment by implementing an abstraction layer that works identically on RDS-based and local recordsets. The nature of this abstraction would depend on the needs of the application and would likely take the form of a runtime business object.

When developing OLE DB applications, a way to improve performance is to turn off any OLE DB Services that are not being used. See the documentation for DBPROP_INIT_OLEDBSERVICES for more information.

Turning off the Automatic Transaction Enlistment will not instantiate the ITransactionJoin interface on the session and will also keep the provider from looking for MTS objects.

•The Index property cannot be set on a static navigational cursor once the recordset has been used. Set the index appropriately before performing the open operation on the recordset. After opening the recordset, the index cannot be changed.