Windows NT Internet and Intranet Development

To learn more about author Sanjaya Hettihewa, please visit the author's homepage.

Previous chapterNext chapterContents


- 12 -

Developing ODBC Database Front-Ends


Databases are used to efficiently store and retrieve information. Certain features of Microsoft Office and the Internet Information Server (IIS) can be used to publish databases on the Web. Databases can be published on the Web in two ways: You can take a snapshot of the database at a certain time and output it to an HTML file, or you can dynamically generate HTML code each time a certain URL is requested. The Internet Database Connector is ideal for dynamically generating Web pages to display information stored in a database.

Publishing Databases on the Web with the Internet Database Connector

Using the Internet Database Connector (IDC), you can publish databases on the Web. IDC is a .DLL file (HTTPODBC.DLL) that is included with IIS. In this section, you'll learn how you can publish a database using the IDC.

To develop a Web interface to a database, you have to create two files. The first file is called the Internet Database Connector file and ends with an .IDC extension. This file contains information for accessing an ODBC data source and executing a SQL statement. After the SQL statement is executed, the HTML file defined in the IDC file is used to format the results of the SQL statement. The other file is called the HTML extension file and ends with an .HTX extension. The .IDC file interfaces with an ODBC datasource and uses the .HTX file to format the result.

Creating a Guest Book with Internet Database Connector

In the following sections, you'll learn how you can use IIS and IDC to create a guest book and interface it with the Web. To create a guest book, perform the following steps:

1. Create a data source.

2.
Make the data source a system Data Source Name (DSN) for using ODBC with Microsoft IIS's IDC.

3.
Create the necessary files: an HTML file for data input, an .IDC file, and an .HTX file for processing and formatting input.

Creating a Data Source

You can create a data source using any application that has an installed ODBC driver. The guest book in this exercise uses a Microsoft Access database. Before you proceed, run Microsoft Access Setup and make sure that the Microsoft Access ODBC driver is installed on your system. Then perform the following steps:

1. Open Microsoft Access and create a table similar to the one shown in Figure 12.1. It does not have to be a complicated table. Simply create an Access database that you can use to store guest-book entries. The database can have fields for the user's name, e-mail address, and comments. After you create the table, make a note of its name and save it as a Microsoft Access file. Also be sure to make a note of the location of the Access file and the names of its data fields.

Figure 12.1. Creating the guest book data source using Microsoft Access.

2. Invoke the Control Panel and locate the ODBC icon, as shown in Figure 12.2. If the ODBC icon is not visible in the Control Panel, no ODBC drivers are installed on your system. In that case, run either Microsoft Access or Office Setup, and select to install the Microsoft Access ODBC driver.

Figure 12.2. Locate the ODBC icon in the Control Panel.

3. After you locate the ODBC icon, execute it to open the Data Sources dialog box shown in Figure 12.3. In this dialog box, you create system DSNs for databases with ODBC drivers. Click the System DSN button to create a system DSN for the Microsoft Access database created earlier. Note that IDC works only with system DSNs. A database has to be set up as a system DSN for Windows NT services, such as IIS, to interface with the database.

Figure 12.3. The Data Sources dialog box.

4. After you select the System DSN button, a dialog box similar to the one shown in Figure 12.4 appears. In the System Data Sources dialog box, click the Add button to create a system DSN for the Microsoft Access database. After a system DSN is created for the Microsoft Access guest book, it is visible in this dialog box (look ahead to Figure 12.7).

Figure 12.4. The System Data Sources dialog box.

5. After you click the Add button, the Add Data Source dialog box appears, as shown in Figure 12.5. Use this dialog box to select the Microsoft Access ODBC driver, then click the OK button to proceed to the dialog box in Figure 12.6. If the Microsoft Access ODBC driver is not visible, reboot your system and reinstall the Microsoft Access ODBC driver.

Figure 12.5. Selecting an ODBC driver for a new system DSN.

Figure 12.6. Creating a system DSN for the Microsoft Access guest book.

6. Using the dialog box shown in Figure 12.6, you can create a system DSN by specifying a data source name and a description for it. Then click the Select button to select an ODBC database that becomes an alias for the system DSN. Use this dialog box to select the Microsoft Access database created earlier. Then click the OK button to create a system DSN for the Microsoft Access guest book.

7. Next, verify that a system DSN has been created for the Microsoft Access guest-book application. You can do so by invoking the Data Sources dialog box (refer to Figure 12.3) and clicking the System DSN button. If a system DSN is successfully created for the Microsoft Access guest book, you see it in the window of the dialog box, as shown in Figure 12.7.

