Microsoft Index Server Tips and Tricks

Microsoft Corporation

August 1997

Contents

Querying

Indexing

Granting Interactive Logon Privileges

Indexing SQL Server

Introduction

During the development of Microsoft® Index Server, the team discovered and invented a number of different ways to use the full power of Index Server. Some of these discoveries are based on helpful feedback from beta testers and customers. This article shares these discoveries and inventions with you.

Querying

The tips in this section help you to further refine queries to return exactly what you are searching for.

Compound Words

To find all the compound forms when querying, enter the hyphenated form of the compound. A common example is the word database, which is a compound of data and base. Often, people are inconsistent in how they create the compound, so forms such as data base and data-base will appear in text. If you query for data-base, Index Server will search for and return all instances of data-base, data base, and database.

Property Queries

In the .idq file, if you set the CiForceUseCi variable equal to True, some property queries with regular expressions will behave differently. For example, if the file Ciquery.htm has been indexed, Index Server will return hits for queries such as:

#filename ciquery.htm
#filename *htm
#filename ciquery*

For the FileName property, both the file name and reversed file name are stored in the content index. The reversed file name is needed to resolve the query #filename *.htm because the content index can match prefixes, but not suffixes.

If a file named Test.doc has been indexed, Index Server will return hits for queries such as #doctitle test* and #doctitle test.doc, but not for queries such as #doctitle *.doc. See the DocTitle reference on the "Querying" page of the Microsoft Windows NT® Server 4.0 Web site.

Vpath (as well as Path and Characterization) are not stored in the content index. Therefore, when CiForceUseCi is set to True, Index Server will not return hits for queries such as the following, even if Test.doc has been indexed:

#vpath /test.doc
#vpath /tes*

Indexing

Recently Accessed Pages

You may often find yourself trying to find information you recently viewed using your browser. If you find yourself thinking "I remember looking at a page just the other day that . . . ," then the following little trick may be useful.

If you're running Index Server on the same computer you run your browser on (for example, on a Microsoft Windows NT Workstation with Peer Web Services), you can create a virtual directory for your browser's cache folder. If you do this, whenever you access pages on your intranet or on the Internet, Index Server will index the pages for you automatically.

When you post queries, pages you accessed recently will be returned. The references to the pages won't be to the actual pages on the Web, but rather to the cached pages in your browser cache. This isn't usually a big problem because just getting the page back will often refresh your memory. Also, the cached pages will often contain links to the original site.

Log Entries

If you've ever tried to find something in the Microsoft Internet Information Sever (IIS) logs, the preceding trick should make the task much easier. Just create a virtual directory for the IIS logs directory. Index Server will index the logs by using the text filter. This allows you to post queries for entries in the log and use hit highlighting in Index Server 1.1 to find the entries quickly.

If fact, because Index Server logs all queries, you can actually query for the words and terms other users are querying for!

Non-Latin Characters

To search for non-Latin characters in set of documents, such as Cyrillic characters, the locale setting in the .idq file must be the same as the locale setting for the server. For example, to set CiLocale in your .idq file to Russian, type the RU code. Alternatively, you can set the HTTP_ACCEPT_LANGUAGE tag in your browser to Russian.

Table of Base Charsets, Display Names, and Aliases

To identify the code page of a Hypertext Markup Language (HTML) file, the HTML filter looks for a meta tag that specifies the charset of the page. For example, the following meta tag specifies that the HTML page uses the Windows-1252 code page:

<meta http-equiv="Content-Type" content="text/html; charset= Windows-1252">

If a code page has been specified, the HTML filter uses the code page to the convert the multibyte character encoding of the page to Unicode.

In Table 1, the Base charset lists the basic translation software built into Microsoft Internet Explorer version 3.0 or available as Langpack. The Aliases column lists all other character-set IDs that are recognized and can be represented without translation by using the base charset translation method. This does not in all cases mean that aliases and the base character set represent the same character set, but that the aliases character set can be a subset of the base character set. Base charset is not a recognized name unless repeated in the Aliases column.

The Display name column represents the name for this character set in the Internet Explorer user interface and should be a localizable resource.

Table 1. Base charsets

The following have been added for Internet Explorer version 3.0a:

The following Base charset will be added in a future version of Internet Explorer:

Granting Interactive Logon Privileges

