Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 21

SQL Server and the World Wide Web


Yesterday you examined SQL Server, Windows NT, and performance tuning. You saw that there are a significant number of parameters to configure for SQL Server, as well as a significant number of performance monitor counters. This makes for a lot of objects and counters available to monitor, not all of which are obvious in meaning. For a more detailed examination of performance tuning issues, please see SAMs' Microsoft SQL Server 6.5 Unleashed.

Today you examine how to integrate SQL Server 6.5 with the Internet. It seems that every Microsoft product has some kind of integration with the Internet, and SQL Server is no exception. Everything you need to publish SQL Server data on the Internet is built into either the database or Windows NT Server (with Windows NT's Internet server--Internet Information Server). SQL Server 6.5 includes the capability to create static World Wide Web pages using a wizard known as the SQL Server Web Assistant. Other technologies (such as Active Server Pages and ActiveX Data Objects) allow you to create Web pages that provide an interactive experience with SQL Server in the form of dynamic queries or data maintenance forms.

Web Page

A Web page is a formatted text file written in a programming language known as Hypertext Markup Language (HTML). There are many versions of HTML, and the language is undergoing constant enhancements as the Internet grows and technology advances.

Web Server

A Web server is a server-based product that returns files to clients when requested. These files are typically returned in the form of Web pages to a client Internet browser. The Web server referenced in this book is Microsoft's Internet Information Server (IIS).

Internet Browser

An Internet browser is a client program that knows how to interpret HTML code as well as display information and graphics to a client. Some Internet browsers also have the capability to interpret and run programming languages at the client computer (such as Java or VBScript).

Push Web Page Technologies

Access to static Web pages is the first form of Internet access to SQL Server you examine. Static Web pages are those that don't change based on a user's interaction. Static Web pages are produced with a method known as push technology. Push Web page development consists of generating static Web pages and writing those pages to a directory on a World Wide Web (WWW) server. The Web server, such as Internet Information Server, then makes these pages available in the form of static Web sites. The majority of Internet sites have historically been developed using push technology with static Web pages. These static pages don't allow dynamic access to data. This type of Web page access is useful, but is being replaced rather rapidly with pull technology.

Pull Web Page Technologies

Pull technology allows Web pages to be built dynamically, as a response to a query to SQL Server. This query can be in the form of a standard Transact-SQL statement, a set of options that build a query, or a form that updates data. This type of access is allowed through a variety of techniques.

1. Microsoft Internet Database Connector (IDC) files

IDC files are a simple and effective way to dynamically query SQL Server. This technology has been around for at least the last three releases of Internet Information Server.

2. Microsoft Active Server Pages (ASP) with ActiveX Data Objects (ADO)

ActiveX Data Objects (ADO) is currently Microsoft's recommended access method for querying SQL Server data dynamically from the Internet. This happens in the form of server-side scripts written in VBScript or Java. VBScript is a subset of the Visual Basic programming language, and Java is a programming language originated by Sun Microsystems. Code is run by IIS, and Web pages are built and returned to the Internet client (typically a Web browser, such as Internet Explorer).

3. Microsoft Remote Data Services (RDS) (formerly Advanced Data Connector (ADC))

RDS is in a state of transition. The easiest way to describe this method is that scripts are run as previously described, except that the scripts are run at the client computer by the user's Web browser. Database access occurs over the Internet from the client. This method allows more functionality, but has security concerns, which restrict this functionality to intranets. An intranet is a set of Web clients and at least one server that allows access inside a company, rather than allowing access via the global Internet.

Push Updates to Web Pages Using the SQL Server Web Assistant

As stated, the push method allows you to create static Web pages using SQL Server data. You can run a select statement with ISQL/W, save the results, and turn the results into an HTML document. To make this task easier, you could use an HTML editor such as Microsoft FrontPage or Microsoft Word 97. However, SQL Server 6.5 includes the SQL Server Web Assistant.

To start the Web Assistant, select Start | Programs | Microsoft SQL Server 6.5 | SQL Server Web Assistant. The SQL Server Web Assistant login screen appears, as shown in Figure 21.1.

Figure 21.1. The SQL Server Web Assistant Login screen.

This opening window prompts you to specify to which server you want to connect and the appropriate login information. You can also request a trusted connection by checking the Use Windows NT Security to Log In Instead of Entering a Login ID and/or a Password. The login you choose must have select permissions on any data you want to publish on a Web page.

You are presented with the Web Assistant Query window, shown in Figure 21.2, after you've logged in to your SQL Server.

Figure 21.2. The SQL Server Web Assistant Query window.

You can specify how you'd like to build your query request. You have three choices.

1. Build a query from a database hierarchy. You can graphically drill down to a table or to individual columns within a table. You also have the option of specifying a WHERE clause, GROUP BY clause, or other options (such as a HAVING clause) in the window below the graphical query display. If you include string comparisons here, you should use double quotation marks around your literal strings.

2. Enter a query as free-form text. You could simply type a Transact-SQL query if you know exactly which data you want to display on your Web page. You must also specify in which database you'd like to run the query.

3. Use a query in a stored procedure. With this option, you specify the database in which you want to run a stored procedure and the name of the stored procedure. The wizard displays the stored procedure's text (unless the procedure was created with encryption). Enter the parameters in the form @parmname = x if there are any parameters you want to submit. See Day 15, "Views, Stored Procedures, and Triggers," for more information about parameters when calling stored procedures.

For this example, you should select the default option to build a query from the database hierarchy. Expand your pubs database, and then expand the authors table to see all columns in the table. Select the au_id, au_lname, and au_fname columns (as you select them, their icons should turn bright green). Enter the following in the additional criteria window to select only authors who don't live in California, and order the results by the author's last name.

:

Where state <> "CA"
ORDER BY au_lname


NOTE: You can perform a join by selecting columns from multiple tables. Be sure you include Transact-SQL join criteria to avoid a cross-join.

Click Next to view the Scheduling screen (see Figure 21.3).

Figure 21.3. The SQL Server Web Assistant Scheduling window.

Specify when you want the Web page to be generated. The default is to create your Web page at this time. This means your page is generated immediately upon completion of the wizard.

You can also choose the following options:

Three triggers are created for each table you select (one for insert, update, and delete) when you have selected the When Data Changes option. A sample trigger is listed here:

CREATE TRIGGER Web_16003088_2 ON dbo.authors
FOR UPDATE AS
exec sp_makewebtask
@outputfile='C:\WEB.HTML',
@query='select a.au_id, a.au_lname, a.au_fname
FROM pubs.dbo.authors a
WHERE STATE <> "CA"
ORDER BY au_lname',
@fixedfont=1,
@bold=0,
@italic=0,
@colheaders=1,
@lastupdated=1,
@HTMLheader=2,
@username='dbo',
@dbname='pubs',
@webpagetitle='SQL Server Web Assistant',
@resultstitle='Query Results',
@maketask=0,
@rowcnt=0
GO

As you can see, it creates triggers with names that begin with Web". If a trigger already exists for a table, the wizard automatically modifies the trigger and displays the modifications to you for your approval. The trigger calls a single stored procedure, sp_makewebtask, which is a system stored procedure that creates the Web page you request.

sp_makewebtask {@outputfile = `outputfile', @query = `query'}
[, [@fixedfont = fixedfont,] [@bold = bold,] [@italic = italic,]
[@colheaders = colheaders,] [@lastupdated = lastupdated,]
[@HTMLHeader = HTMLHeader,] [@username = username,]
[@dbname = dbname,] [@templatefile = `templatefile',]
[@webpagetitle = `webpagetitle',] [@resultstitle = `resultstitle',]
[[@URL = `URL', @reftext = `reftext'] | 
[@table_urls = table_urls, @url_query = `url_query',]]
[@whentype = whentype,] [@targetdate = targetdate,] [@targettime = targettime,]
[@dayflags = dayflags,] [@numunits = numunits,] [@unittype = unittype,]
[@procname = procname, ] [@maketask = maketask,] [@rowcnt = rowcnt,]
[@tabborder = tabborder,] [@singlerow = singlerow,] [@blobfmt = blobfmt]]

This command is extremely flexible (which means it's very complex), so stick with the wizard.

After you've set a schedule for your task (Now was selected for this example), click Next to view the File Options window (see Figure 21.4).

Figure 21.4. SQL Server Web Assistant File options.

Configure the path and filename of the generated Web page. For this example you can leave it at the default (c:\web.html), but you normally locate this file in the path of your Web server. If this is on another server, you can use a UNC path to specify a share on your Web server that holds the file.

You have the option of using an HTML format file. A template file is any HTML-formatted file that contains the <%insert_data_here%> entry. You can also set basic formatting, such as a title for your Web page and a title for your query. You can also add a hyperlink to another Web site on your page. If you select the middle option to add a single link to your page, the wizard conveniently suggests that you insert a link to Microsoft (http://www.microsoft.com), along with a suggested label for the link.Use this default link for your example.

Click Next to view the final step: the Formatting window (see Figure 21.5).

You cannot format the results title if you chose to use a format file, but all other formatting options are available here. Configure your data as you want it to appear, as well as set the options at the bottom of the window. The Limit the Query Results To option specifies that you can limit a query so that your Web page doesn't become excessively long.

Click on the Finish button to complete the Web assistant. You are presented with an All Done dialog box, with a Close button as your only option. Dismiss this dialog, and open the Windows NT Explorer. Go to the root of your C drive, where you see the file web.html. Double-click on the file to start your Web browser and view the file. You can see the file in Figure 21.6.

Figure 21.5. SQL Server Web Assistant Formatting dialog box.

Figure 21.6. The SQL Server Web Assistant Web file.

Pull Methodology for Dynamic Web Pages

As you have seen, the SQL Server Web Assistant is extremely handy for building static Web pages without much (or any) knowledge of HTML. However, the push methodology builds static pages--pages that do not change based on a user or their needs. The Internet has changed very rapidly since SQL Server 6.5 was released, and several other Microsoft technologies that relate to data access from the Internet have been upgraded as well. Each of the techniques you examine here requires that IIS be installed and that you have administrative access to the service and the Web files.

Using the Internet Database Connector (IDC)

The Internet Database Connector (IDC) is the first method of accessing SQL Server from the Internet you examine. IDC access works with a technology known as ISAPI. ISAPI, Internet Services Application Programming Interface, applies to Internet applications using Microsoft Internet Information Server. IDC access to databases is provided with ODBC (Open DataBase Connectivity). ODBC is used to gain access to any relational database that has an ODBC driver, including SQL Server 6.5.

IDC technology uses a combination of two files to produce your dynamic Web pages.

To understand how these files are used, you need to step through a sample site and see them in action.

1. A Web site user fills out a form with parameters to help build a query for a database.

2. The user clicks on a Submit button, which collects the parameters and passes them in a call to the .IDC file. The user might also click on a hyperlink to call the .IDC file, but no parameters are passed.

3. The .IDC file is used to build and run a query against your database.

4. The .HTX file is used as a template, and the query results are merged into the .HTX file.

5. The results are then sent back to the Web browser and displayed as a standard HTML file.

The Guest Book Sample Application

Microsoft's Internet Information Server 2.0 (or newer releases) includes a sample database application of an Internet guest book. You can use that built-in example to teach yourself how to use this set of technologies to provide dynamic Web pages. For the purpose of this discussion, you need to have IIS version 2.0 or later installed on your system (it is included as part of Windows NT 4.0). Begin by opening your Web browser and typing http://yourcomputer.

The name SQLGUY is used in this example (see Figure 21.7). The example shown here is viewed with Internet Explorer version 4.0 and IIS version 3.0.

Figure 21.7. The Internet Information Server default Web page.

Click on the Database button on the upper-right of your Web page to see the Internet Database Connector page, as it's shown in Figure 21.8.

Figure 21.8. The Internet Information Server IDC Web page.

As you read through this page, you see that you can set up a guest book rather quickly. You need to create an ODBC DSN (DataSource Name). To do so, click Start | Settings | Control Panel, ODBC (or 32-bit ODBC, depending on the release of ODBC you have installed). Add a System DSN (you may need to click on the System DSN tab), choosing SQL Server as the type of driver to use, and name the DSN

Web SQL.

Set the login ID as sa (or your Login ID if you are not the database server SA) and the appropriate password. Set the default database to pubs and complete the system DSN configuration.


TIP: You can visit the Microsoft ODBC Web site at http://www.microsoft.com/odbc to get updated ODBC drivers for your computer.

Now switch back to the IDC Web page in your Web browser, and scroll down to the section labeled Sample Guestbook Application. Click on the highlighted text labeled Create a Table. You see the screen shown in Figure 21.9.

Figure 21.9. Successful table creation.

This indicates that a couple of successful things have happened. First, it indicates that you set up your ODBC connection correctly. Second, you just used an .IDC/.HTX file combination to run a query. Notice that the address of the Web page you are viewing (http://sqlguy/scripts/samples/ctguestb.idc?) references an .IDC file. Examine that file (ctguestb.idc) by starting Windows NT Explorer and finding your INETPUB\Scripts\Samples directory (see Figure 21.10).

Figure 21.10. Windows NT Explorer and your scripts\samples directory.

Open the ctguestb.idc file and view it with Notepad. You see the following text:

Datasource: Web SQL
Username: sa
Password:
Template: ctguestb.htx
SQLStatement:
+CREATE TABLE "Guests" (
+"FirstName" varchar(40),
+"LastName" varchar(40),
+"Email" varchar(40),
+"Homepage" varchar(80),
+"Comment" text,
+"WebUse" varchar(40)
+)

The file describes the ODBC DSN, username, password, template file, and SQL statement that will be run. The previous hyperlink you clicked calls this file. The query is run, and the template file (ctguestb.htx) is populated and displayed. View the .HTX file to see the HTML template. This HTML file does not accept any parameters and simply displays a static HTML file.

<HTML>
<HEAD><TITLE>Internet Server Guestbook Table Creation</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>
<CENTER>
<H1>Table "Guests" created successfully</H1>
</td>
</tr>
</TABLE>
</BODY>
</HTML>

Click on the Database link to return to the main IDC Web page. Click on the Use the Guestbook hyperlink. You are presented with the Guestbook Registry Web page (see Figure 21.11).

Figure 21.11. The Guestbook Registry Web page.

Complete the Web page and click on the Submit Entry button. This records the data in your SQL Server database. Again, examine the .IDC file referenced: the register.idc file.

Datasource: Web SQL
Username: sa
Template: register.htx
RequiredParameters: FirstName, LastName
SQLStatement:
+ if exists (
+    select * from Guests
+    where FirstName='%FirstName%' and LastName='%LastName%'
+    )
+      select result='duplicate'
+else
+  INSERT INTO Guests
+  (FirstName, LastName, Email, Homepage, Comment, WebUse)
+  VALUES(`%FirstName%', `%LastName%', `%Email%', `%Homepage%',
+   `%Comment%', `%WebUse%');

You can see that a check is run for the existence of the user in the database, and if the user exists, the word duplicate is returned. Otherwise, an INSERT statement using parameters that came from the previous form is run. The results are then merged with the .HTX file and displayed.

You can find the guest by clicking on the Query Guestbook hyperlink after you've added someone to your guestbook. You see the Query Guestbook form shown in Figure 21.12.

Figure 21.12. The Query Guestbook Web page.

Click View | Source from your Web browser menu to see the HTML used by this Web page (query.htm). As you examine the HTML, you see lines such as the following:

<form action="/scripts/samples/query.idc" method=get>
<input type="text" name="FirstName" value="" size=30> First Name
<p>
<input type="text" name="LastName" value="" size=30> Last Name
<p>

The input lines are the text boxes you see on the Web page, and the name parameter is the name you reference in your .IDC file. Switch back to your Web browser and click the Submit Query button. You are presented with a list of qualifying names in the database. This called the query.idc file:

Datasource: Web SQL
Username: sa
Template: query.htx
SQLStatement:
+SELECT FirstName, LastName
+FROM Guests
+WHERE FirstName like `%FirstName%'
+and LastName like `%LastName%'
+and (WebUse like `%WebMaster%'
+  or WebUse like `%WebSurfer%'
+  or WebUse like `%ISV%'
+  or WebUse like `%ContentProvider%'
+  or WebUse like `%WebIntegrator%')
DefaultParameters:FirstName=%,LastName=%

What you see here is a query that uses parameters from the query.htm file you examined earlier. The name parameter from the form is used as the variables for your SQL query, wrapped in the % character. The results are then merged with the template file, query.htx.

<html>
<title>Guestbook Query Results</title>
<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>
<h1>Selected Guestbook Contents</h1>
<font size=2>
<%begindetail%>
<%if CurrentRecord EQ 0 %>
<h2>Here are the selected contents of the guestbook. Click a name to 
get details:</h2>
<p>
<%endif%>
Name: <a href="/scripts/samples/details.idc?FName=<%FirstName%>
&LName=<%LastName%>"><b><%FirstName%> <%LastName%></b></a>
<p>
<%enddetail%>
<%if CurrentRecord EQ 0 %>
<h2>Sorry, no entries match those criteria.</h2>
<%endif%>
<p>
<hr>
<a href="/scripts/samples/viewbook.idc?">View Guestbook</a>
<p>
<a href="/samples/gbook/query.htm">Query Guestbook</a>
<p>
<a href="/samples/gbook/register.htm">Add New Entries</a>
</font>
</td>
</tr>
</table>
</body>
</html>

The %begindetail% tag is the key to this file. From that tag to the %enddetail% tag, you may code how you want to return the query results. Again, you reference the % symbol around your column names to show where the results of your query should go.

Coding Your Own .IDC and .HTX Files

Tired of looking at all this sample code yet? You can code your own data files and queries rather quickly. In this step, you create three Web files to produce two Web pages:

First, create the HTML file. If you have an HTML editor such as Microsoft FrontPage, you can build it there. However, you can also enter the following code with Notepad:

<html><head><title>Teach Yourself SQL Server 6.5 in 21 days
</title></head>
<body>
<p align="center"><strong>Our Sample Web Page</strong></p><P>
<form method="POST" ACTION="/scripts/samples/authors.idc">
<p>Enter the author's last name: <input type="text" size="20"
name="aulname"></p>
<p align="left"><input type="submit"
name="BtnSubmit" value="Submit"></p>
</form></body></html>

Next, create the authors.idc file referenced previously in the scripts\samples directory on your Web server (the inetpub\scripts\samples directory by default). The file should look like this:

Datasource: Web SQL
Username: sa
Password:
Template: authors.htx
SQLStatement:
+SELECT AU_Fname, AU_Lname
+FROM Authors
+WHERE au_lname like `%aulname%'

Finally, the authors.htx file:

<HTML><HEAD><TITLE>Our Web Results</TITLE></HEAD>
<BODY><TABLE>
<%begindetail%>
<TR><TD><%au_fname%></TD><TD><%au_lname%></TD></TR>
<%enddetail%>
<%if CurrentRecord EQ 0 %>
<h2>Sorry, no entries for that last name.</h2>
<%endif%>
</table></body></html>

When you have created these three files, open your Web browser and type the following line to view your Web page (see Figure 21.13):

http://sqlguy/authors.htm

Figure 21.13. Your author's Web page.

Enter an author's last name (White, for instance), and click on the Submit button. Your .IDC file is called, your query is run against the authors table in the pubs database for this author, and the results are displayed (see Figure 21.14).

Figure 21.14. Your query results.

Congratulations! As you can see, it isn't that hard. Serious Web developers will want to add images, format results, and so on, but you've seen the fundamentals and can proceed from here.

Other Internet Access Methods to SQL Server

ActiveX Data Objects (ADO) is the most significant access technology from Microsoft's perspective at this time. ADO is a data access model for querying OLE DB-compliant databases (those databases that have an OLE DB driver, including SQL Server). You write Active Server Pages (files with a .ASP extension), which are processed by IIS version 3.0 or later, and the results are returned to a client's Web browser. The calls to ADO are written with server-side script, such as Microsoft's VBScript programming language. This means that you can use virtually any Web client, since only "normal" HTML code is returned to the client's Web browser.

ADO access to databases is much more powerful than using IDC files, but also significantly more complex. You can either code ADO database access in your .ASP files, or create ActiveX controls (separate DLL files or executable programs) that call SQL Server. Those ActiveX controls can also be called as part of Microsoft Transaction Server. Understanding all of these technologies and how they work together is a complex. For additional information about programming ADO and server-side scripting, see Sams' Teach Yourself Active Server Pages in 14 Days, ISBN 1-57521-330-3.

Finally, Microsoft also has a technology known as Remote Data Services (RDS). This technology was formally known as the Advanced Data Connector, or ADC. This technology uses ADO to query databases, but runs on your client Web browser using ActiveX controls. Therefore, your Web browser must support ActiveX controls. You can get more information about all of these technologies by going to http://www.microsoft.com/data. You can use Microsoft Visual Interdev to help you develop these Web pages and database access programs.

Summary

SQL Server is the ultimate database for use on the Internet. Microsoft has provided a great tool for building static Web pages--the SQL Server Web Assistant. You can build Web pages on a scheduled basis or create triggers automatically to build Web pages. If you want to examine more advanced and interactive SQL Server usage, you can use the .IDC/.HTX connectivity mechanisms. If you want to proceed even further, you can investigate using ActiveX Data Objects (ADO) as a programmatic interface to SQL Server, either on the server side, or the client side using Remote Data Services (RDS).

Q&A

Q Can the SQL Server Web Assistant produce dynamic Web pages?

A
No. It only produces static HTML pages.

Q Can I write .IDC/.HTX files to dynamically update data?


A
Yes. See the sample guest book registration for an example of data modification.

Q If I already have a trigger on a table, can I use the SQL Server Web Assistant to create triggers on the same table?


A
Yes. The wizard automatically prompts you to modify any existing triggers.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience using what you've learned. Try to understand the quiz and exercise answers before continuing on to the next day's lesson. Answers are provided in Appendix B.

Quiz

1. Which Internet utility is appropriate for use when building static Web pages?

2. Which technology would you use if you want to build a simple query interface to SQL Server on the Internet?

3. Which technology would provide the most functionality for advanced query access and data maintenance?

4. If you receive a query syntax error, which file would you examine to find the error between these three: authors.htm, authors.idc, authors.htx?

Exercises

1. Run the SQL Server Web Assistant to build a static Web page for tables in the database. Try both a scheduled page and data modification updates using triggers. Modify a table with a Web trigger and verify that your Web page is updated.

2. If you didn't do so, create an .IDC/.HTX file combination to allow queries against your SQL Server database.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.