The ActiveX Data Objects Connection object encapsulates a live connection to a data source. To connect to a data source, we use the object's Open method, specifying as a parameter the DSN to which we want to connect:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "MyDSN"
Connecting to an Index Server is very similar, with one crucial difference: The Index Server catalog is not represented by a DSN. This means that there's nothing for us to configure in the Control Panel's ODBC32 applet. This also means that we have to specifically identify the Index Server as our data provider when we initialize our Connection object. The following statements illustrate how to do this:
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "provider=msidxs;"
objConnection.OpenWe now have a live connection to the Index Server. It's time to execute a command against this connection, and create a recordset from it.
Because we've messed around with the ADO before, we come to this part of the Index Server with a little sophistication. We know that we can package up an SQL command in any number of ways, depending largely on the level of efficiency we want to achieve in the query's execution. For example, we can build up an SQL command based on user form input, and then pass the command string to the Connection object's Execute method to retrieve a navigable Recordset. Or we can associate a Command object with an active Connection, and then initialize the Command object's CommandText property to the SQL string.
But regardless of the approach we take to the command's execution, it all begins with the command itself, a string that consists of an SQL query. Since it's also a given fact that we'll build this command based on a site visitor's form input, let's start with a simple form. You can copy this HTML page, which is named sql_sample.htm, from the web site for this book at http://rapid.wrox.co.uk/books/1266/:
<HTML><HEAD><TITLE>Simple SQL Search Form</TITLE></HEAD>
<BODY>
<FORM METHOD="POST" ACTION="sql_sample.asp">
<INPUT TYPE="TEXT" SIZE="20" NAME="SearchString">
<INPUT TYPE="SUBMIT" NAME="ACTION" VALUE="Submit">
</FORM>
</BODY>
</HTML>
This form consists of just a text box named SearchString and a Submit button. When the user clicks the Submit button to post the contents of SearchString, our sql_sample.asp file retrieves the contents of this form field from the Request object, and builds it into an SQL command:
SQL = "SELECT filename, size FROM SCOPE() WHERE CONTAINS "
SQL = SQL & "('" & Request("SearchString") & "'" & ") > 0"Despite the apparent simplicity of this SQL string, it takes into account no less than three of the SQL extensions included with the Index Server. Let's review them.
SELECT keyword identify the friendly names of Index Server fields that we want to retrieve.FROM clause in this query identifies SCOPE() as the source of the data we want to retrieve. Used like this, the SCOPE of this query is open. In the parentheses that follow the SCOPE keyword, we could specify both a catalog and a scope within that catalog. We don't do this (yet), so the catalog used by this query is the system default, and the scope of this query extends to all the directories that are included in the default system catalog.CONTAINS keyword indicates that we're searching based the contents of the documents themselves, rather than on any of their other attributes, such as author, date last written, etc.We'll develop these concepts further in an integrated example that appears later in this chapter. First, though, let's look at how we execute this command.
Remember that we mentioned there were any number of ways to package an SQL command, depending on the efficiency level you want to achieve in the query's execution? Well, the same generalization applies to a Command's execution. However, in executing the command, it's not just efficiency we're after. The idea is to retrieve a Recordset that contains only the capabilities that we need.
For simplicity's sake, we're going to assume here that we're creating a forward-scrolling read-only Recordset. In that case, here's how we execute the command we packaged in the previous section:
Set objRecordset = objConnection.Execute(SQL)
Here's the complete source code of the page that our HTML form posts its data to. It's named sql_sample.asp:
<%
'Make sure the user entered something
If Request("SearchString") = "" Then
Response.Write("No search text entered!")
Response.End
End If
'create the SQL query string
strSQL = "SELECT Filename, size from SCOPE() WHERE CONTAINS "
strSQL = strSQL & "('" & Request("SearchString") & "') > 0"
'create and open a connection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.ConnectionString = "provider=msidxs;"
objConnection.Open
'fill a recordset with the results
Set objRS = objConnection.Execute(strSQL)
If Not objRS.EOF Then
%>
<HTML><HEAD><TITLE>A Simple ADO Search</TITLE></HEAD>
<H3>Here are the results of your search:</H3>
SQL Query is: <%= strSQL %><P>
<TABLE BORDER="0">
<TR>
<TH ALIGN="LEFT">Filename</TH><TH ALIGN="RIGHT">Size</TH>
</TR>
<% Do While Not objRS.EOF %>
<TR>
<TD><%= objRS("Filename") %> </TD>
<TD ALIGN="RIGHT"><%= objRS("size") %></TD>
</TR>
<% objRS.MoveNext %>
<% Loop %>
</TABLE>
</BODY>
</HTML>
<% End If %>
<% Set objRS = Nothing %>
<% objConnection.Close %>
<% set objConnection = Nothing %>And here's the result it produces with our search for 'wrox':
