To develop dynamic Web pages, you need database publishing capability, which is becoming the most common application for ActiveX controls today. This chapter introduces you to mechanisms for feeding the required data to the browser.
This chapter also covers the Microsoft Index Server that will enable you to provide search functions for your Web users to search across data in various formats, such as Excel, Word, PowerPoint, and so on in your servers.
In the beginning of the Internet/intranet, Web pages were static and the information provided was general in nature. Within a year, the information requirement has galloped to a point where the Internet or Web site has become the primary point of business. Users want to get dynamic information fast, whether it's a Federal Express package or checking the status of an order or a product's price through an online ordering system.
Now, an effective Web site requires interface with back-end data. For example, an online catalog management system will include processes to check product inventory levels and reorder points, display account transaction histories, and update the catalog for product additions, deletions, and so on. In the case of an online merchant system, you need real-time prices, order status, customer information, and so on.
In this chapter, you will look at a few solutions to interface
data in databases with the client browser.
| Static or Dynamic Publishing? |
There are mainly two categories of publishing data in the databases. The first can be called snap shot model, where the data at a given time is converted into HTML files. This conversion can occur by manual activation, automatically by an event handler, or a timer. The client browsers can view the data, but there are no facilities for interacting with the data. This model is useful for developing catalogues, price lists, telephone directories, or other corporate reference applications. The second category of applications is the one that interacts dynamically with the data. In these applications, the user will enter part numbers, package ids, or some selection criteria through a Web browser like Internet Explorer. The system will dynamically generate the data set to be displayed. |
The Internet Database Connector (IDC) allows the publishing of
ODBC compliant databases on the Internet/intranet. IDC is a part
of the Microsoft Internet Information Server (IIS), which comes
with Windows NT 4.0. With the IDC, one can publish data in SQL
Server, Access, ORACLE, Informix, SYBASE, and other ODBC-compliant
databases. Microsoft positions IDC as a developer technology that
serves as a foundation for developing custom database applications
with its Internet Information Server (IIS). Basically, it provides
a way to use SQL statements through ODBC drivers and connect the
results to a Web page.
| NOTE |
What is this ODBC? ODBC stands for Open Database Connectivity that has become a de facto standard to access data through programs for client/server applications, especially for the Windows platform. ODBC drivers are available for almost all the databases. The advantage of having an ODBC interface is that because the ODBC drivers are already installed in the systems, you can access data from all databases without any additional drivers and other programs. N |
The Internet Data Connector is an ISAPI DLL named HTTPODBC.DLL. It converses with the Web browsers through the IIS with the http protocol. On the back-end, it talks ODBC. Figure 11.1 shows the schematics of the steps.
Figure 11.1 : IDC interactions with Database and the Web browser.
| TIP |
There are many more fields possible in the .IDC file. Please refer to the IIS product documentation for the .IDC field details. |
| NOTE |
The IDC is capable of creating interactive forms that can create, modify, and delete SQL data. |
The SQL Server Web Assistant is positioned as a tool to publish snapshots of data in Microsoft SQL server databases, in the HTML format. The main distinction from other database tools is that the SSWA is a non-interactive process and in that sense it is a truly publishing solution.
In comparison to the IDC that uses the ISAPI gateway to "pull" data from the an SQL server database, the Web Assistant uses the "push" method to deliver data. It updates the Web page as scheduled by the configuration.
The Web Assistant can handle Transact-SQL queries, stored procedures, and extended stored procedures. On a functional level, the Web assistant is an interface to a stored procedure named sp_makewebtask. The HTML file can be updated whenever relevant data changes (by using a trigger). By using SQL Enterprise Manager scheduling, the HTML file can also be updated at scheduled intervals.
The Microsoft dbWeb is another tool to publish database data in
the HTML format. dbWeb is implemented through ISAPI DLLs and in
that respect, is similar to IDC. The difference is that dbWeb
is an end-user tool with graphical, point-and-click, query-by-example
(QBE) interface. Similar to IDC, dbWeb connects the Web to ODBC
data sources. The dbWeb runs on Windows NT 3.51 with Service Pack
4 installed and now in 4.0. It requires IIS and ODBC 2.5 drivers
for the database you want to publish.
| You Can Publish Data from Any Database Using the dbWeb |
Remember, dbWeb is a very flexible product. It is not limited to Microsoft databases. It can connect to diverse data sources that support 32-bit ODBC. Thus, this product can be used on all the major databases including Oracle, Sybase, and Microsoft Access in addition to SQL server. Even Lotus Notes has an ODBC driver. Also, dbWeb supports dynamic data queries and basic row manipulation which means you cannot only publish data but can manipulate that data. |
As of today, dbWeb is a downloadable product that is free from
Microsoft. Download a copy-it's about 7.7M-from www.microsoft.com/intdev/dbweb.
The file is named dbweb11a.exe. The current version is Version
1.1a. The Web site also contains detailed documentation, an exhaustive
tutorial, and FAQs. You should browse through and download the
information to view offline. The tutorial is very useful, especially
when you begin working with the dbWeb.
| NOTE |
The dbWeb was developed by Aspect Software Engineering Inc., prior to Microsoft acquiring the company. |
Run the dbweb11a.exe to install the dbWeb. Figure 11.2 shows the first screen that gives a choice of components to install.
Figure 11.2 : dbWeb Installation shows the selection screen for the components to install.
Figure 11.3 shows the directory options window for WWW publishing of the data through dbWeb.
Figure 11.3 : dbWeb Installation shows the directories for WWW publishing of data through dbWeb.
To verify proper installation, you can access the dbWeb Test Page as shown in Figure 11.4. The installation program creates all the required databases, other files, and ODBC entries.
Figure 11.4 : This is the dbWeb Installation test page.
From this test page you should be able to access the "pubs" database that shows a successful installation.
The dbWeb consists of two components-the dbWeb service and the dbWeb Administrator. Figure 11.5 shows the dbWeb architecture.
Figure 11.5 : This is dbWeb Architecture.
The dbWeb service runs as a 32-bit multi-threaded NT service. The functionality includes processing data retrieval requests and formatting and displaying the results. The results are displayed based on the configurations done using the dbWeb Administrator. The data display including the query fields, the actions permitted (insert, delete, or update records), and so on, can be controlled.
The dbWeb service gets the user requests from the WWW browser, communicates with the ODBC data source, and returns the results to the server which in turn sends it to the client browser.
The dbWeb Administrator is a very powerful GUI front-end for configuring the system. It uses schema to control how the database information is published on the intranet. Schema defines the query and resulting HTML pages displayed on the Web.
The main advantage of dbWeb is that no HTML or ISAPI programming knowledge is necessary to create the dbWeb schema. The dbWeb Administrator provides an interactive Schema Wizard for this purpose. Figures 11.6 and 11.7 show two representative dbWeb Administrator screens.|
Figure 11.7 : dbWeb Administrator-Table Joins property page
To set up a data page, the administrator will create a schema that is a collection of data required by the dbWeb to setup queries, data sources, tables, and so on.
The schema property page in Figure 11.6 defines the general HTML page layout as well as the actions behind the hot links, such as mail to and help URL. Also, there are options to control user interactions, such as update, add, and delete.
In the Tables property page, shown in Figure 11.7, you can select the tables that will be used in this page. You can also join different tables and create a page as shown in Figure 11.8.
Figure 11.8 : dbWeb Administrator-Tables property page
In the QBE property page, you can include the columns the user can use to fill in the Query-By-Example data. QBE is a user-friendly way of getting query data from the user.
Figure 11.9 : dbWeb Administrator-QBE property page
QBE is not limited to existing columns in the database. You can build a computed column as shown in Figure 11.10 for the QBE user form.
Figure 11.10 : dbWeb Administrator-QBE Computed Column Expression Builder property page
Once the schema is defined, a user can use any WWW browser to interact with the data as defined in the schema. The following list summarizes the main capabilities of dbWeb Administrator:
While the database approach gives the users an interactive path to data stored in databases, the Index Server gives the users ability to search through documents stored in the Internet or corporate intranet sites. The Microsoft Index Server works with Windows NT Server 4.0 and Internet Information Server. It also works with the Peer-Web Services available for Windows NT Workstation.
The following list shows some of the features offered by the Microsoft Index Server:
The Index Server runs on the Windows NT 4.0 platform. That means it will run on the Intel Pentium or RISC-based systems such as the Digital Alpha, PowerPC, and the MIPS R4x00.
One of the important requirements is the hard disk space to store the indexes. The index files occupy about 40% of the total size of the indexed documents.
Server memory is another point to be remembered. Even though the
16M for Windows NT and 32M for the RISC systems are recommended,
if you are deploying an Index Server for a department or corporate
division, you will require a 64M or 80M system.
| TIP |
The indexer task capacity and speed are directly related to the number of processors and the RAM available |
You can limit the resource usage by the Index Server through registry entries. The controls include maximum and minimum RAM used, disk usage, processor utilization, and also the priority of the various tasks such as indexing, query processing, and so on. The indexer runs as a background task and the status of the indexer can be checked. Periodically, the indexer performs clean up and data structure maintenance to optimize the system. The Web site http://www.microsoft.com/ntserver/search/docs/reghelp.htm has an extensive list and description of the registry entries associated with the Index Server.
The Index Server can be downloaded from the Web site http://www.microsoft.com/ntserver/search.
The site contains documentation, feature list, and link to download
the current version (now version 1.1). The downloaded file is
is11enu.exe. Run the file to start the Index Server installation
process.
| NOTE |
The file name is11enu.exe is for the Index Server Version 1.1 English U.S. edition. Your file name could be different if you are using another language or country version. |
The installation process first stops the Gopher, WWW, and FTP services if they are running. Then it proceeds to get directory information as shown in Figure 11.11.
Figure 11.11 : Index Server Installation Screen I
The installation proceeds after the directory prompts. The program also installs some sample and default query forms during the installation. After the installation is finished, the stopped services are started.
The Index Server requires very little administration and maintenance. Server functions such as index updates, optimization, and crash recovery are automatically started and stopped as needed by the Index Server Tasks.
The search is done based on the user's queries. The users fill
in their queries in HTML forms. The system can work with the default
query forms or the forms can be customized using HTML codes. Index
Server assembles query hits into result sets and returns them
to the client browser.
| TIP |
The query forms can be customized for: |
The Index Server is a very unassuming piece of software. It performs a straightforward function-indexing documents as they are changed and displaying to the user the documents that fit the conditions specified.