In some cases a user may need interactive logon privileges on a computer.

To grant these privileges:

1. Open the Windows NT User Manager for Domains on Server A.
   
   
2. In the Username list, select the user ID you want to grant logon privileges to.
   
   
3. From the Policies menu, select User Rights.
   
   
4. In the Right box on the User Rights Policy dialog box, click the arrow and select Log on locally.

A user name may not appear in the Grant To list on the User Rights Policy dialog box.

To add the user:

1. Click Add.
   
   
2. In the List Names From box, click the arrow and select the domain on which you want to grant interactive logon privileges.
   
   
3. Click Show Users.
   
   
4. From the Names list, select the user, and click Add.
   
   
5. Click OK.
   
   
6. Make sure the user appears in the Grant To list in the User Rights Policy dialog box.
   
   
7. Click OK.

Indexing SQL Server

In addition to searching for text in various formats, Index Server can search for Web-based textual data in tables formatted with Microsoft SQL Server™ version 6.5 relational database management system. This article gives you two examples:

• Example 1: Stores Table
  
  
• Example 2: Pub_Info Table

SQL Server contains a stored procedure, sp_makewebtask. This stored procedure exports SQL Server data as Web pages. By combining custom stored procedures and triggers, you can generate a Web page for each record in a SQL Server table and then index the Web pages with Index Server.

The following procedure outlines how this is done.

1. Select a directory that Index Server can access.

   This directory will store Web pages generated from the SQL Server table. In Example1: Stores Table, Web pages are generated in the D:\Sql_web\Pubs\Stores directory.

2. Create a virtual directory corresponding to the directory you selected in Step 1. For both examples, create a virtual directory /pubs and point it to the D:\Sql_web\Pubs directory.
   
   
3. Create a stored procedure that will generate one HTML page per record.

   The primary key value of the record will be used in the name of the generated HTML page to guarantee a one-to-one mapping between a record and an HTML page.

4. Create an .idc file to generate Web pages for all the rows in the table.

   You can run this page once initially, or periodically, to generate Web pages for all the records in the table.

Note   The code in the examples assumes that the directory path name is fewer than 100 characters. For paths with more than 100 characters, you must increase the parameters in the stored procedures.

The following steps are optional.

1. Create an Insert/Update trigger on the table.

   This trigger will invoke the stored procedure you created, with the appropriate parameters. Triggers are necessary only if you want to generate Web pages automatically on updates to the table. If you would rather run a batch command periodically to generate the pages, triggers are not necessary.

2. Create a Delete trigger on the table.

   This trigger will delete the Web page corresponding to the record that was deleted.

3. Create a template file to describe the layout of the generated Web pages.

   In the Stores Table Example, the template file is Stores.tpl.

Preliminaries

To understand the two examples, you need to be familiar with the following:

• The Transact-SQL language.
  
  
• SQL Server operations, tools, stored procedures, and triggers.
  
  
• The syntax and parameter description for the sp_makewebtask stored procedure.
  
  
• .idc script files and .htx files.

You can find information about the first three items in the SQL Server documentation and information about .idc and .htx files in the IIS documentation.

Once you have familiarized yourself with all the items in this list, you need to do the following:

1. Set up the Open Database Connectivity (ODBC) entry as described in the IIS database sample pages.

   These examples also use the Web SQL System data source name (DSN). Therefore, make the appropriate changes to the following examples if you have:

   • Changed the account name from the default (sa)

     —Or—

   • Assigned a password to the Web SQL System DSN. The default is no password.
2. Issue all SQL Server queries through the ISQL/w tool, which comes with SQL Server.

Example 1: Stores Table

The first example uses the stores table in the pubs sample database, which comes with SQL Server. In the stores table, all the columns are smaller than 255 characters. If a text column is likely to have more than 255 characters, then you must use a slightly different technique, which is described in Example 2: Pub_Info Table.

Note   All the quotation marks in the code samples are single marks (' ').

Schema for the Stores Table

The following table shows the schema of the stores table:

Table 2. Stores table schema

Because store_id is the primary key, use the store_id to generate a unique file name for each record in the table. In the stores table example, you will be retrieving only the store_id, store_name, and store_address; also, store_id will be stored as a meta property, and store_name and store_address will be stored as table data.

Directories and Files

