Using Data Dictionary Methods with ActiveX
This chapter contains the following topics:
DDF Handling with the ActiveX Data Source Control
Data Dictionary Files (DDFs) are used by PSQL and by a number of third-party applications to provide column-level access to PSQL data. The ActiveX access method provides a number of methods to assist PSQL developers in creating, reading, and modifying DDFs.
Most of the dictionary file handling involves the addition of new methods to the control. However, because previous versions of the ActiveX Data Source control were aware of data dictionaries, some changes have been made to the behavior of existing properties and methods. Backwards compatibility has been maintained, but some properties have either been expanded (such as RefreshLocations) or deprecated (such as FieldIndex and FieldName).
File Contents Versus Memory Image
The ActiveX Data Source control reads from the DDFs and caches the contents when appropriate. This means that at any given time, there may be two versions of the dictionary: the contents of the DDFs themselves and the contents of the ActiveX access method control’s memory image of those files. In version 3 of the ActiveX controls, the RefreshLocations property determined whether the control would read DDF information from the DDFs themselves or from a persisted memory image (thus saving time on starting the application).
This function has been preserved, but the meaning of RefreshLocations has been expanded to signify whether an operation should be performed using the DDFs themselves or the ActiveX control’s memory image of the dictionary information.
In other words, if an operation could refer to either the control’s memory image or the DDFs, setting RefreshLocations to False causes the control to limit that operation to working with only the control’s memory image. If RefreshLocations is True, the operation will affect both the memory image and the files themselves. As a result, RefreshLocations is an overriding property that affects most of the DDF handling methods and properties of the ActiveX control.
Note When editing DDFs with RefreshLocations set to True, be careful not to overwrite the DDFs.
Reading a Dictionary File
Previous versions of the ActiveX access method exposed much of the information contained in the DDFs, but made it very difficult (and sometimes impossible) to retrieve certain information. In addition, retrieving more than one item of information was tedious because multiple property settings were required for each item. In this version of the ActiveX access method, variant arrays have been used to solve these problems. A variant is a type of variable that can represent multiple data types and that knows which data type it currently represents. The ActiveX access method containers support variants.
A variant array is a type of variant that is actually a reference to a list of variants, each of which may hold a different type of information. This array may be represented in different forms, depending on the coding environment. In Visual Basic, a variant array will appear as just a normal array, e.g., myArray(1, 1). In Visual C++, however, a variant array will simply be a variant of type VT_ARRAY|VT_VARIANT, and the “SafeArray” system APIs must be used to manipulate the array contents.
As mentioned previously, the ActiveX access method uses variant arrays to expose core DDF information. The arrays that it uses are two-dimensional arrays that have the column as the first dimension and the row as the second dimension (this ordering allows Visual Basic users to use the ReDim Preserve function to change the number of rows). The columns represent various facets of the requested information while the rows represent the collection of items. For instance, GetTableList returns a two-dimensional array. The first dimension consists of three pieces of information—TableID, TableName, and TableLocation—while the second dimension contains one row for each table in the dictionary. Getting a list of table names in Visual Basic, therefore, would be done as follows:
►To fill a list box with the table names in the current dictionary
Dim tableList as Variant
Dim tableCount as integer
'Make sure we’re reading everything from the file itself
VAccess1.RefreshLocations = True
'Set the DdfPath – this will open the dictionary
VAccess1.DdfPath = “c:\mydata”
'get the variant array
tableList = VAccess1.GetTableList
'fill a listbox with the names – loop until we reach the 'upper bound of the array
For tableCount = 0 to UBound(tableList, 2)
'Add the correct member of the array to the listbox
List1.AddItem tableList(1, tableCount)
Next tableCount
FieldList and
IndexList work much the same way. But because they are properties, they allow you to modify or add entries to the array. See
FieldList and
IndexList for information on the structure of the first dimension of each array. The second dimension of each array would of course represent the number of fields or indexes.
Modifying a Dictionary File
Modifying a file in the ActiveX memory image is useful as the first stage of a DDF new definition add or change and commit process, or in correcting information in an incorrect DDF. Most of the changes that need to be made can be done by using the FieldList and IndexList properties.
When changing the DDFs, it is usually more efficient to store the changes in the ActiveX memory image until all changes are ready, and then commit them to the file using DdfAddTable. Thus, it is better to set RefreshLocations to False when setting FieldList or IndexList. You can add a field to an existing definition in Visual Basic by using the ReDim function with the Preserve option. And you can use DdfModifyTableName and DdfModifyLocation to alter the table either in the current memory image or in the DDF file itself. For example:
►To add a field to an existing table definition
Option Base 0
Dim fields as Variant
'Get the current information directly from the DDF
VAccess1.RefreshLocations = True
'Set the VAccess to the correct DDF & the correct table
VAccess1.DdfPath = “c:\myData”
VAccess1.TableName = “TableShortAField”
'Get the fieldList
fields = VAccess1.FieldList
'Redimension it & add a field
ReDim Preserve fields(7, UBound(fields, 2) + 1)
'now we have the same array as before, but with an extra, 'empty field at the end
'define the empty field
fields(1, UBound(fields, 2)) = “NewComment”
fields(2, UBound(fields, 2)) = 0 'String
fields(3, UBound(fields, 2)) = VAccess1.DataLength
fields(4, UBound(fields, 2)) = 20 ’20 byte string
fields(7, UBound(fields, 2)) = “This is a new comment”
'We don’t want to write this change to the file just yet
VAccess1.RefreshLocations = False
'Save the empty field to the ActiveX memory image
VAccess1.FieldList = fields
....
<Do more DDF processing>
....
'Ok, our table is set up like we want it. Let’s change the 'name of the table
DdfModifyTableName “TableWithAllFields”
'We want the next operation to affect the DDFs directly, 'so we could set RefreshLocations.
'However, DdfAddTable only affects the DDFs (and not 'the memory image), so it
'ignores RefreshLocations (see DdfAddTable in the 'Reference section).
'Save the table to the file – overwrite the existing 'definition
DdfAddTable True
Note The changes demonstrated in the previous example affect the DDF only. If the table itself does not have room for this extra field, the field will not exist in the file itself. See the following section for information on altering the data file’s structure.
Creating a Table Definition
Creating a table in the ActiveX access method memory image (and in the DDFs) can be useful when a data file has no DDF entry associated with it or when creating a new table. The process includes three basic steps:
1 Create an empty table in the ActiveX access method memory image.
2 Populate the ActiveX access method memory image with the appropriate table, field, and index information.
3 Save the new table to the DDFs.
If you are creating a new table, this would be followed by a fourth step, creating the data file.
Note In the same directory, no two files should share the same file name and differ only in their file name extension. For example, do not name a data file Invoice.btr and another one Invoice.mkd in the same directory. This restriction applies because the database engine uses the file name for various areas of functionality while ignoring the file name extension. Since only the file name is used to differentiate files, files that differ only in their file name extension look identical to the database engine.
When creating an empty table, the ActiveX access method must know that the working table should not exist in the current memory image and that all the field and index information should be cleared out. DdfAddTableName serves this function. Unlike DdfAddTable (which adds the current table memory image to the DDF) or DdfModifyTableName (which changes the current table name but maintains all other information), DdfAddTableName creates an extra slot in the current table list and clears out all of the field and index information associated with the blank table. It is recommended that RefreshLocations be set to False when this is done, so as not to create an empty table in the DDFs.
The new, empty memory image can be populated with the DdfModifyLocation, FieldList, and IndexList operations. It can then be written out to the DDFs with DdfAddTable.
►To add a new table to a dictionary
Option Base 0
'Get the current information directly from the DDF
VAccess1.RefreshLocations = True
'Set the VAccess to the correct DDF
VAccess1.DdfPath = “c:\myData”
'We now want to work only with the memory image while we 'set up the table
VAccess1.RefreshLocations = False
'Create the new table slot in the current memory image
VAccess1.DdfAddTableName “NewTable”
'Set the Location
VAccess1.DdfModifyLocation “newTable.mkd”
....
<set the field and index information>
....
'Save the table to the file – do not overwrite the existing 'definition
'because there should be no existing definition
DdfAddTable False
Note An entire table definition can be dropped with one call to DdfDropTable. As might be expected, this operation suggests a high degree of finality and should be used with caution.
Creating a Dictionary
There are times when the application should create the dictionary itself. For instance, an application could be written to create the DDFs it needs the first time it is run, removing the need of the vendor to ship (and update) DDFs with the application. There is a method that will perform this task—DdfCreateDictionary. It creates an empty set of dictionary files (including COMMENT.DDF and FIELDEXT.DDF) in the DdfPath directory.
Note If the overwrite parameter is True, DdfCreateDictionary will irrecoverably destroy any DDF in the current directory, so use with caution.
Modifying a Data File
All of the changes discussed so far have been exclusive to the DDFs or the ActiveX memory image of the DDFs. Changing the DDFs, however, does not alter the length or structure of the actual records in the data file. To do this, you need to make use of the functions that directly affect the data file.
Changing the length of a data file is not a one-operation issue. Essentially, the data file must be destroyed and rebuilt with the new length. If this is done to an existing file, the records should be copied using two VAccess controls and a temporary file. For example:
►To add a field to a table and a data file
Dim totalRecs as Integer
Dim currRec as Integer
'We want to read the DDFs
VAccess1.RefreshLocations = True
VAccess2.RefreshLocations = True
'Set up our DDFPaths
VAccess1.DdfPath = “c:\myData”
VAccess2.DdfPath = “c:\myData”
'Set up the tables
VAccess1.TableName = “MyTable”
VAccess2.TableName = “MyTable”
'Now use the memory images only
VAccess1.RefreshLocations = False
VAccess2.RefreshLocations = False
'Add the new field to the VAccess2 memory image using 'routine shown above
AddNewField
'Create the actual data file & update the DDF
'The VAccess1 memory image will not be altered
VAccess2.RefreshLocations = True
VAccess2.DdfCreateTable True
'Temporarily assign this table to another location
VAccess2.RefreshLocations = False
VAccess2.DdfModifyLocation “tempdata.mkd”
'Open the files & get the first record
VAccess1.Open
VAccess2.Open
VAccess1.GetFirst
'Copy the records from one to the other
totalRecords = VAccess1.TotalRecords
currRec = 0
While currRec < totalRecords
VAccess2.Buffer(0) = VAccess1.Buffer(0)
VAccess2.Insert 'add error handling here
VAccess1.GetNext
currRec = currRec + 1
Wend
VAccess1.Close
VAccess2.Close
'Now we have two data files – one with the old data & one 'with the new. Use language API to delete the old data 'file and rename the new data file to the old name RenameTempDataFile
'Reset the location to the correct location – don’t change 'DDF, as it should be fine already VAccess2.DdfModifyLocation VAccess1.Location
There are other modifications that can be performed directly to the index structure of the data file. These operations can be found in
ActiveX Method Reference and include
DdfAddIndex,
DdfDropIndex, and
DdfDropIndexMaintain.
Other DDF Manipulations
The methods detailed in this section can be used to handle DDFs for various other purposes.
Securing DDFs with an Owner Name
You can secure DDFs with an owner name. Two methods are provided to deal with this possibility:
•DdfSetOwner. This method sets the current working DDF owner name in the ActiveX memory image (i.e., all DDF file operations will use this owner name). If RefreshLocations is True, this owner name will be applied to the DDFs themselves using the previous DDF owner name for access when making this call. This method does not affect the OwnerName property, which is used only in the context of the data file.
•DdfTestOwnerName. You can use this method to set the working DDF owner name and determine the permissions it will allow.
See
ActiveX Method Reference for more information on these two methods.
Deleting Existing Field and Index Information
DdfClearFields and DdfClearIndexes provide a quick and convenient way for you to wipe out the existing field and index information for the current table.
Note If RefreshLocations is True, these changes will be written to the DDFs, so use with caution.
Forcing Comments with Persisted DDF Memory Image
DdfGetComments. This method has a very limited use. If RefreshLocations is False, the persisted memory image will be read when opening a table rather than the DDFs. However, the persisted memory image does not contain comment information. DdfGetComments ignores RefreshLocations and thus you can use this method to force the ActiveX to get comment information to go with persisted DDF information.
ActiveX Methods with Wide-Ranging Effects
Finally, the following two methods have wide-ranging effects on the DDF handling operations:
•DdfTransactionMode. If this method is True, all DDF handling functions that are requested to write directly to the DDFs will do so within the scope of a transaction. This means that if one part of the write fails, all of it will fail, preventing a busted relation between, for example, FIELD.DDF and INDEX.DDF.
•DdfKeyUseIndexDDFOnly. This method forces the control to use INDEX.DDF only when it is set to True. Normally, the ActiveX will augment the DDF information with the index information stored in the file itself (as given in the status buffer); this makes the control more robust when dealing with incorrect INDEX.DDF entries. However, this behavior also makes it difficult to use the control to repair incorrect INDEX.DDF entries, since the actual DDF information is partially overwritten when the file is opened.
If DdfKeyUseIndexDDFOnly is set to True, the control will not overwrite any of the index information with the index information in the status buffer. This means that if there is an incorrect INDEX.DDF entry, the control will be unable to navigate through the file, but will reflect the current state of the DDF. This makes it easier to diagnose and repair the incorrect entry.