Figure 12.7. Verifying the creation of a system DSN for the Microsoft Access guest book.

This completes the information you need to create a system DSN for a database. You are not done yet, however; you can follow the preceding steps to create database applications and interface them with the Web using the IDC.

Using IDC to Interface a System DSN with the Web

You must create three files to insert data from a form to a system DSN. Listing 12.1 contains the HTML code used to gather data for the Microsoft Access guest book system DSN. Pay particular attention to line 17, which refers to an .IDC file. IIS maps .IDC files to a .DLL file (HTTPODBC.DLL) that is essentially the Internet Database Connector. See Figure 12.8 for an example of how you can use the HTML code in Listing 12.1 to submit a guest-book entry.

Listing 12.1. Guest book using Internet Database Connector.

 1: <html>
 2: <title>Guestbook - Using Internet Database Connector</title>
 3: <BODY BGCOLOR="FFFFFF">
 4: <TABLE>
 5: <TR>
 6:
 7: <h2>
 8: Guestbook - Using Internet Database Connector
 9: </h2>
10:
11: <HR>
12: Please enter the following information and press the SUBMIT button<BR>
13: <HR>
14:
15: <P>
16:
17: <form action="/scripts/samples/GuestBookInsert.idc" method=get>
18:
19: <B>Please enter your name </B>
20: <input type="text" name="Name" value="" size=35 maxlength=40>
21: <P>
22:
23: <B>Please enter your e-mail address</B>
24: <input type="text" name="EMail" value="" size=45 maxlength=50>
25: <P>
26:
27: <B>Please enter your comments below</B>
28: <textarea name="Comments" cols=70 rows=4 ></textarea>
29: <P>
30:
31: <input type="submit" value="SUBMIT Guest Book Entry">
32: <input type="reset" value="Reset Form">
33:
34: </form>
35: <P>
36:
37: <HR>
38: <a HREF="/scripts/samples/GuestBookView.idc?">View Guest Book</a>
39: <HR>
40:
41: </body>
42: </html> 

Figure 12.8. The form used to gather data for the Microsoft Access guest book.

Recall that line 17 of Listing 12.1 contains the statement action="/scripts/samples/GuestBookInsert.idc". The contents of the file GuestBookInsert.idc are shown in Listing 12.2. Note how line 1 of this code listing refers to the guest book system DSN you created in the dialog box shown in Figure 12.6. It also declares Name and Email to be required parameters in line 4. These parameters prevent a user from submitting a guest-book entry that does not contain a name or an e-mail address. Lines 6-8 contain the SQL statement that inserts data submitted by the form shown in Figure 12.8 to the Microsoft Access guest book system DSN. Line 6 specifies the table of the guest-book database into which the data should be inserted. Finally, lines 7-8 insert the three form fields to their corresponding table fields.

Listing 12.2. Inserting data into an ODBC data source.

1: Datasource: GuestBook
2: Username: sa
3: Template: GuestBookInsert.htx
4: RequiredParameters: Name, EMail
5: SQLStatement:
6: +  INSERT INTO GuestBookEntries
7: +  (Name, Email, Comments)
8: + VALUES(`%Name%', `%Email%', `%Comments%'); 

Refer to line 3 of Listing 12.2; it defines the file GuestBookInsert.htx as a template. This file is used to format the message returned to the user after the form is submitted. The message in Figure 12.9 was created with Listing 12.3. Note how the message is personalized to include the user's name in line 10. The expression <%idc.[Field Name]%> can be used to echo information filled in by the user. [Field Name] refers to the name of a form input control.

Listing 12.3. Internet Database Connector HTML extensions file (GuestBookInsert.htx).

 1: <html>
 2: <title>Thanks for the guest book entry!</title>
 3: <BODY BGCOLOR="FFFFFF">
 4:
 5: <h1>Guest Book entry added to Microsoft Access database.</h1>
 6:
 7: <HR>
 8:
 9: <h2>
10: Hello <%idc.Name%>,
11: </h2>
12:
13: Thanks very much for visiting this Web site and signing the guest
14: book. We hope you visit this Web site again, soon.
15:
16: </body>
17: </html> 

Figure 12.9. The message displayed after the successful insertion of a guest-book entry into the Microsoft Access database.