To create the necessary directories and directory structure for storing the scripts, templates, and generated pages, run the following commands:

md d:\sql_web
md d:\sql_web\scripts
md d:\sql_web\pubs
md d:\sql_web\pubs\stores

Scripts directory

Next, create a virtual directory pointing to the D:\Sql_web\Scripts directory. Name this virtual directory /sqlscripts and give it Execute permission only. Copy the following files into the D:\Sql_web\Scripts directory:

Genstores.idc

A script file to generate Web pages for all the rows in the stores table.

Genstores.htx

A template file for Genstores.idc.

Stores.tpl

A template file containing the formatting information of generated pages in the stores table.

Directory of generated pages

The pages that SQL Server automatically generates for the stores table will be put into the D:\Sql_web\Pubs\Stores directory. Create a virtual directory named /pubs and point it to D:\Sql_web\Pubs. Give /pubs Read permission. With this configuration, Index Server can index all pages in D:\Sql_web\Pubs and its subdirectories.

Each generated page will have the primary key as part of its file name, to guarantee that each file name is unique and associates each record with a Web page. For example, if the store_id is 3456, the generated Web page will have the name D:\Sql_web\Pubs\Stores\3456.htm.

wp_stores_update Stored Procedure

The wp_stores_update stored procedure will generate a Web page for a row in the stores table by using the sp_makewebtask system stored procedure. The wp_stores_update stored procedure will issue a query based on the store_id, which is a primary key in the stores table. Therefore, equality with a specific store identity will generate only one page.

The following code executes this stored procedure:

USE PUBS
GO
if exists (select * from sysobjects where id = object_id('dbo.wp_stores_update') and sysstat & 0xf = 4)
        drop procedure dbo.wp_stores_update
GO
CREATE PROCEDURE dbo.wp_stores_update 
@store_id char(4),                        -- Primary Key, id of the store
@filepath varchar(100),                 -- Directory where to create the Web page
@templatefile varchar(100) = NULL       -- Optional template file to use
AS
--
-- This procedure creates a Web page for a row with the specified
--.store_id that is a key in the stores table.
--
-- Forming the strings to give to the "EXEC" command is confusing when
-- we have run time variables in the string. Splitting it up into
-- variables will make it a little more readable.
--
DECLARE @cmd_text1 varchar(255)
DECLARE @cmd_text2 varchar(255)
DECLARE @file_name varchar(255)
--
-- Compose the file name using the primary key "store_id".
-- For example, if the store_id is 3456 and @filepath is d:\sql_web\pubs\stores
-- the generated Web page will be d:\sql_web\pubs\stores\3456.htm
--
SELECT @file_name=@filepath+'\'+@store_id+'.htm'
--
-- Compose the sp_makewebtask command and parameters.
-- Note that store_id is being retrieved twice because we want to
-- Use one instance for a meta tag and another for a table value.
--
SELECT @cmd_text1='sp_makewebtask @outputfile='''+@file_name+''',\
@query=''SELECT store_id, store_id, stor_name,store_address FROM stores \
WHERE stores.store_id='''''+@store_id+''''''+''''
--
-- If a template file is specified, use it
--
IF @templatefile <> NULL
BEGIN
   SELECT @cmd_text2=',@templatefile='''+@templatefile+''''
END
--
-- For debugging purposes
--
--PRINT @file_name
--PRINT @cmd_text1
--PRINT @cmd_text2
EXECUTE( @cmd_text1+@cmd_text2 )
GO

web_stores_update_trigger

Developing triggers is optional, unless you want Web pages to be generated automatically when the table is modified, in which case triggers are necessary. This trigger uses the wp_stores_update stored procedure to generate a Web page for a row that has been changed.

The following code executes this trigger:

USE pubs
go
if exists (select * from sysobjects where id = object_id('dbo.web_stores_update_trigger') and sysstat & 0xf = 8)
        drop trigger dbo.web_stores_update_trigger
go
--
-- This trigger will generate a Web page for the records
-- that match the given store_id. Since store_id is a primary
-- key, only one page will be generated.
--
CREATE TRIGGER web_stores_update_trigger ON dbo.stores FOR INSERT,UPDATE AS
DECLARE @newstore_id char(4)
DECLARE @cmd_text  varchar(255)
--
-- Get the store_id of the row that just changed into the 
-- @newstore_id variable.
--
SELECT @newstore_id = i.store_id 
FROM inserted i
select @cmd_text='wp_stores_update''' + @newstore_id + ''',\
@filepath=''D:\sql_web\pubs\stores'',@templatefile=''D:\sql_web\scripts\stores.tpl'''
EXEC(@cmd_text)
GO

