-20-

The IIS Internet Database Connector (IDC)

The IIS Internet Database Connector is an ISAPI DLL that can provide a Web application access to database information through an associated ODBC driver. This chapter expands on the knowledge you gained in previous chapters and provides the fundamental information needed to begin building Web-based applications for accessing ODBC databases.

Using the Internet Database Connector

The IDC is an Internet Server API (ISAPI) DLL used to retrieve information from ODBC databases. The IDC translates queries into SQL statements by replacing SQL statement variables with user-input data, which is then passed to the respective database driver for processing, as shown in Figure 20.1.

NOTE: The associated file that provides the IDC ODBC access is HTTPODBC.DLL and is automatically installed with IIS.

FIGURE 20.1. IDC access through HTTPODBC.

IDC uses simple ASCII text files with an .idc extension that contain such information as the data source, HTML extension files (.htx), SQL statements, and so on.

TIP: The latest release of Microsoft FrontPage 97 includes a wizard for generating the code required to build IDC result documents.

This section focuses on how to set up the proper IDC files and HTML extension files required to integrate database access into a WWW application.

Required IDC Data File Fields

All IDC (.idc) files must have three required fields in order to properly handle the retrieval and formatting of database information: Datasource, SQLStatement, and Template. Each of these fields is explained in this section. Datasource The Datasource field is the ODBC data source that identifies the database connection information specific to your application.

NOTE: The Datasource field used with the IDC requires that the ODBC resource be set up as a system DSN. System DSN capabilities were introduced under ODBC 2.5, which is included with Microsoft IIS.

If the DSN resource for a specific database is named mydatabase, for example, the IDC Datasource entry looks like this:

Datasource: mydatabase

SQLStatement The SQLStatement field identifies the actual SQL query that will be executed by the IDC. Parameter values can be contained in SQLStatement. If used, parameter values must be enclosed in percent (%) characters. If a SQLStatement entry will span multiple lines, subsequent lines must begin with a plus (+) sign. Here's a sample SQLStatement field:


SQLStatement:
+ SELECT FirstName, LastName
+ FROM Employees, Orders
+ WHERE Employees.EmployeeId = Orders.EmployeeId
+ AND OrderId = `%orderid%'

Template The Template field is the name of the HTML extension file that contains the normal HTML code with the extended database-definition information. The main Web page for an application might use the template filename main.htx, for example. The entry in the corresponding .idc file looks like this:

Template: main.htx
NOTE: By convention, template files use the filename extension .htx and generally use the same prefix as the corresponding .idc file (main.idc and main.htx, for example).

Optional IDC Data File Fields

Additional fields are available for inclusion in the IDC file. These fields contain information such as default parameters, caching expiration settings, maximum field sizes, maximum number of rows returned, username, password, required parameters, and content type. This section explains each of the optional fields. Content Type The Content Type field specifies the Multipurpose Internet Mail Extensions (MIME) type that the query will return. By default, the standard text/html MIME type is returned. The following code specifies the MIME type for a GIF image, for example:

Content Type: image/gif

DefaultParameters The DefaultParameters field specifies default values for designated entries. You can use this field to specify column default values for table columns that are configured as NOT NULL, for example. A sample DefaultParameters field looks like this:

DefaultParameters: payment=creditcard

Expires The Expires field specifies the number of seconds the IDC waits before refreshing a cached output page. For scenarios in which multiple identical queries are executed, the IDC can return the cached values without having to access the database.

NOTE: By default, the IDC does not cache output pages. It does so only when an Expires field is specified.

To have a document cached for one hour, for example, you can specify the following Expires field in the .idc file:

Expires: 3600

MaxFieldSize The MaxFieldSize field specifies the maximum number of bytes allocated by the IDC for each field in the .idc file.

NOTE: The default field size is 8,192 bytes. If a query results in more bytes than allocated by the IDC MaxFieldSize field, subsequent data is truncated.

To specify a MaxFieldSize of 64,000 bytes, use the following syntax in the MaxFieldSize field entry:

MaxFieldSize: 64000

MaxRecords The MaxRecords field specifies the maximum number of records the IDC returns for each user query.

CAUTION: The MaxRecords field has no default value. If the value is left unspecified, a single query could return as many as 4 billion records.

Take a look at a sample MaxRecords field entry that returns no more than 500 records:

MaxRecords: 500

ODBCOptions The ODBCOptions field specifies any advanced options to be passed to the database-specific ODBC driver. When multiple options are specified, they are separated by a comma. Table 20.1 lists the advanced ODBC options available. A sample ODBCOptions entry looks like this:

ODBCOptions: SQL_ACCESS_MODE=1, SQL_MAX_ROWS=100, SQL_QUERY_TIMEOUT=60

Table 20.1. Advanced ODBC Options.
Option Value(s) Description
SQL_ACCESS_MODE 0=read/write Determines whether updates to the
database are allowed. Default=0
1=read only
SQL_LOGIN_TIMEOUT Integer Specifies the number of seconds to wait for a response before returning an error message. If set to 0, HTTPODBC waits indefinitely. When the ODBC data source also has a specified time-out, the lesser value is used. Default=0
SQL_MAX_LENGTH Integer Specifies the maximum number of bytes returned from a binary or character column. Note that this option is available only with drivers that support it.
SQL_MAX_ROWS Integer Specifies the maximum number of rows returned from a single SQL query. A value of 0 returns all rows. Default=0
SQL_NOSCAN 0=search for and
convert escape clauses in curly braces ({}).		 Specifies whether escape clauses in the
SQL string are searched for and converted. Escape clauses are enclosed in the SQL string
1=do not search for and convert escapeclauses in the SQL string Searching for escape clauses can degrade query performance. Default=0
SQL_OPT_TRACE 0=trace off Specifies whether ODBC function calls are written to a trace file. A value of 1 causes all ODBC function calls to be written to a trace file. A value of 0 specifies that no calls should be written to a trace file.
1=trace on
Default=0
SQL_OPT_TRACEFILE Filename Specifies the filename in which trace information should be written. Used with the SQL_OPT_TRACE option. Default=SQL.LOG
SQL_PACKET_SIZE Integer Specifies the number of bytes of the network packet size in which the data-base driver and IIS will communicate.
SQL_QUERY_TIMEOUT Integer Specifies the number of seconds to wait for the SQL statement to execute and return results. If the SQL_QUERY_ TIMEOUT number of seconds is reached, an error message is returned. A value of 0 results in no time-out.
SQL_TRANSLATE_DLL Filename Specifies the name of the DLL file that contains the SQLDriverToDataSource and SQLDataSourceToDriver functions, which are used by the database driver to complete tasks such as character translation.
SQL_TRANSLATE_OPTION Integer Used with the SQL_TRANSLATE_DLL option. Consult the specific translator documentation for option values.
SQL_TXN_ISOLATION 1=read uncommitted Specifies the transaction isolation level. For more information on thisoption, see the SQL documentation.
2=read committed
4=repeatable read
16=versioning
Integer Driver-specific Enables you to specify special options supported by some drivers. You can specify these by using the option number, as in number=value.


Password The Password field specifies the password to be used with the Username field. If the password for the specified username is null, this field is not required. The syntax for the Password field follows:

Password: webpassword

RequiredParameters The RequiredParameters field specifies those parameters that must contain data when returned from the client browser. If a required parameter is not returned, the IDC returns an error to the client browser. A sample RequiredParameters field looks like this:

RequiredParameters:  logon, password

Username The Username field specifies the user account name to use when connecting to the ODBC resource.

NOTE: If you specified the Integrated Security option when using Microsoft SQL Server, the username and password fields in the .idc file are ignored.

A sample Username field entry specifying the logon webaccess looks like this:

Username:  webaccess

Using HTML Extension (.htx) Files

Hypertext extension files are used by Microsoft IIS to identify specially formatted HTML documents, including the special tags used to handle such items as looping constructs for multidocument result sets and if-then-else control blocks.

.htx File Tags

In addition to standard HTML tags and data, .htx files can consist of special HTML-like tags that permit the result-set documents to be processed and output to be generated based on the conditions that the tags validate. In this section, you learn how to use these tags to identify the block of HTML code that will be processed for each result-set document, to perform if-then-else test conditions, and to identify special formatting of output. <%begindetail%> and <%enddetail%> Tags The <%begindetail%> and <%enddetail%> tags surround the portion of the HTML extension file in which the query result-set documents will be merged. Each record returned in the result set is processed against the HTML code contained in the Detail section. From a programmer's point of view, the <%begindetail%> and <%enddetail%> tags act much like a for each looping construct because, if no records are returned, the Detail section is ignored. You reference column names (results data) in the Detail section by enclosing them between <% and %> symbols.

NOTE: An HTML extension file can contain only one set of <%begindetail%> and <%enddetail%> tags.

Suppose that the following SQLStatement is specified in the associated .idc file:

SQLStatement:
+ SELECT FirstName, LastName, Address, City, State, ZipCode
+ FROM Employees
+ ORDER BY LastName ASC

The following .htx file code shows how the <%begindetail%> and <%enddetail%> tags are used, as well as how column results are referenced in the Detail section:

<!--
    Process returned record set data and display the abstract and
    other associated record data.
-->
<%begindetail%>
<p><b>
<i>Record #:</i> <%CurrentRecord%><br>.
<i>Name:</i> <%FirstName%> <%LastName%><br>
<i>Address:</i><%Address%><br>
<i>City:</i> <%City%><br>
<i>State:</i> <%State%><br>
<i>Zip Code:</i> <%ZipCode%><br>
</b>
<%enddetail%>

<%if%>, <%else%>, and <%endif%> Tags The <%if%>, <%else%>, and <%endif%> tags form a conditional logic control flow that can be used to determine how records are processed. The most common use of the if-then-else statement is to determine whether any records were returned by the query. If no records are returned, a standard reply can be generated, such as No records found to match query. Please try again.

The .htx file code that uses the built-in variable CurrentRecord to determine whether no records were returned from the query follows:

<!--
    Example htx file code to check the status of the built-in
    variable CurrentRecord to verify when no records were
    returned from the query.
-->
<%begindetail%>
<%if CurrentRecord EQ 0%>
   Query results:
<%endif%>
<p><b>
<i>Record #:</i> <%CurrentRecord%><br>.
<i>Name:</i> <%FirstName%> <%LastName%><br>
<i>Address:</i><%Address%><br>
<i>City:</i> <%City%><br>
<i>State:</i> <%State%><br>
<i>Zip Code:</i> <%ZipCode%><br>
</b>
<%enddetail%>

<P>
<%if CurrentRecord EQ 0%>
   <I><B> No records found to match query.  Please try again.</B></I>
   <P
<%endif%>

The syntax for using the <%if%>, <%else%>, and <%endif%> tags follows:

<%if condition%>
    HTML code
<%else%>
    HTML code
<%endif%>

where condition has the following form:

valueX operator valueY

Table 20.2 displays the list of valid condition operators. Table 20.2. Valid condition operators. Operator Condition (True If) EQ valueX is equal to valueY

LT valueX is less than valueY

GT valueX is greater than valueY CONTAINS Any part of valueX contains the string valueY X and Y may be in the form of built-in variables, column names, an HTTP environment variable name (see the next section for a complete listing of HTTP variables), or a constant.

Integrating IDC with Other Applications

A very interesting and powerful aspect of IDC is its capability and flexibility to be integrated with other applications. Microsoft Index Server is just such an application.

RESOURCE: A good resource for information and examples on how to use Microsoft Index Server can be found in Designing and Implementing Microsoft Index Server, Swank & Kittel, Sams Publishing.

For example, consider an application in which you want your IDC query results to be used to create customized, dynamic, on-the-fly HTML query forms and query restrictions for an Index Server application. Likewise, results from Index Server queries can be manipulated to create dynamic forms for other applications as well. For example, results from an Index Server query could be used to dynamically create SQL statements used to extract information from a database. The possibilities are endless. With some imagination and creativity, you can easily develop applications that integrate Index Server with legacy databases, spreadsheets, statistical packages, and so on.

Integrating IDC and Index ServerA Simple Example

This section presents a simple example of how an IDC application can be used to dynamically create query forms for subsequent use with Index Server. This example uses the IIS Internet Database Connector to extract names of authors of specific types of books from a publisher's database. These names are then used to create a query form that allows users to select an author and provide additional query restrictions. This form is submitted to Index Server, which searches the corpus for documents referencing the author and matching other restrictions entered by the user.

RESOURCE: Additional detailed information about IDC can be found in Chapter 8, "Publishing Information and Applications," in the online documentation included with IIS. Microsoft provides a free GUI-based publishing utility called dbWeb, which is used to quickly publish open database connectivity (ODBC) databases via IIS and IDC on the Web. dbWeb utilizes IDC but does not require extensive knowledge of SQL, HTML, or other programming methodologies. As such, it provides a powerful mechanism for the creation of .idc and .htx files (in addition to the many other database creation and management functions it provides) that can be integrated with your Index Server applications. dbWeb is free and can be downloaded from 
http://www.microsoft.com/intdev/dbweb/dbweb.htm.



Invoking the .idc File .idc files can be invoked by specifying
the path to an .idc file in the action URL for an HTML form or in an href
anchor for an HTML hyperlink. For this example, we have created a link in the frames-based
utility illustrated in Figure 20.2. The following code creates the link to the Database
Integration example.


<a href="/scripts/test_web/idcexample.idc?">



FIGURE
20.2.A frames-based query tool that provides links to a variety of
query examples developed for this book, including an example of an IDC-Index Server
integration.




Clicking the Database Integration link invokes the idcexample.idc file,
which is used by IIS to perform a query against a database. Notice that no parameters
were appended to the URL in this case. However, like .idq files, IDC files
can accept and perform substitutions of parameters passed as appended query strings
or from HTML forms. Contents of the .idc File The .idc file is
used to specify a SQL Statement or statements to be used for querying a database.
There are also several optional parameters and ODBC fields that can be set in .idc
files, but Listing 20.1 illustrates the use of the three required fields (Datasource,
Template, and SQLStatement).


Listing 20.1. Contents
of the idcexample.idc file.


Datasource: Web SQL
Username: sa
Template: idcexample.htx
SQLStatement:
+SELECT au_lname, au_fname, type, title
+FROM pubs.dbo.authors a,  pubs.dbo.titleauthor ta, pubs.dbo.titles t
+WHERE   a.au_id = ta.au_id and ta.title_id = t.title_id and
Â(t.type like `%%comp%%' or t.type like `%%bus%%' or t.title like `%%comp%%')
+ORDER BY au_lname



Datasource refers to the System DSN specified using ODBC application
under Control Panel. In this case, we associated Web SQL with the local Microsoft
SQL Server database on our server.


Template refers to the .htx file used to format the results
from the database query.


SQLStatement refers to the SQL query submitted to the database. The +
signs simply indicate a continuation to the next line in the file. In this example,
the SQL statement queries the publisher's database for all authors of all computer
and business books as well as all books that have a word like computer in the title.
Contents of the .htx File .htx files used with IDC applications
are very similar to those used for Index Server applications. Basically, they specify
how database results are to be formatted as HTML and displayed to the user. Because
they specify HTML output, they can also be used to dynamically create HTML forms
based on the results of the database query. This means that static query forms are
a thing of the past. Instead, forms can be dynamically created and tailored to a
user's functional and informational requirements. Figure 20.3 illustrates the results
of the database query, which were formatted to create a secondary HTML form used
to make an Index Server query.



FIGURE
20.3. Results of a database query
can be formatted in an .htx file to dynamically create an HTML form for
use with Index Server.


Listing 20.2 provides the content of the .htx file used to format the
results of the database query performed by the .idc file in the previous
section. This listing demonstrates how results of an IDC query can be formatted for
use as another HTML form that is in turn used to make queries to Index Server.


Listing 20.2. Contents
of the idcexample.htx file.


<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 2.0">
<title>Authors</title>
</head>
<body bgcolor="#E8E8E8">
<form action="/scripts/test_web/author_queryhit.idq" method="GET">

<center>
<strong>Select an Author From Our Database of Computer-Related Books:</strong>
<BR>
<select name="UserAuthor" size="5">
<%begindetail%>
<option value="<%au_lname%>"><%au_lname%>, <%au_fname%> </option>
<%enddetail%>
</select> <B><EM>OR</EM></B>
</center>
<center>
<strong>Enter an Author's Last Name:</strong><BR>
<input type="text" size="60" maxlength="100" name="UserAuthor2">
<P>
<input type="hidden" name="CiMaxRecordsPerPage" value="10">
<input type="hidden" name="CiScope" value="/">
<input type="hidden" name="TemplateName" value="basic_queryhit">
<input type="hidden" name="CiSort" value="rank[d]">
<p align="center">
    <font size="2" face="Arial"><strong>Enter an optional query restriction
    </strong></font>:</p>
    <div align="center"><center><table border="1" cellspacing="1"
    width="50%" bgcolor="#A4B0BD" bordercolor="#008080"
    bordercolordark="#008080" bordercolorlight="#008080">
        <tr>
            <td align="center" colspan="2" width="80%"><input
            type="text" size="60" maxlength="100"
            name="CiRestriction"></td>
        </tr>
        <tr>
            <td align="center"><input type="submit"
            value="Execute Query"> <input type="reset" name="1"
            value="Clear"></td>
        </tr>
    </table>
    </center></div>
</form>
</body>
</html>



A few notes about this file are as follows:





When the user submits the form, parameters are substituted in the .idq
file referenced, Index Server performs the query, and an .htx report template
file is used to format the results. Figure 20.4 illustrates the output for such a
query.



FIGURE
20.4.The final results of a two-stage query IDC-Index Server application.



The first stage pulled author information from a database and created a form. The
second stage used inputs to the form to perform an Index Server query.



Summary




The IIS Internet Database Connector provides a standardized solution through the
use of ODBC connectivity for accessing databases via Web-based applications. IDC
files (.idc) work with HTML extension files (.htx) to identify
the key components required by IIS to access a database and format query results
based on special tags inserted into the .htx file.