Creating IDC and HTX Files to View ODBC Data

The .idc file contains the information to send queries to a SQL database. 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 Web service and returned to the client.

The IDC File

An .idc file must contain the following information:

  • The ODBC data source. For example, the following statement identifies the data source called Web SQL:

    Datasource: Web SQL
    
  • Any user name (valid logon name) required to access the data source. For example, the following statement logs on to the "SQLAdmin" account on SQL Server:

    Username: SQLAdmin
    

Caution

It is a security risk to use the SQL "sa" account. After you install SQL, create a new administration account with a complicated name and a strong password. Then, delete the "sa" account. Malicious clients often try to break into servers using the "sa" account because it is well known and has a blank password.

  • The name of the HTML extension file that contains the formatted data to return to the browser:

    Template: sample.htx
    
  • The SQL statement to run. For example, the following statement returns all the author last names and year-to-date sales, in units, from the "pubs" sample database in SQL Server for authors whose books have year-to-date sales of more than 5000:

    SQLStatement: 
    +SELECT au_lname, ytd_sales  
    + from pubs.dbo.titleview 
    + where ytd_sales>5000 
    

The HTX File

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 HTML document that contains IDC tags for data returned from the database. Some HTML formatting has been removed to highlight the IDC tags.

In this example, 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%>.

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