web_stores_delete_trigger

The web_stores_delete_trigger deletes a Web page whenever a record is deleted from the stores table.

The following code executes this:

USE pubs
GO
if exists (select * from sysobjects where id = object_id('dbo.web_stores_delete_trigger') and sysstat & 0xf = 8)
        drop trigger dbo.web_stores_delete_trigger
GO
--
-- This trigger will delete the Web page for the record that
-- got deleted. Since store_id is a primary key and the page
-- was originally generated using the store_id in the file name
-- only one page will get deleted.
--
CREATE TRIGGER web_stores_delete_trigger ON dbo.stores 
FOR DELETE 
AS
DECLARE @cmd_text varchar(255)
DECLARE @deletedstore_id char(4)
--
-- Determine the row that was deleted and store its
-- value in @deletedstore_id.
--
SELECT @deletedstore_id = d.store_id 
FROM deleted d
--
-- Generate the Web page to be deleted using the store_id.
-- For example, if the store_id is 3456 and @filepath is d:\sql_web\pubs\stores
-- the generated Web page will be d:\sql_web\pubs\stores\3456.htm
-- Run an NT command to delete the HTML file corresponding to
-- that record.
--
select @cmd_text='master..xp_cmdshell ''del d:\sql_web\pubs\stores\'
+ @deletedstore_id + '.htm'',no_output'
EXEC(@cmd_text)
GO

Stores.tpl Template File for Generated Pages

The Stores.tpl template file formats the generated pages in the stores table. The template follows the syntax described in SQL Server documentation for using the sp_makewebtask system stored procedure.

Note that the first column, store_id, is stored as a meta tag named store_id. Index Server can search meta tags. The stored procedure sp_makewebtask replaces the <%insert_data_here%> tags with the columns for each row. The order is same as the order specified in the SELECT statement of the wp_stores_update procedure.

The following HTML code is from the Stores.tpl file.

<HTML>
<HEAD>
<TITLE>SQL Server Pubs Database,Stores Table</TITLE>
<BODY>
<H1>Stores Table Row</H1>
<HR>
<%begindetail%>
<META NAME="store_id" CONTENT="
<%insert_data_here%>">
<TABLE>
<TR><TD><B>Store Id</B></TD><TD>
<%insert_data_here%></TD></TR>
<TR><TD><B>Store Name</B></TD><TD>
<%insert_data_here%></TD></TR>
<TR><TD><B>Store Address</B></TD><TD>
<%insert_data_here%></TD></TR>
</TABLE>
<%enddetail%>
</BODY>
</HTML>

Genstores.idc Script File

The Genstores.idc script file runs a SQL command that will generate Web pages for all the records in the stores table. If you have not already set up the database example as described in the IIS documentation, you should set up an ODBC entry as described on the IIS database sample page.

These samples assume that the Genstores.idc file is in the physical directory D:\Sql_web\Scripts, which has a virtual directory /sqlscripts associated with it. The following code is the Genstores.idc file. If you modified the password for the sa account or want to use a different account, then you must modify the following script appropriately.

Datasource: web sql
Username: sa
Template: genstores.htx
SQLStatement:
+USE pubs
+DECLARE stores_cursor CURSOR
+       FOR
+       SELECT store_id FROM STORES
+OPEN stores_cursor
+DECLARE @store_id char(4)
+FETCH NEXT FROM stores_cursor INTO @store_id
+WHILE (@@fetch_status <> -1)
+BEGIN
+       /* 
+               A @@fetch_status of -2 means that the row has been deleted.
+               No need to test for this as the result of this loop is to
+               drop all user-defined tables.
+       */
+       EXEC ('wp_stores_update' + '''' + @store_id + '''' +
+          ',@filepath=''D:\sql_web\pubs\stores'',@templatefile=''D:\sql_web\scripts\stores.tpl''')
+       FETCH NEXT FROM stores_cursor INTO @store_id
+END
+DEALLOCATE stores_cursor

Genstores.htx Template File

