ActiveX Quick Start
The following topics cover the directory structure for the files provided with the ActiveX access method and provide instructions for the basic tasks involved in developing applications with the controls.
(See the preceding topic for the list of major tasks)
Files and Directories Used with ActiveX
Table
2 includes the ActiveX access method files and directories. This table assumes a default installation. For default locations of PSQL files, see
Where are the PSQL files installed? in
Getting Started with PSQL.
For files needed for application deployment, see
Distributing and Registering PSQL ActiveX Files. Sample files are available in the ActiveX SDK download.
Table 2 ActiveX Access Method Files
File | Location | Description |
ACBtr732.ocx | file_path\PSQL\bin\ | PSQL Data Source Control |
Acctr732.ocx | file_path\PSQL\bin\ | PSQL Bound Controls |
*.ddf, *.mkd | file_path\PSQL\Demodata\ | The Data Dictionary files (DDFs) and MicroKernel Engine tables (.MKD files) |
readme_psqlsdk.htm | file_path\PSQL\Docs | Release notes information |
*.exe | file_path\PSQL\bin\ | Executable files for PSQL |
psql.jar | file_path\PSQL\bin\ | The JAR file for PSQL Java Access Library |
Creating an Application with the ActiveX Access Method
This section explains how to accomplish the major tasks involved in creating an application using the ActiveX access method. The tasks are as follows:
Adding the ActiveX Access Method to a Project
►To add the ActiveX access method data source and bound controls to a Visual Basic project
1 Select Components from the Project menu, or right-click in the Toolbox and select Components.
2 Scroll through the Controls list and select the PSQL ActiveX Data Source and PSQL Bound Controls check boxes.
3 Click OK.
The blue PSQL ActiveX icons appear in the Toolbox.
Using a VAccess Control
The VAccess control is the master control which points to your data and to which all other PSQL controls are bound. It handles all communication with PSQL.
►To add the data source control to a Visual Basic form
1 Click the Visual Basic Toolbox containing the PSQL ActiveX controls.
2 Click the VAccess control icon.
3 Put your cursor on the form and drag to create a rectangle to hold the control. When you release the mouse, the ActiveX access method control appears in the rectangle. The control is visible at design time as an icon, but is invisible at run time.
You also may notice the PSQL splash screen indicating that the PSQL workstation engine has been started.
4 By default, the first VAccess control you place on a form is named VAccess1. You can change this name if you want by entering a new name in the Name property.
Now you must set this VAccess control’s properties. Read the sections
Accessing Properties,
Setting Properties, and
Connecting to a PSQL File.
Accessing Properties
The properties of a control can be set in three places:
•Property Pages dialog box
•Visual Basic Properties window
•Visual Basic Code Editor
Property Pages
When you right-click on a control and choose
Properties from the pop-up menu, the Property Pages appear. This is a tabbed dialog box that lets you set PSQL ActiveX properties for the control. For more information, see Figure
2.
Figure 2 Property Pages Dialog Box
Visual Basic Properties Window
The Visual Basic Properties window displays the complete set of properties for the selected object, including properties specific to Visual Basic.
Figure 3 Properties Window Displaying VAccess Properties
Visual Basic Code Editor
When you double-click on a form or a control, the Visual Basic Code Editor opens. Refer to Visual Basic documentation for information on using the Code Editor. Refer to this manual for syntax and other details about each property.
Setting Properties
►To set properties for a control
1 Select the control whose properties you want to set.
2 Find the property you want to change in the Property Pages, the Visual Basic Properties window, or the Code Editor. See
Accessing Properties for information on accessing the properties.
3 Set a value for each property you wish to add or change by entering it in the text box, selecting it from the drop-down list that appears when you click on the field, or clicking on the ellipses to bring up a dialog box where you can set the value.
Note Some properties can be set at either design time or run time, while others can be set only at design time or only at run time.
Connecting to a PSQL File
There are three important properties of the VAccess control that connect it to a PSQL data file: DdfPath, TableName, and Location. The easiest way to access these properties is through the Property Pages, available when you right-click on the control and choose Properties from the pop-up menu. You can also access them through the Properties window.
Figure 4 Setting Properties to Connect to Your Data
The three important properties that connect to your data are the following:
•DdfPath –This property determines the location of the DDFs (Data Dictionary Files) to be used by the VAccess control. The property must contain a valid drive and path where DDFs are located.
•TableName –This property determines which table (data file) of the DDFs is to be used by the VAccess control. The property must be set to a valid table name contained in the DDFs specified in the DdfPath. You can select the table name from the menu.
•Location –This property determines which PSQL table is to be used by the VAccess control. It corresponds to the table location contained in the DDF for the file. The default for this property is the table location for the table specified in the TableName property, but you can give an alternate path to the table if you wish.
►To set properties to point to your data
1 Select the VAccess control that you want to connect to your data.
2 Set the DdfPath of the VAccess1 control.
3 Select a table name from the
TableName list, which contains the table names available in the DDFs you pointed to in Step
2.
All bound controls that you bind to this VAccess control will now refer to this table.
4 When you selected a table name in Step
3, the
Location property changed to the .mkd file containing that table.
To complete the connection to your data, set the IndexNumber property to select the index you wish to access.
Note If you set these properties from code at run time, the same order applies.
Selecting an Index
The IndexNumber property of the VAccess control determines the index used by the VAccess control when record retrieval methods are invoked. It can be set in the Properties window of the VAccess control at design time or in code at run time.
►To set the index number at design time
You can set the IndexNumber property in the Properties window by selecting it from the drop-down list.
You can also set IndexNumber from the Property Pages as follows:
1 Select the specific VAccess control for which you want to set its index number.
2 Right-click to open the Property Pages dialog box.
3 Click on the Settings tab of the VAccess1 Property Pages dialog box and click on the IndexNumber field. This displays the list of indexes that have been defined for the selected table.
4 Choose index
0 - ID and click
OK. The settings are shown in Figure
5.
Figure 5 Setting the IndexNumber Property
There are two ways to set the index number at run time – with code and with the VAComboBox control.
►To set the index number at run time with code
1 Double-click on the VAccess control whose index you wish to set. This brings up the Code Editor.
2 Enter code modeled on the following example:
VAccess1.IndexNumber = 0 'select the first index
►To set the index number at run time with a combo box
1 Place a VAComboBox on the form and select it.
2 Set the VAccessName property to the name of the VAccess control and the VAFieldName property to [Index List]. The VAComboBox automatically fills with a list of indexes from the file, and any selections from this list will set the index used by the VAccess control.
Setting Automatic vs. Manual File Opening
The VAccess control opens a PSQL data file in one of two ways based on how you set its AutoOpen property. (AutoOpen is a design time-only property.)
•If AutoOpen is set to True, the control automatically opens the file when it is loaded at run time based upon the settings of the DdfPath, TableName, and Location properties in the VAccess control. It automatically closes the file when it is unloaded.
•If AutoOpen is set to False, the control does not open the file until the Open method is invoked. In addition to being set in the Properties window, the DdfPath, TableName, and Location properties can be set in code prior to the Open. Thus, you can open different files at different times with the same VAccess control or accept user input to choose a file. The Close method closes an open data file.
►To set the AutoOpen property
You can set the AutoOpen property in the Properties window by selecting True or False from the drop-down list.
You can also set AutoOpen from the Property Pages as follows:
1 Right-click on the control to bring up the Property Pages.
2 Click on the Settings tab.
3 Select or clear the AutoOpen check box. When the check box is selected, AutoOpen is set to True; when clear, AutoOpen is set to False.
Example
This example shows how to close a file and open another one when AutoOpen is set to False.
VAccess1.Close
n
VAccess1.DdfPath = "file_path\PSQL\Demodata\"
VAccess1.TableName = "Student"
VAccess1.Open
Linking Bound Controls to a Data Source
After you have added a VAccess control to a form and set the control’s properties, you can add any bound controls you want and bind them to the VAccess control. This links them to the data to which the VAccess control points. For information about the specific bound controls, see
ActiveX Control Reference.
►To link any of the PSQL bound controls to your data
1 Set its VAccessName property to the name of the VAccess data source control that already points to your data.
2 For field-bound controls such as the VATextBox or VAOptionButton, you also need to set the VAFieldName property to the name of one of the fields in the data source control.
Retrieving Records
You can retrieve records in three ways:
•with the VAHScrollBar or VAVScrollBar
•with the VACommandButton
•with code
►To retrieve records with a scroll bar
1 Add a VAHScrollBar or VAVScrollBar to the form and select it.
2 Set its VAccessName property to the VAccess control you want to use.
►To retrieve records with a command button
1 Add a VACommandButton to the form and select it.
2 Set its VAccessName property to the name of the VAccess control you want to use, and set its VAOperation property to the desired retrieval operation.
See
VAOperation for a list of available PSQL operations.
►To retrieve records with code
•Use one of the methods in Table
3. The Get methods use the index specified in the
IndexNumber property. The Step methods do not use an index.
Table 3 Methods for Retrieving Individual Records
GetDirect | GetLast | StepFirst |
GetEqual | GetLess | StepLast |
GetFirst | GetLessOrEqual | StepNext |
GetGreater | GetNext | StepPrevious |
GetGreaterOrEqual | GetPrevious | |
Example
file_path\
Stat = VAccess1.GetFirst
While Stat = 0
List1.AddItem VAccess1.FieldValue("Last_Name") _
& " " & VAccess1.FieldValue("First_Name")
Stat = VAccess1.GetNext
Wend
Searching for a Specific Record
You can search for a specific record in two ways:
•with the bound controls
•with code
►To search for a specific record with a text box
1 Place a VATextBox on the form.
2 Set the VATextBox’s VASearch property to True. This causes the VAccess control to perform a GetGreaterOrEqual method to retrieve matching records as text is entered into the text box.
►To search for a specific record with code
1 Double-click on the VAccess control.
2 Select the appropriate index by setting the IndexNumber property.
3 Use the FieldValue method to populate the index field with the value you want to search. For indexes which use more than one field, be sure to assign a value to each of the fields.
4 Then perform one of the methods in Table
4 to retrieve the desired record.
Table 4 Methods for Retrieving a Specific Record
GetEqual | Returns the first record which exactly matches the key. |
GetGreater | Returns the next record after the key. |
GetGreaterOrEqual | Returns either the key record or the record after it. |
GetLess | Returns the record prior to the key. |
GetLessOrEqual | Returns either the key record or the record prior to it. |
5 Make sure the IndexNumber property is set to the index that corresponds to the field or fields by which the search is done.
Example
VAccess1.FieldValue("last_name") = "Reich"
VAccess1.FieldValue("first_name") = "Steve"
VAccess1.IndexNumber = 1
stat = VAccess1.GetEqual
If stat = 0 Then
MsgBox "Found the record."
Else
MsgBox "No such record on file."
End If
Adding, Updating, and Deleting Records
There are two ways to add, update, or delete records:
•with the VACommandButton
•with code
►To add, update, or delete records with a command button
1 Place a VACommandButton on the form.
2 Set its VAccessName property to the name of the appropriate VAccess control.
3 Set its VAOperation property to the desired operation. Refer to the VAOperation property in the Property Reference of this manual for a list of possible operations.
►To add, update, or delete records with code
Use one of the custom methods from the following table for the VAccess control to write to the database in code.
Table 5 Custom Methods to Add, Update, and Delete Records
Delete | Deletes the current record |
Insert | Inserts the record in memory as a new record |
Update | Updates the current record with the record in memory |
Example
VAccess1.TableName = "Student"
VAccess1.IndexNumber = 1
FieldValue (ID) = 2001100
stat = GetEqual
, s If stat = 0
VAccess1.FieldValue ("Major") = "Music"
VAccess1.Update
End If
Locking Records
PSQL provides two kinds of locks which are supported by the ActiveX access method: single record no-wait locks and multiple record no-wait locks.
To apply these locks using the ActiveX access method, use one of the following approaches:
•If the VACommandButton is used to retrieve and lock a record, set the VALockBias property.
•Include the appropriate lock bias (200 for single record, 400 for multiple record) as an optional parameter when invoking a Get or Step method.
Use the Unlock method to unlock records.
In
PSQL Programmer's Guide, see
Data Integrity for a complete discussion of record locking and concurrency controls.
Example
Const SINGLE_NOWAIT_LOCK = 200
Const MULTIPLE_NOWAIT_LOCK = 400
VAccess1.FieldValue("ID") = "<valid #>"
stat = VAccess1.GetEqual SINGLE_NOWAIT_LOCK
If stat = 0
VAccess1.FieldValue("Major") = "Astronomy"
VAccess1.Update
End If
Joining Files
The Join property of the VAccess control allows you to specify that two files are to be linked by a key value, so that any changes in the record positioning of the first file will also affect the records retrieved from the second. The Join property provides a simple mechanism for browsing a relational database.
The joined fields in the “self-control must be index fields and must contain the same raw data as their corresponding fields in the master control. The fields in the master control do not have to be indexes. The IndexNumber property of the slave control must remain set to the index involved in the join or the join will be broken.
One-to-one, many-to-one, one-to-many, and many-to-many joins are all supported. The data can be displayed in a VAListBox in record list mode by setting its VAccessName property to the name of the slave control and its VAFieldName property to the fields to be displayed.
►To join two VAccess controls
1 Select the VAccess control that you wish to be the slave control.
2 In the Properties window, click the Join button to open the Join Controls dialog box. The left side of the dialog box shows the table of the slave control from which you are joining and the selected index of that table. The right side shows the master control to which you are joining, and the fields available in that control.
3 Double-click on a field of the master control to join it to the table of the slave control. The field then appears under the Equals column, showing that it is joined.
Enabling Extended Operations
Extended operations allow you to push the processing of requests for multiple records to the MicroKernel Engine. On network server-based PSQL systems, extended operations can dramatically reduce network traffic and speed up data retrieval.
Extended operations are most useful when you are reading a number of records at a time. For example: scanning a file to collect and tabulate statistical data; requesting a number of records at once to populate a list, report, or grid; or performing an ad-hoc search of a database which does not have an appropriate index to collate the records you need.
Extended operations do not replace indexed record retrieval for accessing individual records, but they provide a powerful tool for retrieving sets of data quickly.
►To enable extended operations
1 Select the VAccess control to be used with extended operations.
2 If using the Properties window, set the ExtendedOps property to True. If using the Property Pages, click on the Extended Operations tab, select the ExtendedOps check box, and click OK.
The ActiveX access method supports the following extended fetch methods:
•Get Next Extended retrieves subsequent records in the current key path order according to the filter conditions specified.
•Get Previous Extended retrieves previous records in the current key path order which match the specified filter conditions.
•Step Next Extended retrieves subsequent records in the physical order they occur in the file according to the filter conditions specified.
•Step Previous Extended retrieves previous records in the physical order they occur in the file according to the specified filter conditions.
Using the Init Method
Use the Init method after any changes to the SelectedFields or SelectedRecords properties, or between re-establishing current cursor positioning in the file with a single-record fetch method and invoking an extended fetch method.
Building a Data “Snapshot” with the AutoMode Property
With AutoMode enabled, the VAccess data source caches all of the rows which meet the selection criteria in memory as they are retrieved, allowing you to build a “snapshot” of the data. You can use the Row property to traverse the returned record set, or the RowColumnValue method to retrieve the value at any row and column in the set.
►To use AutoMode
1 Set the VAccess control’s ExtendedOps and AutoMode properties to True to enable extended operations and switch the control into automode.
2 Set the control’s SelectedFields and SelectedRecords properties to specify the record set you wish to retrieve.
3 Set the CacheRows property to the maximum number of records you wish to get per retrieval operation.
4 Set the CancelDialog property to True if you wish to enable the user to cancel the scan before it is finished.
AutoMode makes extended operations easier to use in some circumstances, but it is limited by available memory. You can disable AutoMode (default) and use the explicit extended fetch methods GetNextExtended, GetPreviousExtended, StepNextExtended, or StepPreviousExtended, to return data sets in discreet blocks.
Using Extended Fetch Methods
The extended fetch methods GetNextExtended, GetPreviousExtended, StepNextExtended, and StepPreviousExtended begin at the current cursor position in a file and return a set of rows (records) and columns (fields) which meet a specified selection criterion. The Get methods use the current index to return records in index order. The Step methods return records ordered by physical position. Step methods are generally faster and should be used if the order of the records returned is not important.
Several properties are involved in setting the maximum number of records returned by a given extended fetch operation.
•The total amount of data returned by a single extended fetch operation is limited by the size of the data communications buffer used, which is specified in the MaxBufferSize property. This buffer is limited to 64 KB, but this maximum may be lower, depending upon your particular PSQL MicroKernel Database Engine configuration. Specifying too large a value for this property results in Status Code 97, “The data buffer is too small.”
•The SelectedFields property determines the size of an individual row of data, which is equal to the sum of the storage lengths of the selected fields plus 6 bytes.
•The CacheRows parameter dictates the maximum number of rows to retrieve at a time. The extended fetch methods will use either the value of CacheRows, or a calculated value based on the row size and the MaxBufferSize, whichever is less. If CacheRows is zero, the calculated value will be used.
•The SelectedRecords property allows you to retrieve only those records which meet certain criteria. It works in conjunction with the CacheRejectMax property, skipping records which do not meet the search criteria. If you do not specify a value for the CacheRejectMax property, PSQL will use a system-defined value. The record set is returned when one of two conditions are met: the number of rows which matches the selection criteria equals the value of the CacheRows property, or the number of records scanned since the last match exceeds the value of the CacheRejectMax property. The Status property indicates which condition was met.
•The Rows property indicates the number of rows returned by the extended fetch operation. Always check the value of this property after an extended fetch, as it is possible for the operation to return a non-zero status (such as end of file), and also return valid data.
•The Columns property corresponds to the number of fields specified in the SelectedFields property, or to the number of fields defined for the file if no record selection criteria was specified.
•The RowColumnValue method retrieves the field value at a specified row and column position in the extended fetch record set.
Current cursor positioning after an extended fetch operation corresponds to the last record scanned by the operation. This cursor positioning is indeterminate to the application if record selection criteria have been specified, as it may be beyond the last row actually retrieved.
The IncludeCurrent property setting determines whether the next extended fetch operation will scan the record at the current cursor position, or start with the subsequent record. In most circumstances you will not need to set this property. It is automatically set True by the Init method, False after an extended fetch. To include the record at the current cursor position in subsequent extended fetch operations, set this property to True immediately prior to the operation.
Using ExtendedOps with Joined Files
The ExtendedOps property can speed up and simplify joining VAccess data controls in a one-to-many relationship, such as the classic “order header record to order line items” example. Enabling extended operations on the joined data source, in this example the “order lines” control, will automatically build the appropriate query and cause the control to perform the join using extended operations, returning all of the records from the joined file which meet the join criteria as an extended fetch record set.
Optimizing Extended Fetches
To use extended fetch operations most efficiently, first position the cursor at or as close as you can to the first record which will match your search criteria. Often you'll be able to use the GetEqual or GetGreaterOrEqual method with an index to establish initial positioning and limit the range of records PSQL will have to scan to return the ones you need, and then use GetNextExtended to retrieve the subsequent records.
The CacheRejectMax property is useful if your SelectedRecords criterion limits records on the current index. You can use it to tell the PSQL server process to stop when it encounters the first record that does not match your criteria, for example, setting the CacheRejectMax property value to 1.
If you have to scan an entire file, but the order of the records is not important, you can save some time by using the StepFirst method to position the cursor at the beginning of the file and the StepNextExtended method to scan it. The step methods are generally faster since they do not have to collate records along an index path.
You can also save time and resources by specifying only the fields you’re interested in for the SelectedFields property. The limiting factor in extended fetch operations is the size of the data buffer, and the less memory you need for each row, the more rows you can return at a time.
Remember that because extended operations are executed by another task, typically a PSQL server process, your application relinquishes control of the process as soon as it executes the extended query, and the other process takes over until the query conditions are satisfied. Therefore planning and optimizing extended queries can make a big difference in the performance of your application.
Example
Q = Chr$(34)
VAccess1.ExtendedOps = True
VAccess1.SelectedRecords = "Scholarship_Amount > " & Q & "0" & Q
VAccess1.SelectedFields = "ID,Scholarship_Amount"
VAccess1.StepFirst
VAccess1.Init
Do
Stat = VAccess1.StepNextExtended
For R = 1 to VAccess1.Rows
List1.AddItem VAccess1.RowColumnValue(R, 1) & Chr$(9) & _
Format(VAccess1.RowColumnValue(R, 2), "Currency")
Next R
Loop While Stat = 0
Distributing and Registering PSQL ActiveX Files
For information about distributing your PSQL application, see
Distributing PSQL Applications in
PSQL Programmer's Guide.
The following table includes the files required to run your application built with the ActiveX access method:
Table 6 Redistributable Files
File | Location | Description |
ACBTR732.OCX | file_path\PSQL\bin\ | PSQL Data Source Control |
ACCTR732.OCX | file_path\PSQL\bin\ | PSQL Bound Controls |
PEDTCONV.DLL | file_path\PSQL\bin\ | Data conversion DLL |
PBTRVD32.DLL | file_path\PSQL\bin\ | Meta data handling DLL |
For default locations of PSQL files, see
Where are the PSQL files installed? in
Getting Started with PSQL.
Once you have distributed PSQL ActiveX files and the required DLLs, you must register the ActiveX files so that they function correctly. There are two ways you can register these files:
•Many current installation utilities, such as InstallShield, can be modified to automatically register ActiveX controls during the installation process (for more information, refer to the documentation for your particular installation utility).
•Another way to register ActiveX controls is to install the redistributable file REGSVR32.EXE and run it during (or after) the installation process. This is a simple ActiveX registration utility that takes the name of the ActiveX to be registered as a command-line parameter (e.g., REGSVR32 C:\MyInstall\ACBTR732.OCX).
Additional Information on the ActiveX Access Method
In addition to this documentation, other resources can assist you in developing ActiveX applications:
•The Actian website provides many kinds of information for PSQL developers.
•Status Codes and Messages – This guide lists and describes each status code and message you may encounter while using database components.
