18
- The Common Gateway Interface (CGI)

-18-

The Common Gateway Interface (CGI)

This chapter discusses the Common Gateway Interface (CGI) and the role it plays in linking client-side actions (such as database access requests) to server-side reactions (such as database access and queries, as well as the subsequent dynamic generation of HTML for data presentation). You've already looked at using HTML user interfaces (enhanced with ActiveX and VBScript) to support database access on the client side. Now it's time to start looking at one of the most popular methods for handling information submitted from these forms to a Web server--this is the world of CGI. This chapter demystifies CGI and shows you that it really is no more than another component of a distributed applications development environment.

This chapter begins with an introduction to just what CGI is and some of the tasks it can help you accomplish. A discussion on CGI input and output processing follows, including some specifics on environment variables and their uses, as well as the methods used by the client to make a request to the server when an HTML form is submitted.

Next you will discover how the Windows CGI interface provides an easy means for developing CGI applications in a Windows development environment. Additionally, you will see how the enterprising work of some sharp Visual Basic developers has resulted in a publicly available module of VB code that greatly simplifies development of Web applications using Windows CGI and Visual Basic.

Finally, the remainder of the chapter takes a look at using the DB-Library VB API that comes with Microsoft SQL Server to create sophisticated Web database applications.

What Is CGI?

CGI is most simply described as a means by which a Web server can communicate with applications external to the server software and have those applications perform some processing on its behalf. In essence, CGI is a server-side process that serves as a go-between for the Web server and other applications programs, information resources, and databases. These information resources and databases can reside on the same physical machine as the Web server or on a machine at some other geographical location.

CGI provides a standard interface between the Web server and external applications. These applications commonly are referred to as gateway programs, CGI programs, or simply CGI scripts. This interface abstracts the details of communications between the Web server and the CGI program to the point where the only detail that application developers really need to know about the interface is how to input data into and retrieve data from their application. You therefore need no fundamental knowledge of the process of communicating directly with HTTP servers to use programs such as database query programs. You just need to understand how to handle the data that the server passes to the program and return the data generated by the program.

CGI programs extend the basic functionality of the Web server and give it the capability to service a variety of Web client requests that the server alone ordinarily would not be able to accommodate. CGI provides the mechanism for giving Web clients (your users) access to any application or resource to which you want to provide access.

RESOURCE: The National Center for Supercomputing Applications (NCSA) Website has some tremendous information on CGI, the CGI specification, CGI environment variables, and a number of other Web-related topics. The URL for this site is

http://hoohoo.ncsa.uiuc.edu

Gateway Program, CGI Program, or CGI Script?

Gateway program, CGI program, and CGI script are essentially three names for the same thing; the remainder of this chapter uses the term CGI program. CGI programs contain the code that accepts data from the Web server (most often passed on from its Web clients) and does something with that data. Tasks can range from various forms of processing, accessing information resources, creating output, or all of these. CGI programs can be developed in virtually any language supported on the Web server host machine, including compiled and interpreted languages such as C, C++, Java, JavaScript, Visual Basic, FORTRAN, Perl, Awk, Expect, and Tcl, as well as some of the newer Perl extensions for client/server database access--for example, Sybperl and Oraperl.

What Can CGI Programs Do?

A CGI program enables you to develop applications that greatly extend the functionality and capabilities of your Web server. In doing so, you can integrate other system resources, such as databases, document repositories, statistical applications, specialty processing, and so on with your Web server. These resources do not need to be local to the server host machine; they can be geographically distributed databases and applications that the CGI program can access. In other words, a CGI program running on a machine in Washington, D.C., can be designed to access databases on machines in Los Angeles, California, or any other location around the globe.

By providing a means to access resources external to your Web server, CGI programs extend the availability of these resources to your Web clients. What does this mean? Suppose that your organization has a database of information that it wants the public or users internal to the organization to be able to access. You now can develop CGI programs that accept user input from an HTML forms interface and use that input to perform complex searches and queries against the database. Other applications can be processing HTML order forms for your online store. This order processing also can be integrated with applications that perform inventory and warehouse management, accounting, and so on.

One of the most powerful aspects of CGI programming is that it gives you a means of generating HTML documents on-the-fly. No longer does your Website have to be a repository of static documents and data. Using CGI, you can develop applications that enable users to create dynamic, customized HTML documents and data presentations by specifying their unique content.

The CGI Data-Flow Process

The typical steps in the CGI data-flow process follow, as shown in Figure 18.1:

1. A Web client (for example, a browser) makes a connection to a Web server at the server's address (and port number, if indicated) in the URL.

2. The Web client sends a request. This request generally is made by using one of two
methods: GET or POST.

3. Data from the requesting client (such as user input on an HTML form) is passed from the server to the CGI program referenced in the initial URL.

4. The CGI program reads in the requesting client's data that it received from the Web server and performs some processing on the client's behalf.

5. The CGI program generates a response to the client. Typically, this response is an HTML document, but it can be some other type of document. This response is passed to the client via the server.

6. After passing the server response to the requesting client, the server closes the connection to the client. An exception to this is when the CGI program continues to push data to the client in a technique called server push. In this case, the connection is maintained.

FIGURE 18.1. The CGI data-flow process.

CGI Input and Output Processing

A Web server and a CGI program can communicate and pass data to one another in four ways:

Environment variables: Values inherited by a program from the runtime environment on the system that invoked the program. In the case of CGI, the variables are set by the Web server and inherited by the CGI program it executes.
Standard input: The system's standard input file descriptor. On most UNIX systems, for example, standard input is where a command or program takes its inputs. This can be a terminal device or the output from another program.
Standard output: The system's standard output file descriptor. On most UNIX systems, for example, standard output is where a command or program writes its output. This can be a terminal device or the input to another program.
Command line: A method by which data is split up and passed to a program as if it were placed on a command line. The UNIX convention of passing this data to the program is used. An array of pointers to strings that make up the input, argv, and a value signifying the number of entries in the argv array, argc, are passed to the CGI program. This method is used only for ISINDEX queries, which are discussed later in this chapter.

Actually, CGI programs and a Web server can communicate through one additional method: by using a Windows .INI file. This method is specific to a specialized form of CGI known as Windows CGI. Because the Windows CGI specification is of particular interest to CGI developers in the Windows operating system environment, the latter half of this chapter (beginning with the section entitled "What is Windows CGI?") is dedicated to covering it in detail.

What Is CGI Data Input?

CGI input data is data submitted by the requesting client to the server, which the CGI program uses to perform some action, such as a database query. Depending on the way in which the client request is made (for example, the forms request METHOD=POST), this input data may be encoded before being passed by the server to the CGI program by environment variables or by standard input. When standard input is used, the length of the input string still is passed through an environment variable.

In addition to the actual data, the server also may pass information to the CGI program detailing the type of data that was passed. This is the case when the data is passed to the program by standard input. Standard input gives the CGI program a means of determining how it is supposed to handle or process the input data. This data type information also is passed through an environment variable.

NOTE: Because input data may be encoded or passed by more than one method, CGI programs need to be designed to determine the method of data passing used, as well as the amount of data passed, so that the entire input can be read into the program in an appropriate manner.

What Is CGI Data Output?

A CGI program can generate a variety of types of output. This output can consist of instructions to the server to perform some task, such as calling another program or perhaps redirecting the client to obtain resources at another URL. More often, though, this output consists of HTML, ActiveX controls, and VBScript generated to present the results of some action performed for a requesting client. These results could be the results of a database query, for example.

CGI output always is returned by standard output. Even in cases when the CGI program has no data to return, it must generate a response indicating this. Two methods of return are possible: parsed-header output and non-parsed headers. With parsed-header output, CGI scripts produce output that is interpreted by the server and sent to the client. The script is not required to send an HTTP header, because this function is handled by the server when it parses the script output before passing it on to the Web client. Parsing incurs some overhead in processing by the server. Sometimes, you may want to avoid the overhead of this additional processing. To do so, CGI scripts can generate an HTTP header directly to avoid parsing by the server. These scripts must be prefixed by nph- to tell the server that output is to be passed directly to the client without parsing. Parsed header output is the only concern here, because this is the form that servers are required to support.

When the CGI program has data to return to the client, the data must be sent in a specific format:

Data Header (of a type understood by the server)
     . . .a blank line . . .
the body of the response (i.e. optional output data to the requesting client)

As noted, the response data is optional, but if it is included, it must be prefaced by a MIME Content-type data header as seen in the following line of code:

Content-type: text/html

NOTE: Multipurpose Internet Mail Extensions (MIME) is the latest standard for Internet mail and transfer of a variety of media types, including text, images, audio, and video. For more information about MIME, refer to Appendix G, "An Overview of MIME."

If the output data is not included, the data header must be a Location type (a URL), such as this:

Location: http://www.north.pole/santas_list.html

or a Status type (a message string), such as this:

Status: msg# msg_text

The server reads the CGI output and takes action depending on the header it finds. If the header is a Content-type header, the output is returned to the client. If the header is a Location type, the server ignores anything following the Location header and instead directs the client to access data or resources at the URL specified. If the header is a Status type, msg# and text enable the server to override the defaults it normally would return to the client.

CGI Environment Variables

Whenever you are working on a computer, running a program, or simply logging on, background variables are part of your working environment. These background variables are known as environment variables and typically are set automatically when you log on, established by a program at runtime, or even defined and set explicitly by you. Typically, environment variables persist for the life of a parent process and are passed to or inherited by child processes and applications of the parent.

Several standard CGI environment variables are defined when a server executes a CGI program. These can be used to pass data between the server and the CGI program. These variables come in two basic types. The first type of variable is the variable set during all client requests; this is a not request-specific variable. The second type of variable is a variable specific to the client request the CGI program is fulfilling; this is a client-specific or, more commonly, request-specific variable.

NOTE: Although a standard set of environment variables common to all Web browsers exist, most vendors also define additional HTTP_ environment variables. Netscape browsers also define HTTP_REFERER and HTTP_CONNECTION, for example, which indicate the referring HTML document and the connection type, respectively. Examples of these values follow:

HTTP_REFERER=file:///C|/My Documents/SAMS/WebDev/echoenv.html

HTTP_CONNECTION=Keep-Alive

Environment variable values are used by CGI programs primarily as a means of attending to client requests for processing, database accesses, and so on. Sometimes, however, the variables are used to determine the client's type of Web browser. In other cases, the variables may be used to maintain and pass state information between the client and the CGI program between independent requests for service from the client. This need for state information occurs because HTTP is a stateless protocol. Therefore, after a client/server connection is dropped, your CGI program has no knowledge or memory of preceding connections unless you explicitly build in a mechanism for maintaining this information. You can maintain the state of user input to a form, for example, by designing CGI applications that embed these values as hidden fields in subsequent HTML documents that it generates for the client.

A list of 19 standard CGI environment variables follows (variables that are not request-specific are so indicated):

AUTH_TYPE

CONTENT_LENGTH

CONTENT_TYPE

GATEWAY_INTERFACE (not request-specific)

HTTP_ACCEPT

HTTP_USER_AGENT

PATH_INFO

PATH_TRANSLATED

QUERY_STRING

REMOTE_ADDR

REMOTE_HOST

REMOTE_IDENT

REMOTE_USER

REQUEST_METHOD

SCRIPT_NAME

SERVER_NAME (not request-specific)

SERVER_PORT

SERVER_PROTOCOL

SERVER_SOFTWARE (not request-specific)

The following sections briefly describe each of the CGI environment variables.

AUTH_TYPE

For servers that support user authentication and run protected CGI programs, the value of the AUTH_TYPE variable details the method used to validate users. The authentication method is protocol-specific. For example, this code indicates the most common value used:

AUTH_TYPE = Basic

In this case, a basic authentication scheme requires that a client provide a password and user identification in order to authenticate itself.

CONTENT_LENGTH

The CONTENT_LENGTH variable represents the length (in characters) of the data buffer sent by the client to the server during a request. For example, a test request for forms processing on the server reported this:

CONTENT_LENGTH=105

CONTENT_TYPE

The value of the CONTENT_TYPE variable represents the MIME type of the data sent from the client to the server as the result of a METHOD=POST request from the client. This method commonly is used in HTML forms processing. If no data is sent, the value is set to NULL. For example, a test request for forms (using POST) processing on the server reported this:

CONTENT_TYPE=application/x-www-form-urlencoded