Use the URL http://your.server.com/scripts/samples/GuestBookView.idc? to obtain the contents of the Microsoft Access guest-book database assuming that GuestBookView.idc and GuestBookView.htx are stored in the /scripts/samples/ directory. The contents of GuestBookView.idc are shown in Listing 12.4. The contents of this file are similar to those of GuestBookInsert.idc, except that Listing 12.4 selects data from the GuestBook system DSN instead of inserting it.

Listing 12.4. Selecting data from an ODBC data source.

1: Datasource: GuestBook
2: Username: sa
3: Expires: 2
4: Template: GuestBookView.htx
5: SQLStatement:
6: +SELECT Name, EMail, Comments
7: +FROM GuestBookEntries 


The contents of GuestBookView.htx are shown in Listing 12.5. (Also see Figure 12.10.) This file formats results of the SQL statements in lines 5-7 of Listing 12.4. Pay particular attention to lines 9-15. The keywords <%begindetail%> and <%enddetail%> surround HTML code that formats data from a SQL statement consisting of multiple records in response to a SQL query. The HTML code defined between the tags <%begindetail%> and <%enddetail%> formats and merges each record returned by the SQL query.

Listing 12.5. The contents of GuestBookView.htx.

 1: <html>
 2: <title>Contents of Guest Book</title>
 3: <BODY BGCOLOR="FFFFFF">
 4:
 5: <h1>Contents of Guest Book</h1>
 6:
 7: <HR><P>
 8:
 9: <%begindetail%>
10: <B>Name: </B><%Name%><BR>
11: <B>EMail: </B><%EMail%><BR>
12: <B>Comments: </B><%Comments%><BR>
13: <p>
14: <HR><P>
15: <%enddetail%>
16:
17: <hr>
18: <a href="/samples/gbook/GuestBookInsert.htm">Sign the Guest Book</a>
19: <hr>
20:
21: </body>
22: </html>

Figure 12.10. Contents of the guest book.

Figure 12.11 displays contents of the Microsoft Access guest-book database after two records are inserted into it using the HTML form shown in Figure 12.8. Note how the records shown in Figure 12.10 are stored in the Microsoft Access database.

Figure 12.11. Contents of the Microsoft Access guest-book database.


Publishing on the Web with Microsoft Access 95

The Microsoft Access Internet Assistant (IA) can be used to easily publish Access databases on the Internet. If you are using Microsoft Access 97, proceed to the section "Publishing on the Web with Microsoft Access 97." Visit the Microsoft Access Internet tools Web page for the most up-to-date information about publishing Access databases on the Internet.


URL:The Microsoft Access Internet tools Web page is at

http://www.microsoft.com/msaccess/it_acc.htm

Installing Internet Assistant for Access

IA for Microsoft Access can be downloaded from Microsoft's Web site. After you download it, execute the executable file and allow the installation program to detect the Microsoft Access directory. After it detects what directory Access is installed in, click the large Install button to install IA for Access.


URL:Visit the Microsoft IA for Access download site at

http://www.microsoft.com/msaccess/internet/ia/default.htm

Publishing a Database on the Web with Access 95

The next few sections illustrate how a Microsoft Access database can be published on the Web using IA for Access. To publish an Access database, load the database into Access and select Tools | Add-ins | Internet Assistant from the main menu (see Figure 12.12). IA for Access then begins a welcome message. Click the Next button to display a dialog box similar to the one shown in Figure 12.13.

Figure 12.12. IA for Access can be invoked from the tools menu.

IA for Access can be used to export any combinations of Microsoft Access tables, queries, reports, and forms into HTML. This is done by selecting the object type and names of objects in that type using a dialog box similar to the one shown in Figure 12.13. Note that one or more object types and names can be selected. For example, you might want to select several reports and tables.

Figure 12.13. Microsoft Access objects can be selected to be exported into HTML.

After you select the objects to export as HTML files, click the Next button to continue. IA for Access presents you with a dialog box (shown in Figure 12.14) and asks you for a template. A template can be used to enhance the appearance of data exported by IA for Access by adding a background image, navigation buttons, and graphics to its output. Templates included with IA for Access can be browsed by clicking the Browse button. Note that templates with filenames ending with the suffix _r are used for reports; those without the _r suffix are used for data sheets. When several object types are selected, select the template without the _r suffix and IA for Access applies the correct template based on its filename.

After you select a template, type the directory that will contain the exported HTML files. Click the Finish button to export selected objects as HTML files. IA for Access exports the objects and informs you when it finishes creating the HTML files. The exported data can then be viewed using a Web browser (as shown in Figure 12.15).