The Genstores.htx template is associated with the Genstores.idc file. You must put both of these files into the D:\Sql_web\Scripts directory.

The Genstores.htx code is as follows:

<HTML>
<HEAD><TITLE>SQL Server Web Page Generation Example</TITLE></HEAD>
<BODY BACKGROUND="/samples/images/backgrnd.gif">
<BODY BGCOLOR="FFFFFF">
<TABLE>
<tr>
<TD></TD>
<TD>
<H2>Success!!</H2>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>

Testing

After completing all the steps in the stores example, you can test the stored procedure, templates, and triggers by running the following SQL queries in the ISQL/w tool.

wp_stores_update

Assuming that your physical directory layout is exactly as described in this document, you can see if the wp_stores_update procedure has been correctly created.

Note   This test assumes that '6380' is a valid store_id. If that is not true, substitute a valid store_id for '6380'.

Issue the following query:

EXEC ('wp_stores_update @store_id=''6380'',\
@filepath=''D:\Sql_web\pubs\stores'',\
@templatefile=''D:\sql_web\scripts\stores.tpl''')

Important   All the quotation marks in the preceding sample are single quotation marks.

The HTML page generated by this example is stored in D:\Sql_web\Pubs\Stores\6380.htm, and it looks like:

<HTML>
<HEAD>
<TITLE>SQL Server Pubs Database,Stores Table</TITLE>
<BODY>
<H1>Stores Table Row</H1>
<HR>
<META NAME="store_id" CONTENT="6380">
<TABLE>
<TR><TD><B>Store Id</B></TD><TD>6380</TD></TR>
<TR><TD><B>Store Name</B></TD><TD>Eric the Read Books</TD></TR>
<TR><TD><B>Store Address</B></TD><TD>788 Catamaugus Ave.</TD></TR>
</TABLE>
</BODY>
</HTML>

web_stores_update_trigger

Assuming that there is no entry with a store_id of '1234', you can test the Insert trigger by issuing the following query:

INSERT INTO Stores
(store_id, store_name, store_address, city, state, zip)
VALUES('1234', 'Discount Book Stores', '1234 First Avenue', 'Seattle', 'WA','98000')

web_stores_delete_trigger

After inserting the row, you can test the Delete trigger by issuing the following query:

DELETE FROM Stores
Where store_id='1234'

Generated Web Pages

After successfully completing the previous steps, you can test to see if the pages are being generated correctly or not. From a browser, run the Genstores.idc script:

http://servername/sqlscripts/Genstores.idc?

Example 2: Pub_Info Table

In Example 1: Stores Table, all the three retrieved columns (store_id, store_name, store_address) were smaller than 255 characters, and so they could be retrieved inline in the generated Web page. However, if any column is of the SQL TEXT variable type, the sp_makewebtask stored procedure will limit the output to 255 characters. To work around this limitation, use the @blobfmt parameter. For details, see the SQL Server documentation.

Basically, @blobfmt retrieves columns of TEXT data and puts it into a separate file without limitation. This parameter also creates a link from the main page to the separate file where the data is stored (the subpage). Index Server can then index the main page and subpage separately. There is no easy way of finding out the main page from the subpage. So, when Index Server finds a hit in a subpage, it returns only the subpage, not the main page.

The next example uses the pub_info table in the pubs sample database. For detailed explanations of the stored procedure, triggers, scripts, and templates, refer to the corresponding sections in Example 1: Stores Table.

Schema for the Pub_Info and Publishers Tables

Table 3 shows the schema of the pub_info table:

Table 3. Pub_Info schema

Because pub_id is the primary key, use pub_id to generate a unique file name for each record in the table. In this example, you will retrieve the pr_info TEXT data into a separate file and link it from the main page. Also, you will perform a join with the publishers table in the stored procedure to illustrate a slightly different variation.

Table 4 shows the schema of the publishers table:

Table 4. Publishers schema

The pub_id is the primary key of publishers table. Pub_Info.pub_id is a foreign key linked to Publishers.pub_id.

Directories and Files

To create directories for storing the scripts, templates, and generated pages, run the following commands:

md d:\sql_web
md d:\sql_web\scripts
md d:\sql_web\pubs
md d:\sql_web\pubs\pub_info

Scripts directory

