Corporate database systems are built on making information widely available to qualified users. The information in these situations comes from many sources, ranging from discussion groups to proprietary systems. Probably the biggest repository of "mass" information is the database. If you've ever just browsed a database without specifying meaningful criteria, you know what is meant by "mass" information. There is often so much information represented that it actually makes it less useful.
By making databases available on your site, you can help users make sense of this information and, at the same time, you'll be able to provide access and presentation without the need for any special software to begin using the database effectively. In short, you have a new avenue to provide this access to the information that is probably already stored on your network.
Database access with the IIS system is provided by giving you ODBC connectivity to the HTML pages that execute on the server when the user makes a request of the system. In this chapter, you'll see how to set up pages, what types of information you can provide, and how you can enhance the presentation of the information to make it the most meaningful to the people who request it. n
CAUTION: Be aware that the database connector files are likely to contain and convey sensitive and sometimes very confidential information. For example, they'll contain query information that calls out column names, table names, and database sources that map to your ODBC configurations on the server.In addition, when users click a link to a database connector file, they'll be able to see where you're keeping your scripts and other programs, because it will show up in the URL that is displayed to them.
It is extremely important that your programs, scripts, and supporting files reside in the scripts subdirectory structure and that you provide Execute Only privileges on that directory. Be sure you do not provide Read privileges. This will open your system to unneeded possibilities for trouble, because people would be able to browse and review the applications that are the core of your system.
The Internet Information Server provides access to the ODBC layer with the use of the Internet Database Connector, or IDC. The IDC acts as a go-between for your system, providing the interaction between what is seen in the viewer in terms of HTML and how the information is queried at the database level. The overall access layer map is shown in Figure 23.1.
FIG. 23.1
The IDC provides for access to any ODBC data source.
When users specify the IDC file in the URL from the browser, they are asking the IIS system to use the IDC file and its statements to query the database and return the results. The IDC is specified in the URL, but the HTX file, or HTML Extension file, is what is actually returned to the user. The HTX file, still a standard HTML file, indicates how the resulting data is displayed, what lines constitute the detail lines of information, and more.
From Figure 23.1, you can see that the engine that is doing the database work with ODBC is HTTPODBC.DLL. This DLL, included when you install the IIS system, is an Internet Server API, or ISAPI, application that runs as an extension to the server software. This extension is database-aware and is able to use the two source files, the IDC and HTX files, required to give the information back to the user.
TIP: If you did not install the ODBC component of IIS, you'll need to do so to use the IDC. This not only installs the ODBC portions of the environment, but it also configures the server to be aware of the IDC files you'll be using. If you do not install the ODBC components, when users click the IDC link on their Web page, they'll see a prompt to download the IDC file, rather than view the results of the query. See Figure 23.2 for an example of this prompt.
FIG. 23.2
If the server does not recognize the database connector, it will try to download
the IDC page to the requesting user, rather than processing it and returning the
results.
TIP: When you install IIS, the ODBC option must be selected. Though it may not indicate disk space requirements if you have already installed ODBC from other applications, it will still be necessary to install it to activate the IDC capabilities. See Figure 23.3.
FIG. 23.3
It's a good rule of thumb to select ODBC for all installations--the setup program
is also careful not to overwrite any newer versions of the drivers you may have installed
on your system.
Listing 23.1 shows a sample IDC file taken from the samples included with the server. The sample installs into the \SCRIPTS\SAMPLES subdirectory on your system in the IIS directory structure.
Data source: web sql Username: sa Template: sample.htx SQLStatement: +SELECT au_lname, ytd_sales from pubs.dbo.titleview Âwhere ytd_sales>5000
TIP: You can indicate more than one SQL statement by using the IDC parameter more than once in the IDC file. Start each SQL statement with the SqlStatement: heading. You'll be able to access both results sets in the HTX file.
When this file is loaded by IIS, IIS examines the extension and determines what application should be used for the source file. For certain items, including the IDC extension, the server comes pre-installed, knowing what to do with the source when it's requested. One of the very powerful capabilities and features of IIS is that it is able to use the same Windows-based extension resolution to determine what to do with a given request. Files with a .GIF extension, for example, are known to be graphic images, and files with an .IDC extension are database connector "applets." You set up custom keys in the Registry. Associations are set up in the tree location shown next.
CAUTION: Any time you work with the Registry you should use extreme caution. Making entries in incorrect locations or with incorrect syntax can render your system inoperable.
You'll use the Regedt32 application to modify the registry. When you start it, look for the following key:
location:HKEY_LOCAL_MACHINE SYSTEM CurrentControlSet Services W3SVC Parameters ScriptMap
If you add a new entry, make it of the type REG_SZ and indicate the extension to associate. You'll need to include the period before the extension--for example, .IDC--to correctly map the association. For the value, indicate the path and file name that should be executed when the specified extension is loaded. Remember to provide the path from the root and start the path with a backslash, because this will ensure that IIS will be able to locate the application, regardless of the current working directory.
If you are indicating parameters to the call, you can use %s on the key value where you indicate the application to run. For example, suppose that you have a DLL that you want to run whenever a request is received to open a file with a .FUN extension. Your entry would be as follows:
.fun = c:\inetsrv\scripts\test\fundll.dll %s %s
When you use this option, the first time you use the %s, you'll receive the application to run that was passed to the URL. For example, if your FUNDLL is an application that processes a text file and searches it for a given value, you would expect the user to be passing in the text file and the value to search for. When you provide the URL at the browser level, you first indicate the location of the file you want to run. A question mark is next, followed by any applicable parameters to the call.
For the examples here, the URL that would be used would be something like the following:
http://holodeck3/scripts/search.fun?text+to+find
The resulting command line would be
c:\inetsrv\scripts\test\fundll.dll search.fun text+to+find
where each of the two items specified, the source file and search text, is passed as a parameter.
NOTE: Because parameters are passed as a single string to your application, as in the previous example with the text+to+find string, your application must be able to parse out the plus signs and rebuild the string, most likely in a buffer that can be used by your application to search the database or text file, as needed.
The results-formatting file, or HTX file, is where things can get a little tricky. As you'll see throughout this chapter, the real power and capability of your system is exposed with the HTX file. Until the information is provided to the template, it's of no use to the requester as he or she will not yet have seen the information. You can have one of the best, most comprehensive databases around, but if the presentation of the data is not what your audience needs, the information might as well be under lock and key.
Listing 23.2 shows a simple HTX template, provided in the samples with the IIS product, that displays the results of a query.
<HTML> <HEAD><TITLE>Authors and YTD Sales</TITLE></HEAD> <BODY BACKGROUND="/samples/images/backgrnd.gif"> <BODY BGCOLOR="FFFFFF"> <TABLE> <TR> <TD><IMG SRC="/samples/images/SPACE.gif" ALIGN="top" ALT=" "></TD> <TD><A HREF="/samples/IMAGES/db_mh.map"><IMG SRC="/SAMPLES/images/db_mh.gif" Âismap BORDER=0 ALIGN="top" ALT=" "></A></TD> </TR> <tr> <TD></TD> <TD> <hr> <font size=2> <CENTER> <%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%> <P> <TABLE BORDER> <%begindetail%> <%if CurrentRecord EQ 0 %> <caption>Query results:</caption> <TR> <TH><B>Author</B></TH><TH><B>YTD Sales<BR>(in dollars)</B></TH> </TR> <%endif%> <TR><TD><%au_lname%></TD><TD align="right">$<%ytd_sales%></TD></TR> <%enddetail%> <P> </TABLE> </center> <P> <%if CurrentRecord EQ 0 %> <I><B>Sorry, no authors had YTD sales greater than </I><%idc.sales%>.</B> <P> <%else%> <HR> <I> The web page you see here was created by merging the results of the SQL query with the template file SAMPLE.HTX. <P> The merge was done by the Microsoft Internet Database Connector and the results were returned to this web browser by the Microsoft Internet Information Server. </I> <%endif%> </font> </td> </tr> </table> </BODY> </HTML>
You'll probably notice several different things right away with this file. First, it's a standard HTML document. There is no strange formatting to speak of, and certainly many of the tags will be familiar if you've developed HTML before. Some of the real fun begins in the new capabilities offered by the HTX file. These new functions, above and beyond standard HTML, enable you to have the resulting Web page react to and change depending on the information that is, or is not, returned from the query. For example, in the section of code noted in the following, you'll see the introduction of conditional testing, examining for an empty set:
<%if idc.sales eq ""%> <H2>Authors with sales greater than <I>5000</I></H2> <%else%> <H2>Authors with sales greater than <I><%idc.sales%></I></H2> <%endif%>
Several operators are available when you design your pages. Throughout this chapter, you'll learn more about how to use these new database-oriented features.
As mentioned earlier, the IDC source file indicates the ODBC data source that is used to access the database on your system. From the IDC file listing, notice the Data source item. This item indicates that the web sql data source will be used. Before this sample will work on your system, you must have installed and configured the data source for that name using the ODBC control panel applet.
In the next couple of sections, you'll see how to set up the ODBC data sources for both SQL Server and Microsoft Access. You can use any 32-bit ODBC data source with your IIS application, and changes between setting up other data sources should be minimal; therefore, if you use the information presented here, you'll find that the IDC can work with nearly any database installation you may need to use.
One of the most common reasons for problems with the database connector is the setup of the ODBC data source. This is true across database sources not specific to SQL Server, so it's important to understand the details of setting up the driver for access by IIS.
You may recall that IIS is running as a service. This means that while it's running, it's not logged in as you, the administrator; instead, it's running in the background, logging in when needed as either the anonymous user you've set up or as the validated user that's been authenticated by the NT security subsystem. Because you want to give this service access to a database, and because you don't know whom the service will be logging in as, you need to set up the database source a bit differently than you may be accustomed to.
Microsoft has added a new option to the ODBC configurations to support a S_ystem DSN. These special data sources give you a way to set up a globally available data source. Because users who log on may be set up to have different access to your system and resources, you need to use the S_ystem DSN to make sure they have access to the right databases, regardless of where they log in or who they log in as. Figure 23.4 shows the ODBC setup dialog box, started from the Control Panel.
FIG. 23.4
ODBC setup for IIS requires that you select the S_ystem DSN to configure
the driver.
TIP: If you find that you receive errors trying to access an ODBC data source from your Web pages, one of the first things you should check is that the data source you're referencing is set up as a system data source.
When you start the ODBC manager utility, if the data source is listed in the initial dialog box, it's defined as a user-based data source, not a System DSN. Remove the user-based DSN and redefine it as a System DSN, and you'll be able to see the database.Remember that the only data sources that the Database Connector can use are the System-level data sources.
After you select S_ystem DSN, you'll be able to use essentially the same options to set up the drivers. Note, too, that you can have more than one driver set up at the system level. This allows you to set up drivers for the different applications you'll be running on the Web. Figure 23.5 shows the Data Sources set up dialog box.
FIG. 23.5
Setting up a system-level ODBC driver configuration is much the same as establishing
a new ODBC configuration. You'll need to indicate the driver, database, and other
information required to connect to your database engine.
he Data Source Name you provide is what you'll be using in the IDC file as the data source, so be sure to make note of the database configuration names you set up.
In most cases, you'll want the configuration to be as specific as possible. If it's possible to indicate a default database or table, be sure to do so. It will take some of the variables out of the Web page design you'll be doing. By specifying as much information as possible, you'll help ensure that the information is accessible.
Microsoft Access database data sources are established the same way as they are for SQL Server. You must set up each data source as a System DSN, making it available to the IDC as it logs in to NT's security subsystem.
Of course, there will likely be changes in the SQLStatement options you indicate in the IDC file. These differences relate to how Access interprets the SQL language elements. However, the statements should be nearly identical, especially in those cases where you're issuing SQL statements that are basically SELECT statements, rather than calling stored procedures, which are not supported by Access.
When you create the DSN, you'll be prompted to select the database with which ODBC should connect. Be sure to provide this information because, even though you can indicate it in code, you can make the connection to the database far more bulletproof with this option turned on. The system won't have to guess where to go to get the information.
Database access using the IDC provides a wide-open query system to your database. You should not allow users to have system administrator-level access to your databases just because it provides a way for someone to gain unwanted administrative access to your system. Instead, consider one of two options. First, if you're allowing anonymous connections to your site, be sure the user you have indicated as the anonymous user (usually IUSR_<machine name>) has appropriate rights to the databases that will be needed.
The way the login process works is to first validate the user using the anonymous login if it's enabled. If enabled and the user indicated as the anonymous user does not have sufficient rights, he'll receive an error message that indicates the fact that he may not have rights to the object(s) requested.
If anonymous login is disabled, the IDC will use the current user's name and password to log on to the database. If this login fails to gain access, the request is denied, and the user is prevented from accessing the database requested.
In short, if you want anonymous users gaining access to your system, you'll need to create the user account you want to access the information. Next, assign the user to the database and objects, allowing access to the systems needed.
The second option you have is to use NT's integrated security with SQL Server. Using this method, the currently logged-in user will be logged on to SQL Server, and the same rights will be in force.
Dynamic Web pages, those that build themselves on-the-fly to provide up-to-date information, are going to quickly become the mainstay of the intranet and Internet. This is because with a dynamic Web page, you can always count on getting the latest and greatest information. With the IDC, you can create these dynamic Web pages and have them work against a database to retrieve the information you need to let the user review.
There are three components to this type of page:
While it's not the intent of this book to teach all aspects of HTML, it's important to keep in mind that the samples provided are just that--samples. You'll need to take these samples and adapt them to your organization's way of doing business on the Internet. In short, the HTML code that may be required consists of the field, listbox, and checkbox options provided by HTML. By using these options and the ODBC connectivity, you enable the user to search the possibilities for making a meaningful interface for the user.
When you create a form that you'll be using to prompt the user for information, you create fields and other controls much as you do when creating an application. You'll name the fields and then pass the name and its value to the IDC to be used in your database query, if you desire. In the next sections, you'll see how to create these files and what makes them drive the output pages with the information from the database.
Generally speaking, you'll start the process of working with a database by presenting the users with a form, allowing them to select what information they need. As will often be the case, you have the ability to create forms that allow input that can be used to form the SQL statements you'll be passing to the data source. In the cases where you're creating a form, you'll see two basic HTML tags: the <INPUT> and <FORM> tags that allow you to designate actions to take and information to accept on behalf of the user. Listing 23.3 shows a simple form that prompts for an author name to be searched for in the author's table.
<HTML> <HEAD> <TITLE> Que Publishing's Very Simple Demonstration Form </TITLE> </HEAD> <h1>Sample Form for Database Access</h1> <FORM METHOD="POST" ACTION="/scripts/que/QueForm1.idc"> Enter Name to Find in the Pubs Database: <INPUT NAME="au_lname"> <p> <INPUT TYPE="SUBMIT" VALUE="Run Query"> </FORM> </BODY> </HTML>
The key elements are the "POST" instructions and the text box presented to the user. The <FORM> tag indicates what should happen when the form is executed. In this case, the form will send information to the server, hence the POST method. The <ACTION> tag calls out the program or procedure that is run on the server to work with the information sent in. In the example, the QUEFORM1.IDC is called and passed the parameters.
NOTE: The letter case is not significant when you're specifying HTML tags. "INPUT" is the same as indicating "input" and will not cause any different results when it's processed by IIS. n
It's not immediately apparent what those parameters might be, but if you examine the one or more INPUT fields, you can see that they are named. The following syntax is the basic, required element if you need to pass information back to the host in a forms-based environment:
<INPUT NAME="<variable name>">
The <variable name> is the name you'll use to reference the value provided by the user. Much as you define a variable in Visual Basic by dimensioning it, you must define and declare the different variables and other controls that are used by your HTML. There are other tags that can be used with the <INPUT NAME> tag, including <VALUE>, which allows you to set the initial value of the item you're declaring. For example, the following line declares a new variable, MyName, and assigns an initial value of "Wynkoop" to it:
<INPUT NAME="MyName" VALUE="Wynkoop">
For the preceding example, the intention is to create a very simple form that allows the user to type in a name, or portion of the name, that can be used to search the Author's table in the Pubs database. When the HTML is loaded as shown in the preceding, Figure 23.6 is the result.
FIG. 23.6
Allowing the user to indicate values to pass to the database engine adds polished,
func-tional benefits to your application.
As you can see, the text box size is automatically determined for you as a default. There are <MAXLENGTH> and <SIZE> tags you can place in the INPUT NAME directive if you need to increase the size of the text box. You'll also notice that if you press Enter while you're using this form, the form will automatically be submitted to the server just as if you'd pressed the Submit button. Because there is only a single button on this form, the browser interprets this as a type of "There is only one thing for me to do, so I'll just do it automatically" situation.
What happens in this example from Listing 23.3 is that the browser opens a new URL on the server with the specification
http://holodeck3/scripts/que/QueForm1.idc?au_lname=<name>
NOTE: If you watch your Web browser, it may indicate only that it's loading the URL that is included up to the ? in these examples. The protocol is still passing the parameters to the host; they are simply not shown during the transfer by some browsers. n
The <NAME> is the name you indicate in the text box prior to pressing Enter or clicking the Submit button. The next step in the process is to run the query against the database engine and see what results are returned.
The query source files reside in files in your SCRIPTS area and have a file name extension of .IDC by convention. When the URL is accessed, the server will run the indicated IDC file. As mentioned earlier in this chapter, the IDC file contains the SQL statements and directives necessary to carry out the commands as needed. For this example, Listing 23.4 shows the source for querying the database.
CAUTION: To reiterate the note earlier about security, be sure you place your IDC files in directories that have been set up with Execute, but not Read, privileges. This is important because if users can review your source files, they can see column names, table names, SQL login names and passwords, and so on. This is information you want to make sure is private.
Datasource: web sql Username: sa Template: queform1.htx SQLStatement: +SELECT au_lname, phone, address, city, state, zip + from authors + where au_lname like `%au_lname%%'
The output from this specific file is really nothing. The user will never see this file or output from it directly. This seems a bit strange, but the entire intent of the IDC is to define and perform the query against the data source indicated. After the information is retrieved, the IDC calls the Template indicated and passes in the results to be returned as a Web page.
NOTE: In the example in Listing 23.4, notice that the Where clause specifies like and that there is an extra percent sign in the comparison field. This is standard SQL syntax that allows you to search for wildcarded strings. You specify the part you know, and the IDC will append an extra % character at the end. Because the percent sign is the wildcard for SQL Server, you'll be able to return all items that start with B, for example.
Before working down through it, some basics about this source file are important to understand to explain how it works. First, to reference a variable, place it between percentages, as is the case with `%au_lname%' in Listing 23.4. Note that the single quotes are required because the field is a text-based type.
You can reference variables anywhere in the script. This means that even for the items that are seemingly hard-coded, like parameters, you can allow the user to specify them and then call them dynamically from the IDC file.
Second, in cases where your line length is shorter than your actual line, you can call out the item you want to work on, begin specifying the values, and continue indicating the expanding values as long as you place the + in the first column of the file. The plus sign acts as a line-continuation character for these source files.
The data source indicated in the IDC relates to the ODBC data source you establish with the ODBC manager in the control panel. Remember that the data source you use with the IDC must be a system DSN. If it's not, the call to the database will fail.
The Username, and optionally the Password, will override any settings you may have established in ODBC, and they'll override the current user name as well as how it relates to the execution of the query. Other parameters that may be of interest or use in your integration of the IDC file into your installation are shown in Table 23.1.
RequiredParameters | By naming the parameters that you must have represented from the form filled out by the user, you can make sure the user didn't just press Enter or otherwise ignore a field. Name the fields you want to assure information from, and IIS will kick back a message if the field is left blank for any reason. When you specify the fields, do not use percent signs but simply name the field. To indicate more than one field, separate each field in the list with a comma. |
DefaultParameters | You can set up defaults for the fields you are expecting in from the user. Name each field, followed by an equal sign and the value you want it to have. When the field is retrieved from the form, if it's a blank or nonspecified field, the value you indicate will be filled in. Note that DefaultParameters are applied prior to RequiredParameters being checked, effectively making the use of RequiredParameters unneeded if you can indicate an acceptable default. Keep in mind that your page should indicate what the default will be, because the user will not see the substitution that is made in the IDC file when it's processed. |
Expires | If you submit a query over and over again, you may find that you're retrieving a cached copy of the information rather than an updated database query. This can be especially problematic when developing applications because you'll be continually testing the system, resubmitting queries, and so on. By setting the <EXPIRES> tag, established in seconds, to a value that represents a timeframe that should have passed before the query is retried, you will avoid this problem. In other words, how long will it be before the information should be considered "stale" or in need of being refreshed for viewing? |
MaxRecords | If you are connected over a slower-speed connection, there are few things more frustrating than receiving a huge data file, then realizing that you needed only certain bits of information. For example, you may need to return only the first 100 rows of a table because they will provide the most current, meaningful data to your sales effort. By limiting the MaxRecords, you can indicate this in the IDC file, limiting traffic and database interaction with the new option. |
TIP: You can call SQL Server's stored procedures from an IDC file if you want to specify it in the SQL statement portion of the file. To do so, use the following syntax:EXEC MySP_Name Param1[, Param2...]Include the name of your stored procedure in place of MySP_Name.
In the stored procedure, be sure you're returning results sets, even if they represent only a status value indicating success or failure on the operation. Remember, as with other ODBC data sources, the stored procedure will be passed to the server, and the client will await the response. If your stored procedure does not return a value to the calling routine, you may give the user the impression that you've caused the browser to become frozen.
From here, after you've retrieved the values you want to display, you can move on to the results set source files. These files do the work of formatting and displaying information to the user and are explained next.
The results files are where the fun begins in working with the data that comes back from the query. The HTML extension files, with file name extensions of .HTX, are referenced in the Template entry in the IDC. These files dictate how the information is presented, what the user will see, whether items that are returned actually represent links to other items, and so on.
Listing 23.5 shows the sample HTX file for the example you've been reviewing throughout this chapter. You can see that it has a few extra, not-yet-standard items that make the display of information from the database possible.
<!-- Section 1> <HTML> <HEAD> <TITLE>Authors Details</TITLE> </HEAD> <TABLE> <tr> <TD> <hr> <P> <TABLE BORDER> <caption>Query results:</caption> <TR> <TH><B>Author</B></TH> <TH><B>Phone</B></TH> <TH><B>Address</B></TH> <TH><B>City</B></TH> <TH><B>State</B></TH> <TH><B>Zip</B></TH> </TR> <!-- Section 2> <%begindetail%> <TR> <TH><B><%au_lname%></B></TH> <TH><B><%phone%></B></TH> <TH><B><%address%></B></TH> <TH><B><%city%></B></TH> <TH><B><%state%></B></TH> <TH><B><%zip%></B></TH> </TR> <%enddetail%> <!-- Section 3> <P> </TABLE> <%if CurrentRecord EQ 0%> <H2>Sorry, no authors match your search criteria (<%idc.au_lname%>).</H2> <%else%> <H2>Authors with names like "<I><%idc.au_lname%></I>"</H2> <%endif%> </center> </td> </tr> </table> </BODY> </HTML>
When the URL is accessed, the server is going to run the indicated IDC file. As mentioned earlier in this chapter, the IDC file contains the SQL statements and directives necessary to carry out the commands as needed. For this example, Listing 23.4 shows the source for querying the database.
NOTE: The lines starting with "<!--" are comments and are not interpreted by the HTML client. n
When you design data-oriented pages, you'll want to make sure you take advantage of HTML's start- and end-tag metaphor. To put it simply, for many of the different items in HTML, when you establish a tag, for instance <HEAD>, until the reciprocal argument, </HEAD>, is encountered, the feature you enabled with the first instance of the keyword is in force.
As you can see in Figure 23.7, the sample file turns on the H1 heading style and then doesn't turn it off, resulting in the entire page using oversized fonts.
FIG. 23.7
Because HTML tags are evaluated in pairs, missing the closing tag can make a style
run through the balance of your HTML document.
In the sample HTML in Listing 23.5, you'll notice that there are three sections called out. These sections are inserted only to make reading and explaining the HTML a bit easier. They aren't necessary for the functioning of the document.
In Section 1, the entire purpose is to set up the page. You'll need to establish fonts, set up background images, do any initial formatting, and so on. You'll also need to start any tables that you want to use. Because you initiate a table, add the rows to it, then turn off the table, tables represent an excellent way to present data that will include an unknown number of rows.
For example, as in Figure 23.8, though two rows are shown, there could just as easily have been 20. The other advantage of using tables to display your database information is that the table will size to the user's visible browser area automatically. You won't need to worry about column widths and other formatting issues.
FIG. 23.8
If you can use tables to display data to the user, you'll be keeping with an already
familiar metaphor for the presentation. People are used to table-based data represented
in a columnar fashion.
Section 2 is where you'll work with the detail lines that are returned as part of the data set. Notice that this section is bracketed with a <%begindetail%> and <%enddetail%> tags. Everything between these two tags will repeat once for every row returned in the data set. In the preceding example, section 2 consists largely of building the table that will display the information that was returned.
<! Section 2> <%begindetail%> <TR> <TH><B><%au_lname%></B></TH> <TH><B><%phone%></B></TH> <TH><B><%address%></B></TH> <TH><B><%city%></B></TH> <TH><B><%state%></B></TH> <TH><B><%zip%></B></TH> </TR> <%enddetail%>
When you indicate the data to include, you refer directly to the column names that are in the table or view that is referenced by the IDC file. Place a "<%" before, and "%>" after each column name. In English, the preceding code snippet is reserving a row to put new data into with the <TR> tag, placing the information into the row with the <TH> tags, and ending the row with the closing </TR> tag.
You can do comparisons in your file, as well. For example, if you want to check to make sure that the State was returned as AZ, you could do so in one of two ways. Obviously, the preferred method would be to change your Where clause in the IDC to reflect the fact that you want to filter out non-Arizona states.
Alternatively, you could indicate here that you want to test. Consider the following code sample:
<! Section 2> <%begindetail%> <%if <%state%> eq "AZ"%> <TR> <TH><B><%au_lname%></B></TH> <TH><B><%phone%></B></TH> <TH><B><%address%></B></TH> <TH><B><%city%></B></TH> <TH><B><%state%></B></TH> <TH><B><%zip%></B></TH> </TR> <%endif%> <%enddetail%>
By using the If construct, you can test values and conditions in the data set. You can reference variables that come from the IDC file, as well. To reference these, simply prefix the variable name with IDC. So, if you want to reference the incoming variable from the original HTML form, you can do so by a statement similar to the following:
<%if <%idc.au_lname%> eq "Wynkoop"> <TH><B>Building series...</B></TH> <%endif%>
In this case, the query would go back to the IDC and pull the value for the au_lname variable, make the comparison, and either execute or ignore the statements in the loop following the test. There are three different tests that you can perform. Each is described in Table 23.2.
NOTE: You can also use <%else%> in your If...else...endif loop.
EQ | Indicates an equivalent test. "Is item A equal to item B?" |
GT | Tests for a condition where one item is greater than the other. |
LT | Tests for the condition where one item is less than the other. |
In addition, there are two different data set-related variables. CurrentRecord allows you to reference the number of times the Detail section has executed. If, after the detail loop has run, you want to determine whether there are records in the data set, you can test this variable to see if it's 0. If it is, no information was returned, and you should display a meaningful message to that effect:
<%if CurrentRecord EQ 0> <H2>Sorry, no authors match your search crit... <%else%> <H2>Authors with names like "<I><%idc.au_lname%>... <%endif%>
The other tag that corresponds directly to database-oriented actions is the MaxRecords option. MaxRecords relates to the MaxRecords IDC variable. Using this value, you can determine the total number of records that the IDC file will allow.
You use both CurrentRecord and MaxRecords in conjunction with <%if%> statements. They are implemented as controlling variables that help in your structuring of the logical flow of the HTX file. Just keep in mind that, after the processing of the detail section has completed, if CurrentRecord EQ 0, there were no results returned from the call.
The final section of the HTX file is used largely to close different HTML tags that were used to set up the display of information on the resulting page. Remember, HTML expects most tags in pairs, so it's a good idea to close each item properly.
<!-- Section 3> <P> </TABLE> <%if CurrentRecord EQ 0%> <H2>Sorry, no authors match your search criteria (<%idc.au_lname%>).</H2> <%else%> <H2>Authors with names like "<I><%idc.au_lname%></I>"</H2> <%endif%> </center> </td> </tr> </table> </BODY> </HTML>
Notice, too, that the CurrentRecord variable is used to determine the message that is displayed to the user. There either will be a message indicating no matches, or one explaining what was searched for is shown. You can also see that by referencing the "<%idc.au_lname%>" variable, you can pull the user-specified value from the form.
The results of a successful search are shown in Figure 23.9.
FIG. 23.9
A successful match will show the hits on the PUBS database table, and
will then show the message indicating what was searched for.
If the search of the database tables is not fruitful, the HTX will display a different message indicating the failure of the process. Figure 23.10 shows this dialog box.
FIG. 23.10
If matches for information are not found, you should code a branch of logic to
indicate the problem to the user.
To recap how the IDC works overall, first you will code a form or other HTML document that calls the IDC file on the server. The IDC file is located in the protected /SCRIPTS directory and contains the information necessary to open the ODBC connection and submit a query to the database engine. As the results are returned, they are merged into another document, the HTX or HTML extension document. This HTX file includes the information needed to work with both the detail records and the header/footer information for the page.
The result to the user is the display of the requested information in an HTML document style. Of course, the resulting document, based on the HTX file, can include further links to queries or drill-down information if needed. By using this technique, you can allow a user to select high-level values and then narrow the scope, but increase the detail level provided, for the information because the user is able to narrow the parameters for the operation.
An excellent example of the drill-down technique is provided in Microsoft's samples in the GuestBook application. As you query the guestbook, you are returned high-level detail about the names found. Here's a look at the HTX file's Detail section to see what exactly is done to display the information from the database.
<%begindetail%> Name: <a href="/scripts/samples/details.idc?FName=<%FirstName%>& LName=<%LastName%>"><b><%FirstName%> <%LastName%></b></a> <p> <%enddetail%>
So, for each name returned by the original query, the result will be to show the first and last names. This HTML sets up the names as links to their own details. The code indicates the <A HREF> tag and references the IDC that retrieves the detail information, DETAILS.IDC. As a result, when the users click this in their browsers, they'll immediately be executing the IDC file and retrieving the next level of detail. Figure 23.11 shows what this initial screen of details looks like when the items are first retrieved.
FIG. 23.11
The initial display of the guestbook contents allows the user to select a name and
drill down into the details for that name.
When you click a name to get the details, the IDC is called that retrieves the details from the Guests table on your system. If you take a look at the IDC, you'll see that it's quite simple, returning only a few columns of information based on the name selected from the previous query:
Datasource: Web SQL Username: sa Template: details.htx SQLStatement: +SELECT FirstName, LastName, Email, Homepage, Comment, WebUse +FROM Guests +WHERE FirstName = `%FName%' and LastName = `%LName%'
The final step is to show the information to the requester. The DETAILS.HTX template is called out in the IDC file, and it shows the detail information for the user as requested. The detail section simply displays the user information that has been provided. The HTX file makes heavy use of the <%if%> operator and the comparison of the contents of a given field to ensure that the only information provided to the user are those fields that are non-blank. See Listing 23.6.
<%begindetail%> <h2>Here are details for <%FirstName%> <%LastName%>:</h2> <p> <b><%FirstName%> <%LastName%></b><br> <p> <%if Email EQ " "%> <%else%> Email Address: <%Email%> <br> <%endif%> <%if Homepage EQ " "%> <%else%> Homepage: <%Homepage%> <%endif%> <p> Primary Web Role: <%WebUse%> <p> <%if Comment EQ " "%> <%else%> Comments: <%Comment%> <%endif%> <p> <%enddetail%>
Providing this type of increasing detail based on a user's selection is good for all parties concerned. It's good for your system because it can provide only the information needed to determine the direction to go to for the next level of detail. In addition, it's good for the users because it can mean less content to shuffle through to get to the information they really need. Because they will be determining what information is delved into, they'll be able to control how deep they want to go into a given item.
This technique is really great for supplying company information. You can provide overview- type items at the highest level on everything from marketing materials to personnel manuals. Letting people select their research path also relieves you of the responsibility of second- guessing exactly what the user is expecting of the system.
As you're sure to have noticed, the race to bring content to your Internet site and make all different types of information available to the user base has been fast and furious. One of the recent advances is the capability to have the database engine automatically generate Web pages for you based on content in the database.
With SQL Server 6.5, you have the ability to schedule a task in the system to automatically create these HTML documents at time intervals ranging from a one-time run to many times per day. You can use this capability for a number of things, including reporting on your server's activity levels to you as an administrator.
In this section, you'll see how to set up these automatically generating pages and what the results of the process are. It's not possible to go into great detail on how to use SQL Server, form good database table design, and other administrative issues regarding SQL Server, because they warrant a much more comprehensive discussion than a single group of sections here.
Before you can successfully use the Web Page Wizard and the processes it will create, you'll have to have set up your server to allow for this type of access. Specifically, the Web Page Wizard relies on the task manager and SQLExecutive service. You must have the SQLExecutive service set up to automatically start on startup of your server.
To confirm that the service is set to automatically start, select Services from the Control Panel. Scroll down the list of services installed until you see the SQLExecutive service (see Figure 23.12).
FIG. 23.12
Make sure the SQLExecutive service is listed with a Status of Started and
that Startup is listed as Automatic.
If the service is not already started, click the service and select Startup. You'll be able to set the options that govern when the service is active and, most importantly, when it will be started by the operating system. You'll want to make sure you indicate a valid user account that will be used to log in to SQL Server. This account must exist in both SQL Server and the User database for your domain if you are not using Integrated security, and the user name and password must be the same in both SQL Server and the domain.
If you're using integrated security, selecting a user from the domain user's list will also provide the name to be logged in to the SQL Server.
NOTE: It's a requirement that the information you provide as it relates to the user and password is valid in SQL Server. You will also need to ensure that the account you indicate has access to the database you're reporting against and the MSDB database, because these are key to the creation of the page (see Figure 23.13).If you do not set up the SQLExecutive to automatically start, the services required to generate the Web content you are setting up will not be available, and the page will not be generated.
If this is the first time you're setting these options, and the SQLExecutive was not previously started, when you select OK to save the user ID and startup option changes, you'll need to reselect the SQLExecutive service and then select Start.
FIG. 23.13
The account you indicate for logon for the service must have access to the different
objects in your database(s).
The SQL Web Page Wizard is located in the SQL Server program group on your system. You can run the Wizard from a workstation or the server. In either case, it will generate the pages for you in a directory you'll specify later in the process.
Figure 23.14 shows the initial SQL Server login dialog box you're presented with from the Wizard. From this dialog box, you'll need to provide an appropriate login that will allow you access to all tables and databases you want to use in providing content for your page.
FIG. 23.14
Logon to SQL Server is the first step to using the Wizard.
The option to use Windows NT Security to log on assumes you're using integrated security. If you are, selecting this item means that you don't have to provide separate login account and password information prompted for earlier in the dialog box and that the users will be logged on with their own security rights intact for SQL Server. Their NT logon name is used as their SQL Server logon name.
Selecting the Content for the Page When you select Next >, you'll have three initial options. The first option, Build a query from a database hierarchy, allows you to use the point-and-click interface and indicate the tables and other items you want to include in your query that will be used to generate the page. Figure 23.15 shows an example of what the tree-based architecture will look like if you select this option.
FIG. 23.15
The easiest interface is the Database Hierarchy option because it allows you to select
from the listing of objects on your server when deciding which items to provide for
a report.
If you select the Free Form query option, you'll be able to create any SQL statement you need to fulfill the requirements for your software. Be sure to select the database you need to work against from the "Which database do you want to query?" list box, or you may end up querying the wrong table, and you'll need to come back and rewrite the query or queries on which you based the page.
Using this option also means that you're taking all responsibility for the formation of SQL Server-specific calls. The query you enter will be passed along and executed by the server. Figure 23.16 shows an example of how the dialog options work when working with the Wizard in this scenario.
FIG. 23.16
Entering the query manually can be powerful and painful all at once. If you're not
sure of the syntax, use the more automated features of the Wizard for a few instances
of information you want to publish.
The final option you have in setting up the source of information for your page is to call a stored procedure. When you select the Use a query in a stored procedure button, you'll be able to provide information on the database and stored procedure you want to use. You'll also notice that the text of the stored procedure is shown in the dialog box. You can use this information to verify that you have selected the correct stored procedure (see Figure 23.17).
FIG. 23.17
Calling a stored procedure can be a good way to share coding you've done for an application
and put it to use on your Internet server.
Stored procedures are a powerful mechanism for optimizing your server and providing good database query tuning. You can also take advantage of the fact that if you have another system based on SQL Server, and you're using a stored procedure to produce the results for a printed report, you may be able to reference the same stored procedure in the dialog box and create the report in HTML, making it available at any time.
The text box showing your stored procedure is provided as reference only; you cannot make changes to the code here. If you need to make changes to the code, you'll need to do so by updating the stored procedure in SQL Server. This can be done with ISQL/W, the SQL Enterprise Manager, or any other utility you may be using to manage your stored procedures.
Setting the Update Interval The next dialog box will prompt you for the frequency at which you'd like to have the page rebuilt. Because the database is the source of information for the page, this item may take some work. The reason is that you'll need to talk with all of the users of the application that creates the data in the database and determine how frequently it is changed. A frequency set to be too small will cause additional overhead on the server as it handles the request. The impact on performance should be minimal, but if there are many, many requests for data pages such as this, it may begin to show on the access times to the server. For an example (see Figure 23.18).
FIG. 23.18
Be sure to select a useful time interval: Too frequent will force the server to rebuild
the page without reason; too infrequent an update process makes the information a
bit less useful because it may go out of date.
Your time-frame options and their associated parameters are
Setting Page Options There are two different dialog boxes with information about final formatting. Formatting options include headings for the page, column names on the resulting document, or changing the title or output location of the resulting HTML code. These are the steps you'll be using to create the database-related HTML you set up. See Figures 23.19 and 23.20 for examples.
FIG. 23.19
Review the formatting options carefully. These are the items that will be used
to manage the content, appearance, and other facets of your HTML's presentation.
FIG. 23.20
If you are relying on the engine to do formatting for your tables of data, be sure
to select the "Include column or view column names with
the query results" when you set up the Wizard's Web page.
After you've made any changes you need, you can select Finish to generate the code that will be used to execute the different operations that manage the Wizard and its pages. Now, whenever the page is referenced by a browser, the HTML generated by the Wizard will be the results. The user will be able to see the new view you've constructed and will be assured of up-to-date information.
TIP: One thing you'll want to consider including on every page you generate is a link to another page on your site. For example, you may want to always include a link with the following attributes:
Description: Return to Home PageURL: http://www.<your site>.com/default.htm
It's a good idea to come up with a set of links that you include on each page on your site. These may be back to the home page, back to a search page, and to a copyright page, for example. Consistency across your site will make it much easier for users to navigate and understand.
It's helpful to review your SQL Server installation to understand what's happening when you implement a Wizard-generated Web page. There are a couple of things that have happened when you create a page in this manner.
The master stored procedure, sp_makewebpage, is created in the database you're setting up. This is used by the Wizard to create the code necessary to generate the page. You won't be making changes to this or the other stored procedures, and if you ever want to re-create it, you can run the Wizard again and create another page in the same database. This will create the stored procedure for you.
The page-specific stored procedure is created with a name that begins with Web_ and includes a unique numeric name that includes the date it was created. In the example shown in Figure 23.21, you can see that the stored procedures created to support the demonstration pages are named Web_96042622390211 and Web_96042700554612. If you review the stored procedures, you'll see that they're encrypted, so you won't be able to make any changes to them directly. Of course, the easiest way to make any changes you need is to remove the stored procedures and their associated tasks from SQL Server and then re-create them using the Web Page Wizard.
FIG. 23.21
Be sure not to remove the stored procedures from the database unless you also
remove the corresponding task entries from the task scheduler.
By selecting Server, Tasks... from the menus, you can review the tasks that have been created to run the page at the intervals you set up. Figure 23.22 shows what you'll see. You can quickly determine that the Web pages are queued up and ready to go.
FIG. 23.22
After the Wizard has finished, the task is entered into SQL Server for processing
as you established.
You can also check to make sure a Web page generation process is occurring as you'd expect by clicking the History button from the Task Scheduling dialog box. When you do, you'll see the dialog box shown in Figure 23.23, which indicates the times the procedure has run and whether it was successful. It's a good idea to start with the history review process in any diagnostics you need to run in the future should you encounter problems. In most cases, you can quickly determine exactly what's wrong by just doing some quick investigation with the Task Scheduler.
FIG. 23.23
You can verify that the pages are being generated successfully by reviewing the History
logs.
Keep in mind that you can also change the frequency at which your page is generated by modifying the task scheduling options. If you double-click the page you want to modify, you can set all the different options that control how often the page is generated. This might be helpful if you find that, after installing several pages and your site traffic picks up, you need to lessen server load a bit to provide better throughput at peak times. Simply change the times at which the pages are generated, and you'll be set.
Implementing the page(s) you create on your site is a simple matter. You need to create a link to the pages or publish the URL to your user base. After the page is created by the SQL engine, it appears just as any other HTML document. Listing 23.7 shows the sample page created by the Web Page Wizard.
<HTML> <HEAD> <TITLE>SQL Server Web Page Wizard</TITLE> <BODY> <A HREF = /default.htm>Back to top of site</A>.<P> <HR> <H1>Query Results</H1> <HR> <PRE><TT>Last updated: Apr 26 1996 10:43PM</TT></PRE> <P> <P><TABLE BORDER> <TR><TH ALIGN=LEFT>au_id</TH><TH ALIGN=LEFT>au_lname</TH><TH ALIGN=LEFT>au_fname</TH><TH ALIGN=LEFT>phone</TH><TH ALIGN=LEFT>address</TH><TH ALIGN=LEFT>city</TH><TH ALIGN=LEFT>state</TH> <TH ALIGN=LEFT>zip</TH><TH ALIGN=LEFT>contract</TH></TR> <TR><TD NOWRAP>172-32-1176</TD><TD NOWRAP>White</TD><TD NOWRAP>Johnson</TD><TD NOWRAP>408 496-7223</TD><TD> <NOWRAP>10932 Bigge Rd.</TD><TD NOWRAP>Menlo Park</TD><TD NOWRAP>CA</TD><TD NOWRAP>94025</TD><TD NOWRAP>1</TD></TR> <TR><TD NOWRAP>213-46-8915</TD><TD NOWRAP>Green</TD><TD NOWRAP>Marjorie</TD><TD NOWRAP>415 986-7020</TD> <TD NOWRAP>309 63rd St. #411</TD><TD NOWRAP>Oakland</TD><TD NOWRAP>CA</TD><TD NOWRAP>94618</TD> <TD NOWRAP>1</TD></TR> ... <Edited for brevity see disk file for full listing> ... <TR><TD NOWRAP>899-46-2035</TD><TD NOWRAP>Ringer</TD><TD NOWRAP>Anne</TD><TD NOWRAP>801 826-0752</TD> <TD NOWRAP>67 Seventh Av.</TD><TD NOWRAP>Salt Lake City</TD><TD NOWRAP>UT</TD><TD NOWRAP>84152</TD> <TD NOWRAP>1</TD></TR> <TR><TD NOWRAP>998-72-3567</TD><TD NOWRAP>Ringer</TD><TD NOWRAP>Albert</TD><TD NOWRAP>801 826-0752</TD> <TD NOWRAP>67 Seventh Av.</TD><TD NOWRAP>Salt Lake City</TD><TD NOWRAP>UT</TD><TD NOWRAP>84152</TD> <TD NOWRAP>1</TD></TR> </TABLE> </BODY> </HTML>
When you view the page, all of your SQL table data will be placed into an HTML table. The links you specified will be shown at the top of the page prior to the data from the site. Figure 23.24 shows what the top portion of this sample page looks like when presented in the browser.
FIG. 23.24
The sample Web page includes information from the Pubs database and a link back to
the site home page.
A great use of the Web Page Creation is to query the IIS logs that can be placed into SQL Server. For more information, see the next section that will show how this can be used to remotely monitor your site.
Perhaps one of the biggest improvements you can make to your system administration abilities is to log your IIS access activity to an ODBC database. This is because you can start amassing excellent information about your site, including what information people are retrieving, how frequently they're visiting your site, and more.
As mentioned earlier in this chapter, to allow IIS to access your database, you'll need to set up a System DSN. After you've completed this, you can create the database, table, and user that you'll need to establish the Internet Service Manager for the Web, FTP, and Gopher services you're using.
See the "Building ODBC Data Sources" sections for SQL Server and Access, found earlier in this chapter, for more information about setting up the System DSN, Chapter 24
To change logging to use your ODBC database, first double-click the service from the Internet Service Manager that you want to set up. In the example shown in Figure 23.25, you can see the logging options for the Web Services. Note that all the different services use the same logging tab, so after you've set one up, you'll understand how to establish the remaining services.
Before you can point the services to the ODBC database, you'll need to create the database and corresponding table. Table 23.3 shows the column information for the table that will be used for the logging.
Column | SQL Data Type* | Access Data Type | Size |
ClientHost | Char | Text | 50 |
UserName | Char | Text | 50 |
LogDate | Char | Text | 12 |
LogTime | Char | Text | 21 |
Service | Char | Text | 20 |
Machine | Char | Text | 20 |
ServerIP | Char | Text | 50 |
ProcessingTime | int | Number | Integer |
BytesRecvd | Int | Number | Integer |
BytesSent | Int | Number | Integer |
ServiceStatus | Int | Number | Integer |
Win32Status | Int | Number | Integer |
Operation | Int | Number | Integer |
Target | Char | Text | 200 |
Parameters | Char | Text | 200 |
*Note: Nulls are allowed for all columns in the case of SQL Server.
You may recognize this information from the discussions about installing and setting up the server components as the table structure maps directly to the different components of the standard log file when logged to ASCII files.
You set up the log table to be used by all the different services you're logging for. Notice that the Service column will show exactly what was being done by the user and what operation was being performed by the server. Listing 23.8 shows the SQL Server script for creating the table.
/****** Object: Table dbo.LogTable Script Date: 4/28/96 10:04:11 PM ******/ if exists (select * from sysobjects where id = object_id(`dbo.LogTable') and sysstat & 0xf = 3) drop table dbo.LogTable GO CREATE TABLE LogTable ( ClientHost char (50) NULL , UserName char (50) NULL , LogDate char (12) NULL , LogTime char (21) NULL , Service char (20) NULL , Machine char (20) NULL , ServerIP char (50) NULL , ProcessingTime int NULL , BytesRecvd int NULL , BytesSent int NULL , ServiceStatus int NULL , Win32Status int NULL , Operation char (200) NULL , Target char (200) NULL , Parameters char (200) NULL ) GO
Now that the table exists for logging information, you can indicate where to log information for each of the services. From the Internet Service Manager, double-click the service you want to update. Select the Logging tab to work with the different logging options (see Figure 23.25).
FIG. 23.25
The next step to begin using ODBC for logging is to select the ODBC option and indicate
the login and database information.
After you've selected the Log to SQL/ODBC Database radio button, you'll be able to access the different setup fields for the logging. It's a good idea to set up a different database to manage the logging. If you do, you'll be able to more easily manage the logging information separately from the other information on your system.
In the example, a specific database, table, and user have been created to use for the logging. If you create a user, be sure you set the rights to at least Insert when you establish them on the table. After you apply the changes, stop, and restart the service. You'll be logging all server accesses to the database.
NOTE: If a user is accessing your server by using the FILE: protocol, the accesses will not be logged. These types of URLs are accessed by the client and handled by the client. Although the server will be providing the file to fulfill the request, it will not show up in the database. This is one detriment to using the FILE: type URL. If you want to be able to log accesses, consider making all links standard HTTP: type URLs, rather than providing direct links to the files.
When the logging is established, you can begin querying the database real-time to determine the activity on your server. In the next sections, you'll see some ways to provide this information in an easy-to-use and meaningful manner.
The log data can quickly become overwhelming unless you can wrap it in some meaningful queries. Some good information to know about your site includes information about the following, just as a start:
In the sample query in Listing 23.9, you can see that the database is examined to find out exactly this information, providing summary information for hits against the server.
NOTE: For the following scripts, you'll need to change the database table that they reference to correctly identify your system configuration. Replace "wwwlog" and "ftplog" with the logging database you use for your system logging. n
SELECT "Total hits" = count(*),"Last Access" = max(logtime) FROM wwwlog SELECT "" SELECT "Hit summary" = count(*), "Date" = substring(logtime,1,8) FROM wwwlog GROUP BY substring(logtime,1,8) SELECT "" SELECT "Time of day"=substring(logtime,10,2), "Hits" = count (substring(logtime,10,2)) FROM wwwlog group by substring(logtime,10,2) SELECT "" SELECT "Page" = substring(target,1,40), "Hits" = count(target) FROM wwwlog WHERE ( charINDEX("HTM",target)>0 ) GROUP BY target ORDER BY "hits" desc
In Listing 23.10, a similar script provides good feedback on FTP accesses, showing what files users are accessing on your system. Again, it's important to understand what types of things people are finding most, and least, helpful on your site.
select "Summary of volume by day" select substring(logtime,1,8), sum(bytessent), sum(bytesrecvd) from ftplog where bytessent > 1000 group by substring(logtime,1,8) select "" SELECT "Time of day"=substring(logtime,10,2), "Hits" = count(substring(logtime,10,2)) FROM ftplog group by substring(logtime,10,2) select "" select "Target" = substring(target,1,40), sum(bytessent), sum(bytesrecvd), count(substring(target,1,40)) from ftplog where bytessent> 1000 group by Target order by sum(bytessent) desc
Keep an eye on your site and always be looking for things that can be removed or demoted to less prominent presence on your site, while at the same time providing room for more new content that people are looking for on your site.
Of course, a great use of the IDC is to combine all of these different activities--logging, the IDC, the Web Page Wizard, and dynamic Web Page creation--to provide excellent feedback information online. Setting up the page is easy enough. You simply use the Web Page Wizard to query the database you've set up for logging and it is easy to call up as well, because it's established as a Web page.
Using this technique, you can create a Web page similar to the one shown in Figure 23.26 that will let you review your site activity while online.
FIG. 23.26
Keeping the site activity only a hyperlink click away is a good way to put the database
query capabilities to use at your site.
From here, it's a matter of pruning your Select statement to deliver more selective information. One idea would be to change the listing to return only the hits against the server by minute, for example. Then, by using an IDC link, you can offer the user the ability to drill down into a given time slice to gain more detail about an activity.
Another approach to this is to provide summary information by user with links to detailed log information for that user. The initial HTML page will show all users. The viewer can select the user to review and click the name. The next HTML page shows the user's access times individually. By clicking a specific access time, the reviewing party can see the details about what was accessed by that user during that session.
Integrating the IDC and drill-down informational research like this can really bring great leverage to you as an administrator. Remember, too, that you can place these pages into a protected subdirectory, making them available only to authorized users of your system.
If you're using SQL Server for your logging, you may notice that when you restart the server, the IIS service is unable to begin logging to SQL Server. You'll receive an error message if you select the World Wide Web service from the Internet Service Manager that indicates that the logging configuration is invalid.
This comes from the fact that SQL Server requires more time to start than do the Internet services. Because the services start and immediately attempt access to the logging system, they are unable to get to the database because SQL Server is still starting. To avoid this, you'll need to make an entry in the system registry that tells IIS to wait on SQL Server when it starts the services.
From the Registry, select the following key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\ W3Svc\DependOnService
Add a new service as part of this key for each application you need to have IIS wait on. With SQL Server, the service name is MSSQLSERVER. You'll also want to update the Gopher and FTP service keys. They reside in the same key location, but you'll need to substitute either GOPHERSVC or MSFTPSVC for W3Svc in the preceding code. The resulting keys are
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\ GOPHERSVC\DependOnService HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\ MSFTPSVC\DependOnService
For each of these, you can set the DependOnService value(s) to include the appropriate processes.
Note that if you have selected logging to SQL Server and do not perform these steps, the logging will fail to start, and you'll end up with no logging occurring for your site. It is very important to set up these keys as soon as possible after you begin using SQL Server to log your site accesses.
Internet development tools are evolving so quickly it's nearly impossible to keep up. This book references the Reality Check site in every chapter as a point of reference to additional resources on new technologies that were released or standardized after going to print. Be sure you check in at the Reality Check site frequently to see what's new. The URL for the site is www.pobox.com/~swynk, and it contains information on all sorts of technologies, including those briefly mentioned here.
Database access is one of the most hotly developed areas on the Internet. This is due to the obvious fact that if you are to use your existing systems to their fullest potential, you'll almost certainly be referencing existing or new databases. This chapter has focused on the IDC and HTX combination as a good way of accessing this information. There are additional means of getting to this information, including your choice of scripting languages.
Microsoft has been pushing hard into the Active Server components and technologies. This approach moves the processing to the server for some or all of the content customization, including database access, for your pages. This brings back the whole approach that has been emphasized so heavily with SQL Server, that of providing client/server applications as an efficient way to use both your server and the client systems.
There is a lot of debate right now about the "right" approach to providing database access to users. The choices fall into two distinct areas: providing client-side access to server-based databases and providing server-resolved access to server-based databases.
This might seem strange, but the issues really come down to how well defined and controlled your audience is. The choice is how the connection to the database will be handled. Because Microsoft is providing the software, and because they have a Web Browser, if you can ascertain that all users of your systems will have a Microsoft product, you can pretty much pick and choose between the approaches.
If, on the other hand, you can't absolutely promise what browser will be used, you'll need to consider using a server-based approach. A big benefit of the IDC approach is that it's server-based and completely browser independent. When it was first released, it was the only way of getting to databases; now it's become what amounts to as the lowest common denominator.
When you move to very recent browsers, like Internet Explorer 3.0 and upcoming promised versions of Netscape's product, you get some additional options, outlined next.
Most of the options that you have available to you are moving to using DAO-like statements in VBScript or other scripting languages. These statements are getting added functionality that lets them reference server-based objects that are handling the server-side connection to the database. For example, consider Listing 23.11, taken from one of the sample files provided with IIS.
<HTML> <HEAD><TITLE>ActiveX Data Object (ADO)</TITLE><HEAD> <BODY> <H3>ActiveX Data Object (ADO)</H3> <% Set Conn = Server.CreateObject("ADODB.Connection") Conn.Open "ADOSamples" Set RS = Conn.Execute("SELECT * FROM Orders") %> <P> <TABLE BORDER=1> <TR> <% For i = 0 to RS.Fields.Count - 1 %> <TD><B><%= RS(i).Name %></B></TD> <% Next %> </TR> <% Do While Not RS.EOF %> <TR> <% For i = 0 to RS.Fields.Count - 1 %> <TD VALIGN=TOP><%= RS(i) %></TD> <% Next %> </TR> <% RS.MoveNext Loop RS.Close Conn.Close %> </TABLE> </BODY> </HTML>
The first thing you'll notice is the CreateObject method referenced as a server object. The ADOBB object, based on the server, provides the information needed to connect to the server based on the connection information provided in the Conn.Open call. After the connection is opened, the server is ready to receive statements. You may recall from the chapters on developing with SQL Server that you initiate a recordset by setting up the SQL statement that is used to define the recordset.
This is accomplished with the EXECUTE method, establishing the results to work on. You can then reference the results set exactly as you do with standard DAO calls. You'll recognize the balance of the logic as being identical to how you work with the DAO in Visual Basic or other languages you use with the objects and collections.
Once you're working with the information in your database in this manner, it's a simple thing to populate the page and get the data formatted the way you need.
As of this writing, a new technology has started to arrive from Microsoft, the Advanced Data Connector. The ADC, available from the Web at Error! Reference source not found., is a new client/server development model for web-based database development.
Traditionally, the challenge facing the web-based application developer is that of data handling at the client and server sides of the application. It was not possible to keep an active, open session on the server because of the inherent limitations built into the HTTP protocol. HTTP was designed to let you make a request, have the request fulfilled, then have no further communication until you make another request.
With the IDC, you gained the ability to query and ODBC data source. This is a very good way to query a database independent of the browser. It's also a low-overhead approach to working with the database. Of course, the downside to this is how limited you are in terms of what you can do with the information because the results are static and you're forced to manage the logic to perform any updates.
In the ideal world, you'd be able to manipulate the database as you do others, using the DAO. The DAO objects and methods give you full access to the database, tables and queries or views. This is where the ADC comes in. The ADC gives you access to these types of methods.
The ADC gives you an interface to the remote data sources in ways similar to the traditional Visual Basic approach. Specifically, you have a control that is responsible for the connection to the server, and you can manipulate either that control, or others dependent on it, to retrieve and work with the database information. The ADC is based on the OLEDB specification for working with databases and business objects. You can find more about OLEDB at http://www.microsoft.com/oledb.
The ADC includes several core components, shown in Table 23.4.
Virtual Table Manager (VTM) | You can think of the VTM as "faking out" the data objects, giving them a logical table to work with that represents the data returned from the server. |
AdvancedDataControl object | This control, and the AdvancedDataSpace object, lets you set up forms that are bound to the database columns or fields. This is like the datacontrol in VB. |
AdvancedDataSpace object | As with the AdvancedDataControl object, this control helps set up the environment to support the use of bound controls over the Internet. |
AdvancedDataFactory object | The data factory is responsible for communicating over HTTP and working with the VTM to create the locally cached virtual tables, making them accessible to your application. This is the query and update interface to your database. |
ADISAPI | ADISAPI (Advanced Data Internet Server API) is the API used by IIS to work with ODBC and the server-side datafactory objects. |
Advanced TableGram Streaming Protocol (ADTG) | The ADTG is the internet-shortcomings-aware protocol that is used to shuttle the information between the client and server systems. |
With all of these objects, you might wonder how this will affect the code you write for these web-based applications. The good news is that the code is much like Visual Basic and you will be able to leverage much of your education, testing, and experience to date with VB in your development of these applications. The bad news is that the HTML is getting longer, but you probably have expected that by now.
In the following listing snippets, you can see an abbreviated listing from the sample application provided by Microsoft with the ADC documentation. It gives you a good look at building a form that not only uses these concepts, but also a grid from Sheridan that helps display the information from the server.
NOTE: In the following listing, portions of the code that have been removed are represented in the code by a set of ellipses, "..."
... <PRE> First Name <INPUT NAME=SFirst SIZE=30> </PRE> <PRE> Last Name <INPUT NAME=SLast SIZE=30> </PRE> <PRE> Title <INPUT NAME=STitle SIZE=30> </PRE> <PRE> E-mail Alias <INPUT NAME=SEmail SIZE=30> </PRE> <INPUT TYPE=BUTTON NAME="Find" VALUE="Find"> <INPUT TYPE=BUTTON NAME="Clear" VALUE="Clear"> <INPUT TYPE=BUTTON NAME="Update" VALUE="Update Profile"> <INPUT TYPE=BUTTON NAME="Cancel" VALUE="Cancel Changes">
Standard form definition tags are used to set up the form. In these opening HTML statements, the input forms are implemented and named. They can be later referenced for database updates and queries as needed.
The next section, outlined by the <OBJECT> tags, initiates the Sheridan grid control. Note that this control is assumed to be at the location specified on the server. The reference to Request.ServerVariables("SERVER_NAME") lets you find out the server name that is currently working with the Internet Explorer client.
The balance of the properties, for example AllowAddNew, are specific to the control and let you configure the control to suit your needs.
<OBJECT CLASSID="clsid:BC496AE0-9B4E-11CE-A6D5-0000C0BE9395" ID=Grid1 CODEBASE="HTTP://<%=Request.ServerVariables("SERVER_NAME")%>/MSADC/Samples/Sheridan.cab" HEIGHT= 125 Width = 495> <PARAM NAME="AllowAddNew" VALUE="TRUE"> <PARAM NAME="AllowDelete" VALUE="TRUE"> <PARAM NAME="AllowUpdate" VALUE="TRUE"> <PARAM NAME="BackColor" VALUE="-2147483643"> <PARAM NAME="BackColorOdd" VALUE="-2147483643"> <PARAM NAME="ForeColorEven" VALUE="0"> </OBJECT>
Next, you must set up the AdvancedDataControl object so that it provides the connectivity to the backend database. This object is linked to the Grid control above with the BINDINGS property. The properties for this object include the connect string that will be used to gain access to the remote database. As with the grid control, this object will be downloaded from the server, so you'll need to make sure you have it installed at the location indicated in the CODEBASE parameter.
TIP: Note the ID property in particular as this is what you'll use to refer to the control later when you move around the virtual dataset.
One thing to keep in mind is that the ODBC data source you indicate for the CONNECT property is a data source located and defined on the server, not on your workstation. This data source must be defined as a system data source and must be available in order for you to connect to the database.
This is one control measure that helps to make sure you're aware of all connections to your database and what their intent is. With this in mind, you'll want to make sure your ODBC configurations are as specific as possible. If you're providing SA-level access to data on your server, you're asking for problems as these approaches to working with the database become more prevalent.
... <!-- Non-visual controls - AdvancedDataControl --> <OBJECT CLASSID="clsid:9381D8F2-0288-11d0-9501-00AA00B911A5" ID="SControl" CODEBASE="HTTP://<%=Request.ServerVariables("SERVER_NAME")%>/MSADC/msadc10.cab" WIDTH=1 HEIGHT=1> <PARAM NAME="BINDINGS" VALUE="Grid1;"> <PARAM NAME="Connect" VALUE="DSN=ADCDEMO;UID=guest;PWD=guest;"> <PARAM NAME="Server" VALUE="http://<%=Request.ServerVariables </OBJECT>
Finally, now that the objects are initiated, you can work with the objects and the results set and perform the operations that you need to complete your application. The first SUBroutine, the LOAD routine, will run immediately on loading of the form. This routine populates the grid control with the initial results from the database. The .Refresh method is used to accomplish this, just as it is when the control is based in Visual Basic.
... <!-- VBS scripting for composing queries, updating profiles, and retrieving search results. --> <SCRIPT LANGUAGE="VBScript"> Dim myQuery SUB Load Grid1.CAPTION = "Arcadia Bay Corporate Phone Directory" `Initialize data grid with column names only. SControl.SQL = "Select FirstName, LastName, Title, Email, Building, Room, Phone from Employee where 2 < 1 for browse" SControl.Refresh END SUB
If the user clicks the FIND button, you can see a great example of how a query is issued against the database and how it is used to return the results of the query. The query is built in the variable myQuery. This string variable will contain the select statement that will be submitted against the grid control.
If you work down through this listing, you'll see that the query is using the .Value properties for the fields defined on the HTML form. These properties are inserted into the string if they are non-blank, then a percent-sign, "%" is appended, making the entry a wildcard.
Finally, the .SQL property of the grid control is set to the value of the myQuery variable and the grid is refreshed to show the results of the query.
... `Implement "Find" button - composes a dynamic SQL query to be processed by the database and returns matching records to be bound to the SGrid object. SUB Find_OnClick myQuery = "Select FirstName, LastName, Title, Email, Building, Room, Phone from Employee" `Check QBE fields and compose a dynamic SQL query. IF (SFirst.Value <> "") THEN myQuery = myQuery + " where FirstName like `" + SFirst.Value + "%'" END IF IF (SLast.Value <> "") THEN myQuery = myQuery + " where LastName like `" + SLast.Value + "%'" END IF IF (STitle.Value <> "") THEN myQuery = myQuery + " where Title like `" + STitle.Value + "%'" END IF IF (SEmail.Value <> "") THEN myQuery = myQuery + " where Email like `" + SEmail.Value + "%'" END IF myQuery = myQuery + " for browse" `Mark recordset for editing. `Set the new query and then refresh the SControl so that the new results are displayed. SControl.SQL = myQuery SControl.Refresh END SUB
If you're familiar with the DAO approach to database objects and methods, the navigation routines will look very obvious to you. You'll notice the following statements in the next few subroutines:
SControl.MoveFirst SControl.MoveNext SControl.MovePrevious SControl.MoveLast
Each performs as it does with the DAO, moving the logical record pointer around the dataset. The SubmitChanges method sends all updates to the server. When you call this method, the virtual tables are not updated, though they will likely reflect your changes as the control is bound to the data source.
NOTE: One thing to keep in mind as a difference between this and Visual Basic data-bound controls is that the information is not updated until you call the SubmitChanges method. To restore your dataset to its original state, use the CancelUpdate method. This will drop all changes you've made to the dataset.
`Submits edits made and pull a clean copy of the new data. SUB Update_OnClick SControl.SubmitChanges SControl.Refresh END SUB `Cancel edits and restores original values. SUB Cancel_OnClick SControl.CancelUpdate END SUB
There's an interesting trend here that you've either noticed or will notice soon after you implement these types of pages on your Web site. There's a fair amount of code, ActiveX controls, and logic running behind this type of application. This is a far cry from the typically light- interaction-based systems that HTML is currently sporting on the Internet.
Be careful in your use of this technology. Only use the active connection and dataset pooling capabilities in those cases where another approach simply won't cut it. It's recommended that you consider the IDC approach first, if only for it's smaller footprint at both the client and the server.
Because you can mix and match your approach to different portions of a given application, be sure you keep in mind that each page you design and develop should be considered alone relative to the best approach to the data management.
The ADC is the technologically and functionally superior model to use for developing applications. The downside is that it requires the use of Internet Explorer for the Visual Basic scripting, as of this writing, and it's heavy on the initial downloads of the various ActiveX controls. It may be better suited for intranet application development in cases where an application usage is casual.
In cases where you are building an application that will be used quite a lot, the initial download of the components is not much more than a typical installation of other software and may play out to be less of an issue in the design considerations.
Finally, be sure to review the DAO appendix for details regarding different methods that are supported. Also, check the Microsoft site frequently as more methods are added and additional controls become available. This is a technology that can bring some solid tools to the mainstream, frequently used web-based application development arena.
The IntelliCenter Reality Check site www.intellicenter.com puts into play nearly all of the different techniques listed here, including the ODBC logging. The review of the logs is a very frequent operation and is used to help drive course and online materials content from a marketing stance, as well.
By putting content online and announcing its availability both to internal student users and the customer community at large, IntelliCenter is able to see what types of materials interest people and what other classes and activities might be of interest to customers.
In addition, as class students are assigned accounts on the IntelliCenter system, the usage statistics help drive the online research content. By looking after the content that is most requested on their system, it's possible to increase the online research materials to meet demand.
Database access is used, along with the IDC interface, to provide transcripts of the classes taken by a student or a company's students, with access to the information guided by the individual that is signing in. A corporate leader from the company, as designated by the company, can review the different classes that have been taken by his or her employees. At the same time, an employee can review his or her own records and can gain access to online materials that relate to the classes taken to date.
At the Integra site (www.integra.net), IDCs and HTXs are used extensively to provide download opportunities for trial versions of software. In addition, several Active Server pages have been put into use on the intranet, where we have more control over the user's browser. We've implemented several different databases, from tracking schedules to managing distribution lists and phone numbers. All of these are in SQL Server tables, accessed with the ADO approach.
As you can see, the IDC is a very powerful extension to the IIS environment. Chances are good that after your initial installation of IIS to provide access to static HTML content, you'll quickly find that database-driven information is even more popular with the users you are serving.
This chapter touches on a number of different things. More information is provided about these topics in the following areas:
© Copyright, Macmillan Computer Publishing. All rights reserved.