Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
    Loop
    .Close
End With

'Housekeeping
Set RS = Nothing
Set Qry = Nothing

End Sub
			
Total article views: 4123 | Views in the last 30 days: 1
 
Related Articles
BLOG

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...

ARTICLE

Stairway to SQL Server Indexes: Level 9, Reading Query Plans

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

BLOG

Determine Index Fragmentation in a SQL Server Database

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

BLOG

Basics of Indexes in SQL Server

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

FORUM

Transfer SQL Server Objects Task

Transfer SQL Server Objects Task

Tags
indexing    
programming    
sql server 7    
visual basic 6    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones