SQL Access to Index Server Data

Index Server provides an alternative method of issuing queries on an indexed file system. Instead of using Index Server's standard forms, you can write SQL queries in applications that use ActiveX™ Data Objects (ADO). In your .asp files, use the SQL Extensions to form the query, ADO to retrieve the data, and a scripting language such as Microsoft® Visual Basic®, Scripting Edition (VBScript) to display the data.

Description of SQL Extensions

The SQL used with Index Server consists of extensions to the subset of SQL-92 and SQL3 that specifies queries on relational database systems. This SQL includes extensions to the SELECT statement and its FROM and WHERE clauses. It also includes:

A SET statement that maps file-specific properties to column names.
A CREATE VIEW statement that associates a subset of properties with a user-defined virtual table name that can then be queried against.

The SQL Extensions topics describe:

The querying capabilities reflected by the SQL Extensions to Index Server.
The SQL syntax you use when writing queries.
The ADO properties you need to know when manipulating ADO Recordset objects.

The topics contain information on the following SQL statements and clauses:

Index Server Data, ADO, and OLE DB

Because Index Server can function as an OLE DB provider, and with ADO you can consume OLE DB rowset data, you can take the results of an Index Server query and use them in a subsequent query against a different OLE DB provider (such as the ODBC Provider). For example, you can search for all the travel reports in a given directory and then submit a query against SQL Server to find the current travel budget of each trip report author.

The SQL extensions to Index Server do not support cross-data source queries; you need to issue separate queries against Index Server and (for example) the ODBC Provider to SQL Server.

To open a connection to your OLE DB Provider for Index Server, your ADO program needs to set the Provider property of the ADO Connection object to the string "MSIDXS". This property can also be set by the contents of the ConnectionString property or the ConnectionString argument of the Open method. Alternatively, you can just generate a Recordset object and make sure that the associated Open method takes a ConnectionString argument that includes the string "PROVIDER=MSIDXS;". See the ADO Overview for more information.

Examples

An example of the minimal VBScript code needed to generate an ADO Recordset from Index Server is as follows:


   Set rstMain = Server.CreateObject("ADODB.Recordset")
   RstMain.Open "SELECT DocAuthor, DocTitle, FileName 
   FROM SCOPE() WHERE size>50000",
   "PROVIDER=MSIDXS;"

An example of the ADO code that creates a Connection object is as follows:


   Set Conn = Server.CreateObject ("ADODB.Connection")
   Conn.ConnectionString = "provider=msidxs;"
   Conn.Open

You can now create a Command object, associate it to the active connection you opened using the sample code above, set the CommandText property of your Command object to your SQL content query string, and create the associated Recordset to retrieve the results of your query. See the Samples section following for further information.

Samples

You can view sample files that illustrate the use of the SQL extensions. To examine the source files, go to the \INETPUB\IISSamples\ISSamples directory. The files SQLQHit.htm and SQLQHit.asp contain the sample code.

To see these sample pages:

From the Taskbar, click Start, point to Programs, point to Windows NT 4.0 Option Pack, point to Microsoft Index Server, and click Index Server Sample Query Form.

Your computer’s browser displays the Sample ASP Search Form.

In the left frame of the Sample ASP Search Form, click SQL Sample.

The Sample SQL Based Search Form appears.