This chapter contains the following topics:

This section helps you to integrate the PSQL OLE DB Provider into your Visual Basic development environment.

This procedure shows how to program in ADO and OLE DB without using the visual control objects.

The PSQL provider has one property that is essential for initialization, and that is Data Source. OLE DB providers do not use DSN’s, so the Data Source property should be set to a DBNames value. For example, the connection string for an ADO Connection::Open command would be "Provider=PervasiveOLEDB;Data Source=<data store>", where <data store> is a DBName.

To create a connection, set the Cursor Location, and then either set the ConnectionString and then call Open, or specify the connection string on the Open call.

or

Named databases can be created in the PSQL Control Center.

From the Control Center, right-click on the Configuration node and select Maintain Named Databases. This is the preferred method of specifying a data store, as the data can be moved, if necessary, without breaking your application. All you have to do to point your application to the new directory is to modify the named database paths at the server, limiting your change to one minor task, rather than having to reconfigure several client machines.

It is not recommended to hard-code paths into your application. However, if your application can retrieve a path from a registry entry or an environment variable and build the string below, using a path is an easy way to access a data store without having to define a named database.

As an alternative to a hard-coded path into your application, you can use the Location parameter in the connection string to connect to a remote database.

You can create a connection and then use that connection to create multiple recordsets. This is more efficient than creating a separate connection for each recordset.

Once a recordset has been opened, you can navigate through it using Move x, MoveFirst, MoveNext, MovePrevious, and MoveLast. The Move method takes a single parameter, and that is the number of records to navigate. A negative number will cause the cursor to move backward (toward the first record) in the recordset.

To add a new record, use the AddNew method. No parameters are required, although you can specify the fields and values in the call. The cursor points to the newly added record, so updates can be made immediately to the fields without having to navigate to it.

In immediate-update mode, an empty record is written to the data store, then updates to the field are stored as they are made. In deferred-update mode, an empty record is created, but that record is not written until Update or UpdateBatch is called.

or

To delete a record, use the Delete method. There is one optional parameter, and that specifies which record(s) to delete. Currently, the PSQL provider only supports the default, adAffectCurrent.

To update a record, assign the desired values to the appropriate fields, then call Update or UpdateBatch.

The method you should use to search for records is dependent upon the configuration of your recordset. PSQL supports the Index property and the Seek method for dynamic recordsets.

Although the functionality for the Sort and Find methods (which work only with static client-side cursors) is present in the OLE DB provider, they are not currently supported and it is recommended that you not use them. Index and Seek are more robust and utilize the PSQL engine directly for all searching and sorting, which results in a performance boost over using the client-side layer.

The Seek method requires a variant array containing the values to search for. An index consists of one or more columns and the array contains a value to compare against each corresponding column. Thus, if you have a segmented index, you would need to declare the variant array with a number of elements equal to the number of segments in the current index.

The default lock type in ADO is read-only. This can be changed by specifying the lock type parameter on the recordset’s Open call and affects the method used to write the changes to the recordset.

To update the recordset, change the locktype to use either immediate-update mode, single row deferred-update mode, or multi-row deferred-update mode.

OLE DB providers prior to Pervasive.SQL V8 did not support adLockPessimistic for the lock type. If no lock type is specified, the Update method is unnecessary and updates are written to the data store on a field-by-field basis. This is inefficient and should only be used when other types have been proven to be unacceptable for a particular application.

There are two deferred-update modes: single-row and multi-row:

If adLockOptimistic is used for the lock type, then the Update method should be used for writing changes, and the code should be written to deal with one record at a time. Any operation which causes ADO to act on another row in the recordset or causes a refresh of the current row will trigger an implicit ADO Update call. However, this is much more efficient than immediate-update mode. Here’s an example of opening a recordset using immediate-update mode:

If adLockBatchOptimistic is used for the lock type, then the UpdateBatch method should be used for writing changes, and the code can be written to deal with multiple record changes at once.

This is the best lock type in terms of performance, but it requires that the application design allow ADO to cache and update multiple rows at a time. This may not always be advisable in a multi-user, high-throughput application. Here is an example of opening a recordset using deferred-update mode:

ADO uses objects to access data and provide that to the user. If you are using the Microsoft ADO Data Control, then you don’t have to instantiate your connection and recordset objects. However, if you choose to create and dispose of connections and recordsets on-the-fly, you will need to instantiate and later destroy your objects. Furthermore, the current shipping version of the ADO Data Control will use ADO 2.1 functionality (such as Index support) only if you create the recordset through code and set the Recordset property of the ADO Data Control to this recordset in code (this may also involve changing your project references to include ADO 2.1 rather than the default ADO 2.0).

To instantiate an object, use the New keyword. On declaration of the object:

Immediately before use:

To dispose of those objects:

Bookmarks are used in ADO the same as ordinary bookmarks are used in books, to be able to find that place again later. This is especially useful when searching through a database or when using multiple tables. Use a Variant variable to store the bookmark.

Note: Great care must be exercised in using ADO events so that an event doesn’t make a call that would generate more events. It is easy to get into a situation that would cause an infinite loop. One of the more obvious examples of that is using the WillMove event, instead of the MoveComplete event, to handle an end-of-file condition.

This code will cause an infinite loop, which will ultimately fill up memory and generate an out of stack space error. This error handling should go into the Adodc1_MoveComplete event, instead.

When assigning values to fields in a recordset, you can specify simply the recordset name, an exclamation point, and the FieldName to reference the field, or you can reference the field by number.

You can also use other properties of the Field object to find out more about the data stored there. For example, you can find the length of the data in the fields using ActualSize, the OriginalValue before any change was made in this instance of the recordset, and the Type of data (useful for determining how to compare the value in the field to another field or a value in memory).

Transactions are implemented in ADO using the Connection object. The methods available for transactions are

There are no parameters for these methods.