Next, put the following files in the D:\Sql_web\Scripts directory. In Example 1: Stores Table, you made this directory a virtual directory (/sqlscripts) and gave it Execute permission.

• Genpub_info.idc
  
  
• Genpub_info.htx
  
  
• Pub_info.tpl
  
  
• Pr_info.tpl

Directory of generated pages

The Web pages generated for the pubs_info table will be stored in the D:\Sql_web\Pubs\Pub_info directory. The virtual directory /pubs points to D:\Sql_web\Pubs and should have Read permission. With this configuration, Index Server can index the pages, and IIS can display the generated pages or results.

wp_pub_info_update Stored Procedure

Issue the following SQL queries in the ISQL/w tool.

USE pubs
GO
if exists (select * from sysobjects where id = object_id('dbo.wp_pub_info_update') and sysstat & 0xf = 4)
        drop procedure dbo.wp_pub_info_update
GO
CREATE PROCEDURE wp_pub_info_update
@pub_id char(4),                  -- Publishers id
@filepath varchar(100),           -- Directory in which to generate pages
@pr_infotmpl varchar(100),        -- Template file for pr_info (text) field
@maintmpl varchar(100) = NULL     -- Template file for main page
AS
--
-- The stored procedure illustrates the use of @blobfmt parameter to
-- retrieve TEXT data to a 'sub-file'. For example, if the pub_id is '0736'
-- and @filepath is d:\sql_web\pubs\pub_info, the main page will have
-- the name d:\sql_web\pubs\pub_info\0736.htm and the linked file will have
-- the name d:\sql_web\pubs\pub_info\pr_info07361.htm. The "1" is appended
-- by sp_makewebtask.
--
--
-- It is easier to construct an execution statement using variables.
--
DECLARE @cmd_text1 varchar(255)
DECLARE @cmd_text2 varchar(255)
DECLARE @cmd_text3 varchar(255)
DECLARE @cmd_text4 varchar(255)
DECLARE @cmd_text5 varchar(255)
--
-- mainfile will contain the name of the main file.
--
DECLARE @mainfile  varchar(255)
--
-- pr_infofile will contain the name of the file that will have the
-- pr_info text field.
--
DECLARE @pr_infofile varchar(255)
--
-- @filepath and @pr_infotmpl cannot be NULL
--
IF (@filepath = NULL)
BEGIN
    RAISERROR(16853,11,1)
    RETURN (9)
END
IF (@pr_infotmpl = NULL)
BEGIN
    RAISERROR(16853,11,1)
    RETURN (9)
END
--
-- If @filepath is d:\sql_web\pubs\pub_info, the main page will have
-- the name d:\sql_web\pubs\pub_info\0736.htm and the linked file will have
-- the name d:\sql_web\pubs\pub_info\pr_info07361.htm. The "1" is appended
-- by sp_makewebtask.
--
SELECT @mainfile=@filepath+'\'+@pub_id+'.htm'
SELECT @pr_infofile=@filepath+'\pr_info'+@pub_id+'.htm'
--
-- For Debugging purposes
--
--PRINT @pub_id
--PRINT @filepath
--PRINT @maintmpl
--PRINT @pr_infofile
--
-- In this example, we are retrieving pub_id in two columns so we
-- can use the first one for a META property.
--
SELECT @cmd_text1='sp_makewebtask @outputfile='''+@mainfile+''',\
@query=''SELECT _info.pub_id, pub_info.pub_id, pr_info, pub_name,
city, state, country \
FROM pub_info, publishers WHERE pub_info.pub_id='''''
SELECT @cmd_text2=@pub_id+''''' AND pub_info.pub_id=publishers.pub_id'
SELECT @cmd_text3=''',@blobfmt=''%3% FILE='+@pr_infofile
SELECT @cmd_text4=' TPLT='+@pr_infotmpl+''''
IF @maintmpl <> NULL
BEGIN
   SELECT @cmd_text5=',@templatefile='''+@maintmpl+''''
END
--
-- For Debugging purposes
--
--PRINT @mainfile
--PRINT @cmd_text1
--PRINT @cmd_text2
--PRINT @cmd_text3
--PRINT @cmd_text4
--PRINT @cmd_text5
EXECUTE( @cmd_text1+@cmd_text2+@cmd_text3+@cmd_text4+@cmd_text5 )
GO

web_pub_info_update_trigger

The following code is the Update trigger for pub_info table.

use pubs
if exists (select * from sysobjects where id = object_id('dbo.web_pub_info_update_trigger') and sysstat & 0xf = 8)
    drop trigger dbo.web_pub_info_update_trigger
GO
--
-- This trigger will generate a Web page for the records
-- that match the given pub_id. Since pub_id is a primary
-- key, only one page will be generated.
--
CREATE TRIGGER web_pub_info_update_trigger ON dbo.pub_info
FOR INSERT,UPDATE 
AS
DECLARE @pub_id char(4)
DECLARE @cmd_text1  varchar(255)
DECLARE @cmd_text2  varchar(255)
DECLARE @cmd_text3  varchar(255)
DECLARE @cmd_text4  varchar(255)
--
-- Get the store_id of the row that just changed into the
-- @newpub_id variable.
--
SELECT @pub_id = i.pub_id 
FROM inserted i
select @cmd_text1='wp_pub_info_update @pub_id=''' + @pub_id
select @cmd_text2=''',@filepath=''D:\sql_web\pubs\pub_info'','
select @cmd_text3='@pr_infotmpl=''d:\sql_web\scripts\pr_info.tpl'','
select @cmd_text4='@maintmpl=''D:\sql_web\scripts\pub_info.tpl'''
EXEC(@cmd_text1+@cmd_text2+@cmd_text3+@cmd_text4)
GO