This is the default encoding for forms.

GATEWAY_INTERFACE

The GATEWAY_INTERFACE variable simply contains the version of the CGI specification to which the server complies. This environment variable is not request-specific. The server reports this value as the following, which indicates CGI 1.1:

GATEWAY_INTERFACE=CGI/1.1

HTTP_ACCEPT

The value of the HTTP_ACCEPT variable is a comma delimited list of all the MIME data types the requesting client will accept. MIME is covered in more detail in Appendix G, but basically seven MIME types are used by HTTP. These include application, text, multipart, message, image, audio, and video. All these types also have subtypes associated with them. For example, GIF is a subtype of image. My server reported that a Microsoft Internet Explorer Web client accepts the following image types:

HTTP_ACCEPT=image/gif, image/x-xbitmap, image/jpeg, */*

Note that the value of HTTP_ACCEPT is a list of accepted MIME types and is formatted as type/subtype, type/subtype, and so on.

HTTP_USER_AGENT

The HTTP_USER_AGENT variable contains the Web browser the client used to send a request. It provides a nice way for CGI programs to format output to take advantage of extensions used by certain browsers or to accommodate shortcomings of some browsers. The server reported the following for a Microsoft Internet Explorer Web client, for example:

HTTP_USER_AGENT=Mozilla/1.22 (compatible; MSIE 2.0; Windows 95)

In this case, the value of the environment variable indicates that the client Web browser is Microsoft Internet Explorer (MSIE) 2.0 running under Windows 95 and that it is compatible with Netscape (Mozilla) 1.22.

PATH_INFO

The value of the PATH_INFO variable represents extra path information supplied by the client. Servers can decode this information from URLs before executing the CGI script. Notice the following URL in an HTML forms interface document:

<FORM METHOD="POST" ACTION="http://jupiter.omniscient.com/cgi-bin/test-env/extrastuff">

This URL points to a CGI program called test-env and is appended with additional information (extrastuff). This resulted in the server reporting the following:

PATH_INFO=/extrastuff

CGI applications typically do not have any knowledge of the method by which this value was set. It is up to the developer to know the context in which this value may be used. This variable gives the developer another method of passing information from an HTML document to a CGI program. Suppose that an anchor with the following URL is in the document:

<A HREF="http://jupiter.omniscient.com/cgi-bin/myVBGateway.exe/param1=val1+param2=val2

This results in the following PATH_INFO variable value:

PATH_INFO=/param1=val1+param2=val2

This variable provides a method by which two additional parameters might be passed to the CGI program myVBGateway.exe, which then could obtain these values from the PATH_INFO environment variable.

PATH_TRANSLATED

The value of the PATH_TRANSLATED variable is a virtual-to-physical PATH_INFO mapping provided by the server. In other words, it represents the server's attempt to provide a full operating system absolute path to the data indicated in PATH_INFO. For the URL specified in the FORM ACTION attribute in the previous example, that is

="http://jupiter.omniscient.com/cgi-bin/test-env/extrastuff"

the server uses the server's DocumentRoot value of /usr/local/etc/httpd/htdocs to translate the full path to this:

PATH_TRANSLATED=/usr/local/etc/httpd/htdocs/extrastuff

QUERY_STRING

The value of the QUERY_STRING variable is a URL-encoded string that is appended after a question-mark (?) character at the end of the URL referencing the CGI routine being executed. URL-encoding is a method of translating some data values to be transferred between the client and server to ensure that the data sent is received intact. URL-encoding and using the METHOD=GET form request method to set this variable are discussed in the sections "Getting Client Data to a CGI Program" and "Query Strings" a bit later in this chapter.

For a METHOD=GET test, the following URL-encoded data string was sent to the server by the client:

http://jupiter.omniscient.com/cgi-bin/test-env?uname=&email=&address=&bus=sw
Â&title=Pres&cgidev=Perl5&dbase=Access&description=&dbyesno=YES&subscribe=YES

The server reported the value of the QUERY_STRING variable as this:

QUERY_STRING=uname=&email=&address=&bus=sw&title=Pres&cgidev=Perl5
Â&dbase=Access&description=&dbyesno=YES&subscribe=YES

REMOTE_ADDR

The REMOTE_ADDR variable contains the value of the Internet provider (IP) address for the requesting client or agent (which actually may be a host of the requesting client). For the previous test examples, the server reported the following:

REMOTE_ADDR=168.143.1.44

REMOTE_HOST

The REMOTE_HOST variable contains the full DNS hostname for the requesting client or agent if the server is able to obtain this information. Otherwise, the value is NULL. For the previous test examples, the server reported the following:

REMOTE_HOST=dkittel.clark.net

REMOTE_IDENT

The REMOTE_IDENT variable contains the name of the remote user as retrieved from the server if the HTTP server supports RFC 931 identification. The RFC 931 Authentication Server documentation states that HTTP servers supporting this feature should make an effort to identify the requesting client or agent. This is not considered a valid means of authenticating users. Enabling this feature also can result in performance degradation because extra work is imposed on the server to attempt identification. An example of this variable follows:

REMOTE_IDENT=kittel.omniscient.com

REMOTE_USER

The REMOTE_USER variable contains the authenticated user's name if the server supports user authentication and the CGI application program is protected from unauthorized use. If the AUTH_TYPE variable is set to basic, for example, the REMOTE_USER variable can be set by using the user's identification as sent by the client. An example of this process follows:

REMOTE_USER=julie_wirkkala

REQUEST_METHOD

The REQUEST_METHOD variable contains the client request method. The GET and POST methods are by far the most popular and widely used methods.

In the previous test example of a request for forms processing, METHOD=POST was specified. The server subsequently reported the following:

REQUEST_METHOD=POST

RESOURCE: HTTP servers can service other request types, although POST and GET are by far the most widely used. For information on other methods in the HTTP specification, visit these URLs:

http://www.w3.org/hypertext/WWW/Protocols/HTTP/Request.html

http://www.w3.org/hypertext/WWW/Protocols/HTTP/Methods.html

SCRIPT_NAME

The SCRIPT_NAME variable contains a virtual path to the CGI program being executed by the server. For the previous text example, the server reported this:

SCRIPT_NAME=/cgi-bin/test-env

SERVER_NAME

The SERVER_NAME variable contains the Web server hostname, DNS alias, or IP address. This environment variable is not request-specific. The server reports this value as the following:

SERVER_NAME=jupiter.omniscient.com

In this case, the Web server hostname, jupiter.omniscient.com, was set. In other cases, the following DNS alias might be returned:

SERVER_NAME=jupiter

Or an IP address can be returned, such as this:

SERVER_NAME=555.555.0.19

SERVER_PORT

The SERVER_PORT variable contains the port to which the client request was sent. Ports are communications sockets used by TCP/IP applications. Most TCP/IP applications use a well-known port identifier. Such is the case with HTTP, which uses port number 80 as a default. It is possible, however, for the port number to be explicitly called out in the URL. A server may use a port number such as 8080 as the designated port for the HTTP server. In this case, URLs need to explicitly reference the port, as this example shows:

http://jupiter.omniscient.com:8080/cgi-bin/test-env

The server reported the following:

SERVER_PORT=8080

Using different port numbers enables multiple servers to be run from the same machine. This can be beneficial for establishing servers for CGI testing as well as separate intranets.

SERVER_PROTOCOL

The SERVER_PROTOCOL variable contains information about the protocol used by the requesting client. This variable includes both the name and revision number. For the earlier test example, the server reported this:

SERVER_PROTOCOL=HTTP/"1.0

SERVER_SOFTWARE

The SERVER_SOFTWARE variable contains the name and version number of the Web server that is tending to Web client requests and running the CGI. This environment variable is not client-request-specific. The server reports this value as the following:

SERVER_SOFTWARE=NCSA/1.5

In this case, the server software is the NCSA Web server, Version 1.5.

Getting Client Data to a CGI Program

A server can pass client data to a CGI program by using several methods. Two methods are by far the most functional and widely used: GET and POST. This section contains references to query strings and URL-encoding. This material is covered in the sections "Query Strings" and "URL-Encoding" presented a bit later. Feel free to flip forward for a look at this material if you get curious during the discussion of GET and POST.

Using the GET Method for <FORM> Input

The GET method is used to pass user input to an HTML form to a CGI program for processing. Although GET is the default value for the METHOD attribute, it is considered to be the less-preferred method for forms-input handling because of limitations on the amount of data that can be passed using GET (a limitation not incurred by using the POST method). Because writing CGI programs that obtain their input from the QUERY-STRING environment variable is very straightforward, GET still is useful for simpler forms with only a few input objects. A fair number of older HTML and CGI programs still exist that use GET.

After a user clicks the Submit button on a form, the client browser URL-encodes and assembles user-input data into a query string that is appended to the Action URL specified in the <FORM> tag in the HTML document. A particular request to the server may look like this:

GET /cgi-bin/dosomething.exe?name=myname&title=developer HTTP/1.0

The server then passes the URL-encoded user-input query string to the CGI program through the QUERY_STRING environment variable.

NOTE: If the user fails to enter anything in text and password-entry fields, the field value is empty, but the field name still is appended to the URL query string as "fieldname=". Also note that disabled checkboxes are ignored entirely and are not appended as part of the query string.

CAUTION: As mentioned previously, GET is useful for very simple forms. However, GET has serious limitations on the amount of user-input data that can be transmitted from the browser to the server and subsequently to the CGI program. The amount of data that can be transferred typically is limited to 256 characters. This limitation can be especially constrictive for forms with multiple fields and forms with TEXTAREA input objects. The amount of data, along with URL encoding, easily can surpass the limitations of GET, resulting in data being truncated while being passed. For this reason, POST is the preferred method for forms processing.

Using the POST Method for <FORM> Input

After a user clicks the Submit button on a form, the client browser URL-encodes user input in the same manner it does for GET; however, the data is not appended to the specified Action URL. Instead, the data is sent in a data block to the server as part of the POST operation. (A data block is simply a stream of data, of arbitrary length, passed to the CGI program.) In this case, the Action URL is the URL to which the data block is POSTed. A particular request to the server might look like this:

POST /cgi-bin/dosomething.exe HTTP/1.0
Accept: text/plain
Accept: text/html
Accept: */*
Content-type: application/x-www-form-urlencoded
Content-length: 36
ford=escort&toyota=mr2&special=rolls

The server now passes the encoded user data to the CGI program by standard input. Additionally, the CONTENT_LENGTH and CONTENT_TYPE environment variables are set for use by the CGI program.

CAUTION: Developers of CGI programs should be aware that when sending data to the CGI program using POST, the server is not required to send an End-of-File (EOF) character at the end of the data. You should use the environment variable CONTENT_LENGTH to determine how much data your CGI program needs to read from the standard input file descriptor.

Query Strings

Earlier, you learned that the value of the QUERY_STRING environment variable is obtained from a URL-encoded string that is appended after a ? character at the end of the URL that references the CGI routine being executed. But how is the information added to the end of the URL in the first place? This can occur in two ways.

In the first method, the query information is added manually to an anchor's HREF URL in an HTML document. The URL in the following anchor, for example, references a program that prints a list of all employees due for a promotion this year:

<A HREF http://www.north.pole/print-promotion-list.exe>List Promotions</A>

However, the URL may be manually appended with query string information to cause the CGI program to print a list only if a certain specified name or names are on the list:

<A HREF http://www.north.pole/print-promotion-list.exe?Mark+Drew>
ÂDisplay Promotions List</A>

In the second method, HTML forms using the GET method cause encoded user form input to be appended to the URL.

URL-Encoding

URL-encoding is a method by which requesting client data is changed or remapped to encoded characters before the server passes that data to a CGI program. URL-encoding causes the following things to occur:

Spaces are mapped to the plus-sign (+) character.
Certain keyboard characters are mapped to a percent (%) character appended with their hexadecimal equivalents.
Field data passed from HTML forms is concatenated with the ampersand (&) character.
For each field from a form, an equal-sign (=) character is used as a separator between the field name and the value the user inputs to the field.

Suppose that you have a form using the POST method. This form has three user-input fields for the user's name, e-mail address, and some free-form text. The user fills the fields as follows:

User name field (uname) is set to Drew Kittel
E-mail field (email) is set to kittel@lnk.com
Freeform text (description) is set to ~!@#$%^&*()-=+|\/~

