Tuesday, October 14, 2003
ADO and OLE DB are part of Microsoft's universal data access strategy. These technologies make it very easy to retrieve, view and modify information in a table or database. However, most ADO implementations do not include native support for index operations. In-fact ADOX was developed for the Microsoft Jet Provider specifically to provide index management functionality.
Advantage provides a rich API for creating and maintaining indexes. Most of these API calls require Connection or Table handles that are available from the Advantage OLE DB provider. Index operations will be demonstrated using Visual Basic code.
First Steps
Many index operations (e.g., creation and reindex) require exclusive use of a table. To open a table exclusively with ADO, you must set the Mode property of your connection component to adModeShareExclusive (see the code example below).
When using the Advantage OLE DB provider you must also open the table using the adCmdTableDirect option in order to open a table for exclusive use
We will also be using the Advantage Client Engine (ACE) API calls, so we will need the ACE Connection and ACE Recordset handles. You will also need to add ACE.BAS to your project.
|
Dim lhConnection as LongDim cnExclusive as ADODB.Connection' initialize the connection objectSet cnExclusive as New ADODB.Connection' open the connection to the servercnExclusive.Mode = adModeShareExclusivecnExclusive.Provider = Advantage.OLEDB.1cnExclusive.Open "Data Source=\\server1\share1\data"' get the connection handlelhConnection = cnExclusive.Properties.Item( "ACE Connection Handle" ) Dim lhTable as LongDim rsADS as ADODB.Recordset' initialize the recordset objectSet rsADS as New ADODB.Recordset' open the tablersADS.Open "MyTable", cnExclusive, adOpenDynamic, adLockPessimistic, adCmdTableDirect' get the recordset handlelhTable = rsADS.Properties.Item( "ACE Recordset Handle" ) |
Reindexing
Now that the table is opened exclusively, you can reindex the table. AdsReindex rebuilds all the indexes associated with the open table. You can also use AdsReindex61 to rebuild the indexes and change the index page size (.ADI indexes only). The default page size is 512 bytes with a maximum value of 8192 bytes. If you want to create an ADI index with the maximum page size, we recommend that you use the ADS_MAX_ADI_PAGESIZE constant defined in ACE.BAS.
|
Dim lReturn As Long ' Return value of ACE API' Reindex the tablelReturn = AdsReindex( lhTable )' or you can use AdsReindex61 lReturn = AdsReindex61( lhTable, 2048 ) If lReturn = 0 Then MsgBox "Reindex complete"Else ' error you can display a custom message here End If |
Advantage has the ability to perform a progress callback during index operations. There are examples of this functionality available on the Advantage Developer Zone (DevZone.AdvantageDatabase.com). Choose the Examples link from the Downloads menu.
Common Index Operations
In day-to-day operations, you may have a need to list the available indexes on a table so the user can sort the data. ADO allows for setting of the active index using the Recordset's Index property. However, you cannot get the list of available indexes through ADO.
To get a list of available indexes, you will need to use two ACE API functions. First, use AdsGetAllIndexes to get handles for all the available indexes. Next, use the AdsGetIndexName API to get the names for all the returned handles. You do not need to have the table opened exclusively to make these API calls.
|
Dim lReturn As Long ' Return value of ACE APIDim ahIndex( ADS_MAX_TAGS ) As Long ' Array for index handlesDim asTagNames( ADS_MAX_TAGS ) as String ' Array for index namesDim iNumTags As Integer ' Tag countDim iTagLen As Integer ' Length of tag nameDim x As Integer ' Loop variableDim sTagName As String ' Buffer for tag name ' initialize the tag numberiNumTags = ADS_MAX_TAGS' get the index handleslReturn = AdsGetAllIndexes( lhTable, ahIndex(0), iNumTags ) If lReturn <> 0 Then ' error you can display a custom message here Exit SubEnd If ' Get the tag namesFor x = 0 To iNumTags - 1 ' Initialize the variables iTagLen = ADS_MAX_TAG_NAME sTagName = String( iTagLen, " " ) ' Fill the string with spaces lReturn = AdsGetIndexName( ahIndex(x), sTagName, iTagLen ) If lReturn = 0 Then asTagNames(x) = sTagName Else ' error you can display a custom message here End IfEnd For |
For additional code samples and example applications, please visit the Advantage Developer Zone at http://DevZone.AdvantageDatabase.com.
