To learn more about author Sanjaya Hettihewa, please visit the author's homepage.
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.
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.
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:
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:
Figure 12.1. Creating the guest book data source using Microsoft Access.
Figure 12.2. Locate the ODBC icon in the Control Panel.
Figure 12.3. The Data Sources dialog box.
Figure 12.4. The System Data Sources dialog box.
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.
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.
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.
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.
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.
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.
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.
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.
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 athttp://www.microsoft.com/msaccess/it_acc.htm
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 athttp://www.microsoft.com/msaccess/internet/ia/default.htm
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.
Web-publishing features of Microsoft Access 97 can be used to publish databases on the Internet in the following ways:
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.
Figure 12.16. The Publish to the Web Wizard dialog box.
Figure 12.17. Select objects of the database to save as HTML.
Figure 12.18. Select the HTML template file used to format the data.
Figure 12.19. Choose how the database is saved to HTML.
Figure 12.20. Select the target HTML directory.
Figure 12.21. Select to create a home page for the HTML file(s).
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.
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.