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%';

   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; 

However, because IDC paramters are delimited with precent signs (such as %parameter%), IDC files prefer that a double percent - %% - be used as a wildcard character. When the meaning is not ambiguous, a single percent sign can be used.

   +WHERE "table".fieldname like 'wa%'             Begin with wa
   +WHERE "table".fieldname like '%wa%'            Match the paramter wa
   +WHERE "table".fieldname like '%%wa%%'          Contain wa (test this)
   +WHERE "table".fieldname like '%paramter%%'     Begin with the parameter
   +WHERE "table".fieldname like '%%%paramter%%'   ???
   +WHERE "table".fieldname like '%%%paramter%%%'  Contain the parameter
   +WHERE "table".fieldname like '%paramter%'      Match the parameter

• 'wa%' will match fields that begin with both water and Water
• '%%wa%%' will match fields that contain water, Water, beeswax, and the like
  (I need to test this)

When a field name contains spaces, re-name it with an alias.

   SELECT "Field Name" AS FieldName, "Zip Code" zipcode FROM tableName;

+WHERE abc='Some string'
+  AND SomeDate BETWEEN #7/1/99# AND #3/8/00#
+  AND xyz LIKE 'wa%'

According to the MS ODBC help, when referencing dates in MS Access,

the date "March 5, 1996" must be represented as {d '1996-03-05'} (the ODBC canonical date format) or #03/05/1996#.

   SELECT zipcode, count(*) as xx FROM maillist GROUP BY zipcode;

   Zip Codes  Count
    20111       10
    20222       20
    20232        3
    20521       48

   <a href="<%uniqueURL%>"><%MainTitle%></a>

When the additional information is in your database (rather than at a specific URL), the HTX file can format the link to call a second IDC/HTX pair which will display specific results.

   <a href="/exe/second.idc?ID1=<%uniqueID%>"><%MainTitle%></a>

For example, a form allows you to enter query parameters which are sent to an IDC/HTX pair which provides a summary list (such as books on robots). When you click the name of one of the books, a second IDC/HTX pair is called which presents details about the selected book.

    where fieldname='test'     Is ok
    where fieldname="test"     Generates an error

    where "Table Name"."field name"='test'     Is ok
    where [Table Name].[field name]='test'     May be ok (need to check)

    where ID1=%uniqueID%    Uses a number for the unique ID

   SQLStatement:
   +SELECT iff(len(Description)<100, 
   +   Description, left(Description, 100) + ' ...') as desc
   +FROM table;

    Format ([FieldName], 'Short Date')
    Format ([FieldName], 'Long Date')
    Format ([FieldName], 'Currency')   Adds a Dolllar sign
    Format ([FieldName], 'Percent')
    Format ([FieldName], 'Yes/No')     -1=Yes   0=No

Note: To use the string returned by the Format statement it must be given a name. The standard technique is to use an AS clause.

    Format ([FieldName], 'Short Date') as UniqueName

For formating text, the Format command is ok for forcing upper case or lower case. However, for more extensive control, use the string functions.

Datasource: ODBC Table Name
Username: username
Password: xxxxxx
Template: template.htx
RequiredParameters: parameter1, parameter2
DefaultParameters: numeric1=0, numeric2=0
ODBCOPTIONS: SQL_OPT_TRACE=0, SQL_OPT_TRACEFILE=\TRACE1.txt
SQLStatement:
+exec Stored_Proc_Name '%parameter1%', '%parameter2%',
+ %numeric1%, %numeric2%

• If the text of the IDC file is displayed, then you need to access it via a Microsoft web server and its directory needs to be configured as executable.
• If nothing is displayed when you call the IDC file, then it has an error.
• If the htx file displays without any data, then the query did not return any records.

ODBC connection

Use Start / Settings / Control Panel / ODBC to define the ODBC connection. If a password is needed, be sure to include it.

SQL errors

If the SQL statement contains errors, then only a blank page is displayed. Start with a trivial query until you can get the htx file to display.

The plus sign (+) used to start each line is equivalent to a space.

Commands out of order

If you double click an idc file, a wizard may open. The wizard "aids" you in creating the idc file. However, it places the commands in the wrong order -

Datasource: SQLStatement: Template:

This causes a "file may not exist" error. Simply change the command order to

Datasource: Template: SQLStatement:

to fix the problem. (go Microsoft)

URL: http:// cpcug.org / user / clemenzi / technical / HTML_Examples / IDC_HTX_SQL.htm