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: <% "%z" ,FieldName %>.
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