This section helps you to integrate the Zen OLE DB Provider into your Visual Basic development environment.
Non-Visual Programming
This procedure shows how to program in ADO and OLE DB without using the visual control objects.
►To program for OLE DB in Visual Basic
1 Open a new project in VB 6
2 Click on the Project|References menu item to bring up the references dialog
3 Make sure that Microsoft ActiveX Data Objects 2.1 Library or Microsoft ActiveX Data Objects 2.5 Library is checked and click OK.
4 Add three text boxes to the form.
5 Bring up the code window and dimension and allocate a global record set object using the following code (this should go in the General::Declarations code area):
Dim myData as New ADODB.Recordset
6 To have the record set created on Form::Load, put the following code in the Form_Load event (we've set up DemoData as a DBNAMES entry, and we're going to use the Person file):
7 We're going to want to display the records we find, so create the following subroutine:
Private Sub DisplayFields()
Text1 = myData.Fields(0)
Text2 = myData.Fields(1)
Text3 = myData.Fields(2)
End Sub
8 Add 4 buttons to the form, and add the following code:
Private Sub Command1_Click()
myData.MoveFirst
DisplayFields
End Sub
Private Sub Command2_Click()
myData.MovePrevious
DisplayFields
End Sub
Private Sub Command3_Click()
myData.MoveNext
DisplayFields
End Sub
Private Sub Command4_Click()
myData.MoveLast
DisplayFields
End Sub
9 To get the first record when the form loads, add the following as the last line in Form_Load:
Command1_Click
10 Run the application, and you will be able to use the buttons to navigate through the data. You may want to change the button captions and names to make things easier to see.
11 To add update capabilities, place another button on the form and give it the caption "Update" and the name "btnUpdate"
12 Add the following code so users cannot update the first field. Since the concurrency mode is adLockOptimistic, the update is applied automatically when the cursor moves off the current row. The update may also be used to force an update regardless of cursor position.
Private Sub btnUpdate_Click()
myData.Fields(1) = Text2
myData.Fields(2) = Text3
End Sub
Visual Programming
The Zen 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 use the provider to fill a grid in Visual Basic
1 Start Visual Basic 6
2 Begin a standard project
3 Click the Project | Components menu option to display the components dialog.
4 Select the checkboxes next to the following components and click OK.
•"Microsoft ADO Data Control 6.0 (OLEDB)"
•"Microsoft DataGrid Control 6.0 (OLEDB)"
5 Drag and drop one copy of each control onto the form.
6 Right click on the data control and select ADODC Properties from the pop-up menu
7 Click Build to bring up the connection string builder.
8 Select Pervasive OLE DB Provider from the list and click Next.
9 Enter a DBNames entry or a pathname for the Data Source
10 Click OK.
11 Click the RecordSource tab of the ADODC Properties dialog.
12 Change the command type to adCmdTable using the list.
13 Enter a table name or select one from the list and click OK.
14 Select the grid, and edit the properties using F4.
15 Change the Data Source property to adodc1 using the list.
16 Run the application.
Creating a Connection
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.
Named databases can be created in Zen Control Center.
In Control Center, right-click 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 in 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 in 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 record sets. This is more efficient than creating a separate connection for each record set.
Once a record set 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 record set.
Adding Records
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.
Note Before calling Update or UpdateBatch, you must provide a value for any fields that are not nullable.
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.
To delete a record, use the Delete method. There is one optional parameter, and that specifies which record(s) to delete. Currently, the Zen provider only supports the default, adAffectCurrent.
The method you should use to search for records is dependent upon the configuration of your record set. Zen supports the Index property and the Seek method for dynamic record sets.
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 Zen engine directly for all searching and sorting, which results in a performance boost over using the client-side layer.
Note In order to use the Seek method, you must specify adCmdTableDirect when opening the Recordset.
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. You can change this by specifying the lock type parameter on the record set’s Open call, which affects the method used to write the changes to the record set.
To update the record set, change the lock type to use either immediate-update mode, single row deferred-update mode, or multirow deferred-update mode.
Immediate-Update Mode
OLE DB providers before 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.
Deferred-Update Modes
There are two deferred-update modes: single-row and multi-row:
Single Row Deferred Update Mode
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 record set 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 record set 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 record set 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 record set objects. However, if you choose to create and dispose of connections and record sets 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 record set through code and set the Recordset property of the ADO Data Control to this record set 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:
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
Immediately before use:
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Connection
To dispose of those objects:
Set rs = Nothing
Set cn = Nothing
Using Bookmarks
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.
' do some other things, then find your place again
rs.Bookmark = SaveMyPlace
rs.Close
Tip See Searching for Records for information on using the Find method.
Using Events
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.
Private Sub Adodc1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
If Adodc1.Recordset.EOF Then Adodc1.Recordset.MoveLast
End Sub
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.
Using the Field Object
When assigning values to fields in a record set, you can specify simply the record set 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 record set, and the Type of data (useful for determining how to compare the value in the field to another field or a value in memory).
Using Transactions
Transactions are implemented in ADO using the Connection object. The following methods, which have no parameters, are available for transactions:
•BeginTrans
•CommitTrans
•RollbackTrans
To use transactions, create a connection and then create record sets using that connection.