Database Access via IDC/HTX


The IDC/HTX file pair is used by IIS to provide web-based database access.
IDC - Internet Database Connector - Uses SQL to perform a query
HTX - HTML Extension Template - HTML template used to format the 
      query results (Actually, this extension can be anything.)

Notes on IDC File

Example working code from Location.idc
   Datasource:coe
   Template:Location.htx
   SQLStatement:SELECT LibraryLocation, 
   +Program , Format("date",'Long Date') as xx1,
   +Age, Registration, ProgramInformation
   +FROM "Calendar of Events"
   +WHERE "Calendar of Events".LibraryLocation='%LibraryLocation%';
Explanation of IDC file
   Datasource:Name of ODBC alias (may contain spaces)
   Template:Name of the template file - normally *.htx
   SQLStatement:SQL select statement,
   + additional lines must start with a plus sign
   + must end with semicolon; 
To convert a date into something usable, include into the Select statement. Notice that Long Date must be in single quotes, not double quotes.

Any string which contains a space must be enclosed in double quotes, single quotes, or square brackets ([test this]). Which is used is currently just trial and error. If an MS Access query produces code with double quotes, and that code won't work, try changing them to single quotes. For instance, '%LibraryLocation%' is enclosed in single quotes because the data returned sometimes contains spaces.

An example of a query with embedded formatting HTML

   Datasource:coe
   Template:Location.htx
   SQLStatement:SELECT LibraryLocation, 
   +'<b>' & Program & '</b><br>
   +' & Format("date",'Long Date') & ', ' & Format("Time(beg)",'Medium Time') 
   + & ' ' & Format("Time(end)",'Medium Time') AS Dtime, 
   +Age, Registration, ProgramInformation
   +FROM [Calendar of Events]
   +WHERE [Calendar of Events].LibraryLocation='%LibraryLocation%';
This allows a field in an HTML table (Dtime) to be formatted with special fonts (bold), a line break, commas, and spaces.

Additional SQL examples are provided here.


Notes on HTX File

In the HTX file, the repeating section must be enclosed in <%begindetail%> <%enddetail%> tags. If the IDC file has multiple queries, then each query should have its own <%begindetail%> <%enddetail%> block. If the database fieldname contains spaces, then use the SQL AS command to rename it. Otherwise, the HTX file can not find it.

A Simple Example

Excerpt from calling HTML form
<form action="dbf.idc" method="POST">
   <p><input type="submit" name="B1" value="Submit">
      <select name="city" size="1">
        <option selected>Denver</option>
        <option>Seattle</option>
        <option>Bufflo</option>
     </select>

</form>

dbf.idc
   Datasource: odbc dbf test
   Template: dbf.htx
   SQLStatement: SELECT DISTINCT COMPANY, CITY
   +FROM CUSTOMER
   +WHERE(CITY = '%city%')
   +ORDER BY COMPANY, CITY
dbf.htx excerpt
   <p><%idc.city%>
   <%begindetail%>
   <p><%city%> *** <%company%>

   <p>
   <%enddetail%>


HTX IF Statement Example


Since returns and tabs are ignored by web browsers, it is strongly suggested that text fields in the database contain the necessary html code to format the data.


Security

Normally, if you simply call an IDC file, it will either be displayed in your browser as a text file or you will be prompted to save it to disk. In order to make the IDC/HTX file pair executable, you must access them via a Microsoft web server - either IIS or the Personal Web Server.

If you plan to develop pages on your local machine, load the Microsoft Personal Web Server which is free from Microsoft. It was originally about 700K. Support for Active Server Pages (ASP) adds another 20 Meg and includes VisualBasic and JavaScript interpreters. Unfortunately, Microsoft no longer provides the smaller 700K file.

For security reasons, web servers won't execute a file unless you tell the server that the file is executable. For the Microsoft servers, this is accomplished by running the Server Administration application. With the Personal Web Server, this is done by double clicking on the icon in the system tray. You need to Add a directory, Assign an alias, and make the directory Executable. Once this is done, the IDC and HTX files should be placed in that directory and referenced as http://serverName/alias/YourName.idc.

There are a number of additional security/permission problems related to getting IDC/HTX database access to work when using Windows NT. (Windows 95 does not have these problems ... but it also has no security :) Basicly, IIS does its work as IUSR_servername which needs full access to several sources including the database files and the system temporary directory. Details


References


Author: Robert Clemenzi - clemenzi@cpcug.org
URL: http:// cpcug.org / user / clemenzi / technical / HTML_Examples / IDC_HTX.htm