web_pub_info_delete_trigger

The following code is the Delete trigger for the pub_info table.

USE pubs
GO
if exists (select * from sysobjects where id = object_id('dbo.web_pub_info_delete_trigger') and sysstat & 0xf = 
8)
drop trigger dbo.web_pub_info_delete_trigger
GO
--
-- This trigger will delete the Web page for the record that
-- got deleted. Since pub_id is a primary key and the page
-- was originally generated using the page_id in the file name
-- only one page will get deleted.
--
CREATE TRIGGER web_pub_info_delete_trigger ON dbo.pub_info
FOR DELETE 
AS
DECLARE @pub_id char(4)
DECLARE @mainfile varchar(255)
DECLARE @pr_infofile varchar(255)
DECLARE @cmd_text1 varchar(255)
DECLARE @cmd_text2 varchar(255)
--
-- Determine the row that was deleted and store its
-- value in @deletedstore_id.
--
SELECT @pub_id = d.pub_id 
FROM deleted d
--
-- Generate the Web page to be deleted using the pub_id.
--
SELECT @mainfile='d:\sql_web\pubs\pub_info\'+@pub_id+'.htm'
--
-- Note that it is '1.htm' after the @pub_id because that is
-- how SQL Server generates the name of the sublink. For example,
-- if the id is 9912,the name of the file generated is
-- d:\sql_web\pubs\pub_info\pr_info99121.htm
--
SELECT @pr_infofile='d:\sql_web\pubs\pub_info\pr_info'+@pub_id+'1.htm'
SELECT @cmd_text1='master..xp_cmdshell ''del '
SELECT @cmd_text2=''',no_output'
--PRINT @mainfile
--PRINT @pr_infofile
--PRINT @cmd_text1
--PRINT @cmd_text2
EXEC( @cmd_text1+@mainfile+@cmd_text2)
EXEC( @cmd_text1+@pr_infofile+@cmd_text2)
GO

Genpub_info.idc Script File

The following code for Genpub_info.idc script file generates the Web pages in a batch.

Datasource: web sql
Username: sa
Template: genpub_info.htx
SQLStatement:
+USE pubs
+DECLARE pub_info_cursor CURSOR
+       FOR
+       SELECT pub_id FROM pub_info
+OPEN pub_info_cursor
+DECLARE @pub_id char(4)
+FETCH NEXT FROM pub_info_cursor INTO @pub_id
+WHILE (@@fetch_status <> -1)
+BEGIN
+       /* 
+               A @@fetch_status of -2 means that the row has been deleted.
+               No need to test for this as the result of this loop is to 
+               drop all user-defined tables.
+       */
+EXEC ('wp_pub_info_update @pub_id=' + '''' + @pub_id + '''' +
+',@filepath=''D:\sql_web\pubs\pub_info'','+
+'@pr_infotmpl=''d:\sql_web\scripts\pr_info.tpl'','+
+'@maintmpl=''D:\sql_web\scripts\pub_info.tpl''')
+FETCH NEXT FROM pub_info_cursor INTO @pub_id
+END
+DEALLOCATE pub_info_cursor