When the form is submitted, the requesting client's data is URL-encoded according to the previously described specifications, and the server then passes this data to the CGI program. In this case, the URL-encoded data sent to the CGI program via standard input follows:

uname=Drew+Kittel&email=kittel@lnk.com
Â&description=%7E%21@%23%24%25%5E%26*%28%29-%3D%2B%7C%5C%2F%7E

Note that the form name/value pairs are separated by & characters and that the actual field names and their respective values are separated by = characters. Finally, note how some of the characters in the free-form text are mapped to %hex-equivalent. For example, the = character in the free-form text was encoded as %3D. This differentiates it from the = character the server uses as a separator for field names and values.

NOTE: According to the HTTP specification, certain characters do not need to be encoded as hexadecimal. In the preceding example, you can see that the at sign (@), period (.), minus sign (-), and asterisk (*) characters were not encoded before the data was passed to the CGI program.

Data provided by a requesting client using forms with GET and POST methods always is URL-encoded before being passed to the CGI program.

What Is Windows CGI?

As stated in earlier sections, CGI specifies that data passed to back-end programs, such as a database program, be passed by environment variables or standard input. This specification is very specific to UNIX-type environments and does not suit the Windows development environment particularly well.

The Windows CGI specification was developed to provide a framework for how Web servers should implement CGI interfaces that support the development of CGI applications in a Windows environment. Unlike its UNIX counterparts (or any other known CGI interfaces), the Windows CGI interface does the work of decoding and parsing URL-encoded data passed to the server from the browser client. This decoded data is provided to back-end CGI programs via a formatted profile file (and, in some instances, a raw content file). The profile file is simply a version of the very familiar Windows .INI file.

The Windows CGI interface is the only known CGI interface that performs the work of decoding and parsing form data for you. Doing things in this manner greatly improves the capability to develop Web applications using common Windows development tools, such as Visual Basic and Visual Basic for Applications. This process also enables developers to focus on developing the application instead of worrying about the details of how data is passed from the client to the server and to CGI programs.

RESOURCE: Not all Windows Web servers provide support for the Windows CGI specification and Windows CGI applications. Currently, O'Reilly WebSite servers and Netscape FastTrack and Enterprise servers do provide this support. You can download evaluation versions of O'Reilly's WebSite server from

http://www.ora.com/

Evaluation versions of Netscape's servers are available for downloading at
http://www.netscape.com/

Additionally, you can get the full Windows CGI 1.3a Interface specification at the O'Reilly WebSite central site at
http://website.ora.com/wsdocs/32demo/windows-cgi.html

How Windows CGI Works

In the following sections, you'll examine a few fundamental concepts about the workings of Windows CGI. In particular, you'll learn about these topics:

The Windows CGI data-flow process
Windows CGI I/O spooling
Data decoding performed by the server
The server launching a Windows CGI program
The .INI file and its components

The Windows CGI Data-Flow Process Figure 18.2 illustrates the data flow and processing performed during the request-response cycle. Windows CGI responds to and services requests from client browsers by following these steps:

1. The client browser submits a request to the server. Typically, the request includes data from an HTML form to be processed by a CGI program.

2. The server receives the request and scans the URL to determine the CGI program to be executed
.
3. The server splits the request into headers and content. Any raw content is spooled into the temporary input file.

4. The server converts header data into named variables in the server, combines these variables with other server variables, and writes this information to the temporary .INI file.

5. The server decodes and parses any form content accompanying the request. It creates a named variable for every form field and adds this information to the .INI file.

FIGURE 18.2: The Windows CGI data-flow process.

6. The server creates a temporary output spool filename and adds the input and output spool filenames to the .INI file. At this point, the request package (.INI file and raw-content input file) created by the server contains everything the CGI program needs to process the client request and generate a response.

7. The server then launches the specified CGI program by executing the CreateProcess() service. It provides the full path to the .INI file so that the CGI program knows where to find the data it needs to perform its function.

8. The CGI program locates the .INI file and extracts its contents by using the GetPrivateProfileString() Windows system call.

9. The CGI program now can use the data extracted from the .INI file to perform its tasks. This might include formulating a query to a SQL database, using VBA to interact with an Excel spreadsheet or an Access database, or performing any number of other operations on behalf of the client.

10. After performing its tasks, the CGI program typically generates a response to the client browser. In constructing the response, the CGI program creates an output spool file using the filename specified by the server in the .INI file. It then writes response data (which consists of two sections: a Content-type header and the actual response content) into this spool file. This response content can be anything, such as plain text or images, but it typically is composed of HTML. Note that more sophisticated applications may embed ActiveX controls and VBScript into the returned HTML as well. After writing the response data, the CGI program exits.

11. When the server determines that the CGI program has finished running, it reads data from the output spool file, generates a complete HTTP-compliant response, and forwards the response content to the requesting browser.

Windows CGI I/O Spooling The Windows CGI specification stipulates that servers implementing the Windows CGI interface should provide for a spooled exchange of data between the Web server and CGI programs. In order to ensure efficient transfers of data between spool files and the network, the specification also recommends that the server use memory-mapping techniques while minimizing the number of network I/O requests used.

I/O spooling is performed by Windows CGI-compliant servers for a number of reasons:

Network socket I/O capabilities are not native to most rapid application development packages.
Use of socket I/O techniques in the Windows environment is complex and requires a strong knowledge of the Win32 network interface. Additionally, complex buffering of input and output data is required for network efficiency.
Spooled input (content submitted via the POST method) is memory-mappable, which supports more efficient processing than stream techniques.
Sockets cannot be inherited by 16-bit programs.
Reference spool files can be used for testing and debugging CGI applications.

Data Decoding Performed by the Server A browser can send form data to a server in one of two ways:

URL-encoded data: This is a method by which data passed from a client browser is mapped so that some characters are encoded as different values. See the previous sections "Query Strings" and "URL-Encoding" for detailed information on URL-encoding.
Multipart form data: A data format that permits efficient uploading of files via forms. The browser indicates this method of sending data with a Content-type header of multipart/form-data.

Windows CGI-compliant servers are required to support both of these form data types.

NOTE: The Windows CGI specification requires that compliant Web servers decode HTML data when it is submitted via the POST method. However, the specification does not require decoding of form data that appears in the query string portion of a request URL (such as that submitted via the GET method). Note that the query string data still is available via the CGI environment variable CGI_QueryString provided by the CGI32.BAS framework (which is covered in a later section of this chapter entitled "CGI32.BAS: A Framework for Developing Windows CGI Applications Using Visual Basic"), but it then is up to the developer to decode and parse the data in this string.

How the Server Launches Windows CGI Programs The Windows CGI specification states that compliant servers must launch a CGI program by using the CreateProcess() service with the following command line:

win-cgi-exe cgi-data-file

Here, win-cgi-exe is the complete path to the CGI program executable, and cgi-data-file is the complete path to the CGI data file.

NOTE: The CGI program executable does not actually need to be an executable file. It can be a document file if the appropriate association to a corresponding application program already has been established. Compliant servers will attempt to find the associated application and launch it to process the document.

The Windows CGI Profile (.INI) File As previously stated, Windows CGI-compliant Web servers pass data to CGI programs via a private profile (.INI) file. The data in this file is maintained in key-value format so that CGI programs can use standard Windows API calls for extracting key-value pairs from the file. The profile file consists of eight sections per the following template:

[CGI]

[Accept]

[System]

[Extra Headers]

[Form Literal]

[Form External]

[Form File]

[Form Huge]

[CGI]: This section contains most of the standard CGI environment variables, each provided as a string value. If the value is an empty string, the keyword is omitted from this section.
[Accept]: This section contains a listing of the MIME data types that are accepted by the client that sent the request. Entries in this section are in one of two formats:

Accept: type/subtype {parameters}

Or, if no parameters are present,

Accept: type/subtype=Yes

You can find additional information on MIME in Appendix G.

NOTE: CGI programs using the CGI32.BAS framework (covered later in this chapter) can use the GetPrivateProfileString() function using NULL for the key name to return all the keys (that is, Accept types) listed in this section. Keys are returned as a null-delimited string with a double-null terminator.

[System]: This section contains information specific to Windows CGI. The following keys are used: GMT Offset, Debug Mode, Output File, and Content File.
[Extra Headers]: This section contains extra headers included with the request. For example, it is common to see the browser type listed with the User-agent key. The information is in key=value format.

NOTE: CGI programs using the CGI32.BAS framework can use the GetPrivateProfileString() function using NULL for the key name to return all the keys (extra headers) listed in this section. Keys are returned as a null-delimited string with a double-null terminator.

[Form Literal]: This section contains form data that already has been decoded by the server (if the request was sent using the POST method and a Content-type of application/x-www-form-urlencoded or multipart/form-data). Information in this section is stored in key=value format, where key is the form field name and value is the corresponding form field value.

NOTE: When HTML forms contain SELECT MULTIPLE form objects, there will be multiple occurrences of the key (form field name). Windows CGI-compliant servers handle this case by generating a key=value pair for the initial occurrence and then appending a sequence number to each subsequent occurrence. You can see an example of this in the output generated by the process_form_data.exe program in Figures 18.5 through 18.7. It is necessary for the CGI program to detect and properly handle these tagged keys.

[Form External]: When a decoded string exceeds 254 characters, or if it contains control characters or quotation marks, the string is written to an external file. The entry in this section is of the form

key=pathname length

where pathname is a path to the temporary file that contains the decoded string and length is the length of the string in bytes.

NOTE: You must open the temporary file listed in this section in binary mode unless you are certain that the data is all text.

[Form Huge]: When raw value strings exceed 65,536 bytes, no decoding is performed by the server. The field contained in this section is listed as

key=offset length

where offset is the number of bytes from the beginning of the content file to the start of the raw value string for this key, and length is the length of the string in bytes.

NOTE: You can use offset to perform a seek to the start of the raw value string, and then you can use length to read the appropriate amount of data. The file should be opened in binary mode unless you are certain that its contents are all text.

[Form File]: When requests are in the format multipart/form-data, file uploads may be included. If this is the case, each file is uploaded and placed into external temporary files. Every uploaded file is listed in this section as the following:

key=[pathname] length type xfer [filename]

where pathname is the path to an external file that contains an uploaded file, length is the length (in bytes) of the uploaded file, xfer is the content-transfer encoding used during the upload, and filename is the name of the original file uploaded. Note that the brackets shown in this statement are required.

CGI32.BAS: A Framework for Developing Windows CGI Applications Using Visual Basic

In the previous section, you discovered in great detail how the Windows CGI interface works. In particular, you saw how the interface takes care of many details, such as I/O spooling and the tedious task of decoding URL-encoded form data. Your CGI program still must do a bit of work, though, before it can complete the specific tasks it was designed to perform. Consider how you would perform these tasks:

Getting form data out of the .INI and inputting files in the request package created by the server
Accessing and using CGI environment variables
Handling error conditions
Sending response data back to the requesting client

Because these are functions that should be performed by any well-conceived CGI program, wouldn't it be nice if some proven development tools existed that took care of many of these details for you and enabled you to focus on the application?

Thankfully for VB developers, such a set of proven tools does exist in the form of the Windows CGI framework for Visual Basic. This framework is a 32-bit Visual Basic module (CGI32.BAS) that can be added to your VB projects to provide the following functionality:

Defining several variables that can be used in your program (see Table 18.1 for a summary of these variables).
Providing a number of functions that simplify retrieving form data from the .INI file.
Providing functions for gracefully trapping errors in your CGI programs (see Table 18.2 for a summary of these functions).
Defining a Sub Main() routine for your CGI program.
Providing utility functions for sending response data to the output spool file (see Table 18.2 for a summary of these functions).

RESOURCE: The CGI32.BAS framework module for Visual Basic is the result of some exceptionally fine work by Robert Denny. Anyone who has developed CGI applications in a language such as Perl or C will appreciate the degree to which this framework simplifies the task of using data submitted by HTML forms as well as sending data back to the client browser. This simplicity avoids the need to reinvent the wheel and enables developers to focus on the most important task at hand: developing CGI programs that provide value to their users. The CGI32.BAS framework actually was developed for Visual Basic 4, but development and testing of applications during the writing of this book found that it worked without a hitch in Visual Basic 5 as well. The framework module is available as a zipped file (CGI32.ZIP), and you can download it free of charge from the O'Reilly Software Library at

http://software.ora.com/techsupport/software/extras.html

Using the CGI32.BAS Framework in Your Applications

Using the CGI32.BAS framework in your applications requires that you structure your applications in a specific way and keep a few additional considerations in mind:

Because the CGI32.BAS framework provides a Sub Main(), VB projects that use the framework should be set to start in Sub Main instead of in a form.
When the CGI program runs, the Main routine provided by the framework extracts all form data, request headers, and environment variables and stores them in global variables where they are accessible directly or via utility routines (see Tables 18.1 and 18.2 for more information).
The Main routine provided by the framework establishes a global exception handler that traps runtime errors and produces some response before the CGI program is allowed to exit.
After the setup of the CGI environment is complete, the framework calls the CGI_Main() routine. This is a routine that you must write. In other words, this is where the work your CGI application performs is actually done.
It is important that you never abort or perform an exit within a CGI program that uses the framework. Doing this can cause the program to hang or exhibit undesirable results.
All CGI programs that use the framework must include a simple routine called Inter_Main(). In most cases, this routine is coded to simply display a MsgBox if the CGI executable is invoked by double-clicking it.
Typically, when new VB projects are created, a .frm form file is automatically included, along with a few .ocx custom control files. In most instances, you will not need the form and can discard it (the VB SQL Server API example at the end of this chapter demonstrates a case where the form is required). Additionally, you should attempt to remove as many controls and references to OLE libraries as possible. You can accomplish this by trying to disable checked entries on the Components and References submenus from the Project menu on the VB 5 main menu bar (you use the Custom Controls and References submenus from the Tools menu on the VB 4 main menu bar).
To include the CGI32.BAS framework in your VB 5 project, simply choose Project | Add File. In the Add File dialog box, locate and select CGI32.BAS on your system (if you are using VB 4, you'll choose File | Add File).
In most cases, you simply can compile your program as a standard .EXE executable and place it in the appropriate path used for Windows CGI applications on your system. On the WebSite server, for example, Windows CGI programs are placed in the \WebSite\cgi-win directory.

A Sample CGI_Main() Program As previously mentioned, CGI_Main() is a program you write to perform the primary functions of your CGI program--it is where the meat of your application resides. This program is called from the Main() program in the CGI32.BAS framework. Listing 18.1 shows a simple CGI_Main() program that uses the WebDate() and Send() functions supplied by the framework and returns the date and time at the Web server.

Listing 18.1. A Simple CGI_Main() Program.

Sub CGI_Main()
    Send("Content-type: text\html")
    Send( " ")
    Send("<HTML><HEAD><TITLE>Web Server Date/Time</TITLE></HEAD>")
    Send("<BODY>")
    Send("The Web Server Date/Time is: " & WebDate())
    Send("</BODY></HTML>")
End Sub

A Sample Inter_Main() Program The Inter_Main() program is called from the Main() program in the CGI32.BAS framework if the CGI program is executed from Windows (if a user double-clicks the .exe filename). For most CGI applications, it is sufficient to have the program display a message box stating that this program is a CGI program. The following example illustrates how this is done:

Sub Inter_Main()
MsgBox "This is a Windows CGI Program"
End Sub

Windows CGI Global Variables

Table 18.1 provides a listing and description of global CGI variables provided by the Windows CGI Visual Basic framework. The use of many of these variables is demonstrated in Listing 18.2 and Figures 18.5 through 18.7. Data types in this table are specified as the following:

HT HugeTuple I Integer

S String

T Tupl

V Variant

Table 18.1. Global CGI Variables Provided by the Windows CGI Visual Basic Framework.

Variable Description Data Type

The following variables hold server information

CGI_GMTOffset Number of seconds from Greenwich Mean Time (GMT). V

CGI_ServerAdmin Server administrator's e-mail address. S

CGI_ServerSoftware Name and version of Web server software (for example, WebSite/1.1f). S

CGI_Version CGI version this server complies with
(for example, CGI/1.2). S

The following variables hold browser and user information

CGI_AcceptTypes

MIME data types accepted by the browser.

T

CGI_From

E-mail address of the user. This rarely
is supplied by the user's browser.

S

CGI_NumAcceptTypes

Number of MIME data types accepted by the browser.

I

CGI_Referer

URL that referred to the CGI program.

S

CGI_RemoteAddr

IP address for the remote host running the browser.

S

CGI_RemoteHost

Hostname for the remote host running
the browser.

S

CGI_RequestProtocol

Name and revision of protocol used
(for example, HTTP/1.0).

S

The following variables hold information about executable, logical, and physical paths

CGI_ContentFile

Full path to a file that contains any
attached data (for example, POST data).

S

CGI_ContentLength

Length (in bytes) of attached ContentFile data.

L

CGI_ContentType

MIME content types of request with
attached data (for example, POST data).

S

CGI_ExecutablePath

Path to the CGI program being run.

S

CGI_FormTuples

name=value pairs sent by the form (if any).

T

CGI_HugeTuples

Large name=value pairs.

HT

CGI_LogicalPath

Logical or extra path information.

S

CGI_NumFormTuples

Number of name=value pairs sent by the form (if any).

I

CGI_NumHugeTuples

Number of large name=value pairs.

I

CGI_PhysicalPath

Physical path--the translated version of the logical path in CGI_LogicalPath.

S

CGI_QueryString

Encoded portion of the URL appended after the ? character. This contains GET data or the query string (if it exists).

S

CGI_RequestMethod

Request method (for example, GET or POST).

S

CGI_ServerName

Server hostname for the request (this
can vary in multihomed configurations).

S

CGI_ServerPort

Port number associated with the request.

I

The following variables hold information about security

CGI_AuthPass

Authorized user's password (only if
enabled).

S

CGI_AuthRealm

Authorized user's realm.

S

CGI_AuthType

Authorization method.

S

CGI_AuthUser

Authorized user's name.

S

The following variables hold other miscellaneous information

CGI_DebugMode

CGI tracing flag from the server.

I

CGI_ExtraHeaders

Extra header information supplied by
the browser.

T

CGI_NumExtraHeaders

Number of extra headers supplied.

I

CGI_OutputFile

Full path to the file in which the Web
server expects the CGI program's response.

S

Windows CGI Utility Functions

Table 18.2 provides a listing and description of utility functions provided by the Windows CGI Visual Basic framework. The use of many of these functions is demonstrated in Listing 18.2 and Figures 18.5 through 18.7. Return value types in this table are specified as the following:

N/A Not applicable

T/F True or False

S String

Table 18.2. Utility Functions Provided by the Windows CGI Visual Basic Framework.

Function Description Return Type

ErrorHandler() Global exception handler. N/A

FieldPresent() Tests for the presence of a form field T/F

(especially useful to determine whether

checkboxes on a form were checked).

GetSmallField() Retrieves the contents (for example, S

a value from a name=value pair) of a

named form field.

PlusToSpace() Removes plus sign (+) delimiting N/A

characters from a string. The + signs

are converted to spaces.

Send() Writes a string into the output spool N/A

file. This function is used heavily for

formatting HTML output to the client.

SendNoOp() Sends a complete response that causes the N/A

browser to do nothing and to stay on its

current page.

Unescape() Removes URL-escaping from a string; S

returns a modified string.

WebDate() Returns a Web-compliant date and time S

string (in GMT).

Getting Something Useful Out of a Windows CGI Program

You may remember that one of the benefits of using CGI applications is the capability to generate HTML on-the-fly. This capability to generate HTML documents and data presentations dynamically is not just a nice feature--it is an essential feature when providing access to database, customer service, and electronic commerce applications. These types of applications are interactive by nature and require the capability to formulate responses and outputs at runtime. Prepackaged HTML and static documents may be served up, but more often, responses need to be customized according to the user's input. Besides, it just isn't practical or desirable to have canned output for every permutation of database query a user's form inputs might generate.

Generating HTML on-the-fly might sound complicated, but by using Windows CGI and the CGI32.BAS framework utility functions, it's actually remarkably easy if you remember the following two points. First, a specific format is required if the CGI program is returning data to the requesting client. The format requires a data header, followed by a blank line, followed by the data to be returned. The required format follows:

Content_type: text/html

start of HTML document
   ...body of document...
end of HTML document

Note that the blank line after the data header is absolutely required. This action is something that is required time and again, and it easily can be implemented as the subprocedure (see the SendHTMLStart procedure in Listing 18.2).

Second, CGI programs that use the framework should never abort or perform an exit within the CGI program. This can cause the program to hang or exhibit undesirable results.

Generating dynamic HTML that uses the CGI32.BAS framework module and its Send() utility function is demonstrated in the next section.

A Sample Windows CGI Visual Basic Program

Up to this point, you've been introduced to how Windows CGI interface passes form data to a Windows CGI program and is used to return data to a client. Additionally, you saw an overview of the CGI32.BAS framework and the functionality it provides. Now it's time to look at how the CGI32.BAS Visual Basic framework actually can be used by a CGI program to access the form data and then dynamically generate a response back to the client browser.

Listing 18.2 presents the Windows CGI Visual Basic program process_form_demo.exe. This program uses several CGI32.Bas utility functions and global variables to obtain information about HTML form input and CGI environment variables from the CGI request package created by the server. It then uses the CGI32.BAS Send() function to echo this information back to the client browser in the form of an HTML document. This program can be used with any HTML form and provides a nice way to test HTML forms in your applications.

In this sample Windows CGI Visual Basic program, a user is presented with the HTML form shown in Figures 18.3 and 18.4.

FIGURE 18.3.: An online reader survey and subscription appli-cation form used to invoke the CGI program shown in Listing 18.1.

FIGURE 18.4.: A continuation of the online reader survey and subscription appli-cation form shown in Listing 18.1.

This form, an online reader survey and subscription application for a trade magazine, asks the user to input demographic information and then submit it to qualify for a subscription. Listing 18.2 shows the HTML code for this form.

Listing 18.2. Code Listing for dBWeb_form.htm, an Online Reader Survey and Subscription Application.

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Microsoft FrontPage 2.0">
<title>Web.dB Journal - Reader Survey and Subscription Application</title>
</head>

<body bgcolor="#FFFFFF">
<!-- Example: Magazine Reader Survey and Subscription Form -->
<!-- Author:  Drew Kittel -->
<!-- HTML Specifications: HTML Level 2.0 Compliant (w/TABLE) -->
<!-- Revision History: created 10 Mar 1996 -->
<h2 align="center">
<em>Web.dB</em> - Reader Survey and Subscription Application</h2>

<hr>

<form action="http://dkittel.clark.net:8080/cgi-win/process_form_demo.exe"
method="POST">
<!-- Create TEXT and TEXTAREA widgets for name, e-mail, mail entry -->
<!-- Use TABLE to provide a bit of formatting control -->
<p><strong>Please provide the following personal information</strong> <em>
(Please fill all fields)</em> </p>
    <table border="0">
        <tr>
            <td>Full Name: </td>
            <td><input type="text" size="50" name="uname"></td>
            <td><br>
            </td>
        </tr>
        <tr>
            <td>E-Mail: </td>
            <td><input type="text" size="50" name="email"></td>
        </tr>
        <tr>
            <td>Mailing<br>
            Address: </td>
            <td><textarea name="address" rows="5" cols="50"></textarea></td>
        </tr>
    </table>
<p><strong>What is your company's main product/service</strong>
    (choose the most applicable) <br>
<!-- Use radio buttons to force mutually-exclusive choices -->
DB-Software <input type="radio" checked name="bus" value="sw">
Consulting <input type="radio" name="bus" value="consult">
Engineering <input type="radio" name="bus" value="engr">
Research <input type="radio" name="bus" value="research">
ADP Services <input type="radio" name="bus" value="adp"> </p>
<p><strong>What is your title?</strong> (choose the most applicable) <br>
<!-- Use radio buttons to force mutually-exclusive choices -->
President-CEO <input type="radio" checked name="title" value="Pres">
Manager <input type="radio" name="title" value="Manager">
Designer <input type="radio" name="title" value="Designer">
Programmer <input type="radio" name="title" value="Programmer">
Analyst <input type="radio" name="title" value="Analyst"> </p>
    <hr>
<!-- These scrolled lists allow multiple choices -->
<!-- TABLE used to provide side-by-side formatting -->
<p><strong>Please tell us about your development environment</strong>
<em>(Please select all that apply)</em> </p>
    <table border="0" noborder>
        <tr>
            <th>Languages</th>
            <th>Data Bases</th>
            <th width="20%">Describe Your Applications</th>
        </tr>
        <tr>
            <td><select name="cgidev" multiple size="8">
                <option selected value="Perl5">Perl 5.0 </option>
                <option value="Magic">MAGIC </option>
                <option value="Sybperl">SybPerl </option>
                <option value="Oraperl">OraPerl</option>
                <option value="C">C </option>
                <option value="C++">C++</option>
                <option value="Unix Shells">Unix Shells</option>
                <option value="Python">Python</option>
                <option value="REXX">REXX</option>
                <option value="FORTRAN">FORTRAN</option>
            </select> </td>
            <td><select name="dbase" multiple size="8">
                <option selected value="Access">MS ACCESS</option>
                <option value="Sybase">Sybase</option>
                <option value="Oracle">Oracle</option>
                <option value="FoxPro">FoxPro</option>
                <option value="WDB">WDB</option>
                <option value="Web-Genera">Web-Genera</option>
                <option value="VAX DBMS">VAX DBMS</option>
                <option value="DB2">DB2</option>
                <option value="Informix">Informix</option>
                <option value="RdB">RdB</option>
            </select> </td>
            <td><textarea name="description" rows="8" cols="50"></textarea>
           </td>
        </tr>
    </table>
<p><strong>Do you currently develop/plan to develop
Web-accessible databases?</strong><br>
YES<input type="radio" checked name="dbyesno" value="YES">
NO<input type="radio" name="dbyesno" value="NO"> </p>
<p><strong>Do you want to receive/continue to receive a free subscription
to </strong><em><strong>Web.dB</strong></em><strong>?</strong><br>
<strong>YES!</strong><input type="radio" checked name="subscribe" value="YES">
NO<input type="radio" name="subscribe" value="NO"> </p>
<p><input type="submit" value="Send My Subscription Now!">
<input type="reset" value="Clear Form Entries"> </p>
</form>

<hr>

<p>&nbsp;</p>
</body>
</html>

When the survey form is submitted, this Action URL is invoked:

action="http://dkittel.clark.net:8080/cgi-win/process_form_demo.exe"method="POST"

Form data is passed to the server, and the CGI program process_form_demo.exe is launched.

Figures 18.5 through 18.7 illustrate the dynamic HTML generated by the program and returned to the client.

FIGURES 18.5. Dynamic HTML generated by the program and re- turned to the user. (Remainder of this form is shown in Figures 18.6 and 18.7.)

FIGURE 18.6.Figure 18.5 continued.

FIGURE 18.7. Figures 18.5 and 18.6 continued.

Listing 18.3 shows the VB code for CGI_Main(), Inter_Main(), and a few subroutines used to create the executable.

Listing 18.3. Code Listings for Modules Used to Create the process_form_demo.exe CGI Program.

Attribute VB_Name = "Process_Form_Demo"
Option Explicit

Sub CGI_Main()

    Dim i As Integer

    SendHTMLStart

    Send ("<HTML><HEAD><TITLE>Form Processing Demo</TITLE></HEAD>")
    Send ("<body bgcolor=#FFFFFF>")
    Send ("<CENTER>")
    Send ("<H2>Form Inputs and CGI EnvironmentVariables</H2>")
    Send ("</CENTER>")
    Send ("<HR>")

    SendFormTuples

    Send ("<HR>")

    SendCGIVariables

    Send ("<HR>")

    SendExtraHeaders

    Send ("<HR>")

    SendMIMETypes

    ` Now return to complete HTTP.

    Send ("</BODY></HTML>")

End Sub


Sub Inter_Main()
    MsgBox "This is a Windows CGI program"
End Sub


Sub SendCGIVariables()

    Send ("<H3>CGI Variables</H3>")
    Send ("<UL>")
    Send ("<LI><I>CGI Version: </I>" & CGI_Version)
    Send ("<LI><I>Request Protocol: </I>" & CGI_RequestProtocol)
    Send ("<LI><I>Request Method: </I>" & CGI_RequestMethod)
    If CGI_RequestKeepAlive Then
        Send ("<LI><I>Keep-Alive: </I>  Yes")
    Else
        Send ("<LI><I>Keep-Alive: </I>  No")
    End If
    Send ("<LI><I>Executable Path: </I>" & CGI_ExecutablePath)
    Send ("<LI><I>Logical Path: </I>" & CGI_LogicalPath)
    Send ("<LI><I>Physical Path: </I>" & CGI_PhysicalPath)
    Send ("<LI><I>Query String: </I>" & CGI_QueryString)
    Send ("<LI><I>Content Type: </I>" & CGI_ContentType)
    Send ("<LI><I>Content Length: </I>" & CGI_ContentLength)
    Send ("<LI><I>Server Software: </I>" & CGI_ServerSoftware)
    Send ("<LI><I>Server Name: </I>" & CGI_ServerName)
    Send ("<LI><I>Server Port: </I>" & CGI_ServerPort)
    Send ("<LI><I>Server Admin: </I>" & CGI_ServerAdmin)
    Send ("<LI><I>Remote Host: </I>" & CGI_RemoteHost)
    Send ("<LI><I>Remote Address: </I>" & CGI_RemoteAddr)
    Send ("<LI><I>From: </I>" & CGI_From)
    Send ("<LI><I>Referer: </I>" & CGI_Referer)
    Send ("<LI><I>User Agent: </I>" & CGI_UserAgent)
    Send ("<LI><I>Authentication Method: </I>" & CGI_AuthType)
    Send ("<LI><I>Authenticated Username: </I>" & CGI_AuthUser)
    Send ("<LI><I>Authentication Realm: </I>" & CGI_AuthRealm)
    Send ("</UL>")

End Sub

Sub SendExtraHeaders()

    Dim i As Integer

    If CGI_NumExtraHeaders > 0 Then
        Send ("<H3>Extra Headers:</H3>")
        Send ("<UL>")
        For i = 0 To CGI_NumExtraHeaders - 1
            Send ("<LI><I>" & CGI_ExtraHeaders(i).key & ": </I>" &
                                      ÂCGI_ExtraHeaders(i).value)
        Next i
        Send ("</UL>")
    End If

End Sub

Sub SendFormTuples()

    Dim i As Integer

    Send ("<H3>Form fields data</H3>")
    If CGI_NumFormTuples > 0 Then
        Send ("<UL>")
        For i = 0 To CGI_NumFormTuples - 1
            Send ("<LI><I>" & CGI_FormTuples(i).key & ": </I>" &
                                      ÂCGI_FormTuples(i).value)
        Next i
        Send ("</UL>")
    Else
        Send ("(none)")
    End If

End Sub

Sub SendHTMLStart()
    Send ("Content-type: text/html")
    Send ("")
End Sub


Sub SendMIMETypes()

    Dim i As Integer

    Send ("<H3>MIME Types:</H3>")
    If CGI_NumAcceptTypes > 0 Then
        Send ("<UL>")
        For i = 0 To CGI_NumAcceptTypes - 1
            If CGI_AcceptTypes(i).value = "Yes" Then
                Send ("<LI>" & CGI_AcceptTypes(i).key)
            Else
                Send ("<LI>" & CGI_AcceptTypes(i).key & " (" &
                                              ÂCGI_AcceptTypes(i).value & ")")
            End If
        Next i
        Send ("</UL>")
    Else
        Send ("(none)")
    End If
End Sub

Most of this program is very straightforward and can be easily understood by briefly studying the variables and functions summarized in Tables 18.1 and 18.2. You should note a few things about this example:

The values of environment variables and form field values supplied by the user (which are not known to the CGI program until runtime) are made part of the HTML document returned to the client.
The HTML response to the client truly is generated on-the-fly.
The <HTML> tags sent in the response are not strictly necessary because the Content-type header identifies this data as HTML to the client.
CGI_Mai and Inter_Main programs are both supplied as required by the framework. The CGI_Main program calls a number of subprocedures responsible for processing form data sent when a form is submitted, as well as for reading the values of global CGI variables created by the CGI32.BAS framework. The SendFormTuples subprocedure, for example, loops through all form data (decoded key=value pairs) in the [Form Literal] section of the profile file and returns this information to the client in the form of an HTML list. The SendCGIVariables subprocedure, on the other hand, sends back the value of CGI variables in the form of an HTML list.

As you can see, dynamic HTML generation in a CGI program is very straightforward. Of course, this is a simple example that simply uses HTML tags for creating lists. But the example does demonstrate how powerful this method can be when your CGI integrates other processing, database accesses, and subroutines designed to streamline the generation of the HTML tags and data returned. It also is possible to generate very complex responses to the requesting client. Data retrieved from a database, for example, could be formatted in very elaborate HTML tables using a variety of header fields, different font sizes and styles, as well as different font colors and table cell background colors for emphasis and to create visually appealing presentations of data. Additionally, dynamic generation of responses to the client could be used to generate graphical charts using specialized ActiveX controls. VBScript also could be included to enable the user to manipulate attributes of the displayed chart, such as color, title, graph style, and so on. These techniques are explored in greater detail in Chapter 19, "Database Results Presentation Using HTML and CGI."

Accessing Web Databases Using CGI

Now it's time to explore how a Web server can use the power of CGI programs to access databases and submit queries on behalf of a Web client. This is an exciting time to be involved in Web database development. The world is just now realizing the potential of Web-based database applications. Individuals, organizations, and agencies are rushing to exploit the technology and implement these systems. This section provides you with some of the tools required to get involved right away.

In this section, you'll briefly examine some of the common methods used to access Web databases. You'll look at the reasons for my opinion that customized CGI programs offer one of the most flexible means of providing that access. You'll also see that all useful CGI database applications must accomplish a series of basic steps. Finally, you'll be presented with an example that illustrates how Windows CGI and the Visual Basic API for Microsoft's SQL Server can be used to develop Web database applications.

A Brief Summary of Common Web Database Access Methods

Currently, a few fundamental methods exist by which Web servers can access database systems on behalf of a Web client. Most of these methods use extensions to or are some variant of CGI programs. Note that each method has its inherent strengths and weaknesses, and some have implementations specific to the database system and Web server being used. The following sections briefly explore these common methods:

HTML-embedded SQL extensions
Codeless interfaces
Web server API programs
Custom CGI programs

HTML Embedded SQL Extensions Many database systems provide a mechanism for embedding SQL statements directly within HTML files passed to the Web client by the Web server. When a user submits a query, the HTML file is passed to a CGI program, which parses the SQL statements and passes these on to the database system. Results of the SQL query subsequently are retrieved by the CGI program, reformatted to HTML, and returned to the requesting client. This mechanism is typical of many shareware databases and gateways. Additionally, the developer often is limited in his capability to modify the CGI program performing the actual database access and formatting of results. Chapter 24, "The Development of a PC-Based WWW Database System," demonstrates the use of DBGateway--a gateway application (developed using Windows CGI and Visual Basic) that uses HTML-embedded SQL. Codeless Interfaces Essentially, codeless types of access methods consist of software toolsets (often Perl scripts) that work with developer-defined template files. These template files specify various views into the database and the way in which extracted data should be manipulated and formatted when returned to the requesting client. Programs within the software toolset use these template files to automatically generate HTML forms. These forms, when submitted by the user, are then processed by CGI programs, which query the database and format the results in accordance with the predefined templates.

Most of the toolsets available today are somewhat manual in nature. In the near future, however, visual programming tools that implement drag-and-drop technology for building forms interfaces, specifying database queries, and detailing the format of results sent to the user should become more prevalent. Custom CGI Programs Customized CGI programs are gateway programs specifically written to accept and process Web client requests for a service provided by the Web server. Typically, these requests are submitted by users in the form of input they have entered in an HTML form. CGI programs designed for database access parse this user input, formulate queries based on the input, connect to the database and submit the query, and accumulate results. They also can manipulate these results or perform some other specialized processing and dynamically create HTML documents, which subsequently are sent back to the Web client. Web Server API Programs The use of Web Server APIs is becoming increasingly more popular. Most major Web servers now have an API available to applications developers. Microsoft has ISAPI for Internet Information Server (IIS), Netscape has NSAPI for its line of servers, and O'Reilly has WSAPI for its WebSite servers.

Server API programs provide many of the same benefits of CGI because they are very flexible and enable developers to greatly extend the functionality of the server. Most server APIs do not allow developers to choose the language in which they would like to develop their applications, however. In other words, the server API is language-specific. Additionally, although many server API programs realize a significant performance advantage over their CGI counterparts, this advantage often is realized because the application shares process space with the server itself. This can be dangerous; if a bug causes an application to die, for example, the server may crash as a result.

Why Use Customized CGI Programs for Database Access?

Currently, customized CGI programs are by far the most common method for accessing Web databases. CGI programs give developers a tremendous amount of power and flexibility in extending Web server functionality. Without question, other methods, such as those described earlier, have their place and provide advantages when dealing with well-defined and structured systems. As most experienced software developers have discovered, though, there invariably comes a time when development tools don't provide the functionality, flexibility, or power to do what is necessary to get the job done. Custom CGI programs provide this functionality, flexibility, and power by enabling developers to design the application to best suit the problem at hand.

A custom CGI program allows for development of applications that access databases in a variety of ways, including these:

SQL queries, which typically are used to access relational database systems
Non-SQL queries, which typically are used to access nonrelational database systems, such as network, hierarchical, and CODASYL model databases (some very large legacy systems still use this type of database)
Stored procedures, which typically are database-resident SQL "programs" or batches of commands
Database-specific APIs
Open Database Connectivity (ODBC) API

Additionally, custom CGI programs provide distinct advantages over other methods, including these:

Developers maintain complete control over the application being developed and the mechanisms by which user input is validated and the database is accessed.
The learning curve for CGI developers is often significantly shorter than for gateway products or server APIs.
Developers can more fully exploit advanced methods such as vendor-specific SQL extensions and functionality including the use of triggers, database client-side cursors, and server-side cursors.
Developers often have a choice of the language in which they want to develop the CGI program. In fact, mixed-language development is readily accommodated if it is necessary.
Other access methods often are specific to a single database or Web server, whereas custom CGI programs provide the flexibility to access multiple types of databases on a variety of Web servers through a number of programming interfaces. Additionally, custom CGI programs enable developers to write programs that can access multiple, heterogeneous, geographically distributed database servers.
Complex applications can be developed. These can include preprocessing of user input, interaction with several database servers and information repositories, additional manipulation and processing of database results, integration with other custom or commercial applications, use of multiple APIs, and generation of complex, dynamically created HTML data presentations.

The Flow of a Basic CGI Database Program

Figure 18.8 illustrates the basic steps required in all useful CGI database access programs. The basic steps apply to most programs, but the sequence may vary somewhat from application to application. All useful programs perform the following tasks, however:

Reading, parsing, and decoding data passed from an HTML form (in the case of Windows CGI, this is handled for you)
Creating a database query or command
Connecting to the database server and sending the query/command
Fetching results of the query/command from the server
Formulating and sending a response (typically, HTML and possible ActiveX controls and VBScript) to the Web client through the Web server
Putting a stop to the sending of output and returning to the server

FIGURE 18.8. The fundamental steps that make up basic CGI database programs.

Developing Web Database Applications Using MS SQL Server and the Visual Basic DB-Library API

In this section, you will explore a method of developing Web database Windows CGI applications using Visual Basic. Specifically, you will examine all the fundamentals required to begin developing Web database applications that use Microsoft's SQL Server 6.5 database. In doing so, you will be introduced to the Microsoft DB-Library Visual Basic API for SQL Server.

RESOURCE: You can download a 120-day free evaluation copy of SQL Server from the free software link on the main page at the Microsoft Website at

http://www.microsoft.com/
This evaluation version is a full copy of the SQL Server 6.5 product and includes the DB-Library API for developing VB applications. Note that the main setup program included with the downloadable version does not perform a full installation of all components of the product. You must manually execute the setup programs under the following directories to install ODBC, Programmer's Toolkit (PTK) and other VBSQL sample and help components, respectively. These directory paths are all relative from the temporary installation directory created by the main setup program:
/I386/ODBC/Setup.exe
/PTK/I386/Setup.exe
/PTK/Vbsql/Setup.exe
When installing the PTK components, make certain that if you are running IIS on your system you first shut down the server before running the setup program. Otherwise, the PTK installation will hang occasionally.

NOTE: Examples and code snippets for this chapter were developed using VB 5 and SQL Server 6.5; however, the API is designed to work with SQL Server 6.0 and VB 4 as well. Some minor differences in programmatic syntax need to be accounted for. See "SQL Server Books Online," the online reference documentation that comes with SQL Server, for details on developing VB applications for Version 6.0.

What Is the Microsoft DB-Library Visual API?

Microsoft DB-Library is a library of functions that enables very sophisticated client/server applications to be developed for SQL Server. The VB API is a set of Visual Basic functions and routines that lets your application (in this case, your CGI program) interact directly with Microsoft SQL Server. Functionality in the VB API represents a subset of the functionality available in the C language API.

As you will see in subsequent sections of this chapter, this API enables you to develop CGI programs that act as clients to SQL Server databases. The DB-Library API provides these database client programs with the capability to make multiple connections to SQL Server databases, send very complex Transact-SQL queries to the databases, and retrieve results of those queries. The interface also provides the mechanisms necessary to use more sophisticated SQL Server capabilities, such as stored procedures, triggers, and cursors.

By using the VB API, SQL Server now becomes an integral part of your Visual Basic CGI programs and enables you to perform virtually any database operation supported by SQL Server on behalf of Web clients submitting requests from their browsers. CGI database applications can include standard database operations such as these:

Retrieving and updating values from a database directly
Manipulating database values from database tables
Inserting program values into a database
Moving data between SQL Server and an operating-system file

CGI database applications also can perform more complex operations, such as these:

Connecting to multiple, geographically distributed SQL Server databases
Creating new databases
Adding and populating tables
Working with images
Performing bulk copy (BCP) tasks
Administering SQL Server remotely via a Web browser interface

DB-Library Files Required for Visual Basic Applications

Several files are necessary for developing applications using the VB API; these are summarized in Table 18.3. Note that many of these files are not included with DB-Library for Visual Basic; therefore, you need to make certain that they already exist on your system.

Table 18.3. Files Required for Developing Visual Basic DB-Library Applications.

Filename Description Included with DB-Library?

VBSQL.OCX OLE custom control for DB-Library for Visual Basic. Contains all library functions required to develop client applications. Located in the \PTK\I386 directory under the temporary installation directory used by the main setup program when SQL Server was installed. Yes

VBSQL.BAS An include file that contains all DB-Library Visual Basic function androutine declarations. Located in the \MSSQL\DBLIB\INCLUDE directory of the SQL Server installation on your system. Yes

MFC40.DLL A current version of the MicrosoftFoundation Class (MFC). Yes

NTWDBLIB.DLL The DB-Library for Win32 (Windows 95 and Windows NT). No, but included with SQL Server

MFCANS32.DLL The MFC ANSI to Unicode Translation layer. No

MSVCRT40.DLL The Microsoft Visual C Runtime Library. No, but included with SQL Server

Creating a VB 5 Project Using DB-Library

To create a VB 5 project that uses the DB-Library Visual Basic API, you should follow these five basic steps:

1. Create a new Visual Basic project.

2. Add the DB-Library for the Visual Basic header file to your project.

3. Add the DB-Library for Visual Basic OLE custom control to your toolbox.

4. Add the DB-Library for Visual Basic OLE custom control to a form in your project.

5. Add code to the error-handling and message-handling event procedures.

Adding the VBSQL.BAS DB-Library Header File to the Project To add the VBSQL.BAS DB-Library OLE header file to the project, perform these steps:

1. Choose Project | Add File. The Add File dialog box appears.

2. Select the VBSQL.BAS file. This file is located in the \MSSQL\DBLIB\INCLUDE directory of the SQL Server installation on your system.

3. Click OK. The Project window now includes the VBSQL.BAS module. Visual Basic automatically generates a unique name for this module. For new projects, the default name for this module is Module1. It is recommended that you not make changes to the VBSQL.BAS header file.

Adding the VBSQL.OCX DB-Library OLE Custom Control to the Toolbox To add the VBSQL.OCX DB-Library OLE custom control to the project toolbox, follow these steps:

1. Choose Project | Components. The Components dialog box appears, as shown in Figure 18.9.

FIGURE 18.9.The components dialog box.

2. Click Browse. The Add ActiveX Control dialog box appears, as shown in Figure 18.10.

FIGURE 18.10. The Add ActiveX Control dialog box.

3. Search for and select the VBSQL.OCX file. The evaluation copy of SQL Server 6.5 is located in the \PTK\I386 directory under the temporary installation directory used by the main setup program when SQL Server was installed.

4. Click Open. The Available Controls list now includes the Vbsql OLE Custom Control module, as shown in Figure 18.11.

5. Select the control and click OK. The DB-Library for Visual Basic OLE custom control now is included in the toolbox. Its associated tool tip is Vbsql, as shown in Figure 18.12.

FIGURE 18.11. Adding the VBSQL.OCX custom control to the project toolbox.

Adding the VBSQL.OCX DB-Library OLE Custom Control to a Form in the Project To add the VBSQL.OCX DB-Library OLE custom control to a form in the project, perform the following steps. Do not add a control to more than one form.

1. From the toolbox, select the DB-Library for Visual Basic OLE custom control, as shown in Figure 18.13.

FIGURE 18.12.The VBSQL.OCX control has been added to the project toolbox.

FIGURE 18.13. Selecting the VBSQL.OCX control.

2. Move the pointer onto a form in the project. At this point, the cursor becomes a cross-hair.

3. Drag the crosshair cursor to place the control on the form, as shown in Figure 18.14. The control will be called Vbsql1 by default.

4. Set the Visible property of the control to False, as shown in Figure 18.14.

FIGURE 18.14. Adding the VBSQL.OCX control to a form in the project.

NOTE: It is only necessary to add a single VBSQL.OCX control to a single form in the project. This form always should be loaded using the LOAD method (typically, as an initial operation in your application) so that the DB-Library functions are available. Do not use the UNLOAD method to unload the form containing the control until all operations within the program requiring SQL Server have been fully completed and the connections to the databases in use are closed. Also note that it is not necessary to invoke the SHOW method for the form containing the VBSQL.OCX control because it never needs to be displayed visually.

Adding Error-Handling and Message-Handling Event Procedures The VBSQL.OCX custom control has two event procedures that can be used within your applications:

Sub Vbsql1_Error: Used when DB-Library error events occur.
Sub Vbsql1_Message: Used when SQL Server messages are received.

Use of these handlers is not required, but it is recommended that any complex database program include at least some nominal form of these error and message handlers.

To edit these error-handling and message-handling event procedures in your project, perform these steps:

1. Double-click the DB-Library for Visual Basic OLE custom control on the form in the project. The Visual Basic code appears.

2. From the Procedure box, select Error to bring up the DB-Library error-handler event procedure. Modify this procedure as appropriate for your application.

3. From the Procedure box, select Message to bring up the SQL Server message-handler event procedure. Modify this procedure as appropriate for your application.

Note that Visual Basic creates a VBSQL.OCA file (usually in the same directory as VBSQL.OCX). This file is used to store cached-type library information and other data specific to the custom control and to re-create it as needed.

A Framework for DB-Library Applications Using Visual Basic

Nearly all DB-Library applications perform a common set of operations or steps:

1. Provide an error handler.

2. Initialize DB-Library for Visual Basic.

3. Open a connection and log on to SQL Server.

4. Send Transact-SQL statements to SQL Server.

5. Process the results of SQL statements sent to SQL Server.

6. Close the connection(s) to SQL Server and exit the application.

The following sections briefly describe each of these steps:

NOTE: A complete reference and descriptions of all DB-Library functions (including those referenced in subsequent sections of this chapter) are available in "SQL Server Books Online," the online reference documentation delivered with SQL Server.

Providing Error Handlers As previously mentioned, the VBSQL.OCX control provides the error-handling interface for your application. When added to the primary form in an application, it installs two error-handler event procedures, assigns them the default names VBSQL1_Error and VBSQL1_Message, and supplies the appropriate parameter list as part of each procedure name. Note that only one error handler control can be placed in an application.

After error handlers are added to a form, implementing them is as easy as adding the required code to perform whatever specialized error handling is appropriate for your application. Initializing DB-Library Before calling any DB-Library functions or performing any operations with SQL Server, your application must initialize the DB-Library for use. A special function, SqlInit, is provided for this task. SqlInit takes no parameters and returns the DB-Library version number as a string. Note that the program's error handlers should be available before SqlInit is called. The following code snippet demonstrates the use of this function:

IF SqlInit() = "" THEN
   PRINT "DB-Lib was not properly initialized"
   ... other error-handling code ...
END IF

Connecting and Logging On to SQL Server After the application has been initialized, it interacts with SQL Server by opening one or more SQL Server connections (using appropriate user logon information). Client applications use connections to send SQL queries and other statements to SQL Server. The connection also serves as a means for receiving the results of statements sent to SQL Server.

Opening a connection to SQL Server requires using the SqlLogin, SqlSetLUser, SqlSetLPwd, SqlSetLApp, and SqlOpen functions provided by DB-Library. SqlLogin allocates a SQL Server logon record and returns an identifier for that logon record initially filled with attributes assigned default values. The logon record is made up of a set of attributes that are initially assigned default values. SqlSetLUser and SqlSetLPwd set the username and password logon record attributes that will be used to log onto SQL Server. SqlSetLApp sets the application name that appears in the SQL Server sysprocesses system table. Finally, SqlOpen logs onto SQL Server using the SqlLogin logon record, establishes a connection, and returns a connection identifier that remains associated with the connection for as long as the connection remains open. SqlOpen can be used to open multiple connections to SQL Server, thus providing the potential to develop very complex applications. The following snippet of code demonstrates the use of these functions:

Login = SqlLogin()
Result = SqlSetLUser(Login, "Guest")
Result = SqlSetLPwd(Login, "c0rnd0gs")
Result = SqlSetLApp(Login, "webguest_pubs")
SqlConn = SqlOpen(Login, "OMNISCIENT")

As an alternative, DB-Library also provides a convenience function called SqlOpenConnection, which combines the work of several lower-level functions into a single function. This function provides a more convenient method for logging on to SQL Server and opening a connection in one step, thus reducing the amount of code needed to log on as a user and open a connection. Sending SQL Statements to SQL Server After an application successfully establishes a connection to SQL Server, it can begin the task of doing some real work. DB-Library provides two functions--SqlCmd and SqlExec-- for sending Transact-SQL statements to SQL Server for execution. SqlCmd is used to buffer commands to be sent to SQL Server. It can be called several times in succession to append commands to contents already in the buffer (just be certain that blank spaces are provided where appropriate). Additionally, several SQL statements may be buffered to be sent all at once.

After the command buffer is filled with SQL statements to send, SqlExec actually sends the buffer contents to SQL Server. The following snippet of code demonstrates how these functions can be used to obtain two columns of data from the pubs demonstration database delivered with SQL Server. Note the use of the SqlConn data structure returned from a previous call to SqlOpen:

Result = SqlCmd(SqlConn, "SELECT au_lname, city")
Result = SqlCmd(SqlConn, " FROM pubs..authors")
Result = SqlCmd(SqlConn, " WHERE state = `MD'")
Result = SqlExec(SqlConn)

As an alternative, DB-Library also provides the functions SqlSend and SqlOk, which can be used in place of SqlExec. Processing SQL Server Results After SQL statements are sent to SQL Server for execution, it is necessary to retrieve the results of those statements (and optionally perform some application-specific processing on the data). DB-Library provides two functions--SqlResults and SqlNextRow--for retrieving the results of Transact-SQL statements.

SqlResults sets up the results of a SQL statement for processing. It is called once for each SQL statement that was previously sent in the command buffer and should return the value SUCCEED. Additionally, it is called a final time to return the NOMORERESULTS indicator (which signifies that there are no more results for the current SQL statement being processed).

After SqlResults returns SUCCEED (when results of the current SQL statement being processed become available), SqlNextRow is called in succession (until it returns NOMOREROWS) to retrieve rows of data returned. Note that, unless row buffering has been enabled, the processing of each row returned must be performed after SqlNextRow returns SUCCEED and before the next call to SqlNextRow. The following snippet of code demonstrates the use of these functions:

Result = SqlResults(Sqlconn)

IF Result = SUCCEED THEN

DO UNTIL SqlNextRow(Sqlconn) = NOMOREROWS
      Name = SqlData(Sqlconn, 1)
      City = SqlData(Sqlconn, 2)
      ... perform additional application-specific processing here ...
LOOP
END IF

NOTE: Transact-SQL statements return two types of result rows: regular rows and compute rows. Regular rows are generated from columns in a Transact-SQL SELECT statement, whereas compute rows are generated from columns in a COMPUTE clause. These two types of rows contain very different data; therefore, applications must process them separately. DB-Library provides the functions SqlData and SqlAData for processing regular and compute column results, respectively. For more details, see "SQL Server Books Online," the online documentation that comes with SQL Server.

Closing SQL Server Connections and Exiting the Application After the SQL Server client application performs its work, it must close any open connections to SQL Server, free memory resources, and exit in a clean manner. DB-Library provides the SqlExit and SqlWinExit functions to perform these tasks. SqlExit closes all SQL Server connections opened by an application and also frees system memory associated with each connection. (Note that single, specific connections can be closed by using the SqlClose function.) SqlWinExit is used to free all memory allocated to a Windows application by DB-Library. The following snippet of code illustrates how these functions are used in response to a user clicking a button on a form in an application:

SUB AppQuit_Click()
   SqlExit
   SqlWinExit
END SUB

Developing a VB CGI Application Using DB-Library and the CGI32.BAS Framework

This section ties together much of the material of previous sections on Windows CGI and the SQL Server DB-Library API for Visual Basic. It presents a basic template for CGI applications that integrates these tools, provides a few rules, and finally presents a simple Windows CGI application that demonstrates how all these are tied together.

TIP: Before implementing any Web database applications, consult your DBA and Webmaster about system and database access privileges of guest accounts set up for Web database applications. This step is important to ensure that the integrity of your database is maintained and that users are accorded the appropriate select, add, modify, and delete privileges consistent with your site's policies. Additionally, strongly consider the use of client-side VB scripts as well as subroutines in your CGI programs to validate user input and ensure that sensible SQL queries will be formulated, especially in those cases where users are allowed to insert or modify data in the database.

CGI_Main and the DB-Library Earlier in this chapter, you learned that the CGI32.BAS framework for Visual Basic requires a CGI_Main() procedure, and that it is within this procedure that most of the work of your CGI application is done. Therefore, it stands to reason that any database work using the DB- Library API also must be performed from within this procedure. The following code shows a generalized template of a CGI_Main() procedure that uses the DB-Library to perform some database processing on behalf of a Web client. Notice the close correspondence with the general flowchart for CGI database programs in Figure 18.8.

Sub CGI_Main()
       Load the form containing the VBSQL.OCX control
       Initialize DB-Library using SqlInit
       Use CGI32.BAS functions to obtain form data submitted with the request
       Open a Connection and login using SqlSetLUser, SqlSetLPwd, SqlSetLApp,                 ÂSqlOpen
       Formulate SQL statements using form data submitted with the request
       Add SQL statements to the command buffer using SqlCmd
       Send the command buffer using SqlExec
       Retrieve and process returned results using SqlResults, SqlNextRow and        ÂSqlData
       Perform any (optional) application-specific processing of results data 
       Â(using your own code, third-party software libraries, stats packages, etc.)
       Format results for presentation to the client using Send (output can include
       ÂHTML, ActiveX content as well as VBScript)
       Clean up connections and memory resources using SqlExit and SqlWinExit
       Unload the form containing the VBSQL.OCX control
       Return to CGI32.BAS Sub Main()
End Sub

In addition to previously presented guidelines for developing applications using CGI32.BAS, the following general rules apply to Windows CGI applications using DB-Library:

Load the form containing the VBSQL.OCX control, but do not Show it. The form only serves as a container for the control, and loading it enables the rest of the application to access the DB-Library functions.
Perform SqlExit and SqlWinExit before unloading the form.
Be sure to Unload the form containing the VBSQL.OCX control from within the CGI_Main() routine before the control is returned to the CGI32.BAS framework. Failure to do so will cause your CGI application to hang.
Do not call the End statement. Unload is the preferred (and safe) method for closing the main form.

Putting It All Together: A Sample Windows CGI Database Application

This section presents a simple Web database query example that ties together many of the concepts presented in this chapter. This example illustrates how user input to an HTML form can be used by a Windows CGI Visual Basic 5.0 program to formulate a query, send the query to a Microsoft SQL Server database, fetch results of the query, and very simply present the results to the user (Web client). The example uses the CGI32.BAS framework, the SQL Server DB-Library API for Visual Basic, and the sample pubs database that comes with SQL Server. The pubs Simple Query Form The HTML form shown in Figure 18.15 is used to accept user input for simple queries against the SQL Server pubs database. Listing 18.4 shows the HTML code for this form.

FIGURE 18.15. A form for the simple SQL Server pubs database query.

Listing 18.4. HTML Code for the Pubs Database Query Form Shown in Figure 18.15.

<html>
<head>
<meta name="GENERATOR" content="Microsoft FrontPage 2.0">
<title>SQL Server DB-Library Example</title>
<!-- pubs database query form -->
<!-- Author:  Drew Kittel -->
</head>

<body bgcolor="#FFFFFF">

<h2 align="center"><font color="#0000FF">SQL Server DB-Library
CGI Example<br>
</font><font color="#0000FF" size="4">Pubs Database Query </font></h2>

<hr>

<form action="http://dkittel.clark.net:8080/cgi-win/pubs_dblib_query.exe"
Âmethod="POST">
<p><b>What type of book(s) are you interested in?</b>
<em>(Check all that apply)</em> </p>
<p><input type="checkbox" name="btype0" value="wwwdbdev">
   <strong>WWW Database Development </strong><br>
<input type="checkbox" name="btype1" value="mod_cook">
   <strong>Modern Cooking</strong> <br>
<input type="checkbox" name="btype2" value="trad_cook">
   <strong>Traditional Cooking</strong> <br>
<input type="checkbox" name="btype3" value="popular_comp">
   <strong>Computing</strong> <br>
<input type="checkbox" name="btype4" value="business">
   <strong>General Business</strong> <br>
<input type="checkbox" name="btype5" value="psychology">
   <strong>Pop Psychology</strong> <br>
</p>
<p><b>How much can you spend per book?:</b>
  $<input type="text" size="6" name="uprice"> </p>
<p><input type="submit" value="Start the Search">
   <input type="reset" value="Clear Entries"> </p>
</form>
</body>
</html>

This form includes checkboxes that enable the user to specify the type of book(s) in which he is interested and a text input box in which he can enter the maximum price (per book) he is willing to spend. Note the values of the name attributes associated with these user-input objects; you will see these in the Windows CGI program that processes this form and handles the database query.

After the user clicks the Start the Search button, the form inputs are submitted to the server. The METHOD and ACTION attributes assigned to this form specify the CGI program to be used by the server and the method by which user input is passed. In this case, the CGI program that will process the user input is called pubs_dblib_query.exe, and the POST method is used. This means that the CGI program will need to read decoded form data (in key=value format) from the [Form Literal] section of the profile (.INI) file. The pubs Query CGI Database Program The program used to handle the simple pubs database query is a VB 5 program that uses the DB-Library VB API and the CGI32.BAS framework, both of which were covered in detail earlier in this chapter. Because the CGI32.BAS framework does all the work of decoding form data submitted using the POST method, the only thing the CGI developer needs to know at this point is the names of the form-input objects (the checkboxes and text-input field). The values associated with these input widgets now can be obtained directly from the profile file using the GetSmallField() and FieldPresent() functions provided by the CGI32.BAS framework.

Listing 18.5 shows the code for the entire CGI program. As you look through the code and explanations that follow it, note how each of the steps specified in the flowchart in Figure 18.8 is implemented.

Listing 18.5. VB Code for the pubs_dblib_query.exe Windows CGI Program Used to Process Input to the Form Shown in Figure 18.15.

Attribute VB_Name = "PUBS_DBLIB_QUERY"

