by Wayne Berry
The Internet Database Connector is an ISAPI server extension DLL that Microsoft provides for connecting ODBC connections and the Web server. The DLL's name is httpodbc.dll, and it is referred to as the IDC. With the IDC, Web authors can create dynamic pages that connect to databases through ODBC without writing CGI scripts.
When a URL with the extension idc is called on the server, the Web server turns control over to the IDC server extension. The IDC then loads the file related to that URL; once loaded, the IDC makes the calls described in that file to an ODBC data source. The information the data source returns is put into another file with the extension htx. The htx file acts like a template to display the information from the database. Once the information is formatted in the htx template, the template is passed back to the browser as an HTML page. Both the htx file and the idc file are text files and can be edited with a text editor. Each matching pair needs to be put into the scripts directory or a directory containing execute permissions.
Because the idc file will contain a name and password to the database you're using, make sure that the directory in which the idc files are placed cannot be read. In other words, make sure that the directory has execute, but not read permission, with the IIS Manager.
You can save yourself a lot of time when programming a Web site by using the IDC. Programming to an ODBC data source with a C++ script is somewhat tricky, and because the IDC is text based and doesn't need to be compiled, it is an easy way to retrieve information for a Web page. Second, all string and error are handle by the server extension, which allows the Web author to concentrate on the format and content of the Web page.
There are many ways to use the samples in this book, but the most beneficial way is to actually work through the examples. Because the SQL Server ODBC driver comes with IIS, this is the driver the examples will be based on. First, you must create a database in the SQL Server. Refer to the SQL Server instructions for creating a database. I have supplied a routine to create a table and some sample data in the newly created database. This routine can be run out of ISQL_w, a utility that comes with SQL Server. This routine (shown in Listing 16.1) creates a table called IDCSample. The table contains two attributes: Name and Age. The routine also fills in two rows of the table with 'John Doe' Age 24 and 'Jane Doe' Age 23.
Listing 16.1. Create the sample table.
CREATE TABLE IDCSample ( Name varchar (20) NULL, Age int NULL ) GO INSERT INTO IDCSample (Name,Age) VALUES ('John Doe',24) INSERT INTO IDCSample (Name,Age) VALUES ('Jane Doe',23)
After you have created the database, the table, and the data, you need to make an ODBC data source connect to the database so that the IDC can connect to the database.
The Internet Database Connector requires you to connect to a database through ODBC by using a data source. Name your example data source: WebSql. You can use the IDC to connect to any ODBC data source. Remember that when writing to other data sources, the SQL syntax may change from the example given throughout the chapter. To create an SQL Server data source, follow these steps:
When creating a data source name for the Web server to connect to, make sure that it is a system data source name. The Web server can connect only to system data sources names.
After the data source is created, the IDC can be programmed.
There are four types of IDC variables that can be accessed in the htx, all of which are parsed into the htx file before it is sent back to the browser. These variables are referenced by adding <% before them and %> after them.
Let's begin by showing a query in Listing 16.2.
Listing 16.2. A simple query's .idc file.
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +SELECT Name, Age +FROM IDCSample
Notice that data source is the reference to the database. For these examples, we will be using the data source WebSql, which was created in the previous section "Creating a Data Source." The user name in this example is sa. We assume that the sa user name for SQL Server has no password. If there is a password, an extra line will need to be added that reads
Password: Wow
where Wow is the password. The Web server doesn't need to use the System Administrator Account (sa). The Web server can use any account that has permissions to execute the SQL statement in the idc file.
The URL to execute this IDC example is
http://MYMACHINE/scripts/lst16_2.idc
The IDC will load the file lst16_2.idc and then execute the SQL call in the SQL statement. It will then fill in the Template lst16_3.htx. The template to fill in must be referenced in the idc file.
The SQL statement for this example gets every Name and Age in the table.
To make sure that your SQL statement will work, use ISQL_w. ISQL_w is a query program that comes with SQL Server. Just copy the SQL statement from the idc file, paste it into ISQL_w, and remove the plus signs that are in front of each line. Make sure the correct database is selected and press the Run button. The result set should be the same as the result set for the idc.
Listing 16.3 provides a simple query to demonstrate the Internet database connector.
Listing 16.3. A simple query's .htx file.
<HTML> <BODY> <%begindetail%> Name: <%Name%> Age: <%Age%><BR> <%enddetail%> </BODY> </HTML>
The htx file greatly resembles an HTML file. The difference is that the htx file can use special tags that are parsed out before the page is sent to the server. These IDC tags are not like the regular HTML tags. HTML tags are send to the client where they are used for formatting. IDC tags are used by the IDC so that it knows where to insert information from the query. The page that is sent to the client doesn't have any IDC tags in it. The Internet database connector tags used in this example are
<%Name%> <%Age%> <%begindetail%> <%enddetail%>
All IDC tags start with <% and end with <%. When the IDC makes the SQL call in the example, it will come back with a result set that contains two rows. It then iterates between the <%begindetail%> and the <%enddetail%> IDC tags for each row in the result set. When the IDC reaches <%Name%>, it will parse out <%Name%> and replace it with the actual Name attribute in the row. This is the same for <%Age%> except it will replace <%Age%> with the <%Age%> attribute.
The section of the htx page that looks like this:
<%begindetail%> Name: <%Name%> Age: <%Age%><BR> <%enddetail%>
will look like this:
Name: John Doe Age: 24<BR> Name: Jane Doe Age: 23<BR>
when it is sent to the client from the IDC.
The result from running the example looks like Figure 16.1.
Figure 16.1. The result of a simple query.
The first result set actually comes back before the IDC reaches the <%begindetail%> tag. This is important to know in case you want to use some part of the first row returned for the title of the HTML page and also have the row in a table with the other rows later on in the page. Listings 16.4 and 16.5 illustrate this.
Listing 16.4. An experiment with the <%begindetail%> tag in the .idc file.
Datasource: WebSql Username: sa Template: lst16_5.htx SQLStatement: +SELECT Name, Age +FROM IDCSample
Listing 16.5. An experiment with the <%begindetail%> tag in the .htx file.
<HTML> <BODY> The Name Before the begindetail tag: <%Name%> <BR> <HR> <%begindetail%> The Name Inside: <%Name%><BR> <%enddetail%> <HR> The Name After the begindetail tag: <%Name%> <BR> </BODY> </HTML>
The URL to execute this IDC example is
http://MYMACHINE/scripts/lst16_4.idc
where MYMACHINE is your machine name. (See Figure 16.2.)
Figure 16.2. The results of an experiment.
Notice that the first row in the table can be accessed before the <%begindetail%> tag. This trick increases the flexibility of the IDC.
Viewing all the rows in a particular table is useful, but it is equally useful to be able to select certain rows from the table and to view them.
We can send parameters from an HTML form to the idc file. The idc file then uses these parameters to construct an SQL statement to the database. (See Listing 16.6.)
Listing 16.6. The HTML selection form.
<HTML> <BODY> <FORM ACTION="../scripts/lst16_6.idc" METHOD=POST> <SELECT NAME="Age"> <OPTION>24 <OPTION>23 <OPTION>54 <INPUT TYPE=SUBMIT> </FORM> </BODY> </HTML>
lst16_6.htm will have to be put into the wwwroot directory where it can be read. The action of the form calls the lst16_7.idc, which must be in the scripts directory where it can be executed. You might have to change the path in the ACTION attribute if you have your scripts directory set up differently than the default.
Lst16_6.htm should look like Figure 16.3.
Figure 16.3. Listing 16.6's HTML displayed
Notice that the form in p16_6.htm sends a parameter called Age to lst16_7.idc. We can use the Age parameter to select only those people that are that age. Listing 16.7 shows an .idc file that does this:
Listing 16.7. The idc file.
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +SELECT Name, Age +FROM IDCSample +WHERE Age=%Age%
Notice that the idc is using the template lst16_3.htx, the same file that we used for lst16_2.idc. This is a good example of recycling the files and works perfectly fine.
After the IDC loads the idc file from the hard drive, it substitutes the %Age% variable with the Age that is sent in from the form. It then executes the query on SQL Server. If 24 is submitted as the age, the SQL query looks like this:
+SELECT Name, Age +FROM IDCSample +WHERE Age=24
Because the Age attribute in the database is an integer, it doesn't need to be encapsulated in single quotes. If Age were a varchar or text attribute, the WHERE clause in the idc file would look like this
+WHERE Age='%Age%'
and the actual query would look like this
WHERE Age='24'
if 24 were selected.
If 24 is selected from lst16_6.htm using the example database, the result looks like Figure 16.4.
Figure 16.4.The result of selecting 24 from lst16_6.htm.
If 56 is selected from lst16_6.htm using the example database, the page will be empty because there is no one in the database with an age of 56.
The IDC is only able to handle one result set for each page it displays. This means that if you want to run two SQL statements in the idc file, there must be only one result set returned. The first result set listed in the idc file will be the one that the IDC uses. SQL statements like INSERT and DELETE return empty result sets. If you wanted to INSERT a person into the example table and SELECT all the people in the same table to display, you could not do this:
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +INSERT IDCSample (Name,Age) VALUES ('Joe Junior',3) +SELECT Name, Age +FROM IDCSample
The problem with doing this is that the IDC will display the result set of the INSERT that is blank and will not display the result set of the SELECT statement. The reverse will work, however.
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +SELECT Name, Age +FROM IDCSample +INSERT IDCSample (Name,Age) VALUES ('Joe Junior',3)
The problem with this is that the SELECT statement doesn't include Joe Junior because it isn't inserted into the table until after the SELECT statement is run.
To accomplish this task, you need to merge both SQL statements into a stored procedure on the SQL Server. Listing 16.8 is sample code that is run in the ISQL_w to create the stored procedure that we will be using.
Listing 16.8. A stored procedure for inserting and selecting.
CREATE PROCEDURE LST16_8 AS INSERT INTO IDCSample (Name,Age) VALUES ('Joe Junior',3) SELECT Name, Age FROM IDCSample
Now all we have to do is write an SQL statement to call the stored procedure from the idc file. (See Listing 16.9.)
Listing 16.9. The idc file.
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +EXECUTE Lst16_8
Run lst16_9.idc by typing its URL into the browser. Notice that Joe Junior is inserted in the table and displayed. Now refresh the URL server times and watch Joe Junior data being inserted for every refresh.
Notice that out of the two SQL statements, one returns no rows and the other returns many rows. You cannot have a stored procedure that has two SELECT statements in it. This trick will work with any number of SQL statements as long as there is only one SELECT statement.
If a form is sending the idc file parameters from input tags, the idc file must be able to pass these parameters to the stored procedure. The stored procedure must be able to take parameters. The stored procedure in Listing 16.10 is a variant of Listing 16.8.
Listing 16.10. A second stored procedure.
CREATE PROCEDURE LST16_10 @Name varchar(20), @Age int AS INSERT INTO IDCSample (Name,Age) VALUES (@Name,@Age) SELECT Name, Age FROM IDCSample
Run Listing 16.10 in ISQL_w so that the stored procedure is created.
Now an idc file can be created that calls the stored procedure. (See Listing 16.11.)
Listing 16.11. Calling Lst16_10 from an .idc file.
Datasource: WebSql Username: sa Template: lst16_3.htx SQLStatement: +EXECUTE Lst16_10 @Name="%Name%", @Age=%Age%
Listing 16.12 shows an HTML page that calls the idc file sending the Name and Age that is entered into the page.
Listing 16.12. The .htx file to display the date.
<HTML> <BODY> <FORM ACTION="../scripts/lst16_11.idc" METHOD=POST> Name : <INPUT TYPE=TEXT NAME="Name"><BR> Age: <INPUT TYPE=TEXT NAME="Age"><BR> <INPUT TYPE=SUBMIT> </FORM> </BODY> </HTML>
Remember that you will need to save the .htm file in a directory with read permission, not the same directory as the .idc file. You might have to modify the form action in the .htm file based on where the .idc file is located.
If you are a high-level language programmer, you probably understand about variables, variable passing, and variable casting. If not, this discussion might not make a lot of sense, but it is important.
Notice in the EXECUTE statement that %Name% is wrapped in double quotes; this is because Name is a varchar and can contain spaces. Also notice that in the HTML page, both variables are of type text. They are represented as strings and are passed as strings to the idc file. At the idc file's level, they are still strings and are parsed into the SQL statement as strings. Not until the SQL Server's query engine reads the text-based SQL statement are the variables divided into integers and varchars. When the SQL Server returns the information, the IDC converts the attributes of the rows back to text and parses them into the htx file. The only time you have to worry about variable passing is when you try to insert something into SQL Server and when using the if-then-else clause of the idc. The if-then-else clause will be discussed in another chapter.
Try the htm file; type a name and an age.
The previous example (consisting of Listings 16.10 through 16.12) is riddled with bugs. They are put there on purpose to illustrate some points about the IDC. The bugs come when the users of the HTML page enter in bad data or unexpected data. Remember on the Internet, anyone can reach your HTML page, and anyone can enter data.
Try entering John and Thirty for the name and age. You should get a screen that looks like Figure 16.5.
Figure 16.5. Browser view after inputting Thirty.
This error occurs when SQL server tries to convert thirty to an integer and it can't do it. There are two solutions to this problem. One is to offer a selection box on the HTML page that is full of all the possible ages. The other is to make the Age field a varchar so that anything can be entered in. The last fix means that the users could enter anything; for instance, None of your business. You would then need to sort out the bad data at a later time. If your database is large, extra work is involved to remove the bad data.
Try entering Suzzy and 3.5 for the name and age. Notice that the 3.5 is rounded to 3. This happens because SQL Server rounds the fraction to an integer when it is inserted in the database.
Try entering for the name John's Kid and an age of 5. The entry in the database will look like this: John''s Kid.
The error occurs because the SQL Server thinks that you're referring to the end of a string when a is typed. There is very little we can do about this error, although entering John's Kid works correctly.
Try entering for the name Joseph 'Joe' Doe and an Age of 102. The entry in the SQL Server looks like this: "Joseph `Joe' Doe". The problem here is that the SQL Server changes the first single quote into a `.
Try entering for the name Theodore Roosevelt, Jr. and an Age of 30. Notice that the entry is truncated to Theodore Roosevelt. This is caused by the fact that we allowed the Name to be only 20 characters in SQL Server. When the INSERT command occurs, the name is truncated. The solution to this problem is to change the <INPUT> tag to have a maximum length. This can be set in the HTML page like this:
Name : <INPUT TYPE=TEXT NAME="Name" MAXLENGTH=20><BR>
In the previous section, you learned how to have two SQL statements called from an idc file. The only limitation is that both cannot be SELECT statements. There is a workaround to this problem. If the second SELECT is programmed correctly and we modify the htx file, we can have two select statements in one idc file. This workaround is most useful when used with aggregate functions. In our example reports, it would be interesting to see all the people in the table and their average age. In standard SQL, this would require two SELECT statements:
SELECT Name, Age FROM IDCSample SELECT AVG(Age) FROM IDCSample
These two SQL statements return two result sets; however, if we rewrite the SQL statements, they will return only one result set. (See Listing 16.13.)
Listing 16.13. The idc file.
SELECT Age, Name FROM IDCSample UNION SELECT AVG(Age) Age, 'zzzzzAverage' Name FROM IDCSample ORDER BY Name
The UNION operator was used to bind the two SELECT statements, which causes the output to be only one result set. Run this routine in ISQL_w. Notice that the result set comes back with all the names and ages in order, and the very last row is 'zzzzzAverage' with the Age column being the Average Age. Now let's put the query into an idc file, shown in Listing 16.14.
Listing 16.14. The Average idc.
Datasource: WebSql Username: sa Template: lst16_15.htx SQLStatement: +SELECT Age, Name +FROM IDCSample +UNION +SELECT AVG(Age) Age, 'zzzzzAverage' Name +FROM IDCSample +ORDER BY Name
We will have to do something special with the htx file to divide out the Average from the rest of the rows. (See Listing 16.15.)
Listing 16.15. The Average htx.
<HTML> <BODY> <%begindetail%> <%if Name EQ "zzzzzAverage" %> Average: <%Age%> <%else%> Name: <%Name%> Age: <%Age%><BR> <%endif%> <%enddetail%> </BODY> </HTML>
There are three new IDC tags in this htx.
<%if%> <%else%> <%endif%>
These new tags create an if-then-else clause in the htx. This clause is resolved during runtime on the server side. We will talk more about this in the next section. The output to the client looks like this:
<HTML> <BODY> Name: Jane Doe Age: 23<BR> Name: Joe Junior Age: 3<BR> Name: John Doe Age: 24<BR> <B>Avergage: 17</B> </BODY> </HTML>
Because we told the SQL statement to order by the Name attribute and we named the Average "zzzzzAverage", Average ends up at the bottom of the page where we want it. (See Figure 16.6.)
Figure 16.6. Browser view after running Listing 16.14.
It is possible to have more than one SELECT statement in a single idc file if you bind the SELECT statements with the UNION operator.
Let's take a closer look at the if-then-else expression used in the previous section as one of the sample problems.
<%if Name EQ "zzzzzAverage" %>
When comparing strings, the constant strings need to have double quotes surrounding them, as the "zzzzzAverage" has. Also, the variables coming back from SQL Server do not have to have the <% %> marks around them like the rest of the page. The EQ operator with the string comparison is case-sensitive. The other operators that can be used in the expression are shown in Table 16.1.
Notice that the AND, OR, or NOT operators aren't there. To work around not having a NOT operator, just remove the NOT and flip the false section with the true section. If this were your section of code
<% if NOT Name EQ "Joe" %> <H1>Expression is True</H1> <%else%> <H1>Expression is False</H1> <%endif%>
you would have to change it because there is no NOT operator.
<% if Name EQ "Joe" %> <H1>Expression is False</H1> <%else%> <H1>Expression is True</H1> <%endif%>
You can't nest an if-then-else clause. The following example doesn't work:
<% if Name EQ "Joe" %> <% if Name EQ "Smith" %> <H1>It's Joe Smith</H1> <%else%> <H1>It's only a Joe</H1> <%endif%> <%else%> <H1>This is not Joe</H1> <%endif%>
The reason that dynamic pages are created is because something is changing on a regular basis, and you want to be able to reflect that in your Web page. What is changing could be any of the following: the user, the data in the database, or the date. Let's look at an example of the latter. Suppose that you had a table of names and birthdays and you wanted the Web page to show all the birthdays for today. First, let's create a table in the database that has this information. Use the following routine in the ISQL_w to create the table in our example database WebSql:
CREATE TABLE IDCBirthday ( Name varchar (20) NULL, Birthday datetime ) GO INSERT INTO IDCBirthday (Name,Birthday) VALUES ('John Doe','4 Oct 1971') INSERT INTO IDCBirthday (Name,Birthday) VALUES ('Jane Doe','9 Nov 1967')
This routine creates a table with Name and Birthday attributes, along with two rows. Now let's create an idc file (see Listing 16.16) that finds out if anyone has a birthday today.
Listing 16.16. Comparing dates with an .idc file.
Datasource: WebSql Username: sa Template: lst16_16.htx SQLStatement: +SELECT Name, Birthday +FROM IDCBirthday +WHERE CONVERT(varchar (5),GetDate(),101) = CONVERT(varchar (5),Birthday,101)
We will also need a template to display the birthdays. (See Listing 16.17.)
Listing 16.17. Displaying dates in the .htx file.
<HTML> <BODY> <%begindetail%> Name: <%Name%> Age: <%Birthday%><BR> <%enddetail%> </BODY> </HTML>
In the Where line of the SQL statement, we have converted Birthday to the first five characters. The first five characters are the month in two digits, the day in two digits, and the delimiter. We do this so that we aren't comparing the year or the time of the datetime variable. SQL Server's GetDate() function is used to get the current date; it is also truncated to the first five characters. You need to use SQL Server's Convert() function for all datetime variables because the IDC has no formatting conventions.
Because this query doesn't take any parameters, you can run it right from the browser address box. Open the idc from your Web browser. In most cases, you should see a blank screen. This is because of the day you are running it. Is it the fourth of October or the ninth of November? Try adding to the database another row that has today's date. Use this routine in the ISQL_w to do this:
INSERT INTO IDCBirthday (Name,Birthday) VALUES ('Joe Jr.',GetDate())
Now open the idc in the browser again. Notice that the date and time to the millisecond are displayed for the birthday. The date and time to the millisecond is the default return for SQL Server. To get the data we want, we will have to convert the Birthday to a varchar using the CONVERT function. Replace the SELECT line in Listing 16.17 with
+SELECT Name, CONVERT(varchar (5),Birthday,101) Birthday
Now open the idc in the browser again. Notice that Birthday is now just the month and the day.
CurrentRecord and MaxRecords are variables that are built into the IDC. They can be used in the if-then-else expression in the htx file. You cannot view either CurrentRecord or MaxRecords in the page. Neither <%CurrentRecord%> nor <%MaxRecords%> tags work. MaxRecords is set to the value that MaxRecords is defined as in the idc file. CurrentRecord is a count of the rows that have come down from the SQL Server. For example, before the <%begindetail%> tag and for the first row in the SQL statement, CurrentRecord equals 0. When <%enddetail%> is reached and the IDC goes back to the <%begindetail%> tag for another row, CurrentRecord is incremented by one. If no rows are returned from the SQL statement, the detail section is skipped and CurrentRecord equals zero after the <%enddetail%>, also. Suppose that in the preceding example, we wanted to display the message "No birthdays today" instead of having a blank page. We could use code similar to Listing 16.18.
Listing 16.18. An example of CurrentRecord.
<HTML> <BODY> <%begindetail%> Name: <%Name%> Age: <%Birthday%><BR> <%enddetail%> <%if CurrentRecord EQ 0%> No birthdays today <%endif%> </BODY> </HTML>
To test this, modify lst16_16.idc to use the template lst16_18.htx. You will also have to remove any birthdays that happened today. Use this SQL routine in ISQL_w:
DELETE IDCBirthday WHERE CONVERT(varchar (5),GetDate(),101) = CONVERT(varchar (5),Birthday,101)
Now open lst16_17.idc in your browser, and you should see something similar to Figure 16.7.
This works because if no rows are returned, CurrentRecord still equals zero after <%enddetail%>.
Another good use of the CurrentRecord variable is to add a title row to an HTML table. Listing 16.19 shows an example of such a table.
Listing 16.19. Title rows using CurrentRecord.
<HTML> <BODY> <TABLE BORDER=1> <%begindetail%> <%if CurrentRecord EQ 0%> <TR> <TD><B>Name</B></TD> <TD><B>Birthday</B></TD> </TR> <%endif%> <TR> <TD><%Name%></TD> <TD><%Birthday%></TD> </TR> <%enddetail%> </TABLE> <%if CurrentRecord EQ 0%> No birthdays today <%endif%> </BODY> </HTML>
Here we have taken the data coming back from the SQL statement and put it into a table.
This section of code
<%if CurrentRecord EQ 0%> <TR> <TD><B>Name</B></TD> <TD><B>Birthday</B></TD> </TR> <%endif%>
displays bold titles if there is a least one row and that row is the first row returned.
Notice that the table tags are outside of the <%begindetail%> <%enddetail%> tag. If they were inside, there would be one table for every SQL row instead of one HTML row for every SQL row. The only problem is that if no rows are returned from SQL Server, there will be an empty table returned to the browser. This is not really a big problem because empty tables are not displayed on most browsers.
To test this, modify lst16_16.idc to use the template lst16_19.htx. You will also have to add a birthday to view by using Listing 16.18.
Now open lst16_16.idc in your browser. (See Figure 16.8.)
The MaxRecords variable is best used when there are large numbers of rows returned from the SQL Server and you want to display only a few of them at a time. For the examples of MaxRecords, we will need another table. Use Listing 16.20 in ISQL_w to create another table under the WebSql database.
Listing 16.20. Table creating script for IDCCompany table.
CREATE TABLE IDCCompany ( Id int NOT NULL IDENTITY PRIMARY KEY, Name varchar (20) NULL, Location varchar(2) NULL, ) GO INSERT INTO IDCCompany (Name,Location) VALUES ('Company A','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company B','OR') INSERT INTO IDCCompany (Name,Location) VALUES ('Company C','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company D','OR') INSERT INTO IDCCompany (Name,Location) VALUES ('Company E','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company F','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company G','OR') INSERT INTO IDCCompany (Name,Location) VALUES ('Company H','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company I','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company J','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company K','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company L','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company M','WA') INSERT INTO IDCCompany (Name,Location) VALUES ('Company N','WA')
The table created has an ID that is both an identity and a primary key. This ID will be used to track which records have been viewed already.
Now let's create an idc file (see Listing 16.21) that calls the newly created table.
Listing 16.21. Selecting from the IDCCompany table.
Datasource: WebSql Username: sa Template: lst16_25.htx MaxRecords: 10 SQLStatement: +SELECT Id, Name, Location +FROM IDCCompany +WHERE IDCCompany.Id >= %Id% AND IDCCompany.Location = '%LOCATION%'
Notice that MaxRecords has been defined as 10 in the idc file.
The matching template using MaxRecords is shown in Listing 16.22.
Listing 16.22. Displaying from the IDCCompany table.
<HTML> <BODY> <TABLE BORDER=1> <%begindetail%> <%if CurrentRecord EQ 0%> <TR> <TD><B>Name</B></TD> <TD><B>Location</B></TD> </TR> <%endif%> <TR> <TD><%Name%></TD> <TD><%Location%></TD> </TR> <%enddetail%> </TABLE> <%if 'Id' EQ '0' %> <%else%> <A HREF=lst16_24.idc?Id=<%Id%>&Location=<%Location%>>Next 10 Records</A> <%endif%> <%if CurrentRecord EQ 0%> No Matches <%endif%> </BODY> </HTML>
If, after the <%enddetail%>, an ID is present, we know that <%enddetail%> ended because the maximum number of records was reached. Because there might be more records, we put in an anchor to call the same idc again to get the next set of records. If an ID isn't present, we know that we have all the records. The ID would not be available if MaxRecords were not set in the idc.
<%if CurrentRecord EQ MaxRecords%> <A HREF=lst16_24.idc?Id=<%Id%>&Location=<%Location%>>Next 10 Records</A> <%endif%>
Now create an htm file (see Listing 16.23) to call the idc and save the htm file in the wwwroot directory.
Listing 16.23. HTML to call lst16_24.idc.
<HTML> <BODY> <FORM ACTION="../scripts/lst16_24.idc" METHOD=GET> <SELECT NAME="Location"> <OPTION>WA <OPTION>OR </SELECT> <INPUT TYPE=HIDDEN NAME="Id" VALUE="0"> <INPUT TYPE=SUBMIT> </FORM> </BODY> </HTML>
Notice that we pass a value of zero the first time we call the idc. This makes the SQL statement return the first row that matches the location.
Select WA from the drop-down box and submit it to the idc. (See Figure 16.9.)
Figure 16.9. The first page of Washington companies.
Now click the anchor entitled Next 10 Records. The next set of records should show up, only this time there is only one left. (See Figure 16.10.)
Figure 16.10. The second page of Washington companies.
Go back to the selection screen and select OR; only three entries should show up. (See Figure 16.11.)
Figure 16.11. The only page of Oregon companies.
By changing the structure of the files a bit, we can let the user tell the idc how many files to retrieve at one time. Here is the htm that submits not only the location but also the number of files to display, using the parameter MaxRecords. (See Listing 16.24.)
Listing 16.24. HTML to calls lst16.28 with maximum number of rows.
<HTML> <BODY> <FORM ACTION="../scripts/lst16_28.idc" METHOD=GET> Location: <SELECT NAME="Location"> <OPTION>WA <OPTION>OR </SELECT> <BR> Row to return: 5 <INPUT TYPE=RADIO NAME="MaxRecords" VALUE=5> 10 <INPUT CHECKED TYPE=RADIO NAME="MaxRecords" VALUE=10> 20 <INPUT TYPE=RADIO NAME="MaxRecords" VALUE=20> <INPUT TYPE=HIDDEN NAME="Id" VALUE="0"> <INPUT TYPE=SUBMIT> </FORM> </BODY> </HTML>
Notice that the radio buttons let the user select 5, 10, or 20 records. (See Listing 16.25.)
Listing 16.25. An Example of the MaxRecords attribute being used in an .idc file.
Datasource: WebSql Username: sa Template: lst16_29.htx MaxRecords: %MaxRecords% SQLStatement: +SELECT Id, Name, Location +FROM IDCCompany +WHERE IDCCompany.Id >= %Id% AND IDCCompany.Location = '%LOCATION%'
In Listing 16.26, the MaxRecords input from the HTML form is inserted in the idc as MaxRecords.
Listing 16.26. The matching .htx file for Listing 16.25.
<HTML> <BODY> <TABLE BORDER=1> <%begindetail%> <%if CurrentRecord EQ 0%> <TR> <TD><B>Name</B></TD> <TD><B>Location</B></TD> </TR> <%endif%> <TR> <TD><%Name%></TD> <TD><%Location%></TD> </TR> <%enddetail%> </TABLE> <%if 'Id' EQ '0' %> <%else%> <A HREF=lst16_28.idc? Id=<%Id%>&Location=<%Location%>&MaxRecords=<%idc.MaxRecords%>> Next <%idc.MaxRecords%> Records</A> <%endif%> <%if CurrentRecord EQ 0%> No Matches <%endif%> </BODY> </HTML>
The only change to this file was to enable the user selection for the maximum number of records to be passed in the next records anchor. Each time the idc is called, it needs to know what the maximum number of records is. Notice that the htx uses a tag called <%idc.MaxRecords%> to do this. This tag is equal to MaxRecords that was passed to the idc, or in other words, the original number that was selected in the form. You may reference parameters that are passed into the idc from the form in the htx file by adding idc in front of them. For instance, in Listing 16.26 we could also reference <%idc.Location%>.
The IDC is a simple way to connect the IIS Web server to a database using ODBC. IDC lends itself to rapid application development. The files are text based, making them easy to change and maintain. The format isn't much different than HTML itself, making for a small learning curve. The biggest advantage is that it comes with the IIS; there is no additional cost for hooking the IIS to the database. Unfortunately, its simplicity leads to disadvantages. For all practical purposes, you can have only one query per Web page. Each Web page consists of two files that both have to be loaded from the hard drive for every hit, causing a decrease in performance.