To use Internet Database Connector, you must install the ODBC drivers that are shipped with Internet Information Server or Personal Web Server. You then add database query statements to an Internet Database Connector (.idc) file. Finally, you create an HTML extension file (.htx) that contains HTML tags to format the returned data. The .htx file is returned to the browser.
The Internet Database Connector requires 32-bit ODBC drivers, which are installed with the Web server.
The .idc file contains the information to send queries to a database. An .idc file must contain the following information:
Datasource: Web SQL
Username: sa
Template: sample.htx
SQLStatement: +SELECT au_lname, ytd_sales + from pubs.dbo.titleview + where ytd_sales>5000
For a description of the fields allowed in an .idc file, see Internet Database Connector Fields.
To return data to the browser, the Internet Database Connector merges the HTML extension .htx file and the ODBC data. This combined data is attached to standard HTTP headers (200 OK status, Content-Type, and so on) and passed to the WWW service and returned to the client.
The .htx file is an HTML document with some additional tags enclosed by <%%> or <!--%%-->, which Internet Database Connector uses to add dynamic data to the document. The HTML formatting in the .htx file typically formats the data being returned. There are six keywords (begindetail, enddetail, if, else, endif, and “%z”) that control how the data from the database is merged with the HTML format in the .htx file. Database column names specify what data is returned in the HTML document. For example, the following line in an .htx file merges data from the Emailname column for every record processed:
<%begindetail%><%Emailname%><%enddetail%>
The following example shows a complete a complete HTML document that contains IDC tags for data returned from the database (the tags are shown in bold for clarity). Some HTML formatting has been removed to highlight the IDC tags.
<HTML> <BODY> <HEAD><TITLE>Authors and YTD Sales</TITLE></HEAD> <%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%> <P> <%begindetail%> <%if CurrentRecord EQ 0 %> Query results: <B>Author YTD Sales<BR></B> <%endif%> <%au_lname%><%ytd_sales%> <%enddetail%> <P> <%if CurrentRecord EQ 0 %> <I><B>Sorry, no authors had YTD sales greater than </I><%idc.sales%>.</B> <P> <%else%> <HR> <I> The Web page you see here was created by merging the results of the SQL query with the template file Sample.htx. </I> <%endif%> </BODY> </HTML>
The <%begindetail%> and <%enddetail%> sections delimit where rows returned from the database will appear in the document. Columns returned from the query are surrounded by <%%>, such as <%au_lname%> and <%ytd_sales%> in this example.
To run the IDC query, you usually embed a link to the .idc file in an HTML file. For example, the following HTML statement sends a request for the file dbsamp.idc to the Web server.
<A HREF="http://webserver/scripts/dbsamp.idc">Click here to run query</A>
When the Web server receives the request, it uses the Web site's application mappings properties to associate the file name extension (.idc) with the program used to process IDC files (Httpodbc.dll). The Web server calls Internet Database Connector (Httpodbc.dll), which connects to the specified data source and sends the SQL statements to the database. When the SQL statements have been executed, Internet Database Connector merges the returned data into the .htx file. Internet Database Connector sends the merged document back to the Web server, which returns it to the browser.
By default, the file name extension .idc was mapped to the Internet Database Connector application when you installed Internet Information Server or Personal Web Server. You can add, remove, or edit application mappings as needed. For information, see Setting Application Mappings.
You can construct more powerful database queries by passing parameters to the .idc file. Parameters are the names and values of HTML-form controls, such as the Submit button, and names specified directly in URLs. These names and values are sent by Web browsers and can be used in SQL statements on the server. Note When passing parameters, spaces or escape characters such as ?, &, and % can often be problematic. When specifying a field name that will be used as a parameter in another IDC query, use the "%z" escape sequence to specify a field name as:
For example, following SQL statement returns only the authors whose year-to-date sales exceeded 5000:
+SELECT au_lname, ytd_sales + from pubs.dbo.titleview + where ytd_sales>5000
By using a parameter, you could build a Web page that asks the user to decide what number to use instead of 5000. The Web page must prompt the user for the year-to-date sales figure and then name the associated variable to sales. The following example shows an HTML form with an input field used to obtain the number:
<FORM METHOD="POST" ACTION="/scripts/sample2.idc"> <P> Enter YTD sales amount: <INPUT NAME="sales" VALUE="5000" > <P> <INPUT TYPE="SUBMIT" VALUE="Run Query"> </FORM>
In the IDC file (Sample2.idc), you use the parameter shown in bold in place of the number 5000:
SQLStatement: +SELECT au_lname, ytd_sales + from pubs.dbo.titleview + where ytd_sales > %sales%
Here the parameter name must be sales so that it corresponds to the <INPUT NAME= "sales"> on the Web page. Parameters must be enclosed with percent characters (%) to distinguish them from a normal identifier in SQL. When the Internet Database Connector encounters the parameter in the .idc file, it substitutes the value sent by the Web browser and then sends the SQL statement to the ODBC driver.
The percent character (%) is also a wildcard character in SQL. You can use wildcards in an SQL query to search for an element in a table that contains certain characters. To insert a single percent character for a SQL wildcard, use %%. This prevents IDC from trying to use the percent character as a parameter marker. For example:
SQLStatement: +SELECT au_lname, ytd_sales, title + from pubs.dbo.titleview + where title like '%%%title%%%'
For a percent sign to be recognized as an SQL wildcard you must double it and then add the percent characters around the parameter to distinguish the string as a parameter. In the example, the query searches for all entries in the title column with the word title in them. This query returns the following:
title title and deed main title page author and title
To return all entries with the word title as the first five letters, you would format the query as follows:
SQLStatement: +SELECT au_lname, ytd_sales, title + from pubs.dbo.titleview + where title like '%title%%%'
In this example, the following results are returned:
title title and deed
To return all entries with the word title as the last five letters, you format the query as follows:
SQLStatement: +SELECT au_lname, ytd_sales, title + from pubs.dbo.titleview + where title like '%%%title%'
In this example, the following results are returned:
title author and title
When an HTML form containing a <SELECT MULTIPLE…> tag is used, Internet Database Connector converts the items selected into a comma-separated list; the list can be used in the .idc file just like other parameters. However, because the parameter is actually a list, it will typically only be used for SQLSelect statements with an IN clause, as in the following examples.
If the parameter name in the .idc file is enclosed in single quotation marks, each element of the list will be enclosed in single quotation marks also. You should enclose the parameter name in single quotation marks whenever the column in the IN clause is a character column or other type in which literals are quoted (dates and times, for example). If there are no single quotation marks around the parameter name, no quotation marks will be placed around each element of the list. You should not enclose the parameter name in single quotation marks when the column in the IN clause is a numeric type or any other type in which literals are not enclosed in single quotation marks.
For example, if an HTML form contained the multiple-choice list box shown below:
<SELECT MULTIPLE NAME="region"> <OPTION VALUE="Western"> <OPTION VALUE="Eastern"> <OPTION VALUE="Northern"> <OPTION VALUE="Southern"> </SELECT>
You can construct an .idc file with an SQL statement:
SQLStatement: SELECT name, region FROM customer WHERE region IN ('%region%')
If the user selected “Northern,” “Western,” and “Eastern” from the HTML form, the SQL statement would be converted to:
SELECT name, region FROM customer WHERE region IN ('Northern', 'Western', 'Eastern')
Another example of an HTML form is shown below, but this time uses numeric data, and therefore no quotation marks enclose the parameter in the .idc file.
<SELECT MULTIPLE NAME="year"> <OPTION VALUE="1994"> <OPTION VALUE="1995"> <OPTION VALUE="1996"> </SELECT>
You can construct an .idc file with an SQLStatement:
SQLStatement: SELECT product, sales_year FROM sales WHERE sales_year IN (%year%)
If the user selected “1994” and “1995” from the HTML form, the SQL statement would be converted to:
SELECT product, sales_year FROM sales WHERE sales_year IN (1994, 1995)
In an .idc file, you can group SQL queries in two ways, as batch queries or as multiple queries.
If you are querying databases that can simultaneously process several queries in a SQL statement (such as SQL Server database), you should format your statements in batch query syntax to optimize performance. For example:
SQLSTatement: +insert into perf(testtime, tag) values (getdate(), '%tag%') +SELECT au_lname, ytd_sales from pubs.dbo.titleview where ytd_sales>5000 +SELECT count(*) as nrecs from pubs.dbo.titleview where ytd_sales>5000
If you are querying databases that cannot process a series of SQL queries simultaneously, then formulate your queries as multiple queries. For example:
SQLStatement: +insert into perf(testtime, tag) values (getdate(), '%tag%') SQLStatement: +SELECT au_lname, ytd_sales from pubs.dbo.titleview where ytd_sales>5000 SQLStatement: +SELECT count(*) as nrecs from pubs.dbo.titleview where ytd_sales>5000
Batch queries are processed together at once, whereas multiple queries are processed one at a time. Therefore, you will get better performance by formatting your queries as a batch if your database can handle batch queries.