Option Explicit
    Dim Login
    Dim Result
    Dim SqlConn
    Dim Title
    Dim Price

    Dim Clauses As String

    `Form field name variables
    Dim btype0 As String
    Dim btype1 As String
    Dim btype2 As String
    Dim btype3 As String
    Dim btype4 As String
    Dim btype5 As String
    Dim uprice


Sub CGI_Main()

    `Load (but don't show) the form containing the
    `VBSQL.OCX control

    Load Form1

    SendHTMLStart

    Send ("<HTML>")
    Send ("<HEAD><TITLE>SQL Server Query Results</TITLE></HEAD>")

    Send ("<body bgcolor=#FFFFFF>")
    Send ("<CENTER>")
    Send ("<H2><font color=#0000FF>SQL SERVER Query Results</font></H2>")
    Send ("</CENTER>")
    Send ("<HR>")

    `Process form inputs and format the clauses portion
    `of SQL query

    Clauses = "type in ("

    If FieldPresent("btype0") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype0") & Chr(34) & ", "
    End If
    If FieldPresent("btype1") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype1") & Chr(34) & ", "
    End If
    If FieldPresent("btype2") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype2") & Chr(34) & ", "
    End If
    If FieldPresent("btype3") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype3") & Chr(34) & ", "
    End If
    If FieldPresent("btype4") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype4") & Chr(34) & ", "
    End If
    If FieldPresent("btype5") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype5") & Chr(34) & ", "
    End If

    Clauses = Clauses & Chr(34) & Chr(34) & ") AND price < " &
         ÂGetSmallField("uprice")


    `Initialize DB-Library
    If SqlInit() = "" Then
      Send ("<EM><B>The program encountered an error</B></EM><BR>")
      Send ("<EM><B>DB-Library was not properly initialized</B></EM>")
    Else
    `Get a Login record and set login attributes.
        Login = SqlLogin()
        Result = SqlSetLUser(Login, "Guest")
        Result = SqlSetLPwd(Login, "c0rnd0gs")
        Result = SqlSetLApp(Login, "webguest_pubs")

    `Get a connection for communicating with SQL Server.
        SqlConn = SqlOpen(Login, "OMNISCIENT")

    `Put the command into the command buffer.
        Result = SqlCmd(SqlConn, "SELECT title, price")
        Result = SqlCmd(SqlConn, " FROM pubs..titles")
        Result = SqlCmd(SqlConn, " WHERE " & Clauses)

    `Send the command to SQL Server and start execution.
        Result = SqlExec(SqlConn)

    `Process the command.
    `Retrieve result rows, format lightly and send to client.

        Result = SqlResults(SqlConn)
        If Result = SUCCEED Then
            Send ("<PRE>")
            Do Until SqlNextRow(SqlConn) = NOMOREROWS
                Title = SqlData(SqlConn, 1)
                Price = SqlData(SqlConn, 2)

            ` ... other application-specific processing here ...

                Send ("<EM>" & Title & "  " & Price & "</EM>")
            Loop
           Send ("</PRE>")

        End If

    `Close connection and exit program.
        SqlExit
        SqlWinExit

    End If

    `Unload the  form containing VBSQL.OCX control

    Unload Form1

    `Now return to complete HTTP
    Send ("</BODY></HTML>")

End Sub

Sub Inter_Main()
    MsgBox "This is a Windows CGI program"
End Sub

Sub SendHTMLStart()
    Send ("Content-type: text/html")
    Send ("")
End Sub

The output generated by the program in response to the query shown in Figure 18.15 is shown in Figure 18.16.

FIGURE 18.16. Results of the simple query shown in Figure 18.15.

The program begins with a few variable declarations followed by a call to the SendHTMLStart procedure. This procedure simply sends an HTML Content-type header to the server for inclusion in the response package to be sent to the client. Following this, the standard <HTML>, <BODY>, <TITLE>, and <HEAD> tags used to begin an HTML document are sent using the CGI32.BAS Send() function. Note that the page also is made a bit more aesthetically pleasing by setting the background color and the color of the font used for heading text.

When the form in Figure 18.15 is submitted (using the POST method, in this case), the Web server decodes the form data and creates the request package, as described earlier in this chapter. At this point, the data is readily available to the program by using the CGI32.BAS functions FieldPresent() (to first check whether data for each specific checkbox in the field was sent) and then GetSmallField() (which retrieves the value associated with the key--the name of the form field). For example, this code first checks to see whether data for the WWW Database Development checkbox in Figure 18.15 exists (that is, whether the user enabled the checkbox):

If FieldPresent("btype0") Then
        Clauses = Clauses & Chr(34) & GetSmallField("btype0") & Chr(34) & ", "
End If

If this data does exist, the Clauses string is appended with the current content of the string, a quotation mark (") (using the Chr(34) function, the value of the field as retrieved by the GetSmallField function), another quotation mark ("), and, finally, a comma (,) character. This sequence is repeated for each checkbox in the form to determine whether it was selected. The value of the price input field also is determined in this manner and is used to append the Clauses string with a < price condition. For the user input shown in Figure 18.15, the result of these operations is a Clauses string of

type in ("wwwdbdev", "popular_comp", "business", "") AND price < 59.95

Now that the user's form input has been processed, the program sets about the work of initializing the DB-Library, getting a connection, and logging on. Note that a Guest account has been established on the SQL Server. This account has limited privileges (essentially, read-only) to the pubs database that is to be queried. After a connection is successfully established, three calls to the SqlCmd function formulate a SQL statement to send to the pubs database. Note that, although the bulk of the query could be hard-coded, you still can support ad-hoc user queries by appending the Clauses string that was formulated by using form data from the user. For example, the SQL statement created and sent to the server as a result of input in Figure 18.15 follows:

SELECT title, price
FROM pubs..titles
WHERE type in ("wwwdbdev", "popular_comp", "business", "") AND price < 59.95

After the query is sent to the server and executed, the SqlResults, SqlNextRow, and SqlData DB-Library functions are used within a loop to retrieve each row returned from the server. Within this loop, a minimal amount of formatting is performed by using the HTML <PRE> tag. This enables you to simply send each record of data returned from the query back to the requesting client in the form of preformatted text. Note that, with a little more work and imagination, the output generated dynamically at this stage of the program could include tables complete with headers, various background colors, and fonts. You could even develop your own set of VB functions that use the Send() function and <TABLE> tags to support creation and population of various types of HTML tables to be returned to the client.

After all data from the server is processed, formatted, and sent to the server for inclusion in the response package, the SqlExit and SqlWinExit calls are made to clean up before finally unloading the form that contains the VBSQL.OCX control. The program then sends the closing </BODY> and </HTML> tags before returning to the Sub Main program in the CGI32.BAS framework.

RESOURCE: As you have seen in this chapter, Windows CGI provides a very powerful method for developing Web database applications. In addition to providing an interface to SQL Server databases via the DB-Library API, Windows CGI can be used to interact with ODBC databases using Visual Basic data-access objects (DAOs). The following site presents a chapter from Using CGI, Special Edition, published by Que Corporation, that illustrates in great detail how you can accomplish this task:

http://www.mcp.com/37064500504674/que/et/se_cgi/Cgi21fi.htm

Summary

This chapter presented an in-depth overview of CGI and what it enables Web application developers to do. It emphasized that the primary strength of CGI is that you can use it to expand the functionality of the Web server and then make that functionality accessible to Web clients. You saw that CGI programs invoked via HTML forms give users access to databases, resources, and applications that previously were not accessible.

You then took a brief look at the CGI data-flow process and how data moves from a Web client to the server and then to the CGI program. You learned how results from a CGI program follow this path back to the requesting client. You then delved into CGI input and output, examining the various methods to move client data to the server and on to the CGI program. CGI environment variables were introduced, the differences between GET and POST methods were outlined, and URL encoding was explained in depth.

The remainder of this chapter focused on using CGI in a Windows development environment. You explored using the Windows CGI and took a long look at how CGI32.BAS simplifies the task of developing Windows CGI Visual Basic. Next, you discovered how you can use Windows CGI with the Microsoft SQL Server DB-Library VB API to create Web database applications to perform a variety of complex operations on behalf of Web clients. Finally, the chapter pulled these concepts together by presenting an example showing how to put these concepts into practice.

*Variable*	*Description*	*Data Type*
The following variables hold server information
`CGI_GMTOffset`	Number of seconds from Greenwich Mean Time (GMT).	V
`CGI_ServerAdmin`	Server administrator's e-mail address.	S
`CGI_ServerSoftware`	Name and version of Web server software (for example, WebSite/1.1f).	S
`CGI_Version`	CGI version this server complies with (for example, CGI/1.2).	S
The following variables hold browser and user information
`CGI_AcceptTypes`	MIME data types accepted by the browser.	T
`CGI_From`	E-mail address of the user. This rarely is supplied by the user's browser.	S
`CGI_NumAcceptTypes`	Number of MIME data types accepted by the browser.	I
`CGI_Referer`	URL that referred to the CGI program.	S
`CGI_RemoteAddr`	IP address for the remote host running the browser.	S
`CGI_RemoteHost`	Hostname for the remote host running the browser.	S
`CGI_RequestProtocol`	Name and revision of protocol used (for example, HTTP/1.0).	S
The following variables hold information about executable, logical, and physical paths
`CGI_ContentFile`	Full path to a file that contains any attached data (for example, `POST` data).	S
`CGI_ContentLength`	Length (in bytes) of attached `ContentFile` data.	L
`CGI_ContentType`	MIME content types of request with attached data (for example, `POST` data).	S
`CGI_ExecutablePath`	Path to the CGI program being run.	S
`CGI_FormTuples`	name`=`value pairs sent by the form (if any).	T
`CGI_HugeTuples`	Large name`=`value pairs.	HT
`CGI_LogicalPath`	Logical or extra path information.	S
`CGI_NumFormTuples`	Number of name`=`value pairs sent by the form (if any).	I
`CGI_NumHugeTuples`	Number of large name`=`value pairs.	I
`CGI_PhysicalPath`	Physical path--the translated version of the logical path in `CGI_LogicalPath`.	S
`CGI_QueryString`	Encoded portion of the URL appended after the ? character. This contains `GET` data or the query string (if it exists).	S
`CGI_RequestMethod`	Request method (for example, `GET` or `POST`).	S
`CGI_ServerName`	Server hostname for the request (this can vary in multihomed configurations).	S
`CGI_ServerPort`	Port number associated with the request.	I
The following variables hold information about security
`CGI_AuthPass`	Authorized user's password (only if enabled).	S
`CGI_AuthRealm`	Authorized user's realm.	S
`CGI_AuthType`	Authorization method.	S
`CGI_AuthUser`	Authorized user's name.	S
The following variables hold other miscellaneous information
`CGI_DebugMode`	CGI tracing flag from the server.	I
`CGI_ExtraHeaders`	Extra header information supplied by the browser.	T
`CGI_NumExtraHeaders`	Number of extra headers supplied.	I
`CGI_OutputFile`	Full path to the file in which the Web server expects the CGI program's response.	S

*Function*	*Description*	*Return Type*
`ErrorHandler()`	Global exception handler.	N/A
`FieldPresent()`	Tests for the presence of a form field	T/F
	(especially useful to determine whether
	checkboxes on a form were checked).
`GetSmallField()`	Retrieves the contents (for example,	S
	a value from a name`=`value pair) of a
	named form field.
`PlusToSpace()`	Removes plus sign (+) delimiting	N/A
	characters from a string. The + signs
	are converted to spaces.
`Send()`	Writes a string into the output spool	N/A
	file. This function is used heavily for
	formatting HTML output to the client.
`SendNoOp()`	Sends a complete response that causes the	N/A
	browser to do nothing and to stay on its
	current page.
`Unescape()`	Removes URL-escaping from a string;	S
	returns a modified string.
`WebDate()`	Returns a Web-compliant date and time	S
	string (in GMT).

*Filename*	*Description*	*Included with DB-Library?*
`VBSQL.OCX`	OLE custom control for DB-Library for Visual Basic. Contains all library functions required to develop client applications. Located in the `\PTK\I386` directory under the temporary installation directory used by the main setup program when SQL Server was installed.	Yes
`VBSQL.BAS`	An include file that contains all DB-Library Visual Basic function androutine declarations. Located in the `\MSSQL\DBLIB\INCLUDE` directory of the SQL Server installation on your system.	Yes
`MFC40.DLL`	A current version of the MicrosoftFoundation Class (MFC).	Yes
`NTWDBLIB.DLL`	The DB-Library for Win32 (Windows 95 and Windows NT).	No, but included with SQL Server
`MFCANS32.DLL`	The MFC ANSI to Unicode Translation layer.	No
`MSVCRT40.DLL`	The Microsoft Visual C Runtime Library.	No, but included with SQL Server