SQLServerCentral Article

Index Server COM Object Programming in Visual Basic


Programming with Index Server

Index Server is a very useful tool for querying text within documents. Index Server has its own query form that may be used from within the application, but if you need to integrate Index Server functionality into a web application or a 'back-end' process, you will obviously need to use it programatically. I had difficulties locating a complete source on 'How To Program with Index Server'. So, hopefully here, I can offer a slightly more well rounded resource than what I found.

My first venture into Index Server was a bit difficult. The SQL syntax for Index Server is slightly different than T-SQL, and the objects' properties and methods are not necessarily intuitive. One significant problem I ran into were the differences between the NT Option Pack 4.0 Index Server object (ixsso.dll) and the Windows 2000 Index Server object (Cisso.dll). Not only are the names different (which throws off your declarations), but some of the defaults are different as well. The major difference between the two versions is the Dialect property. In ixsso.dll, the dialect property defaults to 'free text' mode (1). In Cisso.dll, the default dialect is 'phrase mode' (2). Knowing this can save hours in debugging time.

Two objects are required to program with Index Server, the Query object and the Utility object. In the larger example below, I use the Utility object to specify the scope of the query. The scope is the physical location of the documents. This location should be identical to the directory path listed on Index Server for a specific catalog. I had better luck accessing the catalog when the scope referenced a share to the scope location and not the actual path. Once the scope is assigned to the query object, the Utiliy object may be destroyed. The Query object is more robust. In my example, I use the query object for defining custom columns, assigning the query restriction, choosing the columns to be returned, opening the recordset and setting the dialect property. The query method I use in my example is not the sole method for querying Index Server. You may use the more T-SQL like approach:

Select Filename, Size from scope('shallow traversal of "d:\reports"') where contains
(contents, '"accounting"') > 0

When using this syntax, the scope may be left empty (this will set the scope to the default) or, as in the example above, you may pass arguments to the scope which dictate the 'universe' of the query. The options are 'shallow traversal of' and 'deep traversal of'. 'Shallow' specifies that only the directory listed in the scope argument will be queried. 'Deep' infers that the specified directory and its children will be included in the query. I have not used this method, but in the examples I have found, this method is mostly used with the ado recordset object. Here is some sample code that may help:

Dim rs as new adodb.recordset
Dim CmdText as string
CmdText = "select filename from scope('" & chr(34) & scope_arguments & chr(34 & "' where " & _
query_restriction & " order by " & sort_text

rs.open CmdText, "provider=msidxs", adOpenForwardOnly

For more examples of how to use Index Server and other specifics, MSDN Online and the Windows Help files are a good resource. To view my other articles, feel free to visit my homepage.

Sub IndexServerQuery()

Dim RS As ADODB.Recordset
Dim vRestriction As String
'Index Server objects
Dim Qry As Cisso.CissoQuery
Dim Utl As Cisso.CissoUtil
'instantiate objects
Set Qry = New Cisso.CissoQuery
Set Utl = New Cisso.CissoUtil
'Adds scope to query - physical location of files
Utl.AddScopeToQuery Qry, Scope
Set Utl = Nothing
With Qry
    'Set dialect of query object
    .Dialect = 1
    'Definition of custom properties in Index Server.
    .DefineColumn "pState (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pState"
    .DefineColumn "pUpdtYMD (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pUpdtYMD"
    .DefineColumn "pCountry (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCountry"
    .DefineColumn "pAppID (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pAppID"
    .DefineColumn "pSrcGrp (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pSrcGrp"
    .DefineColumn "pCrType (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCrType"
    .DefineColumn "pYrsExp (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pYrsExp"
    .DefineColumn "pFullTime (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pFullTime"
    .DefineColumn "pSalary (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pSalary"
    .DefineColumn "pDegree (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pDegree"
    .DefineColumn "pCitizenShip (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pCitizenShip"
    .DefineColumn "pRgnIDRsdnt (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pRgnIDRsdnt"
    .DefineColumn "pRgnIDPrfSt (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pRgnIDPrfSt"
    .DefineColumn "pChannelID (DBTYPE_WSTR) = d1b5d3f0-c0b3-11cf-9a92-00a0c908dbf1 pChannelID"
    'colums to be returned by query
    .Columns = "filename, vpath, pState, pUpdtYMD, pCountry, pAppID, pSrcGrp"
    'An example of a query string for Index Server
    vRestriction = "(@pSrcGrp WB) & (@pUpdtYMD >'2000-10-04 00:00:01') " & _
"(@Contents ((unix) or (systems administration) or " & _
"(unix systems administrator)))"
    'Assign query to Query property
    .Query = vRestriction
    'Location of catalog/index files
    .Catalog = Catalog
    'Returns query results in desired sort order - This is ordering by the 
    'pUpdtYMD column in desc order
    .SortBy = "pUpdtYMD[d]"
    'opens recordset - may also use 'nonsequential' as a cursor type option
    Set RS = .CreateRecordset("Sequential")
End With
With RS
    Do Until .EOF
'loop through recordset
End With
Set RS = Nothing
Set Qry = Nothing
End Sub


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating