Internet programming primer (general overview and history of ASP.NET)

Introduction

This document is intended to be read as a general overview of Internet/enterprise technology to get newbies to this domain up to speed. The contents of this document is arranged to provide:

• Quick overview of what the Internet actually is
• Description of what HTML and XML is
• Web forms
• Basic understanding of CGI/HTTP/MIME
• ISAPI
• ODBC
• ActiveX
• ASP (Active Server Pages)
• OLE DB/ADO
• ASP.NET
• Some examples

The document was originally researched and written by myself in 1997. However, it is still very relevant today (c. 2005). I am publishing it here freely for you so that beginner ASP.NET developers today can appreciate and understand how ASP.NET and database connection technology came about and how it has evolved over the years.

I hope you find this enough information for you to get you started developing enterprise applications for the Internet...

Comments, suggestions welcome to: kevleyski@hotmail.com.

Kevin Staunton-Lambert BSCS

Quick overview of the Internet and TCP/IP

The �Internet� was the name given to the project and prototype system which was originally developed by the Advanced Research Projects Agency (ARPA) to investigate ways to solve the problem of getting incompatible computer networks to communicate with one another.

Through this project, two fundamental software standards were developed:

• Transmission Control Protocol (TCP) which makes sure that all data passed through the network arrives as it was intended.
• Internet Protocol (IP) which specifies the �language� which must be spoken and understood by any computer system to communicate with another over the Internet.

These software standards are generally referred to as �TCP/IP� (Tea Sea Pea Eye Pea). However, a more precise title for it is �TCP/IP protocol suite� because this software also includes other protocols such as the User Datagram Protocol (UDP) which is used for short packets of data such as live video and audio which do not require error checking.

Any machine connected to the Internet has a unique Internet (IP) address. The IP address is a four byte code (32-bit code which has the potential to support over 4 billion machines) which is assigned so that all machines belonging to a similar network have the same prefix. (This is similar to telephone numbers being grouped into locations, however location is irrelevant when assigning IP addresses).

There is currently an improved IP addressing system being developed known as IPng (IP Next Generation) which will use eight bytes (128-bit) to support 3x1038 machines, sufficient enough to potentially make it possible to control any light-switch in any house on the planet without any worries of running out of addresses. (I expect that we will see some interesting computer viruses should this ever be implemented!)

Humans are generally not very good at remembering IP addresses, so they are often assigned a name commonly known as Host Name, Universal Resource Locator (URL), or Universal Resource Identifier (URI). An example of such a name is http://www.codeproject.com. URLs along with the IP address are held as an entry on a Domain Name System (DNS) which is essentially a series of computers known as 'domain servers' on the Internet supporting this ever increasing database.

The World Wide Web (WWW)

The World Wide Web (WWW) was originally developed by Tim Berners-Lee and other scientists at CERN laboratories (in Geneva just on the French border) to allow particle physicists to share information around the world.

Today �The Web� as it is generally called, is used by millions of people around the world to pass and organize hypermedia (text/graphics/sound etc.) over the Internet. Estimating the size of the web is a near impossible task. On 25^th May 1996, it was estimated by 'Internet Solutions' that there were 59,628,024 people accessing 304,177 sites; today in 1998, these figures may have near doubled.

The Web�s success has been achieved by creating hypermedia document standards:

• The HyperText Transport Protocol (HTTP) which is used by all web �Browsers� to communicate with web �Servers� (sometimes this is also referred as the HyperText Transmission Protocol).

• HyperText Mark-up Language (HTML) which is the �language� which must be used by web page authors to format the hypermedia documents. (It is often the specification of this language that has sparked major controversy between some software producers.)

Mark-up Languages

HTML (Hyper Text Markup Language)

HTML is the default scripting language supported by the HyperText Transfer Protocol (HTTP) for formatting web documents (formatting tags) and hyper-linking (anchor tags) to other web documents and scripts held on a web server.

The syntax for HTML, which was adapted from SGML (Simple Generic Mark-up Language or Standard Generalized Markup Language ISO 8878:1986), is very easy to learn and is ideal for use on the web because of its low size. (Compare the size of a formatted HTML document with a similarly formatted document written using a Word processor). HTML documents are generally held in ASCII (American Standard Code for Information Interchange) format which is a standard between most software/hardware platforms. International coding characters (Unicode) can also be added to documents by using their HTML abbreviation after an ampersand (&) character, and then followed by a semi-colon (;). (For example, the symbol for the ampersand character is marked up as &.)

Although there is plenty of material covering HTML, we need to look at some basic HTML structures because we will keep coming across them in the following sections.

HTML document structure and Tags

HTML documents always have a basic structure made up of a header (containing the document title, and other meta-data such as the author's name and date) and a body. Documents are essentially plain text with formatting tags which are very similar to the principles used by early word processors, such as Word Perfect for DOS. HTML Tags are defined between less than (<) and greater than (>) symbols and always come in pairs *, i.e., a section start tag (e.g., <large> to make text large) and a section closing tag (e.g., </large> to make it normal again).

* Some browsers, such as Microsoft Internet Explorer allow lazy HTML as well as strict HTML. This allows you to miss out some tags which may seem obvious (such as the tag used to end a row in a table (</tr>)). However, it is very important to keep to the official strict syntax defined by the WWW Consortium (W3C) so that we can maintain software independence throughout the web.

The following HTML script demonstrates some of the formatting tags.

Notice the use of indentation to identify the affected section between a start and closing tag; this is valid HTML because tabs, and spaces larger than one character in length, are ignored. (To force double spaces, you need to use the HTML abbreviation ).

<!-- 
Comments
-->

<!doctype HTML PUBLIC 
       "-//W3C//DTD HTML 3.2//EN"> 
<html>

<head>
 <title>
   Document Title
 </title>

 <meta HTTP-EQUIV="Content-Type"
  CONTENT="text/html; 
  charset=Windows-1251">
 <meta HTTP-EQUIV="Keywords"
  CONTENT="HTML,Tables,Forms">
</head>

<body bgcolor=#ffffff
 text=#000000
 link=#0000ff vlink=#909090>

<table>
 <tr>
  <th>
    Tag
  </th>
  <th align=left>
    Example
  </th>
</tr>

<TABLE cellSpacing=0 border=1>
 <TBODY>
  <TR>
   <TD>1</TD>
   <TD>2</TD>
   <TD>3</TD>
  </TR>
  <TR>
   <TD>4</TD>
   <TD>5</TD>
   <TD>6</TD>
  </TR>
  <TR>
   <TD>7</TD>
   <TD>8</TD>
   <TD>9</TD>
  </TR>
 </TBODY>
</TABLE>

<hr size=3>
<br>

<ul>
 <li>Bulleted</li>
 <li>un-ordered</li>
 <li>list</li>
</ul>

<font color=green 
     face='courier' size=6>
Green
</font>

<a href='http://...#ODBC'></a>
<a 
  href='mailto:
  kevleyski@hotmail.com'>
</a>

<applet code="JavaText.class" 
    codebase="/examples/JavaText" 
    width="110" height="110">
  <param name="text" value="Java">
</applet>

<object id="Birthday"
classid="clsid:8E27C92B-
1264-101C-8A2F-040224009C02"
width="110" height="110")
  <param name="Day" value="28">
  <param name="Month" value="5">
</object>

<style>
  .style1 
   {font: bold 28pt Times,serif;}
  .style2 
   {font: italic 14pt Times,serif;}
  h1 
   {font: 8pt Arial; color: blue}
</style>
<p class='style1'>Style 1</p>
<p class='style2'>Style 2</p>
<h1>Re-styled header (h1)</h1>

 </body>
</html>

XML (eXtensible Mark-up Language)

So far we have looked at the syntax (grammar) of HTML. However, like all languages, we should also consider the semantics (meaning) of the information we are portraying.

For example, if text has been marked up in bold, it could be because it has more importance, or it might be because the author wants it to look like that. Similarly, we might use a colored typeface to denote a title, or again it might be personal preference to make the page appear more attractive.

Essentially, the point of this argument is that if text is desired to be displayed as bold because it is of more importance, then it should be marked up with the logical tag <strong> rather than the physical <b>. Likewise, if a sentence is a title header, then we should consider using a logical tag such as <h1> rather than forcing a style using physical tags such as <font size=6>.

As mentioned above, we can override existing, and define our own logical tags using cascading style sheets. However, it can look clumsy, e.g., having to repeat tags such as <p class='Style1'>, and for this reason, is often harder for the reader to gain meaning from than not use it at all.

To tackle this problem, XML, unlike HTML, does not use preset tags so it is entirely up to the author how a page is formatted. The use of nested metadata tags, i.e. information about information, is also introduced. For example, say that we have a piece of information related to this paragraph.

We might physically mark it up using HTML as follows:

<b>Classification number</b> 1:02:01:13:00                 <br> 
<b>Thesaurus entry</b>       XML                           <br> 
<b>Article</b>               Extensible Markup Language    <br> 
<b>URL</b>                   /dissertation/week2/HTML.html <br> 
<b>Bookmark</b>              XML

In XML, we would instead consider the semantics of the information, such as:

<Article> 
         <Classification>
                         <Level1>                      1             </Level1> 
                         <Level2>                      02            </Level2> 
                         <Level3>                      01            </Level3> 
                         <Level4>                      13            </Level4> 
                         <Level5>                      00            </Level5> 
        </Classification> 
        <Thesaurus>      XML                           </Thesaurus> 
        <Article>        Extensible Markup Language    </Article> 
        <URL>            /dissertation/week2/HTML.html </URL> 
        <Bookmark>       XML                           </Bookmark> 
</Article>

The author is then left to their own devises in writing an XSL (Extended Style Language) style sheet (see www.w3.org/Style/) which will correctly format any section marked up as 'Article' on a web page back to the original desired HTML format.

Web Forms

Forms are used to pass user input to a web server from an HTML document. You should already be quite familiar with the behavior of the Form controls as they are used extensively in Graphical User Interfaces (GUIs) such as MS Windows, MacOS and X-Windows. (Microsoft ActiveX Controls can also be incorporated into a form to provide additional input types (e.g., Date format) and user input styles.)

To better understand how forms are actually processed by the web server, set the form action to call the ISAPI example described later. (Try method= "POST" as well as the default "GET" to see the difference in use of the CGI querystring.)

<form 
  action="/examples/ CGIExample/CGIExample.exe">

<input type="text" size="20" 
       name="textbox" value="Text Box">

<input type="password" 
  size="20" name="password" value="Password">

<textarea name="areabox" rows="3" cols="20">
 Example of a multiple line input box (Text Area)
</textarea>

<input type="checkbox" 
  name="check1" value="chosen">1
<input type="checkbox" 
  name="check2" value="chosen">2
<input type="checkbox" 
  checked name="check3" value="chosen">3

<input type="radio" 
 checked name="optiongroup" value="1">1
<input type="radio" 
 name="optiongroup" value="2">2
<input type="radio" 
 name="optiongroup" value="3">3

<select name="combobox" size="1">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option selected value="3">
       Option 3</option>
<option value="4">Option 4</option>
</select>

<select name="listbox" multiple size="4">
<option selected value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
<option selected value="4">Option 4</option>
</select>

<input type="submit" 
  name="pressed" value="Submit 1">
<input type="submit" 
  name="pressed" value="Submit 2">
<input type="reset">
<input type="image" name="pressed" 
    src="/dissertation/week3/button.gif">

<input type="hidden" name="somedata" 
          value="UsefulDataYourAppProcesses">

</form>

CGI: The Common Gateway Interface

Common Gateway Interface is a specification for creating executable programs (CGI scripts) that can be run by a web server to carry out dynamic tasks such as:

• Security (e.g. User authorization / IP identification / Software identification)
• Producing dynamic HTML (e.g. Web site visit counters, Advertising (using the metadata refresh tag), reporting the date/time, etc.)
• Querying and updating a database (e.g. Internet Search Engines, Reports).

CGI scripts are very easy to create using languages that can support standard output to a console, such as C/C++, PASCAL, Visual Basic and Perl (Practical Extraction and Report Language). The web server handles the programs by passing output from the programs directly to the calling web browser rather than updating the users console (screen / client window).

The web server generally passes data to the CGI program (script) via environment variables. The CGI program can then look at environment variables using the same method that is used to get the value of an operating system environment variable, (e.g. MS-DOS %PATH%).

When environment variables are passed to CGI scripts they are usually sent using one of two methods, GET and POST. The difference between them is essentially that data passed using the GET method, (the default method) is read by the script as command variables (e.g. read via argc and argv[]), and POST data is processed by reading from the standard input. (e.g. read via stdin >>).

Before we plung into CGI Scripting we need to know a little about the most useful of environment variables, the Query String and also the use of MIME (Multi-purpose Internet Main Extensions)...

CGI: Query Strings

The query string is information that can be passed to a CGI script by a web browser via the URL. If you have used an Internet search engine, such as Yahoo, you may have noticed odd characters popping up (such as, ?, &, %, +) appearing in site address line of the browser, for example:

If you start Yahoo (http://www.yahoo.co.uk/) and search for 'Writing CGI Scripts using C++ ', the following URL is passed by the browser:

http://search.yahoo.co.uk/search/ukie?p=Writing+CGI+Scripts+using+C%2B%2B&y=y.

What this URL does is action (call/execute) the program ukie located at http://search.yahoo.co.uk/search and passed the data p=Writing+CGI+Scripts+using+C%2B%2B&y=y as a query string to the program. (N.B. The first question mark (?) is not included as part of the query string.)

This extra piece of data can be broken down into two parameters where they are split by the ampersand symbol '&', these are:

p=Writing+CGI+Scripts+using+C%2B%2B

This first parameter 'p' is equal to the original search specification entered 'Writing CGI Scripts using C++'. However, because white space characters are not supported as part of a URL, the HTML form converts the spaces to + symbols; and because + symbols mean spaces, the two + symbols (in C++) are converted to HTML format %2B (the hexadecimal ASCII equivalent for the + symbol). (N.B. The data passed to a CGI script from a URL rather than an HTML form is passed as %20 (the ASCII space character) rather than the + symbol, why?)

y=y

This second parameter 'y' is equal to the search space option flag set by the use radio button control to select 'All Sites' (y=y) or 'UK and Ireland Sites Only' (y=u)

CGI: HTTP MIME Headers (Multi-purpose Internet Mail Extensions)

For software, such as a web browsers and e-mail, which are capable of handling several types of information such as plain-text, HTML formatted text and graphic images, we are required to include some additional information which indicates how we desire the content to be processed. Up till this point we have not been required to include this information because it is assumed that information which is passed to a web browser with a file extension of '.HTM' or '.HTML' should be naturally be processed as an HTML document, however CGI programs are passed as raw data by the server rather than documents, so for the web browser to know how to porcess the data from the CGI script we are required to pass an additional piece of information known as a MIME header.

In the our examples we will be passing HTML formatted text between our web server and browser (client). This requires the following plain-text MIME header:

Content-Type: text/html <carriage return>
<carriage return>

Without this information a web browser will interpret any output from our CGI script to be plain-text and will either ignore the data or proceed to treat formatting tags as regular text. MIME headers must be the first line in any information being passed to a browser and they must be on their own line and followed by a blank line. (hence the two carriage returns). For more information regarding MIME headers you should refer to the HTTP/1.1 specification.

CGI Example

CGI 'scripts' can be written using various programming languages (see PERL example later), however for the sake of compatibility with the code in this document, we will be using C++ for our scripting.

CGI C++ programs are very simple to create, technicalities such as multiple user file sharing and communications are handled entirely by the web server, so they are similar to conventional C++ console programs (i.e. simple DOS or UNIX programs).

The following snippet of C++ code simply passes the environment variable QUERY_STRING passed by the web server back to the web browser (client) that called it...

// Include the standard C++ classes and Input/Output Stream classes

#include <stdlib.h> 

#include <iostream.h>


void main()
{
    char *EnvVar = getenv("QUERY_STRING");
    // Standard DOS/UNIX environment variable command

    if (EnvVar == NULL) 
        EnvVar = "No+Parameters+Passed";
        // If there is no query string

        // set the parameter to No Parameter Passed

        
    cout << "Content-Type: text/html\n\n";
    // Write HTTP MIME Header

    cout << "<html>";
    // Write HTML start tag

    cout << "Query String: " << EnvVar;
    // Place the query_string parameter

    // to the console output stream (cout)

    cout << "</html>";
    // Write HTML end tag

}

When we run this program directly from DOS (or UNIX) the program outputs the expected plain text document back to the console, however when the same program is run through a web server the plain text code is treated as HTML code and the environment string QUERY_STRING is passed back to the web browser as an HTML document.

IIS/ISAPI

The Internet Server Application Programming Interface can be likened to an advanced form of CGI. CGI works on the principle of executing programs on the server when a client (web browser) requests it.

There is a major flaw in this principle because each call to the CGI program requires its own individual instance of the program and thus its own memory space on the server. Therefore if 50 clients are all accessing (hitting) the server then 50 separate instances of the CGI program are required and each CGI environment variable needs to be passed to each of the memory spaces allocated. This is a heavy burden and inefficient use of server resources.

ISAPI programs however work on the principle of Dynamic Link Libraries (DLL's ) which are shared between instances. The draw back to this however is that the programming becomes more complicated because we a required to implement multi-threading in out application. However for our examples we will be using the Microsoft Foundation Classes which hides the multi-threaded work in two classes; CHttpServer and CHttpServerContext.

For our purposes we will be writing CGI type applications officially known as ISAPI Extensions, however there is another side of this technology known as ISAPI Filters which are are used to intercept information as it is being passed through the web server. This allows us to carry out tasks such as usage logging, user identification and security, etc.

NSAPI (Netscape Server Application Programming Interface) is as the name suggests, the programming interface for Netscape web servers. However Netscape is intending to change its server architecture to use ISAPI. (Microsoft's ActiveX technology.)

(ISAPI extension DLL's are executed from web pages exactly in the same way that that we execute CGI scripts, i.e. as a hyperlink or actioned from a form.)

ISAPI Extensions

Among the seven MFC classes related to ISAPI we will be using two specific MFC (Microsoft Foundation) classes which are required to create an ISAPI extension DLL. These are:

• CHttpServer - The base class for all ISAPI extensions. This class essentially handles connection between our DLL and the web server.
• CHttpServerContext - The class object that we pass data to which is used to create our virtual dynamic web page. (Similar to the principle used to pass data from CGI using cout <<.)

With CGI C++ scripting we were required to manually process the QUERY_STRING environment string to establish which parameters have been passed to the script; with ISAPI C++ programming we can use the far simpler method to do this for us known as parse mapping...

ISAPI Parse Mapping (with MFC)

A Parse Map is an MFC (Microsoft Foundation Class) macro used to bind (map) a function to a parameter specified in the Query String passed to our DLL. The easiest way to describe parse mapping is by demonstration.

A parse map is declared in the class CISAPIExampleExtension which is derived from CHttpServer. For example...

// ISAPIExample.h

// Define the class (which inherits CHttpServer),

// declaring the PARSE_MAP and our functions

class CISAPIExampleExtension : public CHttpServer
{
public:
        // Declare PARSE_MAP

        DECLARE_PARSE_MAP()

        // Prototypes for our functions

        void Example1(CHttpServerContext* pCtxt);
        void Example2(CHttpServerContext* pCtxt, LPCTSTR Param1, 
             LPCTSTR Param2, LPCTSTR Param3, LPCTSTR Param4, 
             LPCTSTR Param5, LPCTSTR Param6, LPCTSTR Param7, 
             LPCTSTR Param8, LPCTSTR Param9, LPCTSTR Param10);
};

// Parse Map Definition, Used to process

// parameters specified by in the Query String

BEGIN_PARSE_MAP(CISAPIExampleExtension, CHttpServer)

// Handle Example1 that takes no parameters

ON_PARSE_COMMAND(Example1, CISAPIExampleExtension, ITS_EMPTY)

// Handle Example2 that takes upto 10 parameters

// (all parameters are defaulted to have no value

// if they are not submitted)

ON_PARSE_COMMAND(Example2, CISAPIExampleExtension, 
         ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR 
         ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR)
ON_PARSE_COMMAND_PARAMS("line1= line2= line3= line4= 
         line5= line6= line7= line8= line9= line10=")

// Set the default to Example1 (i.e. execute Example1

// when the query string is empty)

DEFAULT_PARSE_COMMAND(Example1, CISAPIExampleExtension)

END_PARSE_MAP(CISAPIExampleExtension)

Essentially this parse map will carry out the following operations depending on the Query String passed to the DLL.

ISAPIExample.dll - No query string specified, so the default function Example1 which takes no parameters is executed (defined by DEFAULT_PARSE_COMMAND).

• ISAPIExample.dll?Example1 - The function Example1 is executed.
• ISAPIExample.dll?Example2 - No action (fail), the function Example2 requires parameters.
• ISAPIExample.dll?Example2?line1=Hello&line2=World - Parameters line1 and line 2 are passed to the function Example2. The function actually takes 10 text parameters line1..line10, however these have been defined as optional by placing the equals (=) sign after each parameter and giving a default value of NULL. If these parameters were not defined as optional, then the function would fail unless all ten parameters were supplied in the query string. (N.B. The use of two question marks in a query string is not supported in some early versions of some web browsers.).
• ISAPIExample.dll?Example2?line13=Fail - No action (fail), the function Example2 does not map a parameter named line13.

Database connections (ODBC, OLE DB and ADO)

Database Management Systems (DBMS), such as MS SQL Server, MySQL and Oracle, were designed to save the software developer the trouble of writing their own code to carry out tasks such as, multiple user file handling, indexing/searching (querying) and data security.

ODBC is a standard, developed by Microsoft, to bridge the gap between a database such as an SQL database or indeed a simple text file (supported by the MS Jet Engine) and an application (such as Crystal Reports) that abides by the ODBC rules. For the software developer this generic standard is particularly useful when it comes to system portability. For example an application can be written to process a spreadsheet and update a database at the same time essentially using the same coding principles. Another example might be where an application was written to manipulate data in an Excel Worksheet, is upsized to an Oracle Database without the need to make any changes to the software application, likewise if the software application was changed, perhaps from an MS Windows to an Apple Macintosh environment, there would be no need of making changes to the database.

The foundations of ODBC are based upon an open standard generated by the SQL Access Group (SAG) which is based on the well know relational database Structured Query Language (SQL) which you should be familiar with.

Before we can get stuck in with ODBC, OLE DB and ADO, we need to know a little about the principles for establishing a connection to a database.

Data Source Names (DSN) and Connection Strings

To register a database with the ODBC Manager we need to create a unique Data Source Name (DSN) entry. DSN entries vary between different ODBC drivers and details for these will be provided with your database server.

After creating a DSN entry we can test the connection by using any ODBC application to connect to the database.

Connections are made by passing a connection string to the ODBC manager which contain information such as the DSN and login details. A connection string to connect to my example MS SQL database is:

ODBC;DSN=InternetPAL;UID=sa;PWD=;

This string is then processed by the ODBC manager and fills in the missing information, and presents the full ODBC connection string ... e.g.

ODBC;DSN=InternetPAL;UID=sa;PWD=;APP=ODBC 
   Test Program;WSID=KEVS;LANGUAGE=us_english;DATABASE=InternetPAL

Using MFC, it is very easy to make an ODBC connection. Essentially, all we need to do is create a database object (CDatabase) and pass the relevant details to the objects class member Open ...

CDatabase m_database;
m_database.Open("", FALSE, TRUE, "ODBC;", FALSE);

This is the completed example function that generates a connection string from the ODBC dialog boxes and connects to a database...

// Open ODBC Connection (opens default connection string 'ODBC;')

void CODBCTestDoc::OpenOdbc()
{
    m_strConnect = "ODBC;";
    // Set the default connection string,

    // i.e. no database name/login details

    BeginWaitCursor();
    // Pop up hourglass mouse pointer

    // to show that we are busy connecting


    // Attempt to send connection string to the ODBC manager

    BOOL bRet;
    try
    {
        bRet = m_database.Open("", FALSE, TRUE, m_strConnect, FALSE);
    }

    catch (CDBException* pe) // Catch ODBC excpetion if there was a problem

    {
        AfxMessageBox(pe->m_strError);
        // Present user with ODBC Error in a message box

        EndWaitCursor();
        // Change mouse pointer back to normal

        pe->Delete();
        // Clear up exception pointer memory space

        return;
        // Exit function

    }

    EndWaitCursor(); // Change mouse pointer back to normal


    // If connection returned OK, then open a recordset

    if (bRet)
    {
        m_strConnect = m_database.GetConnect();
        // User has selected a new connection string


        CDocument::SetTitle(m_strConnect);
        // Set current document title to this connection string


        OpenRecordset();
        // Call function to open a recordset

    }
}

Database recordsets

Once we have connection to a database we can create a recordset (also traditionally know in the database world as a data rowset).

There are several types of which have various advantages and disadvantages in use...

Dynasets - Allow bi-directional scrolling (MoveNext / MovePrevious). Data content changes can be seen by issuing CRecordset::RefreshRowset. (Sometimes called keyset-driven)

Snapshots - Similar to the camera principle in that a photograph of the data is taken. Bi-directional scrolling is still permitted, however data cannot be updated until the recordset is physically closed and then re-opened.

Dynamic - Similar to dynaset principle, however changes in record sort order can affect other users. (Not widely supported by DBMS's)

Forward Only - Recordsets can only be scrolled from the beginning to end, and can only be read from. This has significant speed advantages however we need to close and re-open the recordset to start again.

Recordsets (CRecordset objects) are produced easily in the following way...

CRecordset m_pRecordset; // Create recordset object

m_pRecordset = new CRecordset(&m_database);
// Point recordset to our database object

m_pRecordset->Open(CRecordset::dynaset, 
      "select ... from [Sites]", CRecordset::readOnly);

and for our stored procedure call...

m_pRecordset->Open(CRecordset::dynaset, 
                    "{CALL ODBCTest}", CRecordset::readOnly);

This is the completed example function that creates a dynaset from the query string held in m_strQuery.

// Open recordset

void CODBCTestDoc::OpenRecordset()
{

    // Create a new recordset object a pass the SQL query to it

    CRecordset m_pRecordset;
    m_pRecordset = new CRecordset(&m_database);
    try
    {
        if (m_sp == 0) 
        // m_sp is our flag to determine if we are sedning

        // a call to a stored procedure or an SQL statement

            m_pRecordset->Open(CRecordset::dynaset, 
                    m_strQuery, CRecordset::readOnly);
                    // Standard SQL Query

        else
            m_pRecordset->Open(CRecordset::dynaset, 
                 "{CALL " + m_strQuery + "}", CRecordset::readOnly);
                 // Stored Procedure Call

    }

    catch (CDBException* pe) 
    // Catch ODBC excpetion if there was a problem

    {
        m_bConnected = FALSE;
        // Set flag used test for an opened recordset as closed

        pe->Delete();
        // Clear up exception pointer memory space

        return;
        // Exit function

    }

    // Calculate the number of records in the recordset

    if (!m_pRecordset->IsBOF())
    // N.B. If the record set is before

    // the Beginning Of File then the file is empty

    {

        while(!m_pRecordset->IsEOF())
        // Iterate through the recordset until the End Of File is reached

            m_pRecordset->MoveNext();
    }

    m_nRowCount = m_pRecordset->GetRecordCount() + 1;
    GetFieldSpecs(); // Get field names and sizes

    UpdateAllViews(NULL); // Update all open documents (i.e. other connections)

    m_bConnected = TRUE; // Set flag used test for an opened recordset as open

}

Once we have an operational recordset object, we can then think about doing something with it. The table below contains some of the basic properties of a CRecordset object that you should become familiar with before studying the appendix example ...

ActiveX and the Component Object Model (COM)

The Component Object Model (COM) is the industry standard communications mechanism for executables and dynamic link libraries (DLLs) within the Microsoft Windows and Digital equipment operating systems. Prior to COM, developers who wanted to get porgrams to communicate had a choice of using various ad hoc standards such as DDE (Dynamic Data Exchange), OLE (Object Linking and Embedding) and VBX's (Visual Basic eXtensions). ActiveX is essentially a standard derived from these ad hoc standards and is used to describe the following COM components:

ActiveX Controls

Essentially a control (e.g. Form Controls) are used as an interface for input and output of data to an application. ActiveX Controls are derived from the principles behind OLE controls (OCX's) and VBX's to promote a standard way of re-using the code to support controls between applications, and specifically for use over the World Wide Web.

For software developers it has the benefit that it is platform in-dependent. Controls can be re-used when developing in various environments such as Borland Delphi or Visual Basic and compiled components can be used on Apple and UNIX platforms. (However this technology requires Microsoft products, such as MS Internet Explorer, which has caused some controversy in the software market). Another useful benefit of using ActiveX controls is that their state can be captured and stored in an ActiveX document. The document could be held on an ActiveX Document Server for use perhaps when we are on-line, or we can work with them off-line using any ActiveX enabled software such as MS Word 97.

N.B. ActiveX Controls can be written using MFC (Microsoft Foundation Classes), however controls that are destined for the web should be written using the ActiveX Template Libraries (ATL) because these libraries have been specifically designed to keep the code size of these controls to the bare minimum.

Active Server Pages (ASP)

These are essentially regular HTML documents which contain sections of embedded scripting code that can be be processed by the ISAPI Extension DLL (asp.DLL). The embedded scripting can be written using various methods (such as JavaScript, JScript, PERL) however the default method (and in my opinion, the easiest) is to use Visual Basic Scripting (VBScript). (VBScript is processed by a scripting engine which is in itself a COM/ActiveX component.)

All tasks carried out by Active Server Pages can be hardcoded using the more efficient ISAPI DLL method, however rather than 're-inventing the wheel' it is worth considering the following additional benefits of using ASP:

Session Management - The World Wide Web is a state-less environment. When a user moves from one web page to another any information which may have been entered by them (perhaps into a form) is lost, i.e. the state of the previous page is not maintained. Anyone (such as myself) who has spent time filling in a form to be told that I have missed out a fax number and is presented with a blank form will know what a pain this can be.

Rapid Development- Active Server Pages are easy to code and have the additional benefit that they are interpreted and thus do not require re-compilation which saves a lot of time in the development stage (however because of this they are not as efficient as ISAPI DLLs). ISAPI DLL's are shared files, therefore any program that links to them, i.e. the web server, is required to be shut down and restarted. This operation can take anything up to about a minute, which is a nuisance in itself, however if the web server is being used for other purposes it would need to be carried out at times when it is not busy. (Which for a large international corporation such as Microsoft, would be very rare). Because Active Server Pages are not compiled we can change them at any time without effecting the server.

The example below (AXExample.asp) shows an Active Server Page which carries out a similar task to CGIExample and ISAPIExample by adding any number passed in the QUERY_STRING environment variable:

<%
    '''' Request the entire query string

    Query = Request.QueryString()

    '''' Lists the input parameters into an array

    NumParameters = 1

    '''' Create storage for to hold parameters (50 should be sufficient)

    Dim Param(50)

    '''' Request first parameter

    Param(NumParameters) = Request.QueryString("num1")
    do while Param(NumParameters) <> ""
        '''' Calculate the sum total

        total = total + Int(Param(NumParameters))

        NumParameters = NumParameters + 1

        '''' Request the next parameter from the query string (num#)

        Param(NumParameters) = 
          Request.QueryString("num" + CStr(NumParameters))
    loop
    
    NumParameters = NumParameters - 1

    '''' Calculate the average (Avoid division by zero)

    if NumParameters > 0 then average = total / NumParameters
%>

<html>

    <body bgcolor=#ffffff>
        <p ><font size=5 color=#800080><b><i>
            Basic example of an ActiveX Server Page
        </i></b></font></p>
        <font size=2>

            This page has been generated by the web server from the page 
            <b>AXExample.asp</b>. This script splits out 
            the QUERY_STRING into it's various parameter components 
            and lists the server environment variables. This script also 
            demonstrates <i>state</i> on a web page 
            by calling itself with the original QUERY_STRING + 
            the additional parameter submitted with the form on this page.
        </font>
        <hr>
        <font color=#000080 face='Arial'><strong>

            Command line used to invoke this page
        </strong></font><br><br>
        <small>
            AXExample.asp?<%=Query%>
        </small><hr><font color=#000080 face='Arial'>
          <strong>QUERY_STRING Parameters split apart</strong></font>

        <form action='AXExample.asp' ID="Form1">

        <table ID="Table8">
            <tr><th>Parameter</th><th>Value</th><th>SubTotal</th></tr>

<%
    for f = 1 to NumParameters
%>
            <tr>
              <td>
                 num<%=f%>
              </td>

              <td>
                <input type=text name='num<%=f%>' 
                          value='<%=Param(f)%>' ID="Text1">
              </td>
            </tr>
<%
    next
%>

            <tr>
            <td><td><input type=text 
                   name='num<%=f%>' ID="Text2"></td>
            <td><input type=submit value='<<< Add parameter' 
                    ID="Submit1" NAME="Submit1"></td></tr>

            </table>

        </form>
        <font color=RED><b>Total 
           = <%=total%></b></font> (Average = <%=average%>)
    </body>
</html>

Active Data Objects (ADO)

ADO uses similar principles which we met previously in the ODBC section. However the underlying structure is based on COM (today .NET) rather standard Dynamic Link Libraries used by ODBC.

The final result obtained from using ActiveX Data Objects is similar to the principles illustrated by the ISODBCExample. Although this method is less efficient than the ISAPI method (due to the over heads of the interpreter) it is useful for implementing databases which do not support a large number of users.

The example below (ADOExample.asp) demonstrates how to connect to our example database and produce a report from it.

<%
    '''' Create connection object to InternetPAL database

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "dsn=InternetPAL;uid=sa;pwd=;"

    '''' Create Recordset object to the stored procedure ISAPIInterface

    Set RS = CreateObject("ADODB.Recordset")
    Set RS = Conn.Execute("ISAPIInterface 'ListArticles', '1'")

    '''' Get database position parameter from the query string

    pos = Request.QueryString("pos")
    if Request.QueryString("button") = "First" then pos = 0
    if Request.QueryString("button") = "Previous" then pos = pos - 15
    if Request.QueryString("button") = "Next" then pos = pos + 15
    if Request.QueryString("button") = "Last" then

    pos = 0
    do while not RS.EOF
        pos = pos + 1
        RS.MoveNext
    loop
    
    RS.MoveFirst
    pos = pos - 7

    end if
    query = Request.QueryString()
%>

<html>
    <body bgcolor=#ffffff>
        <p ><font size=5 color=#800080><b><i>
            Basic example of an ActiveX Data Objects
        </i></b></font></p>

        <font size=2>
            This page has been generated by the web server from 
            the page <b>ADOExample.asp</b>. Command line used 
            to invoke this page: ADOExample.asp?<%=Query%>
        </font>
        <table ID="Table8">

            <tr>
                <th>
                </th>
<%

    '''' Create table header with recordset field names

    for i = 0 to RS.Fields.Count - 1

%>
                <th align=left><font color=#800000 face="Arial" size=2><u>
                    <%=RS(i).Name %>

                </u></th>
<%
    next

    '''' Move to offset record

    for offset = 0 to pos - 9

        if Not RS.EOF then RS.MoveNext

    next

    '''' If the record position is not at the begin show

    '''' that there are previous records with a + symbol

    if pos > 0 then
%>

                </tr>
                <tr><td><i>+</i></td></tr>

                <tr>

<%
    end if

    '''' List 15 records from the open recordset object

    f = 0
    for t = 1 to 15
        if Not RS.EOF then
            f = f + 1
%>
                <tr><td></td></tr><tr>
<%
            for i = 0 to RS.Fields.Count - 1
%>
                <td align=left><font face="Arial" size=1><%=RS(i)%></td>

<%
            next
%>
            </tr>
<%
            RS.MoveNext
        end if
    next

    '''' If there are more recordset then display + symbol

    if not RS.EOF then
%>
                <tr><td><i>+</td></tr>
<%
    end if

    '''' Close the recordset object

    RS.Close

    '''' Close the connection object to the database

    Conn.Close
%>

        </table>

        <form action="ADOExample.asp" ID="Form1">

            <input type="submit" name="button" value="First" ID="Submit1">
            <input type="submit" name="button" value="Previous" ID="Submit2">
            <input type="hidden" name="pos" value="<%=pos%>" 
                      ID="Hidden1"><b><%=pos%></b>

            <input type="submit" name="button" value="Next" ID="Submit3">
            <input type="submit" name="button" value="Last" ID="Submit4">

        </form>

    </body>

</html>

ADO/PERL Example: Using ADO to connect to database from a PERL CGI Script

Although it is possible to extend our C++ example (CGIExampe) to access a database. There is a far simpler scripting languages known as PERL (Practical Extraction and Report Language) which is particularly good at processing data. It was originally developed by Larry Wall and uses some familiar structures that we have met in the various C++ examples. The example script below produces a report from our example database.

# Include the OLE (i.e. ActiveX) libraries
use OLE;

# Write MIME Header for HTML document
print "Content-type: text/html\n\n";

print "<html>\n";

# Create ADO connection object
$Conn = CreateObject OLE "ADODB.Connection";
$Conn->Open("InternetPAL");

# Create ADO recordset object
$RS = $Conn->Execute("ISAPIInterface 'ListEntries', '1'");

# Write field names in table header
print " <tr>\n";
$Count = $RS->Fields->count;
for($i = 0; $i < $Count; ++$i)
{
    print " <th>", $RS->Fields($i)->name , "</th>\n";
}
print " </tr>\n";

# Populate table with all records
while(!$RS->EOF)
{
    print " <tr>\n";
    for ( $i = 0; $i < $Count; $i++ )
    {
        print " <td>", $RS->Fields($i)->value, "</td>\n";
    }
    print " </tr>\n";
    $RS->MoveNext;
}

# Close ADO Recordset and Connection
$RS->Close;
$Conn->Close;

print " </table>\n";
print "</html>\n";

ASP.NET (and the .NET Framework)

The 'traditional' ASP with ADO model described above has a slight hang up in that the code is interpreted. This method has a major flaw which is to say it is somewhat inefficient when deployed on a large enterprise scale.

Developers have tried to skirt some of the problems with interpreted code, such as caching their results etc., however Microsoft has now revised their ASP model significantly so that the embedded source code is now compiled.

What is rather nice about the way this was achieved is that the original source .aspx pages are compiled into the common IL (Interface Language) exposing the .NET Framework. This makes it very easy for developers to now link and run into any other managed code sources they might have sitting around.

A useful side-effect to this is we are no longer restricted to Visual Basic scripting and Jscript, any managed code source can be used, for example regular C# code can be directly transferred into an .aspx page.

For example:

<html>
    <script language="C#" runat="server">

int nNumber = 10 * 16;
    </script>

    <body>
        Example of a C# variable nNumber=<%=nNumber%>
    </body>
</html>

A slight downside to this is that deploying each .aspx program is a little slower, that is, the process of IIS recompiling/linking the sources on first hit is not as speedy as the interpreted model, however the long term benefits far out weigh this slight delay.

ASP.NET Example: Connecting to a MySQL database using ASP.NET

Here is an example of a console C# application that connects to MySQL using the freely available MySql ADO .NET Connector.

using System;
using MySql.Data.MySqlClient;

public class Kevs
{
    protected MySqlConnection sqlConn;

    private bool Open(string strDNS)
    {
        bool bRetVal = false;
        try
        {
            sqlConn = new MySqlConnection(strDNS);
            sqlConn.Open();
            bRetVal = true;
        }
        catch (Exception e)
        {
        }
        return bRetVal;
    }

    private void Close()
    {
        sqlConn.Close();
    }

    private void ExecSQL(string strSQL)
    {
        try
        {
            MySqlCommand sqlCmd = new MySqlCommand(strSQL);
            sqlCmd.Connection = sqlConn;
            sqlCmd.ExecuteNonQuery();
        }
        catch (Exception e)
        {
            System.Console.WriteLine(e.Message);
        }
    }

    public static void Main()
    {
        Kevs obj = new Kevs();
        if (obj.Open("Database=InternetPAL;Data " + 
                     "Source=localhost;User Id=sa;Password="))
        {
            obj.ExecSQL("AddYourSQLCommandHere', 0, 0)");
            obj.Close();
        }
    }
}

To convert this code to instead run as an ASP.NET page over the internet is very simple as is can be more or less copy and pasted.

A more reusable method so that you can use your code in other projects and other .ASPX pages would be to import your managed code library... such as

<%@ Import namespace="Your.Managed.Code" %>

(or directly from a managed compiled library .dll)

<%@ Assembly src="YourManagedCode.dll" %>

ASP.NET Data Binding

There are many new features in ASP.NET beyond the scope of this document, however one worthy of mention is databinding. Databinding allows you to code a single .aspx page as if it had some form of internal saved state. In the example below we create an ordinary ASP listbox control and 'bind' the selected item of this listbox using <%# yourBindingVariable%>. (In the example the listbox object property picker.SelectedItem.Text is the bound variable.)

<html>
    <script language="C#" runat="server">

// Name: kev1.aspx

// Desc: Kev's C# ASP.NET hi ya

// Author: KJSL

// Copyright (c) Kev 2004


void kev_Click(Object sender, EventArgs e)
{
    Page.DataBind();
}
    </script>

    <body>
        <form runat="server" ID="Form1">
            <asp:DropDownList id="picker" runat="server">
                <asp:ListItem>Kev</asp:ListItem>

                <asp:ListItem>Rach</asp:ListItem>
                <asp:ListItem>Someone else</asp:ListItem>
            </asp:DropDownList>
            <asp:button Text="Submit" OnClick="kev_Click" runat="server/">
        </form>

        G'day <%# picker.SelectedItem.Text%>
    </body>
</html>

The method employed to do this is quite straight forward, an easy way to see how data binding works is to study the JavaScript that the server generates for the browser client: (the trick of course is supplying a hidden value in the web form.)

<html>
    <body>

        <form method="post" action="example.aspx" id="ctl00">
<div>
<input type="hidden" name="__VIEWSTATE" 
  value="/wEPDwUKMTI5MzYyOTg5Nw9kFgQCAg9kFgICAQ8PDxYCHgtfIURhdGFCb3Vu
         ZGdkZGQCAw8VAQNLZXZkZHUwCgrQcgJvNGiS9QZW7G3q1Ebm" ID="Hidden1"/>
</div>

    <select name="picker" id="picker">
    <option selected="selected" value="Kev">Kev</option>

    <option value="Rach">Rach</option>
    <option value="Someone else">Someone else</option>

</select>
    <input type="submit" name="ctl01" value="Submit" ID="Submit1"/>
    </form>

    G'day bound_variable_gets_put_in_here
    </body>
</html>

----

Appendix A: ISAPI example code

This is the implementation of the functions Example1 and Example2 (for ISAPIExample.h)

// ISAPIExample.cpp

#include <afx.h>

// Include standard MFC classes

#include <afxisapi.h>

// Include the ISAPI MFC classes

#include "ISAPIExample.h"

// Include our parse map and function prototypes (see above)


// Create the extension object (this is required

// by the web server to establish a handle to our DLL)

CISAPIExampleExtension theExtension;

// Example1: Takes no parameters

void CISAPIExampleExtension::Example1(CHttpServerContext* pCtxt)
{

    StartContent(pCtxt);
    // Passes the default HTML MIME header to our virtual HTML page

    WriteTitle(pCtxt);
    // Include the default HTML title (You should over-ride

    // this function if you wish to change the title)


    *pCtxt << 
           " <body bgcolor=#ffffff>\n";
    *pCtxt << 
           " <p ><font size=5 color=#800080>
           <b><i>Basic example of ISAPI (Internet 
           Server Application Programming Interface)</i>
           </b></font></p>\n";
    *pCtxt << 
           " <font size=2>This page has been generated by the 
           C++ dynamic link library <b>ISAPIExample.dll</b> 
           or <b>ISAPIExample.dll?Example1</b>.
           </font><br><hr>\n";
    *pCtxt << 
           " <form action=/examples/ISAPIExample/ISAPIExample.dll?
           Example2 method=POST ID="Form1">\n";
    *pCtxt << " <table ID="Table3">\n";
    *pCtxt << 
           " <tr><td><input type=text name=line1 
           ID="Text1"></td></tr>\n";
    *pCtxt << 
           " <tr><td><input type=text name=line2 
           ID="Text2"></td></tr>\n";
    *pCtxt << 
           " <tr><td><input type=text name=line3 
           ID="Text3"></td></tr>\n";
    *pCtxt << 
           " <tr><td><input type=submit 
           value='Submit to ISAPIExample.dll?Example2' 
           ID="Submit1" NAME="Submit1">\n";
    *pCtxt << " </table>\n";
    *pCtxt << " </form>\n";
    *pCtxt << " </body>\n";

    EndContent(pCtxt);              // Close HTML page

}

// Example2: Takes up to 10 named parameters

void CISAPIExampleExtension::Example2(CHttpServerContext* pCtxt, 
     LPCTSTR Param1, LPCTSTR Param2, LPCTSTR Param3, LPCTSTR Param4, 
     LPCTSTR Param5, LPCTSTR Param6, LPCTSTR Param7, LPCTSTR Param8, 
     LPCTSTR Param9, LPCTSTR Param10)
{
    StartContent(pCtxt);
    WriteTitle(pCtxt);

    CString Param[11] = {Param1, Param2, Param3, Param4, Param5, 
            Param6, Param7, Param8, Param9, Param10}, Params = "";
    char NumString[20], ParamConversion[20], 
         TotConversion[20], *AvgConversion = "";

    // Create query string

    int f = 0;
    while (Param[f] > " " && f < 10)
    {

        itoa(f + 1, NumString, 10);
        Params = Params + "line" + NumString + "=" + Param[f] + "&";
        f++;

    }

    *pCtxt << " <body bgcolor=#ffffff>\n";
    *pCtxt << 
           " <p ><font size=5 color=#800080>
           <b><i>Example 2: ISAPI function that takes 
           several parameters</i></b></font></p>\n";
    *pCtxt << 
           " <font size=2>This page has been generated by the C++ 
           dynamic link library <b>ISAPIExample.dll?Example2?" 
           << Params 
           << "</b>.</font><br><hr>\n";
    *pCtxt << 
           " <form action=/examples/ISAPIExample/ISAPIExample.dll?
           Example2 method=POST ID="Form2">\n";
    *pCtxt << " <table ID="Table4">\n";
    *pCtxt << 
           " <tr><th>Parameter</th>
           <th>Value</th><th>SubTotal
           </th></tr>\n";

    // Add up the values of the parameters

    f = 0;
    int total = 0;
    while (Param[f] > " " && f < 10)
    {

        itoa(f + 1, NumString, 10);
        itoa(atoi(Param[f]), ParamConversion, 10);
        total = total + atoi(Param[f]);
        itoa(total, TotConversion, 10);
        *pCtxt << " <tr><td>line" 
               << NumString << "</td><td><input 
               type=text name='line" << NumString << "' 
               value='" << Param[f] 
               << "' ID="Text4"></td><td><i>" 
               << TotConversion 
               << "</i></td><td><small>(Extracted " 
               << ParamConversion 
               << " from parameter)</small></td></tr>";
        f++;

    }

    // Add another extra parameters (if there are less than 10 parameters)

    if (f < 10)
    {

        itoa(f + 1, NumString, 10);
        *pCtxt << 
            " <tr><td></td><td><input 
            type=text name='line" << NumString 
            << "' ID="Text5"></td><td><input 
            type=submit value='<<< Add parameter' ID="Submit2" 
            NAME="Submit2"></td></tr>\n";

    }

    *pCtxt << " </table>\n";
    *pCtxt << " </form>\n";
    itoa(total, TotConversion, 10);
    itoa(total / f, AvgConversion, 10);
    *pCtxt << " <font color=RED><b>Total = " 
           << TotConversion << "</b></font> 
           (Average = " << AvgConversion << ")\n";
    *pCtxt << " </body>\n";

    EndContent(pCtxt);
}

Appendix B: Connecting to a database from ISAPI using ODBC example code

This page describes the steps involved in implementing the example ISAPI extension DLL.

Now that we are familiar with the basics concepts of ODBC and ISAPI we can put the two together to create a program which produced dynamic HTML code from an SQL database.

One major issue which was missing from this example was how we update the database rather than just querying it. This can be achieved using RFX macros:

ISAPI/ODBC Example: Record Field Exchange (RFX)

RFX macros (see also parse maps) are used to bind (map) database rowset field data (and stored procedure parameters) to data members in our object derived from CRecordset.

To demonstrate RFX I will use an example class CISODBCExample derived from CRecordset.

The class contains three data members which are arrays of CString objects which have been given the following the following roles:

m_Field [6] - Used to hold 6 data fields from the current database record into the 6 subscripts (0..5) of our array.

m_StoredProcParam, and m_Pass [6] - Used to pass 6 parameters (0..5) to the stored procedure defined by m_StoredProcParamas.

class CISODBCData : public CRecordset
{

public:

    // Array of data fields to map to the database

    CString m_Field[6];

    // Array use to pass parameters to a stored procedure in the database

    CString m_StoredProcParam;
    CString m_Pass[6];

    // The mapping changes at runtime,

    // so say that we will use the DYNAMIC MFC macro

    DECLARE_DYNAMIC(CISODBCData)

    // Constructor used to pass a pointer from

    // our CDatabase object to our CRecordset object

    CISODBCData(CDatabase* pDatabase = NULL);

    // Functions used to override the default CRecordset functions

    virtual CString GetDefaultConnect(); // Overide the default connection string

    virtual CString GetDefaultSQL(); // Overide the default SQL for our Recordset


    // Function called to exchange data (contains RFX macros)

    virtual void DoFieldExchange(CFieldExchange* pFX);

};

// This macro is placed in the implementation file to denote dynamic binding

IMPLEMENT_DYNAMIC(CISODBCData, CRecordset)

ISAPI/ODBC Example: Initializing the CISODBCExample object.

The two CRecordset data members m_nFields and m_nParams must be overridden with the number of fields (6) and the number of parameters (6) respectively. This is done in the constructor that initializes the public CRecordset database data member 'pdb'. We also initialize our defined data members with null strings.

// Constructor which is used to link to our CDatabase object

CISODBCData::CISODBCData(CDatabase* pdb) : CRecordset(pdb)
{

    // Initialise the array of data fields to map to the database

    m_Field[0] = _T("");
    m_Field[1] = _T("");
    m_Field[2] = _T("");
    m_Field[3] = _T("");
    m_Field[4] = _T("");
    m_Field[5] = _T("");

    // Number of fields (N.B. This is an overridden CRecordset data member)

    m_nFields = 6;

    // Set our recordset type Click here

    // for a description of the snapshot type

    m_nDefaultType = snapshot;

    // Stored Procedure parameter passing variables

    m_StoredProcParam = _T("");
    m_Pass[0] = _T("");
    m_Pass[1] = _T("");
    m_Pass[2] = _T("");
    m_Pass[3] = _T("");
    m_Pass[4] = _T("");
    m_Pass[5] = _T("");

    // Number of parameters

    // (N.B. This is an overridden CRecordset data member)

    m_nParams = 6 + 1;

}

// Overide the Default ODBC connection string

// (opens database InternetPAL)

CString CISODBCData::GetDefaultConnect()
{

    return _T("ODBC;DSN=InternetPAL;UID=sa;PWD=");

}

// Default SQL statement (calls stored procedure ISAPIInterface)

CString CISODBCData::GetDefaultSQL()
{

    // CALL stored procedure. N.B. Require one ? for each parameter passed

    return _T("{CALL ISAPIInterface (?, ?, ?, ?, ?, ?, ?)}");

}

ISAPI/ODBC Example: Binding the database data to our data members using RFX Macros

To bind these data members to database fields we use RFX macros in the DoFieldExchange function. For example:

RFX_Text(pFX, _T("Field1"), m_Field[1]); // Exchange textual data

The macro RFX_TEXT binds the database rowset field named Field 1 to our data member m_Field[1]. What this macro does for us is to help update the data depending on which data member has changed. When we move to another record in the recordset, perhaps using RS.MoveNext, the CString in m_Field[1] is updated with the text data from the database field "Field1" when we call the DoFieldExchange function. Likewise when we change the contents of the data member m_Field[1] it updates the database.

If the data we are exchanging is not text, then we cannot use the RFX_TEXT macro, we must use one of the other RFX macros depending on the data type required:

// RFX Data exchange

void CISODBCData::DoFieldExchange(CFieldExchange* pFX)
{
        // RFX Bindings for our 6 stored procedure parameters

        pFX->SetFieldType(CFieldExchange::param);
        RFX_Text(pFX, _T("Report"), m_StoredProcParam);
        RFX_Text(pFX, _T("Pass1"), m_Pass[1]);
        RFX_Text(pFX, _T("Pass2"), m_Pass[2]);
        RFX_Text(pFX, _T("Pass3"), m_Pass[3]);
        RFX_Text(pFX, _T("Pass4"), m_Pass[4]);
        RFX_Text(pFX, _T("Pass5"), m_Pass[5]);
        RFX_Text(pFX, _T("Pass6"), m_Pass[6]);

        // RFX Bindings for the 6 fields in our recordset

        pFX->SetFieldType(CFieldExchange::outputColumn);
        RFX_Text(pFX, _T("Field1"), m_Field[1]);
        RFX_Text(pFX, _T("Field2"), m_Field[2]);
        RFX_Text(pFX, _T("Field3"), m_Field[3]);
        RFX_Text(pFX, _T("Field4"), m_Field[4]);
        RFX_Text(pFX, _T("Field5"), m_Field[5]);
        RFX_Text(pFX, _T("Field6"), m_Field[6]);
};

ISAPI/ODBC Example: Connecting to an example ISAPI Extension DLL

Once we have established our CRecordset class we can included it directly an ISAPI class derived from CHttpServer. This example displays the rowset generated by the stored procedure ISAPIInterface in the example database 'InternetPAL'. Using the same stored procedure we can add/delete records and update the database by passing a form containing the 6 fields to the procedure.

The following ISAPI extension code has eleven functions of which the four are directly accessible via the parse map:

1. Default - About box. (ISODBCExample.dll)
2. Open Recordset - Connects to the InternetPAL database, creates a recordset from the stored procedure ISAPIInterface and executes the show table function. (ISODBCExample.dll?OpenRecordset)
3. Close Recordset - Closes recordset and the connection to the database. (ISODBCExample.dll?CloseRecordset)
4. Show - This function behave differently depending on the value of the parameter 'Option' passed to it:

• First - Show the first record. (ISODBCExample.dll?Show?Option=First)
• Prev - Show the previous record. (ISODBCExample.dll?Show?Option=Prev)
• Next - Show the next record. (ISODBCExample.dll?Show?Option=Next)
• Last - Show the last record. (ISODBCExample.dll?Show?Option=Last)
• Current - Show the current record (default option). (ISODBCExample.dll?Show)
• Table - Show the recordset in a table view. (ISODBCExample.dll?Show?Option=Table)
• Form - Show the current record in a form view. (ISODBCExample.dll?Show?Option=Form)
• Edit - Show the current record in an edit form. (ISODBCExample.dll?Show?Option=Edit)
• New - Show a blank record for adding to the database. (ISODBCExample.dll?Show?Option=New).
• Delete - Delete the current record (and show first record). (ISODBCExample.dll?Show?Option=Delete)
• Locate - Show the record where its OID (Object ID) = the parameter option. (ISODBCExample.dll?Show?Option=1 ... ,2,5,20)

// Header file: ISODBCExample.h

#include "ISODBCData.h"             // Include database class

class CISODBCExample : public CHttpServer
{
public:

    CISODBCExample();        // ISAPI Thread Constructor

    ~CISODBCExample();       // ISAPI Thread Destructor (closes database)


    CDatabase db;            // InternetPAL Database Object SQL Database

    TR Field1,  rs;          // Current database Stored Procedure Recordset Object

    CString Previous_repNo;  // Current Stored Procedure report variable

    int DBView;              // Current database view (1 = Table / 2 = Form View)

    char DataState;          // Current state of the data (1 = Open / 2 = Closed)


    void Default(CHttpServerContext* pCtxt);
    void OpenRecordset(CHttpServerContext* pCtxt, 
         LPCTSTR repNo, LPCTSTR param);
    void CloseRecordset(CHttpServerContext* pCtxt);
    void ShowTable(CHttpServerContext* pCtxt);
    void WriteTitle(CHttpServerContext* pCtxt);
    void Show(CHttpServerContext* pCtxt, LPCTSTR option);
    void PutRecord(CHttpServerContext* pCtxt);
    void EditRecord(CHttpServerContext* pCtxt);
    void NewRecord(CHttpServerContext* pCtxt);
    void UpdateDB(CHttpServerContext* pCtxt, LPCTSTR Field1, 
         LPCTSTR Field2, LPCTSTR Field3, LPCTSTR Field4, 
         LPCTSTR Field5, LPCTSTR Field6);
    void AddToDB(CHttpServerContext* pCtxt, LPCTSTR Field1, 
         LPCTSTR Field2, LPCTSTR Field3, LPCTSTR Field4, 
         LPCTSTR Field5, LPCTSTR Field6);
    void DeleteFromDB(CHttpServerContext* pCtxt);

    // Declare the parse map

    DECLARE_PARSE_MAP()
};

// Implementation file: ISODBCExample.cpp

#include <afx.h>         // Include the standard MFC classes

#include <afxisapi.h>    // Include the ISAPI MFC classes

#include <afxdb.h>       // Include the MFC Database classes

#include "ISODBCExample.h"     // Include ISAPI extension header


// Handle to our extension (required by the web server)

CISODBCExample theExtension;

// PARSE MAP, Maps the web browsers URL: ISODBCExample.dll?

//<Function>&<Params> to the equivelent

// ISODBCExample.DLL Functions

BEGIN_PARSE_MAP(CISODBCExample, CHttpServer)

    // URL: ISODBCExample.dll?OpenRecordset

    //&<Recordset> --> OpenRecordset(Recordset)

    ON_PARSE_COMMAND(OpenRecordset, CISODBCExample, ITS_PSTR ITS_PSTR)
    ON_PARSE_COMMAND_PARAMS("Recordset=ListEntries Pass1=1")

    // URL: ISODBCExample.dll?ShowRecord&<option> --> Show(Option)

    ON_PARSE_COMMAND(Show, CISODBCExample, ITS_PSTR)
    ON_PARSE_COMMAND_PARAMS("Option=Current")

    // URL: ISODBCExample.dll?CloseRecordset --> CloseRecordset()

    ON_PARSE_COMMAND(CloseRecordset, CISODBCExample, ITS_EMPTY)
    // Close recordset


    // URL: ISODBCExample.dll?UpdateDB&<Pass1>

    // &<Pass2>&<Pass Parameters ...> -->

    // UpdateDB(Pass Parameters ...)

    ON_PARSE_COMMAND(UpdateDB, CISODBCExample, 
       ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR)
       // Update recordset

    ON_PARSE_COMMAND_PARAMS("Pass1= Pass2= Pass3= Pass4= Pass5= Pass6=")

    // URL: ISODBCExample.dll?AddToDB&<Pass1>

    //&<Pass2>&<Pass Parameters ...> -->

    // AddToDB(Pass Parameters ...)

    ON_PARSE_COMMAND(AddToDB, CISODBCExample, 
       ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR ITS_PSTR)
       // Add new record to recordset

    ON_PARSE_COMMAND_PARAMS("Pass1= Pass2= Pass3= Pass4= Pass5= Pass6=")

    // URL: ISODBCExample.dll?Default --> Default()

    ON_PARSE_COMMAND(Default, CISODBCExample, ITS_EMPTY)

    // URL: ISODBCExample.dll *No parameters* --> Default()

    DEFAULT_PARSE_COMMAND(Default, CISODBCExample)

END_PARSE_MAP(CISODBCExample)

// Thread Constructor

CISODBCExample::CISODBCExample()
{
    DBView = 1;
}

// Thread Destructor (Close the recordset

// and database connection if left open)

CISODBCExample::~CISODBCExample()
{
    if (DataState == 1)
    {
        rs.Close();
        db.Close();
    }

}

// DEFAULT, Displays a basic HTML about page

// for the ISODBCExample.DLL program

void CISODBCExample::Default(CHttpServerContext* pCtxt)
{
    StartContent(pCtxt);
    *pCtxt << _T("<body bgcolor=#ffffff>\n");
    *pCtxt << _T(" <p ><font size=5 color=#800080>
                         <b><i>Example of ISAPI and 
                         ODBC</i></b></font></p>\n");
    *pCtxt << _T(" ISODBCExample: ISAPI Server Extension 
                         which uses ODBC32, Copyright Kevin Staunton-Lambert 
                         1997<br><br>\n");
    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
} 

// OPEN RECORDSET, Connects to the InternetPAL SQL database

// (via ODBC), and sends previous data and the report function

// parameter to the stored procedure ISAPIInterface

void CISODBCExample::OpenRecordset(CHttpServerContext* pCtxt, 
                                 LPCTSTR repNo, LPCTSTR param)
{
    StartContent(pCtxt);
    if (!DataState)
    {
        try
        {
            db.OpenEx(_T("DSN=InternetPAL;UID=sa;PWD="), 
                         CDatabase::noOdbcDialog);
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

        rs.m_StoredProcParam = (CString)repNo;
        rs.m_Pass[1] = param;

        // Open the recordset

        try
        {
            if (rs.Open(AFX_DB_USE_DEFAULT_TYPE, 
                _T("{CALL ISAPIInterface (?, ?, ?, ?, ?, ?, ?)}") ,NULL))
            {
                DataState = 1;
                // Let other functions know that

                // the recordset is sucessfully open

                Show(pCtxt, "Table");
                // Initially display the recordset in 'Table' view

            }

        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }
    // If there is a recordset is open, close it then try

    // opening the recordset again (using recursion)

    else
    {
        CloseRecordset(pCtxt);
        OpenRecordset(pCtxt, repNo, "1");
    }
    
    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);

    // Update the global report identifier

    Previous_repNo = repNo;
}

// CLOSE RECORDSET, If a recordset is open this function closes

// the recordset and database connection

void CISODBCExample::CloseRecordset(CHttpServerContext* pCtxt)
{
    StartContent(pCtxt);
    if (DataState == 1)
    {
        DataState = 0;
        try
        {
            rs.Close();
            db.Close();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }
    else
        *pCtxt << "Error: Recordset already closed\n";

    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
}

// Overide the default CHttpServerContext::WriteTitle

void CISODBCExample::WriteTitle(CHttpServerContext* pCtxt)
{
    *pCtxt << _T("<head><title>ISODBC Example
                        </title></head>\n");
}

// SHOW TABLE, Displays a recordset as a table

// (Uses current recordsets memory space)

void CISODBCExample::ShowTable(CHttpServerContext* pCtxt)
{
    CODBCFieldInfo Field;
    int i = 1, f = 1;
    CString TempField, TempOID;
    TempOID = rs.m_Field[1];

    // Generate table header

    *pCtxt << _T("<table border=\"0\" ID="Table8"><thead>
                        <tr><th></th><b>\n");
    for (f = 2; f <= (int)rs.m_nFields; f++)
    // Hide first coulmn (OID) from table

    {
        // Attempt to get the Recordsets field names (Show any ODBC exceptions)

        try
        {
            rs.GetODBCFieldInfo(f - 1, Field);
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

        // Show Recordset's field names

        *pCtxt 
          << _T("<TH align=LEFT><font color=#800000 
                       face=\"Arial\" size=2><U>") 
          << _T(Field.m_strName) << _T("</U></th>\n");
    }

    // Scroll back 10 records to show records at top of page.

    // (Show '+' if not at top of recordset)

    for (f = 1; f < 10 && !rs.IsBOF(); f++)
    {
        try
        {
            rs.MovePrev();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    if (!rs.IsBOF())
        *pCtxt 
          << _T("<tr><td><i>+</td></tr>\n");
    else
    {
        try
        {
            rs.MoveNext(); // Prevents the first record being displayed twice

        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    // Generate table rows (Display only 25 records from the current recordset)

    while (i < 25 && !rs.IsEOF())
    {
        *pCtxt 
          << _T("</tr></b></thead><font face=Arial size=1>\n");
        // Highlight current record (or records if the recordset

        // doen not have unique object ID's)

        if (TempOID == rs.m_Field[1])
            *pCtxt 
              << _T("<TR BGcolor=#00FFFF><td><b>></td>\n");
        else
            *pCtxt << _T("<tr><td></td>\n");

        // Show Recordset's data fields

        for (f = 2; f <= (int)rs.m_nFields; f++)
        // Hide first coulmn (OID) from table

            *pCtxt 
              << _T("<td align=left><font face=\"Arial\" size=1>") 
              << _T(rs.m_Field[f]) << _T("</td>\n");
        *pCtxt << _T("</tr>\n");

        try
        {
            rs.MoveNext();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        i++;
    }

    // Show '+' if there are more records to come

    if (!rs.IsEOF()) *pCtxt 
      << _T("<tr><td><i>+</td></tr>\n");
    *pCtxt << _T("</font></table>\n");

    // Place user back where they were in the recordset

    try
    {
        rs.MoveFirst();
    }
    catch(CDBException* e)
    {
        *pCtxt << e->m_strError;
        return;
    }

    while (!rs.IsEOF() && rs.m_Field[1] != TempOID)
    {
        try
        {
            rs.MoveNext();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }
}

// PUT RECORD, Displays a record in 'Form' view

void CISODBCExample::PutRecord(CHttpServerContext* pCtxt)
{
    CODBCFieldInfo Field;
    *pCtxt << _T("<body bgcolor=#ffffff>\n");

    *pCtxt << _T("<table ID="Table9">\n");
    for (int f = 2; f <= (int)rs.m_nFields; f++)
    {
        // Attempt to get the Recordsets field names (Show any ODBC exceptions)

        try
        {
            rs.GetODBCFieldInfo(f - 1, Field);
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

        // Show fields (Hide NULL fields)

        if (Field.m_strName > "")
        {
            // Show field name

            *pCtxt << _T("<tr><td align=right>
                                <font color=#800000 face=\"Arial\" size=2>")
                   << _T(Field.m_strName) << _T("\n");

            // Show data field

            *pCtxt << _T(": </td><td align=left>
                                 <font color=#000000 face=\"Arial\" size=2>")
                   << _T(rs.m_Field[f]) << _T("</td></tr>\n");
        }
    }
    *pCtxt << _T("</font></table>\n");
}

// EDIT RECORD, Displays a record in 'Edit' mode

void CISODBCExample::EditRecord(CHttpServerContext* pCtxt)
{
    CODBCFieldInfo Field;
    // Create an ODBC field information object

    char* TempStr = "";
    // Temporary variable used when converting integers to strings


    *pCtxt << _T("<form action='/examples/ISODBCExample/
                        ISODBCExample.dll?UpdateDB' 
                        method=post ID="Form1">\n");

    // Pass OID to the stored procedure but keep it hidden from user

    *pCtxt << _T("<input name='Pass1' type=hidden value='") 
           << _T(rs.m_Field[1]) 
           << "' ID="Hidden1"><table ID="Table10">\n";

    for (int f = 2; f <= (int)rs.m_nFields; f++)
    {
        // Attempt to get the Recordsets

        // field names (Show any ODBC exceptions)

        try
        {
            rs.GetODBCFieldInfo(f - 1, Field);
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

        // Show fields (Hide NULL fields)

        if (Field.m_strName > "")
        {
            // Show field name

            *pCtxt << _T("<tr><td align=right>
                                <font color=#800000 face=\"Arial\" size=2>") 
                  << _T(Field.m_strName) << _T(":</td>\n");
            // Show input box HTML: INPUT name=Pass<FieldNo>

            // type=text value=<data field>


            *pCtxt << _T("<td><input name='Pass");
            itoa(f, TempStr, 10); // Convert field number to string

            *pCtxt << _T((CString)*TempStr);
            *pCtxt << _T("' type=text value='");
            *pCtxt << _T(rs.m_Field[f]) 
                   << _T("'></td></tr>\n");
        }

    }
    // Show a 'submit' button that POST's the updated form

    // to the ISODBCExample.dll?UpdateDB function

    *pCtxt << _T("</font><tr><td>
                        <input type=submit value='Update Record'>
                        </td></tr></table></form>\n");
}

// NEW RECORD, Displays a record in 'New' record mode

void CISODBCExample::NewRecord(CHttpServerContext* pCtxt)
{
    CODBCFieldInfo Field;
    char* TempStr = "";

    *pCtxt << _T("<body bgcolor=#ffffff>\n");

    *pCtxt << _T("<form action='/examples/ISODBCExample/
                        ISODBCExample.dll?AddToDB' method=post>\n");

    // Copy Field1 as Pass1 field. (Unique object ID's

    // are automatically generated by the SQL Database)

    *pCtxt 
         << _T("<input name='Pass1' type=hidden value='") 
         << _T(rs.m_Field[1]) << _T("'><table>\n");

    for (int f = 2; f <= (int)rs.m_nFields; f++)
    {
        // Attempt to get the Recordsets field names

        // (Show any ODBC exceptions)

        try
        {
            rs.GetODBCFieldInfo(f - 1, Field);
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        // Show fields (Hide NULL fields)

        if (Field.m_strName > "")
        {
            // Show field name

            *pCtxt 
              << _T("<tr><td align=right>
                    <font color=#800000 face=\"Arial\" size=2>") 
             << _T(Field.m_strName) << _T(": ");
            // Show input box HTML: INPUT name=Pass<FieldNo>

            // type=text value=NULL (New data)

            *pCtxt << _T("<input name='Pass");
            itoa(f, TempStr, 10);
            *pCtxt << _T((CString)*TempStr);
            *pCtxt << _T("' type=text></td></tr>\n");
        }
    }
    // Show a 'submit' button that POST's the new form

    // to the ISODBCExample.dll?AddToDB function

    *pCtxt 
      << _T("</font><tr><td><input 
                   type=submit value='Update Record'></td></tr>
                   </table></form>");
}

// SHOW RECORD, Multi purpose function which

// controls various aspects of a recordset

void CISODBCExample::Show(CHttpServerContext* pCtxt, LPCTSTR option)
{
    StartContent(pCtxt);
    *pCtxt << _T("<body bgcolor=#ffffff>\n");
    *pCtxt 
      << _T(" <p ><font size=5 color=#800080>
                    <b><i>Example of ISAPI and ODBC</i>
                    </b></font></p>\n");
    *pCtxt 
      << _T(" <font size=2>This page has been generated 
                    by the ISAPI extension DLL <b>ISODBCExample.dll</b>. 
                    This program allows viewing and updation of articles found 
                    in the InternetPAL database.</font><br><hr>\n");

    CString opt = option, TempOID = rs.m_Field[1];
    if (DataState)
    {
        // Record Navigation

        // Move to the first record (Update the table/form display)

        if (opt == "First")
        {
            try
            {
                rs.MoveFirst();
            }
            catch(CDBException* e)
            {
                *pCtxt << e- ID="Text1">m_strError;
                return;
            }
            switch (DBView)
            {
                case 2: PutRecord(pCtxt); break;
                default: ShowTable(pCtxt); break;
            }
        }
        // Move to the previous object ID (Update the table/form display)

        else if (opt == "Prev")
        {
            if (DBView == 1)
            {
                while (!rs.IsBOF() && rs.m_Field[1] == TempOID)
                {
                    try
                    {
                        rs.MovePrev();
                    }
                    catch(CDBException* e)
                    {
                        *pCtxt << e->m_strError;
                        return;
                    }
                }
            }
            ShowTable(pCtxt);
        }
        else
        {
            try
            {
                rs.MovePrev();
            }
            catch(CDBException* e)
            {
                *pCtxt << e->m_strError;
                return;
            }
            PutRecord(pCtxt);
        }
    }

    // Move to the next object ID (Skip over table

    // groups) (Update the table/form display)

    else if (opt == "Next")
    {
        if (DBView == 1)
        {
            while (!rs.IsEOF() && rs.m_Field[1] == TempOID)
            {
                try
                {
                    rs.MoveNext();
                }
                catch(CDBException* e)
                {
                    *pCtxt << e->m_strError;
                    return;
                }
            }
            ShowTable(pCtxt);
        }
        else
        {
            try
            {
                rs.MoveNext();
            }
            catch(CDBException* e)
            {
                *pCtxt << e->m_strError;
                return;
            }
            PutRecord(pCtxt);
        }
    }

    // Move to the last record (Update the table/form display)

    else if (opt == "Last")
    {
        try
        {
            rs.MoveLast();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        switch (DBView)
        {
            case 2: PutRecord(pCtxt); break;
            default: ShowTable(pCtxt); break;
        }

    }
    // Update the display (Recordset position does not change)

    else if (opt == "Current")
    {
        switch (DBView)
        {
            case 2: PutRecord(pCtxt); break;
            default: ShowTable(pCtxt); break;
        }
    }

    // Recordset view

    // Change to 'Table' view

    else if (opt == "Table")
    {
        DBView = 1;
        ShowTable(pCtxt);
    }
    // Change to 'Form' view

    else if (opt == "Form")
    {
        DBView = 2;
        PutRecord(pCtxt);
    }
    // Change to 'Edit' mode

    else if (opt.Left(4) == "Edit")
    // For some reason simply testing for "Edit"

    // seems to fail after the first use, why ?

    {
        EditRecord(pCtxt);
    }
    // Change to 'New' record mode

    else if (opt.Left(3) == "New")
    // Again, simply testing for "New" seems to fail after the first use.

    {
        NewRecord(pCtxt);
    }
    // Delete record

    else if (opt.Left(6) == "Delete")
    // Again, simply testing for "Delete" seems to fail after the first use.

    {
        DeleteFromDB(pCtxt);
    }
    // Locate a record (Match object ID with the option parameter)

    else
    {
        try
        {
            rs.MoveFirst();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        int Found = 0;
        while (!rs.IsEOF() && !Found)
        {
            if (rs.m_Field[1] == opt)
            {
                switch (DBView)
                {
                    case 2: PutRecord(pCtxt); break;
                    default: ShowTable(pCtxt); break;
                }
                Found = 1;
            }
            else
            {
                try
                {
                    rs.MoveNext();
                }
                catch(CDBException* e)
                {
                    *pCtxt << e->m_strError;
                    return;
                }
            }
        }
        if (!Found) *pCtxt 
          << "Could not match a record with Object ID: " 
          << opt << _T("\n");
        }

        // Show if the recordset pointer is at the first or last record

        if (rs.IsBOF()) *pCtxt << "<i>(First Record)</i>\n";
        if (rs.IsEOF()) *pCtxt << "<i>(Last Record)</i>\n";
    }
    else
        *pCtxt << "Error: Recordset not open\n";

    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
}

// UPDATE DataBase, Maps the submitted 'update record'

// HTML form to the data set, or sends the data to the stored

// procedure UpdateDB <Global Recordset>


void CISODBCExample::UpdateDB(CHttpServerContext* pCtxt, 
     LPCTSTR Field1, LPCTSTR Field2, LPCTSTR Field3, 
     LPCTSTR Field4, LPCTSTR Field5, LPCTSTR Field6)
{
    StartContent(pCtxt);

    // Attempt to update the recordset directly

    if (rs.CanUpdate())
    {
        try
        {
            rs.Edit();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        if (Field1 > "") rs.m_Field[1] = Field1;
        if (Field2 > "") rs.m_Field[2] = Field2;
        if (Field3 > "") rs.m_Field[3] = Field3;
        if (Field4 > "") rs.m_Field[4] = Field4;
        if (Field5 > "") rs.m_Field[5] = Field5;
        if (Field6 > "") rs.m_Field[6] = Field6;
        try
        {
            rs.Update();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    // Otherwise try sending the data to the 'UpdateDB'

    // stored procedure for the current recordset being used

    else
    {
        CISODBCData Temp_rs;
        Temp_rs.m_StoredProcParam = "Update" + (CString)Previous_repNo;
        Temp_rs.m_Pass[1] = Field1;
        Temp_rs.m_Pass[2] = Field2;
        Temp_rs.m_Pass[3] = Field3;
        Temp_rs.m_Pass[4] = Field4;
        Temp_rs.m_Pass[5] = Field5;
        Temp_rs.m_Pass[6] = Field6;
        try
        {
            Temp_rs.Open(AFX_DB_USE_DEFAULT_TYPE, 
                 _T("{CALL ISAPIInterface (?, ?, ?, ?, ?, ?, ?)}"), 
                 NULL);
            Temp_rs.Close();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

    }

    // Requery the recordset and show the updated data


    rs.Requery();
    Show(pCtxt, Field1);
    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
}

// ADD TO DataBase, Maps the submitted 'new record' HTML

// form to the data set, or sends the data to the stored

// procedure AddToDB <Global Recordset>


void CISODBCExample::AddToDB(CHttpServerContext* pCtxt, 
     LPCTSTR Field1, LPCTSTR Field2, LPCTSTR Field3, 
     LPCTSTR Field4, LPCTSTR Field5, LPCTSTR Field6)
{
    // Attempt to add to the recordset directly

    if (rs.CanUpdate())
    {
        try
        {
            rs.AddNew();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
        if (Field1 > "") rs.m_Field[1] = Field1;
        if (Field2 > "") rs.m_Field[2] = Field2;
        if (Field3 > "") rs.m_Field[3] = Field3;
        if (Field4 > "") rs.m_Field[4] = Field4;
        if (Field5 > "") rs.m_Field[5] = Field5;
        if (Field6 > "") rs.m_Field[6] = Field6;

        try
        {
            rs.Update();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    // Otherwise try sending the data to the 'AddToDB'

    // stored procedure for the current recordset being used

    else
    {
        CISODBCData Temp_rs;
        Temp_rs.m_StoredProcParam = "Add" + (CString)Previous_repNo;
        Temp_rs.m_Pass[1] = Field1;
        Temp_rs.m_Pass[2] = Field2;
        Temp_rs.m_Pass[3] = Field3;
        Temp_rs.m_Pass[4] = Field4;
        Temp_rs.m_Pass[5] = Field5;
        Temp_rs.m_Pass[6] = Field6;

        try
        {
            Temp_rs.Open(AFX_DB_USE_DEFAULT_TYPE, 
                  _T("{CALL ISAPIInterface (?, ?, ?, ?, ?, ?, ?)}") ,NULL);
            Temp_rs.Close();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    // Requery the recordset and show the updated data

    // (N.B. Cannot use ShowRecord() because object ID is unknown)

    try
    {
        rs.Requery();
    }
    catch(CDBException* e)
    {
        *pCtxt << e->m_strError;
        return;
    }
    while (Field2 != rs.m_Field[2] && 
           Field3 != rs.m_Field[3] && !rs.IsEOF())
    {
        try
        {
            rs.MoveNext();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }
    switch (DBView)
    {
        case 2: PutRecord(pCtxt); break;
        default: ShowTable(pCtxt); break;
    }
    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
}

// DELETE FROM DataBase, Attempts to delete a record from

// the recordset, or deletes the record using

// the DeleteFromDB stored procedure

void CISODBCExample::DeleteFromDB(CHttpServerContext* pCtxt)
{
    StartContent(pCtxt);

    // Attempt to delete the record from the recordset directly

    if (rs.CanUpdate())
    {
        try
        {
            rs.Delete();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }

    }
    // Otherwise try the delete stored procedure

    // for the current recordset being used

    else
    {
        CISODBCData Temp_rs;
        Temp_rs.m_StoredProcParam = "Delete" + (CString)Previous_repNo;
        Temp_rs.m_Pass[1] = rs.m_Field[1];
        try
        {
            Temp_rs.Open(AFX_DB_USE_DEFAULT_TYPE, 
                         _T("{CALL ISAPIInterface (?, ?)}") ,NULL);
            Temp_rs.Close();
        }
        catch(CDBException* e)
        {
            *pCtxt << e->m_strError;
            return;
        }
    }

    // Requery the recordset (User is placed

    // at the first record in the recordset)

    try
    {
        rs.Requery();
    }
    catch(CDBException* e)
    {
        *pCtxt << e->m_strError;
        return;
    }
    switch (DBView)
    {
        case 2: PutRecord(pCtxt); break;
        default: ShowTable(pCtxt); break;
    }
    *pCtxt << _T("</body>\n");
    EndContent(pCtxt);
}

Appendix C: Example MS SQL database used in examples

This is the Transact-SQL required to recreate the example database that gets connected to by the various ODBC/ADO examples in this document.

/****** Object:  Stored Procedure dbo.GetNextClassification ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.GetNextClassification') 
           and sysstat & 0xf = 4)
    drop procedure "dbo"."GetNextClassification"
GO

/****** Object:  Stored Procedure dbo.ISAPIInterface ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ISAPIInterface') and sysstat & 0xf = 4)
    drop procedure "dbo"."ISAPIInterface"
GO

/****** Object:  Stored Procedure dbo.ListAllKeywords ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListAllKeywords') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListAllKeywords"
GO

/****** Object:  Stored Procedure dbo.ListArticles ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListArticles') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListArticles"
GO

/****** Object:  Stored Procedure dbo.ListIndexes ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListIndexes') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListIndexes"
GO

/****** Object:  Stored Procedure dbo.ListMatches ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListMatches') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListMatches"
GO

/****** Object:  Stored Procedure dbo.ListRelated ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListRelated') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListRelated"
GO

/****** Object:  Stored Procedure dbo.ListRelatedKeywords ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListRelatedKeywords') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListRelatedKeywords"
GO

/****** Object:  Stored Procedure dbo.ListURLs ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.ListURLs') and sysstat & 0xf = 4)
    drop procedure "dbo"."ListURLs"
GO

/****** Object:  Stored Procedure dbo.Search ******/
if exists (select * from sysobjects where 
           id = object_id('dbo.Search') and sysstat & 0xf = 4)
    drop procedure "dbo"."Search"
GO

/****** Object:  Stored Procedure dbo.SearchSounds ******/
if exists (select * from sysobjects where id = object_id('dbo.SearchSounds') 
                                                  and sysstat & 0xf = 4)
    drop procedure "dbo"."SearchSounds"
GO

/****** Object:  Stored Procedure dbo.ClassCode ******/
if exists (select * from sysobjects where id = object_id('dbo.ClassCode') 
                                               and sysstat & 0xf = 4)
    drop procedure "dbo"."ClassCode"
GO

/****** Object:  Table dbo.Entries ******/
if exists (select * from sysobjects where id = object_id('dbo.Entries') 
                                              and sysstat & 0xf = 3)
    drop table "dbo"."Entries"
GO

/****** Object:  Table dbo.Indexes ******/
if exists (select * from sysobjects where id = 
           object_id('dbo.Indexes') and sysstat & 0xf = 3)
    drop table "dbo"."Indexes"
GO

/****** Object:  Table dbo.Thesaurus ******/
if exists (select * from sysobjects where 
   id = object_id('dbo.Thesaurus') and sysstat & 0xf = 3)
    drop table "dbo"."Thesaurus"
GO

/****** Object:  Table dbo.URLs ******/
if exists (select * from sysobjects where id = object_id('dbo.URLs') 
                                          and sysstat & 0xf = 3)
    drop table "dbo"."URLs"
GO

/****** Object:  Table dbo.Entries ******/
CREATE TABLE "dbo"."Entries" (
    "OID" "int" NOT NULL ,
    "Name" varchar (100) NOT NULL ,
    "URL" "int" NOT NULL ,
    "Bookmark" varchar (255) NULL ,
    CONSTRAINT "PK___3__10" PRIMARY KEY  CLUSTERED 
    (
        "OID"
    ),
    CONSTRAINT "NoDuplicateNames" UNIQUE  NONCLUSTERED 
    (
        "Name"
    ),
    CONSTRAINT "OID" CHECK (OID >= 100000000 and (OID <= 999999999))
)
GO

/****** Object:  Table dbo.Indexes ******/
CREATE TABLE "dbo"."Indexes" (
    "OID" "int" IDENTITY (1, 1) NOT NULL ,
    "Name" varchar (100) NOT NULL ,
    "Base_Entry_OID" "int" NOT NULL ,
    "Top_Entry_OID" "int" NOT NULL ,
    CONSTRAINT "PK___2__10" PRIMARY KEY  CLUSTERED 
    (
        "OID"
    ),
    CONSTRAINT "NoDuplicateIndexNames" UNIQUE  NONCLUSTERED 
    (
        "Name"
    ),
    CONSTRAINT "BaseEntry" CHECK (Base_Entry_OID >= 100000000 
                and (Base_Entry_OID <= 999999999)),
    CONSTRAINT "TopEntry" CHECK (Top_Entry_OID >= 100000000 
                and (Top_Entry_OID <= 999999999))
)
GO

/****** Object:  Table dbo.Thesaurus ******/
CREATE TABLE "dbo"."Thesaurus" (
    "OID" "int" IDENTITY (1, 1) NOT NULL ,
    "Entry_OID" "int" NOT NULL ,
    "String" varchar (30) NOT NULL ,
    CONSTRAINT "PK___1__10" PRIMARY KEY  CLUSTERED 
    (
        "OID"
    ),
    CONSTRAINT "EntryOID" CHECK (Entry_OID >= 100000000 
                and (Entry_OID <= 999999999))
)
GO

/****** Object:  Table dbo.URLs ******/
CREATE TABLE "dbo"."URLs" (
    "OID" "int" IDENTITY (1, 1) NOT NULL ,
    "URL" varchar (255) NOT NULL ,
    CONSTRAINT "PK___4__10" PRIMARY KEY  CLUSTERED 
    (
        "OID"
    ),
    CONSTRAINT "NoDuplicateURLs" UNIQUE  NONCLUSTERED 
    (
        "URL"
    )
)
GO

/****** Object:  Stored Procedure dbo.ClassCode ******/
--- Split the class code (Entry object ID) into the various facets by colons

CREATE PROCEDURE ClassCode @Code int = 0 as

  --- Convert @Code to string and add colons a appropirate points

  DECLARE @ClassCode varchar(13)
  SELECT @ClassCode = CONVERT(varchar(9), @Code)
  SELECT @ClassCode = SUBSTRING(@ClassCode, 1, 1) + ":" + 
         SUBSTRING(@ClassCode, 2, 2) + ":" + 
         SUBSTRING(@ClassCode, 4, 2) + ":" + 
         SUBSTRING(@ClassCode, 6, 2) + ":" + 
         SUBSTRING(@ClassCode, 8, 2)

  --- Return new string

  SELECT @ClassCode
GO

/****** Object:  Stored Procedure dbo.GetNextClassification ******/
--- Returns the next valid classification number in a particular index

CREATE PROCEDURE GetNextClassification @index int = 0AS

  --- Find the band range for the index specified in @index

  DECLARE @Base int
  DECLARE @Top int
  DECLARE @OID int
  SELECT @Base = Base_Entry_OID, @Top = Top_Entry_OID 
    FROM Indexes 
    WHERE OID = @index

  --- Calculate the next availible OID by selecting all records in order

  SELECT @OID = (OID + 1) FROM Entries WHERE Entries.OID 
   >= @Base AND Entries.OID <= @Top ORDER BY OID

  --- Return the @OID found

  SELECT @OID
GO

/****** Object:  Stored Procedure dbo.ISAPIInterface     ******/
-- This procedure is used by the ISAPI extension (ISODBCExample.dll)

-- as a fixed entry point into the InternetPAL database

CREATE PROCEDURE ISAPIInterface @Action varchar(20) = "DataErr", 
       @Pass1 varchar(40) = "", @Pass2 varchar(40) = "", 
       @Pass3 varchar(40) = "", @Pass4 varchar(40) = "", 
       @Pass5 varchar(40) = "", @Pass6 varchar(40) = "", 
       @Pass7 varchar(40) = "", @Pass8 varchar(40) = "" AS

  --- If list of entries in a particular index is required...

  IF @Action LIKE "%List%"
  BEGIN

    --- Find index banding boundaries

    DECLARE @Base int
    DECLARE @Top int
    SELECT @Base = Base_Entry_OID, @Top = Top_Entry_OID 
      FROM Indexes 
      WHERE OID = CONVERT(int, @Pass1)

    --- Return articles that fit in this banding

    SELECT Thesaurus.OID, Entries.OID AS "Classification", 
           String AS "Term", Name AS "Article", URLs.URL AS "URL", 
           Bookmark, NULL, NULL
      FROM Entries, URLs, Thesaurus
      WHERE Entries.OID >= @Base AND Entries.OID <= @Top 
            AND Entries.URL = URLs.OID 
            AND Thesaurus.Entry_OID = Entries.OID
      ORDER BY Entries.OID
  END

  --- If record has been updated...

  IF @Action LIKE "%Update%"
  BEGIN

    --- Update the thesaurus entry

    UPDATE Thesaurus
      SET
        String = @Pass3
      WHERE OID = CONVERT(int, @Pass1)

    --- Find the current entry OID for this term

    DECLARE @OID int
    SELECT @OID = Entry_OID
      FROM Thesaurus
      WHERE OID = CONVERT(int, @Pass1)

    --- Find the current URL OID for this entry

    DECLARE @URL int
    SELECT @URL = URL
      FROM Entries
      WHERE OID = @OID

    --- Update the URL

    UPDATE URLs
      SET
        URL = @Pass5
      WHERE OID = @URL

    --- Update the entries table accordingly

    UPDATE Entries
      SET   
        OID = CONVERT(int, @Pass2),
        Name = @Pass4,
    Bookmark = @Pass6
      WHERE OID = @OID
  END

  --- If record is being added to the database... (N.B. This is only

  --- an example for completeness and should NOT be used!)

  IF @Action LIKE "%Add%"
  BEGIN

    --- Add the URLs

    INSERT URLs
      VALUES (@Pass5)

    --- Get URL OID

    SELECT @URL = OID
      FROM URLs
      WHERE URL = @Pass5

    --- Add the entries

    INSERT Entries
      VALUES (CONVERT(int, @Pass2), @Pass4, @URL, @Pass5)

    --- Add the thesaurus entry

    INSERT Thesaurus
      VALUES (CONVERT(int, @Pass2), @Pass3)
  END

  --- If record is being deleted from the database... 

  --- (N.B. This is only an example for completeness

  ---                       and should NOT be used!)

  IF @Action LIKE "%Delete%"
  BEGIN

    --- Remove thesaurus entry

    DELETE FROM Thesaurus
      WHERE OID = Convert(int, @Pass1)
  END
GO

/****** Object:  Stored Procedure dbo.ListAllKeywords ******/
-- List all the terms in the thesaurus

CREATE PROCEDURE ListAllKeywords AS
    SELECT String From Thesaurus 
      GROUP BY String 
      ORDER BY String
GO

/****** Object:  Stored Procedure dbo.ListArticles ******/
--- List the Articles's (Required by AddURL.asp)

CREATE PROCEDURE ListArticles AS
  SELECT OID, Name
  FROM Entries ORDER BY Name
GO

/****** Object:  Stored Procedure dbo.ListIndexes ******/
--- Lits all indexes (Indented depending on position in hierachy)

CREATE PROCEDURE ListIndexes AS
  SELECT Indexes.OID, "Indent" =
  case
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 9, 1) > "0" 
         then ". . . . . . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 8, 1) > "0" 
         then ". . . . . . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 7, 1) > "0" 
         then ". . . . . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 6, 1) > "0" 
         then ". . . . . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 5, 1) > "0" 
         then ". . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 4, 1) > "0" 
         then ". . . . . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 3, 1) > "0" 
         then ". . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 2, 1) > "0" 
         then ". . . " + Indexes.Name
    when substring(CONVERT(char(9), Indexes.Base_Entry_OID), 1, 1) > "0" 
         then Indexes.Name
  end
  FROM Indexes ORDER BY Base_Entry_OID
GO

/****** Object:  Stored Procedure dbo.ListMatches ******/
--- List all matches for a specific query string (ignoring those

--- that are excluded by index or query string)

CREATE PROCEDURE ListMatches @Entry varchar(150) = "", 
       @Exclude varchar(150) = "", @Restrict int = 1 AS

  --- Find which index band to restrict the entries into

  DECLARE @Base int
  DECLARE @Top int
  SELECT @Base = Base_Entry_OID, @Top = Top_Entry_OID 
    FROM Indexes 
    WHERE OID = @Restrict

  --- If we are not excluding then match with @Entry only

  if @Exclude = ""
  BEGIN
    SELECT "Hyperlink"=
           case
             when MAX(URLs.URL) = "mailto:" then MAX(URLs.URL + Bookmark)
             when MAX(URLs.URL) = "ftp:" then MAX(URLs.URL + Bookmark)
             when MAX(Bookmark) > "" then MAX(URLs.URL + "#" + Bookmark)
             else MAX(URLs.URL)
          end,
          "Indent" =
          case
            when substring(CONVERT(char(9), MAX(Entries.OID)), 9, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 8, 2) + 
                 " <font size=2><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 8, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 8, 2) + 
                 " <font size=2><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 7, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + 
                 " <font size=3><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 6, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + 
                 " <font size=3><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 5, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + 
                 " <font size=4><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 4, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + 
                 " <font size=4><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 3, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + 
                 " <font size=5><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 2, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + 
                 " <font size=5>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 1, 1) > "0" then 
                 SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + 
                 " <font size=6><b>" + MAX(String) + 
                 " </b><i>" + Entries.Name + 
                 "</i></font>"
          end
      FROM Thesaurus, Entries, URLs
      WHERE (Entries.OID >= @Base AND Entries.OID <= @Top) 
       AND (@Entry Like "%" + String + "%" OR String Like "%" + 
       @Entry + "%" OR @Entry Like "%" + Name + "%" OR Name Like "%" + 
       @Entry + "%") AND Thesaurus.Entry_OID = Entries.OID 
       AND Entries.URL = URLs.OID
      GROUP BY Name
      ORDER BY MAX(Entries.OID)
  END

  --- If instread we are are excluding then match with @Entry and don't match @Exclude

  else

  BEGIN
    SELECT "Hyperlink"=
           case
             when MAX(URLs.URL) = "mailto:" then MAX(URLs.URL + Bookmark)
             when MAX(URLs.URL) = "ftp:" then MAX(URLs.URL + Bookmark)
             when MAX(Bookmark) > "" then MAX(URLs.URL + "#" + Bookmark)
             else MAX(URLs.URL)
          end,
          "Indent" =
          case
            when substring(CONVERT(char(9), MAX(Entries.OID)), 9, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 8, 2) + 
                      " <font size=2><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 8, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 8, 2) + 
                      " <font size=2><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 7, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + 
                      " <font size=3><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 6, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 6, 2) + 
                      " <font size=3><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 5, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + 
                      " <font size=4><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 4, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 4, 2) + 
                      " <font size=4><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 3, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + 
                      " <font size=5><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 2, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + ":" + 
                      SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 2, 2) + 
                      " <font size=5>" + MAX(String) + " </b><i>" 
                      + Entries.Name + "</i></font>"
            when substring(CONVERT(char(9), MAX(Entries.OID)), 1, 1) > "0" 
                 then SUBSTRING(CONVERT(varchar(9), MAX(Entries.OID)), 1, 1) + 
                      " <font size=6><b>" + MAX(String) + 
                      " </b><i>" + Entries.Name + 
                      "</i></font>"
          end
      FROM Thesaurus, Entries, URLs
      WHERE (Entries.OID >= @Base AND Entries.OID <= @Top) 
       AND (@Entry Like "%" + String + "%" OR String Like "%" + 
       @Entry + "%" OR @Entry Like "%" + Name + "%" OR Name Like "%" + 
       @Entry + "%") AND (@Exclude NOT Like "%" + String + "%" 
       AND String NOT Like "%" + @Exclude + "%" AND @Exclude NOT Like "%" 
       + Name + "%" AND Name NOT Like "%" + @Exclude + "%") AND 
       Thesaurus.Entry_OID = Entries.OID AND Entries.URL = URLs.OID
      GROUP BY Name
      ORDER BY MAX(Entries.OID)
  END
GO

/****** Object:  Stored Procedure dbo.ListRelated ******/
-- List all articles that fall into index given (Order by name)

CREATE PROCEDURE ListRelated @IndexOID int = 1 AS

  --- Find the indexes band range

  DECLARE @Base int
  DECLARE @Top int
  SELECT @Base = Base_Entry_OID, @Top = Top_Entry_OID 
    FROM Indexes 
    WHERE OID = @IndexOID

  --- Return articles in this banding

  SELECT "Hyperlink"=
         case
           when URLs.URL = "mailto:" then URLs.URL + Bookmark
           when URLs.URL = "ftp:" then URLs.URL + Bookmark
           when Bookmark > "" then URLs.URL + "#" + Bookmark
           else URLs.URL
         end
         , "Indent" =
         case
           when substring(CONVERT(char(9), Entries.OID), 9, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 6, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 8, 2) + 
                     " <font size=2><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 8, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 6, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 8, 2) + 
                     " <font size=2><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 7, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 6, 2) + 
                     " <font size=3><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 6, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 6, 2) + 
                     " <font size=3><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 5, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + 
                     " <font size=4><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 4, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 4, 2) + 
                     " <font size=4><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 3, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + 
                     " <font size=5><b>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 2, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + ":" + 
                     SUBSTRING(CONVERT(varchar(9), Entries.OID), 2, 2) + 
                     " <font size=5>" + Entries.Name + 
                     "</b></font>"
           when substring(CONVERT(char(9), Entries.OID), 1, 1) > "0" 
                then SUBSTRING(CONVERT(varchar(9), Entries.OID), 1, 1) + 
                " <font size=6><b>" + Entries.Name + 
                "</b></font>"
         end
    FROM Entries, URLs
    WHERE Entries.OID >= @Base AND 
          Entries.OID <= @Top AND Entries.URL = URLs.OID
    ORDER BY Entries.OID
GO

/****** Object:  Stored Procedure dbo.ListRelatedKeywords ******/
-- List the keywords that fall into a particular index (Order by name)

CREATE PROCEDURE ListRelatedKeywords @IndexOID int = 1 AS
  --- Find the index band range

  DECLARE @Base int
  DECLARE @Top int
  SELECT @Base = Base_Entry_OID, @Top = Top_Entry_OID 
                 FROM Indexes WHERE OID = @IndexOID
  
  --- List thesaurus terms in this range

  SELECT "Hyperlink"=
         case
           when URLs.URL = "mailto:" then URLs.URL + Bookmark
           when URLs.URL = "ftp:" then URLs.URL + Bookmark
           when Bookmark > "" then URLs.URL + "#" + Bookmark
           else URLs.URL
         end
         , Thesaurus.String
    FROM Entries, Thesaurus, URLs
    WHERE Entries.OID >= @Base AND Entries.OID <= @Top AND 
          Entries.OID = Entry_OID AND Entries.URL = URLs.OID
    ORDER BY Thesaurus.String, Entries.Name
GO

/****** Object:  Stored Procedure dbo.ListURLs ******/
--- List the URL's (Required by AddURL.asp)

CREATE PROCEDURE ListURLs AS
  SELECT OID, URL
  FROM URLs ORDER BY URL
GO

/****** Object:  Stored Procedure dbo.Search ******/
--- List indexes that are related to the phrase 

---    given in @Entry, but not those given in @Excluded

CREATE PROCEDURE Search @Entry varchar(150) = "", @Exclude varchar(150) = "" AS
  SELECT MAX(Indexes.OID), 
         "Indent" =
         case
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 9, 1) > "0" 
                then ". . . . . . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 8, 1) > "0" 
                then ". . . . . . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 7, 1) > "0" 
                then ". . . . . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 6, 1) > "0" 
                then ". . . . . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 5, 1) > "0" 
                then ". . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 4, 1) > "0" 
                then ". . . . . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 3, 1) > "0" 
                then ". . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 2, 1) > "0" 
                then ". . . " + MAX(Indexes.Name)
           when substring(CONVERT(char(9), MAX(Indexes.Base_Entry_OID)), 1, 1) > "0" 
                then MAX(Indexes.Name)
         end
    FROM Thesaurus, Indexes 
    WHERE (@Entry Like "%" + String + "%" OR String Like "%" + @Entry + 
           "%" OR @Entry Like "%" + Name + "%" OR Name Like "%" + @Entry + "%") 
           AND (@Exclude NOT Like "%" + String + "%" AND NOT String Like "%" + 
           @Exclude + "%") AND Indexes.Base_Entry_OID <= Thesaurus.Entry_OID 
           AND Indexes.Top_Entry_OID >= Thesaurus.Entry_OID 
    GROUP BY Indexes.OID
    ORDER BY MAX(Indexes.Base_Entry_OID)
GO

/****** Object:  Stored Procedure dbo.SearchSounds ******/
-- List all thesaurus terms that 'sound like' the words given in the phrase '@Entry'

CREATE PROCEDURE SearchSounds @Entry varchar(150) = "" AS
    SELECT String From Thesaurus 
      WHERE DIFFERENCE(@Entry, String) = 4 OR @Entry Like "%" + String + "%" 
                                           OR String Like "%" + @Entry + "%"
      GROUP BY String 
      ORDER BY String
GO