OLE DB Provider Architecture
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:
Figure 1 Architecture of PSQL OLE DB Provider
Relational Performance
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.
Remote Connections
Use the Location parameter in the connection string to specify a remote server.
Provider=PervasiveOLEDB;Data Source=MyDBname;Location=MyServer
For additional information, see Remote Connections.
Pessimistic Cursors
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.
Support for ADO Refresh Method
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.
Review of Previous OLE DB Providers
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.
First Version: Pervasive.SQL 2000i (SP2)
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.
Figure 2 Architecture of First PSQL OLE DB Provider
Second Version: Pervasive.SQL 2000i (SP3, SP4)
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.
Figure 3 Architecture of Second OLE DB Provider
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.
The PSQL v12 version of the provider is designed to overcome this performance issue.
Features in Pervasive.SQL 2000i OLE DB Provider
This section provides an overview of the changes made to the PSQL OLE DB driver in the Pervasive.SQL 2000i SP3 release.
Command-Based Recordsets Supported
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.
ADOX
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.
Navigational Recordsets in the Provider
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.
Large Binary Objects
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.