SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Index Server COM Object Programming in Visual Basic

By Jon Winer,

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
Total article views: 4124 | Views in the last 30 days: 1
Related Articles

Querying Microsoft SQL Server : Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...


Reading Query Plans: Stairway to SQL Server Indexes Level 9

Determining how, and if, SQL Server is using your indexes.


Determine Index Fragmentation in a SQL Server Database

Index fragmentation can adversely affect query response time. When accessing data through an index, ...


Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see...


Transfer SQL Server Objects Task

Transfer SQL Server Objects Task

sql server 7    
visual basic 6