Genpub_info.htx Template File

Genpub_info.htx looks exactly like the Genstores.htx file, and contains the following code:

<HTML>
<HEAD><TITLE>SQL Server Web Page Generation Example</TITLE></HEAD>
<BODY BACKGROUND="/samples/images/backgrnd.gif">
<BODY BGCOLOR="FFFFFF">
<TABLE>
<tr>
<TD></TD>
<TD>
<H2>Success!!</H2>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>

Pr_info.tpl Template File

The code for the Pr_info.tpl template file is specified by the @blobfmt parameter and contains formatting information for the pr_info TEXT data collected in a separate file.

<HTML>
<HEAD>
<TITLE>Pr_Info Table, Pr_Info Text Field</TITLE>
<BODY>
<H2>Large amount of text from Pr_Info Text Field</H2>
<HR>
<%insert_data_here%>
</BODY>
</HTML>

Pub_info.tpl Template File

The following code for the Pub_info.tpl template.

<HTML>
<HEAD>
<TITLE>Pubs Database, Pub_Info Table</TITLE>
<BODY>
<%begindetail%>
<META NAME="pub_id"  CONTENT="<%insert_data_here%>">
<H1>Publisher Info For Publisher Id <%insert_data_here%></H1>
<HR>
<TABLE>
<TR><TD><B>Publisher Name:</B></TD><TD><%insert_data_here%></TD></TR>
<TR><TD><B>City:</B></TD><TD><%insert_data_here%></TD></TR>
<TR><TD><B>State:</B></TD><TD><%insert_data_here%></TD></TR>
<TR><TD><B>Country:</B></TD><TD><%insert_data_here%></TD></TR>
</TABLE>
<%enddetail%>
</BODY>
</HTML>

Testing

After completing all the steps in the pub_info example, you can test the stored procedure, templates, and triggers by running the following SQL queries in the ISQL/w tool.

wp_pub_info_update

Assuming that '0736' is a valid pub_id, you can test the wp_pub_info_update procedure by issuing the following query.

EXEC('wp_pub_info_update @pub_id=''0736'',\
@filepath=''d:\sql_web\pubs\pub_info'',\
@pr_infotmpl=''d:\sql_web\scripts\pr_info.tpl'',\
@maintmpl=''d:\sql_web\scripts\pub_info.tpl''')

The HTML pages (0736.htm and 07361.htm) generated by this example are stored in D:\Sql_web\Pubs\Pub_info and look like:

0736.htm

<HTML>
<HEAD>
<TITLE>Pubs Database, Pub_Info Table</TITLE>
<BODY>
<META NAME="pub_id" CONTENT="0736">
<H1>Publisher Info For Publisher Id 0736</H1>
<HR>
<TABLE>
<TR><TD><B>Publisher Name:</B></TD><TD><A HREF = "file:///d:\sql_web\pubs\pub_info\pr_info07361.htm">New Moon Books</A></TD></TR>
<TR><TD><B>City:</B></TD><TD>Boston</TD></TR>
<TR><TD><B>State:</B></TD><TD>MA</TD></TR>
<TR><TD><B>Country:</B></TD><TD>USA</TD></TR>
</TABLE>
</BODY>
</HTML>

07361.htm

<HTML>
<HEAD>
<TITLE>Pr_Info Table, Pr_Info Text Field</TITLE>
<BODY>
<H2>Large amount of text from Pr_Info Text Field</H2>
<HR>
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusetts.
</BODY>
</HTML>

web_pub_info_update_trigger

This test checks the Insert trigger. Because pub_info.pub_id is a foreign key in the publishers table, a row must be inserted into the publishers table first.

insert into publishers
(pub_id, pub_name, city, state, country)
values('9914','Discount Publishers', 'Seattle', 'Wa', 'USA')
go
insert into pub_info
(pub_id, pr_info)
values('9914', 'This is the pr_info for Discount Publishers in Seattle, WA, USA')
go

web_pub_info_delete_trigger

This test checks the Delete trigger.

delete from pub_info
where pub_id='9914'
go

delete from publishers
where pub_id='9914'
go