Figure 12.14. A template can be used to format HTML output generated by IA for Access.

Figure 12.15. Data exported by IA for Access can be viewed with a Web browser.

Note that IA for Access might not always copy all the graphics files that are part of the HTML files it creates into the target HTML directory. Use a Web browser to look at HTML files created by IA for Access. If you see broken images, check the source code to locate the graphics files it refers to and copy them to the target HTML directory. These graphics files are located in the \MSOFFICE\ACCESS\IA95 directory (assuming you installed Office 95 in the \MSOFFICE directory). Alternatively, you can simply copy all the graphics files from the \MSOFFICE\ACCESS\IA95 directory to the target HTML directory.

Publishing on the Web with Microsoft Access 97

Web-publishing features of Microsoft Access 97 can be used to publish databases on the Internet in the following ways:

Converting a Database to HTML Using Microsoft Access 97

For the purpose of demonstrating Internet-publishing features of Microsoft Access, the next section shows how to convert the Northwind (a sample database shipped with Microsoft Access) database to HTML. Follow these steps, clicking the Next button at the end of each step to proceed to the next dialog box.

1. Open the database in Access and select File | Save As HTML from the menu bar. The Publish to the Web Wizard dialog box shown in Figure 12.16 appears. This dialog box can be used to select a previously used Web publication profile. (When saving a database in HTML format, Microsoft Access provides you with the option of saving selected settings for future use--more on this in step 7). Click the Next button.

Figure 12.16. The Publish to the Web Wizard dialog box.

2. Use the dialog box in Figure 12.17 to select objects of the database to publish on the Internet. After selecting objects, click the Next button. The dialog box in Figure 12.18 appears.

Figure 12.17. Select objects of the database to save as HTML.

3. The dialog box in Figure 12.18 is used to select the HTML template file used to format the data. Microsoft Access is shipped with several sample HTML template files. Select a template file by clicking the Browse button, and click the Next button.

Figure 12.18. Select the HTML template file used to format the data.

4. Choose how you want to save the database to HTML using the dialog box in Figure 12.19. In this exercise, you are shown how to save the database as a static HTML file. If you select to create a dynamic HTX/IDC or dynamic ASP publication, you must create a system DSN for the Access database in the computer running IIS. Also, be sure to copy the ASP, HTX, and IDC files to a directory of IIS that has Execute permissions.

Figure 12.19. Choose how the database is saved to HTML.

5. The dialog box in Figure 12.20 is used to specify the target directory of the HTML files. You might want to create a new directory if you selected more than one object to avoid duplicate filenames being overwritten.

6.
Select the check box in Figure 12.21 if you want a home page created for the HTML files. This feature is particularly useful if you select to convert more than one object to HTML.

Figure 12.20. Select the target HTML directory.

Figure 12.21. Select to create a home page for the HTML file(s).

7. The dialog box in Figure 12.22 gives you the opportunity to save the settings you specified as a Web publication profile for future use. After a Web publication profile is created, the same settings can be used to convert databases into HTML in the future. This feature is a tremendous time-saver when converting several databases into HTML. Click the Finish button to create the Web publication.

8.
The Microsoft Access Publish to the Web wizard creates HTML files containing data from the selected database as shown in Figure 12.23. These HTML files can be published on the Internet by transferring them to a publishing directory of a Web server.

If you selected to create a home page for the HTML files, the Publish to the Web wizard creates a home page similar to the one shown in Figure 12.24. The home page can be used to browse the contents of various HTML files as shown in Figure 12.25.

Figure 12.22. Save settings as a Web publication profile.

Figure 12.23. Files created by the Microsoft Access Publish to the Web wizard.

Figure 12.24. Home page created by the Publish to the Web wizard acts as an index to various HTML files.

Figure 12.25. Contents of the database in HTML format.

Summary

The Internet Database Connector (IDC) and active server pages can be used to effortlessly publish databases on the Internet. Both the IDC and the active server are included free of charge with IIS and can be used to create Web interfaces to ODBC data sources. To demonstrate capabilities of the IDC, you learned how to use the IDC to set up a guest book. Internet Assistant for Microsoft Access 95 and Web-publishing features of Microsoft Access 97 can also be used to publish databases on the Internet.


Previous chapterNext chapterContents


© Copyright, Macmillan Computer Publishing. All rights reserved.