Assume that you are a publisher
who wants to publish the list of authors in a web page on a regular
basis. As new authors are added to your database or any information
about the authors changes, you want this page to be refreshed to
contain the latest information. You can view this requirement as
either a dynamic page requirement that gets generated each time
a user requests for the page, or as a static page requirement that
is generated periodically.
Since these kinds
of "catalog" based web-pages do not require much interactivity,
it is better to implement the authors list page as a static page
that is re-generated periodically, rather than using scripting languages
to connect to the data source and then render the page each time
a request is made.
There are many ways to implement static pages that
are re-generated periodically, but the solution that we are going
to see in this article is by using the SQL Server stored procedure
called sp_makewebtask. This task creates a task that produces
an HTML document containing data returned by executing queries.
Before we delve into the various parameters of this command, let's
see some traditional methods of generating such web pages from SQL
Server.
The examples in this chapter work with the pubs
database. If you want to try out the various examples, you will
need access to the pubs database.
Using Stored Procedures
The first solution that we are going to see involves
writing a stored procedure that outputs a series of HTML tags with
the data embedded in between them. The following is a simple stored
procedure that outputs HTML.
CREATE PROCEDURE GetAuthorsHTML AS
BEGIN
SET NOCOUNT ON
SELECT '<html>', '<head>', '<title>List of Authors</title>', '</head>', '<body>', '<table border="1">', ''
UNION ALL
SELECT
'<tr>',
'<td>' + au_id + '</td>',
'<td>' + au_lname + ', ' + au_fname + '</td>',
'<td>' + address + ', ' + city + ', ' + state + ' - ' + zip + '</td>',
'<td>' + phone + '</td>',
'<td>' + au_fname + '@hotmail.com' + '</td>',
'</tr>'
FROM
authors
UNION ALL
SELECT '</table>', '</body>', '</html>', '', '', '', ''
END
The above procedure picks the relevant information from the authors
table and displays the same in HTML format. Note that various HTML
tags are mixed along with regular column values. Now that this procedure
is done, the following command extracts the output of this command
into a .html file.
Now, if you execute the Authors.html file from a browser, you will
see the following output.
It is very easy now to schedule this command
using either the windows scheduler or the SQL Server scheduler.
The scheduler can be programmed to generate this HTML every day.
But, the problem from adopting this approach is very evident. Trying
to generate "proper" HTML from a case like above is very
difficult, since data access and HTML strings are mixed, thus making
it very difficult to embed complex HTML attributes like style sheets,
or other DHTML features. Also, using the osql command only
allows us to generate HTML periodically, but what if we want to
generate the HTML when a column changes?? This is where the next
technique can be used.
Using sp_makewebtask
The sp_makewebtask is a stored procedure
that can be used to generate HTML pages based on a query. The query
generation can be automated to a great extent and also customized
so that we can add custom features (like DHTML scripting) to the
same. This stored procedure takes approximately 33 parameters and
it is not possible to cover all of them here!! We will see some
of the most common usages of this command and how we can customize
the generated HTML.
Before we start exploring this command, let's create
the following stored procedure that provides us the necessary output.
CREATE PROCEDURE GetAuthors AS
BEGIN
SET NOCOUNT ON
SELECT
[Author ID] = au_id,
[Author Name] = au_lname + ', ' + au_fname,
[Address] = address + ', ' + city + ', ' + state + ' - ' + zip,
[Phone] = phone,
[Email] = au_fname + '@hotmail.com' + ''
FROM
authors
END
Now, let's see the most simplest usage for the sp_makewebtask command.
The following table explains the parameters used in this command.
Some of them are defaults and need not be included, but I've included
them in the command for the sake of completion.
@outputfile.
This parameter specifies the output file for the execution of
the command.
@query.
The query that is to be used for generating the HTML output.
This is mostly a SELECT statement or a stored procedure call.
@colheaders.
This parameter indicates whether column headers are to be displayed
in the output. The default is to display headers.
@lastupdated.
This parameter indicates whether the last-updated date information
is to be displayed in the output. The default is to display
the value.
@dbname.
This parameter indicates the database on which the query will
be executed.
@resultstitle.
This parameter indicates the title to display on top of the
results section.
@URL.
This parameter indicates a URL that needs to be displayed at
the bottom of the result set.
@reftext.
This parameter indicates the text to be displayed for the URL
identified by @URL.
The following figure illustrates the output the
is produced by this command:
If you see the output of this command, SQL Server
allows us to customize the title of the results, title of the web
page and the ability to add some hyperlinks at the bottom of the
page. Beyond, this, if we need to provide other features like a
custom table header, color scheme and other DHTML features, we need
to use a concept called a template file. The template file
is a skeleton HTML file that contains all the necessary feature
inclusions with relevant commands to instruct SQL Server to include
the output of the query. The following example shows a template
file and the associated web query.
<html>
<head>
<title>Authors Catalog</title>
<style>
.tableHeaderStyle
{font-weight:bold; font-size:8pt; color:white; background-color:blue;}
.tableColumnStyle
{font-weight:normal; font-size:8pt;}
.normalStyle
{font-weight:bold; font-size:8pt; color:gold;}
</style>
</head>
<body>
<h1>Our Authors Catalog</h1>
<hr/>
<table border = "1">
<!-- Generate the header for the table -->
<tr>
<th class="tableHeaderStyle">Author Name</th>
<th class="tableHeaderStyle">Address</th>
<th class="tableHeaderStyle">Phone</th>
<th class="tableHeaderStyle">Email</th>
</tr>
<!-- Generate the actual table content using place markers -->
<!-- This sequence of statements will be repeated for each row -->
<%begindetail%>
<tr class="tableColumnStyle">
<td><a href=http://yourserver.com/authordetails?au_id=<%insert_data_here%>><%insert_data_here%></a></td>
<td><%insert_data_here%></td>
<td><%insert_data_here%></td>
<td><%insert_data_here%></td>
</tr>
<%enddetail%>
</table>
<hr/>
<div class="normalStyle">Home | Contact Us | Privacy Statement</div>
</body>
</html>
The above will can be saved as a template file (for example Authors.template)
and then included in the query as shown.
Note the usage of the @templatefile
option that points to the file. If you see the template file, we
see the place holders <%begindetail%>, <%enddetail%>
and <%insert_data_here%>. The <%begindetail%>
and <%enddetail%> markers indicate a portion
of the file where output will be included. The <%insert_data_here%>
marker indicates the location where a column information is to be
placed. Note that the number of <%insert_data_here%>
markers is the same as the number of columns that are retrieved
and the order in which the marker is replaced also depends on the
order of column selection.
The following is the output of the above command.
Now having seen how to generate HTML pages, let's
see how we can instruct sp_makewebtask to schedule the page
to be generated when data changes. Let's assume that each time some
information changes in the authors table, we need to re-generate
the web page. The following command can be used for the same.
The @whentype=10 specifies that the page should be immediately
generated and then re-generated each time anything in the authors
table changes. Specifying @datachg='TABLE=authors'automatically creates an INSERT/UPDATE/DELETE trigger
on the authors table that executes the sp_makewebtask command
whenever any change is made to the table. If any column(s) are to
be specified, then the command becomes:
In the above command, whenever any change to made to the au_id
or the au_fname columns, then the web-page is again created.
Using this feature, we can ensure that the users are always guaranteed
to have the latest web pages.
Conclusion
In this article, we have seen how simple, yet effective
web pages can be created using the sp_makewebtask stored procedure.
I encourage you to evaluate this procedure and try all its commands
to really appreciate the power of this command and its customizability.
