Understanding How htmSQL Works: The Technical View

htmSQL is a CGI program that is written in the C language and resides on your Web server. It can process special directives that are embedded in an HTML file. These directives describe one or more SQL statements and incorporate formatted results into the Web page that is created by the HTML file.

htmSQL passes your SQL to a SAS/SHARE server, performs the requested updates and queries, and retrieves the results sets. The desired page is created dynamically and returned through the Web server to the browser. See the data flow and required components in the following diagram:

[htmSQL diagram]

The Web server calls htmSQL each time it receives a URL that specifies the htmSQL program name. htmSQL supports both the GET and POST CGI methods for sending form data.

htmSQL reads the input file for information that is contained within any of its directives. It processes this information and returns the results to the Web server. It returns all HTML information to the Web server just as it appears in the input file.

The following example illustrates a URL for htmSQL:

http://yourserver/dir/executable_file/filename.hsql?query_string

yourserver is your Web server host name (and port, if required).
dir is the path of the Web server CGI program directory that contains htmSQL.
executable_file is the htmSQL program name. For UNIX and z/OS, the program name is htmSQL. For Windows, the program name is htmSQL.exe.
filename.hsql is your htmSQL input file. Each file can contain multiple SQL statements and also can include other input files by reference.
query_string specifies values for one or more of the variables that are referenced in the input file. The variable name and value pairs are separated by ampersands (&) and are specified using the following format:
```
var1=value1&var2=value2&...varN=valueN
```

Note: Some Web servers can be configured to recognize an input file by its file extension and to automatically call the appropriate CGI program to process the file. If your Web server can be configured this way, you can omit the path to htmSQL when you specify the URL (that is, you can omit the dir and executable_file values). Consult your Web server documentation for details on whether and how your server can be so configured.

For more information about CGI and CGI scripting, refer to the Common Gateway Interface. documentation provided by W3C

Processing an htmSQL Input File

htmSQL uses a defined set of processing rules to process the information in the input file:

htmSQL sends all text and characters that are not part of htmSQL (such as HTML tags and newline indicators) to stdout exactly as they occur in the file. If a variable reference appears in this text, htmSQL resolves the reference before sending the text to stdout.
htmSQL collects the information that is contained in the SQL section and sends it to the SAS server as a complete SQL statement that is to be executed. Each variable reference that is in the SQL section is resolved to the current value of the variable. htmSQL ignores newline indicators in the SQL section.
For SQL queries, htmSQL retrieves a row of the results set and writes it to stdout according to the information that is included in the eachrow section. Variable references that correspond to column values are resolved. htmSQL repeats this step for each row in the results set.
For SQL statements that perform update functions, htmSQL processes the success section if the return code is zero and the error section if the return code is not equal to zero.

Note: z/OS is the successor to the OS/390 and MVS operating systems. SAS/IntrNet 9.1 for z/OS is supported on the MVS, OS/390, and z/OS operating systems and, throughout this document, any reference to z/OS also applies to OS/390 and MVS, unless otherwise stated.