User's Guide

Introduction

ODBiC provides an interface between your Web pages and your ODBC databases. (ODBC stands for Open Database Connectivity, a standard for interfacing applications programs to a variety of databases.)

ODBiC may be used as a CGI program to access the data in "real time", or it may be used as a DOS program to generate the Web pages and save them as disk files. (CGI stands for Common Gateway Interface, a standard for interfacing applications programs to the Web.)

The interface to the database is provided by connecting to any ODBC driver. ODBC uses SQL (Structured Query Language) statements to SELECT, INSERT, UPDATE or DELETE the data. Some ODBC drivers support additional SQL statements such as CREATE and ALTER (table definitions) and GRANT (access privileges).

ODBiC provides commands for connecting to the database and executing SQL, but it also provides many other useful commands to facilitate the Web page generation.

This User's Guide will assume that you are already fairly familiar with the HTML (Hypertext Markup Language, which is used to format Web pages) and with SQL. If you are not, there is much information available on the Web, and you will need this information to use ODBiC effectively. For the HTML part, you might consider using one of the specialized Web page editors.

ODBiC reads "template" files as input. The template files generally contain standard HTML commands and text, which are simply copied directly to the output, but they also contain ODBiC commands and variable references. When ODBiC finds a command statement, that statement is executed immediately. Some commands cause text to be inserted into the output at that point. When ODBiC finds a variable reference, the current value of the variable is inserted into the output text.

ODBiC Template Files

ODBiC Variables

A variable is referenced in an ODBiC template file by enclosing a variable name within a pair of dollar signs ($). Variable names must always begin with an alphabetic character, "a" to "z" or "A" to "Z". (This helps to avoid confusion with an actual dollar amount like $100.) After the initial character, variable names may be any uppercase or lowercase alphabetic character, any numeric digit, the underscore character (_), or a space (blank) character.

ODBiC Commands

ODBiC commands are embedded in the template files by using the standard HTML "comment" notation: The start of an HTML comment is marked by the characters "<!--" and the end of the comment is marked by the characters "-->". Browsers ignore these comments, but ODBiC will look for command names beginning immediately after the characters "<!--". NOTE: There must not be any spaces between the "<!--" character sequence and the first character of the command name, so the "<!--" is effectively part of the command name. Command names may be typed in uppercase or lowercase characters.

All command statements must be ended with the "-->" characters to make the entire command a valid HTML comment. Many commands will have additional parameters or processing specifications between the command name and the ending "-->" marker. Some commands will require more specifications than will fit comfortably on one line, so multiply lines are allowed before the ending "-->".

NOTE: Any comments that are not recognized as ODBiC commands are simply copied to the output. If you see that a "command" is being output as a comment instead of being executed, check the spelling and syntax carefully. (Remember to use your browser's "View Source" option to see these unrecognized commands, however, since the browser will not show HTML comments in the normal display.)

For security reasons, ODBiC commands cannot be embedded in variables. That is, only commands that are actually in the template file, before variable text substitution, will be recognized.

<!--DATABASE odbc_connection_string -->

The DATABASE statement specifies the ODBC database connection that will be used for subsequent SQL commands. ODBiC uses the ODBC SQLDriverConnect function to connect to databases, so any of the optional arguments supported by a particular driver are valid in this connection string. Typical arguments are "DSN" (Data Source Name), "DRIVER" (explicit driver specification), "UID" (database-defined user ID), and "PWD" (user password).

A minimal connection string should define the ODBC "Data Source Name" as "DSN=data_source_name" (where "data_source_name" is the actual Data Source Name defined in your ODBC setup; see below). For databases that support security, the connection string should also specify a UID user ID and PWD password.

<!--DEFAULT variable=value [, variable=value, ...] -->

The DEFAULT statement allows you to set values for variables in case they are left empty on a user input form, or for database columns that might not have any values in a given row. If an "empty-valued" variable is used anywhere in the template file and there is a DEFAULT value defined, then the DEFAULT value will be inserted. (Otherwise, of course, the variable's actual value will be inserted.) NOTE: An "empty-valued" variable is one that is not defined by an INPUT field or a database column, or a defined variable that contains no data.

You can give a list of variable=value pairs in the DEFAULT statement. All values are treated as text strings. Quotes around values (e.g. variable="value" or variable='value') are not required unless the value contains a comma (which separates variable specifications in the statement), but you may use quotes if you like. If you don't use any quote marks around a value text string, no leading or trailing spaces for the value will be included. (For example, if the command were "DEFAULT var1 = value one , var2 = value 2 , ...", the actual text values used would be "value one" and "value 2".) If the actual text value contains any double-quote characters ("), you must use single-quotes (') around the string, such as '"value"'.

You can use multiple DEFAULT statements, or you can use a single DEFAULT statement that spans multiple lines (with the "-->" marker after the last variable on the last line).

The EACHROW command marks the beginning of formatting that is to be applied to each result row after a SELECT query. The end of the formatting is marked by the ENDROW command. (The ENDROW is required if you use an EACHROW command.)

You can use any sort of text or HTML commands in the EACHROW formatting, and any reference to a database variable (that is, a result column name prefixed and suffixed with $ signs) will be replaced by the value of that column in the current result row.

The EACHROW command does not need to be immediately after the SQL SELECT statement -- for example, you may output a table header before you begin formatting the results -- but EACHROW always refers to the last SQL SELECT statement that was executed.

As noted previously, there is no "fetch" command. An SQL statement that is a SELECT causes an immediate, automatic fetch of the first result row. Additional "fetches" are automatically performed at the bottom of the EACHROW processing, until there are no more rows. Therefore, if you are selecting a single row from a table, you do not need to use the EACHROW command.

You may use additional SQL statements inside an EACHROW loop, but you can only "nest" SQL statements three (3) levels deep. (This includes the implicit SQL statements generated by the OPTIONLIST, QBE, and SEARCH commands.) An SQL statement inside an EACHROW loop can connect to a different database. If you use an SQL statement inside an EACHROW loop, you can use EACHROW, TABLE, or UPDATEFORM to format the results, if necessary. (If you SELECT a single row from inside an EACHROW loop, you don't need any of those looping commands because the result columns from that row are immediately available as variables, just as with an SQL statement outside an EACHROW loop.)

The EXEC command allows you to run DOS programs and execute system commands. (To run programs, you must specify the complete path to the directory where the executable file is stored.) You may specify any necessary arguments (which, of course, may be ODBiC variables) to the system command or program.

One useful purpose for this command is to execute additional ODBiC processes. For example, in a template file that has made a change to a database, you might execute ODBiC to regenerate a "fixed" page. Using this technique, rather than always querying the database in "real time", can save on document access time.

The FORMAT command allows you to specify special formatting for variables, such as forcing a certain number of decimal places for numbers, adding commas to mark thousands, and adding a dollar sign or pounds sign in front of money amounts. For alphabetic variables, a formatting mask can be used to insert any special characters.

The IF statement allows you to test the current value of variables or arithmetic expressions involving constants and variables, and to generate HTML output or execute ODBiC commands only if the specified conditions are true.

Every occurrence of an IF statement must have a matching ENDIF to mark the end of the conditional processing. You can "nest" IF statements (that is, you can have another IF statement in the conditional part of an IF or ELSE section).

The IFNEW is a special test that can be used to determine if a variable has changed value since the last time it was tested with an IFNEW statement. This statement is primarily intended to be used to test a database column inside an EACHROW formatting specification: If the result rows have been sorted by some "grouping" column value, then this test can be used to do special "master/detail" or "category" formatting whenever that grouping column changes value. (To sort or group the result rows by the desired column or columns, you should include an ORDER BY clause in the SQL SELECT statement.)

For example, the following could be used to list items grouped by category with a category header before each group:

    <!--SQL SELECT category, item_number, description FROM item ORDER BY category -->

    <!--EACHROW-->

    <!--IFNEW category -->

      <H1> $category$ </H1>

    <!--ENDIF-->

    <P> $item_number$ $description$ 

    <!--ENDROW-->

    <!--SQL SELECT * FROM master, detail WHERE master.id = detail.id 

            ORDER BY master.id, detail.order_date -->

    <!--EACHROW-->

    <!--IFNEW id -->

      (... format any data from the master table)

    <!--ENDIF-->

    (... format the data from a single detail row)

    <!--ENDROW-->

Like the IF statement, you can have an ELSE section, and you must have an ENDIF to mark the end of the conditional processing. You may also "nest" IFNEWs and IFs.

You may use multiple IFNEWs if you have several levels of grouping. However, remember that you must include each tested column in the ORDER BY clause, with the "major order" (highest level of grouping) first, and you should test them in that same order. If you use more than one IFNEW test in an EACHROW loop, ODBiC assumes that you are using multi-level grouping and resets all "lower" level variables whenever any "higher" level produces a "new" result. (That is, they will also return a "new" result the next time they are tested, without actually testing the old values. This is to force a "new detail" break when the "master" changes, even if the actual value of the "detail" column happens to be the same as the previous "master".)

The INCLUDE command will read and process the specified file. The INCLUDE file can contain any HTML and ODBiC commands, and it is processed as if the text from that file were "pasted" into the template file at the point of the INCLUDE command. This command allows you to reuse standardized text and formatting in multiple files, and it is most useful for allowing that text to be changed easily without editing multiple files. INCLUDED files may also INCLUDE additional files.

The OPTIONLIST command may be used to generate an HTML form "select option" input field by querying the acceptable values from a database table. Selecting the values from a database table informs the user of the available choices and prevents entering values that are not in the table.

This command should only be used when outputting an HTML form, and it should appear inside the <FORM> declaration.

The HTML commands <SELECT> and <OPTION> are used together to define a data entry field on a form with the acceptable values listed. The user may click on an entry to highlight and select it. The field on the form will either be a single-line box with a "pull-down" menu arrow, or it will be a scrolling window, depending on the optional "size" specification. (Note: Some browsers do not support the "size" option.) The default for "size" is one (1), which produces a "pull-down" menu box, and any number greater than one will create a window that number of lines high. (If the browser supports the "size" specification, the window will show "size" lines, at most, and the window will have a scroll bar if the actual number of available selections exceeds the number of lines displayed.)

The options in the list will be generated by an automatic SQL query, which will be "SELECT DISTINCT column FROM table [WHERE ...] ORDER BY column". All column values from this query will be inserted as <OPTION> values on the form being created.

The generated HTML <SELECT> form field acts like an INPUT variable to the form's ACTION function. In the case of the OPTIONLIST command, the name used for this input variable will be the same as the column name in the OPTIONLIST specification. The value will be the user's highlighted selection. To the ACTION function (e.g, ODBiC processing a second template file), there will simply be an INPUT variable with the same name as the column, just as if the user had typed the value into a standard INPUT field with that name.

Examples:

    <FORM METHOD="post" ACTION="\scripts\odbic.exe\your_dir\template2.htm" >

    Select category: <!--OPTIONLIST 10 Category from Products --> <BR>

    <INPUT TYPE="submit" VALUE="Get Products">

    </FORM>

    <FORM METHOD="post" ACTION="\scripts\odbic.exe\your_dir\template3.htm" >

    Select product: <!--OPTIONLIST 25 Product from Products WHERE Category = '$category$' --> <BR>

    <INPUT TYPE="submit" VALUE="Get Product Description">

    </FORM>

The OUTPUT command specifies that the processed output is to be written into a file rather than writing the output back to the Web browser. The "filename" given in this command must specify the complete file system directory path, and no mapping of the Web server's root directory is recognized. The file will be created if it does not already exist. Note that a temporary file is actually used for writing. The original file, if any, will be deleted only when the program terminates, and the temporary file will be renamed. (This helps to avoid contention for an HTML file that another browser may be reading.)

Note that the OUTPUT command is executed at the point that it is encountered in the template file. Therefore, if you want all output to go into the file, you must make the OUTPUT command the first command in the file.

An OUTPUT command without any file name (i.e., <!--OUTPUT-->) causes output to revert back to the browser. Thus, you can embed an OUTPUT command in the file after writing some browser output, write some data into the file, and use <!--OUTPUT--> to revert back to normal browser output.

The optional APPEND specification means that the current output is to be added to the end of an existing file. (If the file doesn't already exist, however, then one will be created and the output will be "appended" to this empty file.)

The two INSERT options allow you to insert new text somewhere in the middle of an existing file. The insertion point is determined by searching the specified file for the given "marker" text string. For example, you can insert a user's message in a "guestbook" page by putting some unique text string in that page to identify where new messages are to be inserted. The marker might be in an HTML comment, such as <!-- insert_messages_here -->. The command <!--OUTPUT guestbook.htm INSERT BEFORE insert_messages_here --> would copy the existing file up to that marker, then begin inserting any output produced by the template. When a new OUTPUT command is encountered in the template (which causes the current output to be closed), then the full line containing the marker text string is reinserted into the file and the remaining part of the existing file is copied into the new file. Warning: Do not use quotes around the marker text string unless the quotes actually exist in the target string in the file. Also, do not attempt to use "-->" as part of the marker text string; it will be taken as the end of the OUTPUT command.

INSERT AFTER is similar except that the insertion will be immediately after the given text marker. For example, the above description of INSERT BEFORE would cause the guestbook entries to appear in the order that they were entered, but INSERT AFTER would cause the entries to appear in "latest first" order.

REPLACE BETWEEN allows you to replace everything between two markers with new text. The existing file is copied down to and including the first specified marker, and all text from that marker down to the second specified marker is discarded. The template's output is inserted at this point, then all of the text from the second marker to the end of the existing file is copied to the new file. REPLACE BETWEEN will allow you to update a section of a file without disturbing anything else in the file.

OUTPUT commands are not "nested". That is, each occurrence of an OUTPUT command causes any previous output file to be closed, and you may not resume writing to that closed file. (You could, however, use the APPEND option to add to the end of that file.)

The OUTPUT file will be created whether ODBiC is run as a CGI program or as a command line program. If ODBiC is being run as a CGI program and an output file has been specified (either with the OUTPUT command or by passing in a variable named "output"), but no output has been sent to the Web browser, then the page returned to the user will simple say "File created: filename". (If multiple output files have been created, only the last one will be identified to the user.)

Examples:

<!--QBE TABLE=database_table, [SELECT=column(s),] [ORDER=column(s),] [ROWS=number,] numeric_field | 'text_field' [, ...] -->

The QBE command automatically generates and executes a "Query By Example" SQL SELECT statement by examining the specified input fields. This command allows a wide variety of different queries from a single input form. The QBE-generated query statement has two major advantages over direct SELECT statements that you might use in a template file. The first is that the user controls which columns are included in the selection criteria. That is, the input form can have entry fields for all of the database columns for which the user might want to specify query criteria, but the user does not have to specify all of them in a given query. Instead, the user may enter values in any one or in several fields, and only those fields will actually be used in the query.

The second advantage is that the values entered do not need to be tests for an exact match in the database (i.e., "column = value"). Rather, the user can specify a comparison for "not equal", "less than", "greater than", "less than or equal", or "greater than or equal" the given value. The user can also use the flexible SQL comparisons of LIKE or NOT LIKE (i.e., pattern matching), BETWEEN or NOT BETWEEN (given limits), and IN or NOT IN (i.e., contained in a specified list of values).

The WHERE clause in the generated SQL statement will depend on which of the input form fields contain any values. That is, input fields with no user-entered values (and no DEFAULT values!) will not be included in the WHERE clause.

Form input fields that do contain values will be checked to see if they begin with any of the following characters: "=" (equal), ">" (greater than), "<" (less than), ">=" (greater than or equal), "<=" (less than or equal), "<>" or "!=" (not equal). If any of these characters are found at the beginning of the field, then the generated WHERE clause will use the specified comparison operator (instead of "="), between the database column and the input value.

Next, fields are scanned for the presence of a percent character, "%", anywhere in the field. The "%" character is the SQL "wild card" character used in a LIKE comparison: Any occurrence of a "%" character matches zero or more occurrences of any character in the database column. For example, the SQL clause, "WHERE last_name LIKE 'harr%' " might select HARRIS, HARRINGTON, and any other names that begins with "harr". A pattern of "%ton" would select any name ending in with "ton". A pattern of "%ing%" would select any name with "ing" anywhere within the name (including the beginning and the end). If a percent character is found in the input form value, ODBiC will automatically use a LIKE comparison in the WHERE clause. (Note: If you need to select on a value that contains an actual "%" in the database, then you can use the "=" at the beginning of the input value to override the automatic LIKE comparison.)

Next, the entered fields are checked to see if they begin with any of the special SQL comparison operators: LIKE or NOT LIKE (followed by a pattern containing any number of "%" characters; note that the LIKE is not really required if the field contains any "%" character); BETWEEN or NOT BETWEEN (followed by a lower limit, the keyword AND, and an upper limit); or IN or NOT IN (followed by a list of comma-separated values enclosed inside a set of parentheses). (The IN or NOT IN list of values can also be the single-column result rows of a "subquery" SELECT statement.) If any of these operators is found, then it is directly inserted into the WHERE clause instead of an "=" sign. NOTE: When any character-data value (as opposed to a numeric value) is used in any of these special operators, the user must enclose the data inside of single-quote (apostrophe) characters. (This is because the comparison is inserted "as is" into the WHERE clause.)

If none of these special operators is found in the input value, then the SQL WHERE clause will just be "column = value" for that input field.

In the QBE command, the "TABLE=" specification is required. The generated SQL statement will be "SELECT ... FROM" the specified table.

The "SELECT=" specification is optional. If it is given, then only the specified columns will be selected from the table. A single column can be specified as "SELECT=column" and multiple columns can be specified in parentheses, "SELECT=(col_1, col_2, ...)". If the SELECT option is not given, then the generated SQL statement will be "SELECT * FROM" the given database table. ("SELECT * " causes all defined columns to be returned).

The "ORDER=" specification is also optional. If it is given, then the specified columns will be used in an ORDER BY clause. A single column can be specified as "ORDER=column" and multiple columns can be specified in parentheses, "ORDER=(col_1, col_2, ...)". If no ORDER is specified in the QBE command, then no ORDER BY clause will be added to the generated SQL statement, and the query results will be in the table's default order. (The default order is usually by the primary key column, or if there is none, the order that the rows were entered.)

The optional "ROWS=" specification limits the number of rows that will be selected. (The limit is set by using the SQL keyword "TOP".) If no limit is set, then all rows matching the selection criteria will be returned.

The "TABLE=", "SELECT=" and "ORDER=" keywords may be used in the QBE command in any order. Following these specifications, you can specify one or more column names which may be included in the WHERE clause of the SQL SELECT statement. These should match the input variables that you have provided in your query form. As noted above, fields that do not have any current value will not be included in the WHERE clause.

NOTE: There are several special considerations for the column names given in the QBE command:

• When ODBiC generates the SQL SELECT statement, it needs to distinguish between numeric fields and text fields. This is because, in SQL statements, numeric fields are given as plain digits but text fields must be enclosed in single-quotes (apostrophes). Therefore, you MUST tell ODBiC that a field is to be treated as a text field by enclosing its QBE command name in apostrophes. If you fail to do this, the generated SQL statement will not "quote" those values, and the statement will be rejected when it is executed.
• The database QBE column names must be exactly the same as the corresponding input ODBiC variable names. This is because ODBiC uses the field names in the generated SQL statement, and it also gets the current values for those fields from its variable list. Therefore, when you design an HTML form that will provide input variables for a QBE command, make sure that the <INPUT NAME="..." ... > matches the database column name for each field.
• Although ODBiC will expect to find variables for each of the fields, do not put the $ prefixes and suffixes on the field names in the QBE list.

After the QBE command is executed, all the database column values from the specified table will be selected, and you can use the TABLE, EACHROW, or UPDATEFORM commands, just as you would after any SELECT statement.

Example:

    <!--QBE TABLE=Employees, EmployeeID, 'LastName', 'FirstName', 'Title',

       'BirthDate', 'HireDate', 'Address', 'City', 'Region', ReportsTo -->

<!--REDIRECT url -->

The REDIRECT command may be used to "transfer" the client browser to a different URL. The specified URL is the same as that which might be used in an HTML <A HREF="url"> link tag. The specified URL may be a variable or it may have variables embedded in it (e.g., the file name).

NOTE: This command can only "redirect" the client browser if no output has been produced by the current template file. That is, the only things that can appear before the REDIRECT command in the template file are ODBiC commands that do not produce output. For example, you might have an SQL statement to select the URL from a database table, or you might use an IF statement to conditionally execute the REDIRECT command. NOTE: Any spaces before the '<!--" at the beginning of an ODBiC command and any spaces after the "-->" at the end of a command do cause output, since ODBiC cannot determine if these spaces are significant. Therefore, if you use the REDIRECT command, make sure that all preceding ODBiC commands begin in the first column of each line, and that there is a RETURN immediately after the "-->" marker at the end of the command.

If any output has already been produced by the template file before the REDIRECT command is encountered, then ODBiC cannot automatically redirect the user's browser. In such a case, ODBiC will insert an HTML text link to the specified URL instead. This link line will say "Please click here to continue".

The SEARCH command will generate and execute a "keyword search" query on one or more columns in a database table. The "keywords" (which will generally be a form field input by a user) may be one or more terms. The user may combine multiple terms with AND, OR, or NOT (which are not case-sensitive). The default if more than one term is entered is an AND condition; that is, all the given terms must be present somewhere in a table row for that row to be selected. An OR condition between two terms will select a row if either of the given terms is present in the row.

A NOT condition excludes any row from being selected if it does contain the term immediately following the NOT. (To be more precise, the row will be excluded unless the user enters "OR NOT", since the default is an AND NOT condition between the two terms.)

The user may also enter a word phrase enclosed inside double-quotes, "...", and only those rows that contain the whole phrase will be selected. Quoted phrases may be combined, using AND, OR or NOT, with other single terms or quoted phrases.

The generated SQL statement will use the LIKE comparison operator with "wild card" characters (%) on each side of each term. This means that the search will actually be for a substring rather than full words. With most database systems, the search will not be case-sensitive.

In the SEARCH command, you may specify more than one column to be searched; you may give a list of comma-separated column names. In this case, when two search terms are combined with AND, both terms must appear somewhere in the row, but they may appear in different columns. If the NOT specification is given, the term must not appear in any of the columns for the row to be selected.

The SELECT option allows you to specify the columns to be selected by the query. For multiple columns, enclose the list in parentheses separated by commas. (For example, "SELECT=(col_1, col_2, col_3)...") If SELECT is no specified, the query will be "SELECT * " which returns all columns in the TABLE.

ORDER is also optional, and again, multiple columns may be specified by listing them inside parentheses, separated by commas. If ORDER is not specified, the results will be in the table's default order (which is generally by the primary key, of if there is none, then in the order that the rows were entered).

The optional "ROWS=" specification limits the number of rows that will be selected. (The limit is set by using the SQL keyword "TOP".) If no limit is set, then all rows matching the selection criteria will be returned.

Example (where "keywords" is an INPUT variable passed in from an HTML form):

    <!--SEARCH TABLE=Products, KEYWORDS=$keywords$, Heading, Description -->

    Keywords            Rows selected

    plastic widget      Both "plastic" and "widget" in either Heading or Description

    plastic and widget  (Same as above, where "and" is implicit)

    plastic or widget   Either "plastic" or "widget" in either Heading or Description

    widget not plastic  "Widget" in either Heading or Description but "plastic" in neither

    "plastic widget"    Exact phase "plastic widget" in either Heading or Description

<!--SENDMAIL SERVER=smtp_host, FROM=sender, TO=recipient [,SUBJECT=subject] [,MIME] -->

The SENDMAIL command may be used to send e-mail directly from a template. All text produced by the template following the SENDMAIL command, up to an <!--ENDMAIL--> command (which is required), is the body of the e-mail message.

The specifications for SENDMAIL may be given in any order. The SERVER specification must be the Internet host name of an SMTP mail server (which might be your own local host). If your local host does not have an SMTP server running, you will need to know the name of a host that will accept outgoing mail from your host.

The FROM specification can be anything, typically your own e-mail address. It may be in the form of "Real Name <my_email@myhost.com>" (that is, any name followed by an e-mail address inside angle brackets). The FROM e-mail address will be used for any "reply" messages from a recipient, so you should be careful to use a valid e-mail address.

The TO specification may be a single e-mail address or a list of names enclosed in parenthesis, such as "TO=(one@some.com, two@other.com, three@another.com)". If the TO specification is given as a variable (e.g., "TO=$addrlist$"), then the variable may contain a single address or a comma-separated list of recipients.

<!--SET variable = value [, variable = value, ...] -->

<!--SETCOOKIE name=value [,EXPIRES=hours, DOMAIN=domain, PATH=path, SECURE] -->

A "cookie" is simply a named character string that can be sent to the user's browser, and the browser will automatically send it back again when connecting to any URL in the specified domain and path. The primary purpose of cookies is to overcome the fact that, ordinarily, each HTML page or CGI program that a user accesses is a single, isolated transaction, with no knowledge of any previous pages or processes that the user may have used. A cookie is actually similar to an INPUT variable on a form, except that it is automatically available to any CGI process in the given domain. Furthermore, browser's store these cookies on the user's disk for as long as you specify when you set them, so they can still be set when the user returns next week or next year.

The typical usage for a cookie is to assign a user ID that can be used to track the user's actions on your site. But you can store quite a bit of information in cookies and, in effect, make the user's browser a database automatically keyed to that user. Some shopping cart systems, for example, don't actually use a database on the server to track the contents of your cart; it's all stored in temporary cookies in your browser.

The only required parameter for the SETCOOKIE command is the "name=value" specification. The "value" can be a quoted string, but it doesn't need to be quoted unless it contains a comma (which separates parameters to the command). Of course, either the name or the value or any of the other parameters can be ODBiC variables. Technically, the "=value" part is not required: If there is no "=" sign, the browser takes the whole string as the name of the cookie and that cookie will have a null value.

The EXPIRES keyword can be used to specify how long the browser should retain the cookie. This retention period should be given as a number immediately followed by a letter to signify the units: "m" for minutes, "h" for hours, "d" for days, or "y" for years. For example, "EXPIRES = 3d" would mean that the browser should retain the cookie for three days. If you don't specify an EXPIRES time, the default will be no retention. (Actually, browsers don't delete cookies until you exit, so the cookie will always be available for the "current session" at least. A retention of 0 is considered to be a temporary cookie.) Any previously set cookie can be "deleted" in the browser by resetting it with a negative EXPIRES value (that is, "already expired"); "-1y" will do.

The DOMAIN keyword can be used to qualify which URLs the cookie should be sent to. Note that browsers will only send a cookie to a node within the same second-level domain (e.g., "yourdomain.com") as the server that set the cookie. The DOMAIN keyword can only be used to qualify which nodes within that domain should receive the cookie. For example, if ODBiC is running on "nodeA.yourdomain.com" then you might set "DOMAIN=nodeA.yourdomain.com" to insure that the cookie is only sent to nodeA, but not nodeB. If, however, you set "DOMAIN=.yourdomain.com" (note the leading dot), the cookie will be sent to nodeA, nodeB, and any other nodes in that second-level domain. If you don't specify a DOMAIN, the default is that the browser will only send the cookie back to the node that set it.

The PATH specification allows you to further qualify which URLs should receive the cookie. This specification is given as a full or partial file path, and only URLs on that path and below it will receive the cookie. The default is "/" which means the cookie should be sent to all URLs on the domain. If, however, you have many applications running on the same machine and you need to avoid conflicts, you might set "PATH=/myhtml" so the cookie will be sent to any CGI applications on (or below) the "/myhtml" directory, but not to any on "/yourhtml".

The SECURE keyword (which has no parameter) indicates that the cookie should only be sent back across a "secure" connection, such as SSL (Secure Socket Layer). If any non-secure connections reference the URL, the browser will not send the cookie.

Cookies can be reset at any time, and the browser will replace the old value with the new one. As noted above, resetting with a negative EXPIRES parameter causes the cookie to be deleted.

Important note: There are only two places in a template file that you may use the SETCOOKIE command, and the precise location alters the behavior of this command. The first place is at the very beginning of the template, before you have produced any output. (You may have other ODBiC commands before the SETCOOKIE, including database queries, but only commands that don't cause any output. If you have other commands before the SETCOOKIE, make sure that they begin in the first column of each line and that there aren't any extra spaces after the command-terminating "-->" marker.) When you use a SETCOOKIE command before you have written any output, ODBiC will send the command as an HTTP "Set-cookie: ..." header. The other legal place to use SETCOOKIE is somewhere after the <HEAD> HTML tag but before the <BODY> tag. In this case, ODBiC will send an HTML tag, <META HTTP-EQUIV ...>, to set the cookie. There is some evidence to suggest that this second method is somewhat more reliable with a wider variety of servers and browsers, since some servers apparently attempt to validate headers being sent back but won't pay any attention to tags in the document. This location is also easier to use, since you don't have to worry about producing any previous output. Note that ODBiC does not validate that you are using one of these two legal locations for the SETCOOKIE, but if you use the command anywhere else in the document, it will probably not work for most browsers.

<!--SHOWINPUT-->

In this command, you can use any SQL statements acceptable to your ODBC driver. The SQL statement is issued immediately at the point that it is encountered in the HTML template file.

For SELECT statements, after the statement is executed, each result column will be defined as a variable. That is, any of the result column database values may be inserted into the output by referencing a column name enclosed in a pair of "$" characters. For example, if you use this SQL statement:

    <!--SQL SELECT item_number, description FROM items -->

The TRANSLATE command allows you substitute one variable value with another, such as translating "code" values into meaningful text. Whenever the referenced variable is used in the HTML input file, the TRANSLATE table of values will be scanned to determine if the current value of the variable has a "newvalue" specified. If so, the "newvalue" will be inserted into the output.

Note that the TRANSLATE command does not cause variable substitution at the point that it is executed; it defines a substitution table that will be used anytime the variable is referenced for output. Therefore, the TRANSLATE command can be used anywhere in the template file before the variable is output. (Specifically, the TRANSLATE command should not, and cannot, be used inside an EACHROW loop specification. Specify the TRANSLATE table before the EACHROW.)

You don't necessarily need to define a translation for all possible values of a variable. If there is a translation table for a particular variable but a given value is not found to have a translation, then the original value will be used. One handy use for a partial translation table is to define a translation "newvalue" just for zero values, such as 0=none or even 0="" (empty string).

Each TRANSLATE command defines the translations for a single variable, and all translation values for that variable must be in the same TRANSLATE command. Statements can span multiple lines. The "-->" marker must be used to terminate the list of translation values in each statement.

Quotes around values (e.g., value="newvalue" or value='newvalue' or "value"="newvalue") are not required unless a value contains a comma (which separates pairs in the statement), but you may use them if you like. If you don't use the quote marks around a value text string, no leading or trailing spaces for the value will be included. (For example, if the command were "TRANSLATE var 1 = value one , 2 = value 2 , ...", the actual text values used would be "1"="value one" and "2"="value 2".) If the actual text value contains any double-quote characters ("), you must enclose the string in single-quotes, such as '"value"'.

The UPDATEFORM command automatically writes an HTML form back to the user's browser. The generated HTML <FORM ACTION=...> specification contains a URL back to the ODBiC CGI program. The form includes HTML <INPUT> fields for all specified variables (see below). These field will contain their current values (usually, values previously selected from the database) so that the user can modify the current data. An SQL UPDATE statement will also be generated as a "hidden" form field. When the form is submitted, this statement will be used to apply the user's changes to the database.

String Functions

The following string functions can be used anywhere that you can use a variable: in the output text; in HTML commands; and in ODBiC commands and expressions.

All string function names begin with a $ sign. Like variables, the names are not case-sensitive. Each function has one or more "arguments" enclosed within a set of parentheses. You can put spaces between the function name and the left "(" of the arguments if you like.

Each of the arguments to these functions are evaluated like SET expressions, so you can use any arithmetic or logic expressions as arguments. For each of these string functions, the result of evaluating the first argument will be treated as a character string, even if it is an arithmetic expression. Like SET and IF expressions, character strings do not need to be enclosed in quotes unless you need to prevent inadvertent interpretation (e.g, if the string contains math operation symbols or commas), but you may use quotes if you like. (Note, however, that variable references within quotes will not have their values substituted.) Character strings may consist of several "concatenated" quoted or unquoted strings or variables in a row, such as ("Variable one is" $var1$ "and variable two is" $var2$), which will be treated as a single string argument.

Math Functions

Running ODBiC as a CGI Program

To run ODBiC as a CGI program, the executable file (odbic.exe) must be installed on a "CGI directory". CGI directories are defined in your Web server's configuration file. CGI directories are generally created as subdirectories of the primary HTML directory (that is, the same root directory as your Web pages), and they are commonly named something like "scripts", "cgi", or perhaps "cgi-bin". If you already have such a directory defined, then simply copy odbic.exe there. If you do not already have such a directory, you will need to check your Web server's documentation for configuration details.

With a properly defined CGI directory, your Web server will automatically recognize CGI programs when this directory is specified in a URL. Like the URL for a Web page, a CGI URL should omit the primary HTML directory. For example, if you have a Web page named "pagex.html" and your primary HTML directory is defined in your server's configuration file to be "httpfile", then the directory path to the file would be "\httpfile\pages.html". However, the URL would be something like "http://www.yourdomain.com/pagex.html" because your server expects that all HTML files will be on the "httpfile" directory. Similarly, if your CGI directory is "\httpfile\scripts" and ODBiC has been installed there, then the URL would be "http://www.yourdomain.com/scripts/odbic.exe".

Note that some Web servers will allow you to omit the file extension (in this case, ".exe") for CGI programs. Refer to your server's documentation for details.

There are two ways that CGI programs may be referenced from an HTML document. Perhaps the most common is as the ACTION function of a FORM declaration, such as:

    <FORM METHOD="post" ACTION="http://www.yourdomain.com/scripts/odbic.exe">

Running ODBiC from the Command Prompt

You can run ODBiC from the DOS command line prompt and write the output to an HTML file on disk. If you do not need "real time" access to your database (for example, if the data does not change often), then using this method will avoid CGI and database overhead for every page access. Or, if your Web pages are on your Internet Service Provider's host machine and you cannot install CGI programs and databases on that host, then you can generate your pages from a database on your PC and upload the generated HTML. Also, you can use the Windows NT Schedule server (which you will find in the Servers window) to run ODBiC on at regular timed intervals.

To use this mode, the odbic.exe program file can be on any directory. Run ODBiC as you would any DOS program -- from the MS-DOS prompt or from the Windows "Run..." box. Following the program name (odbic) in the run command, you must specify an input file (the template) with a "-i" prefix and an output file (the generated HTML) with a "-o" prefix. (The file name may be placed immediately after the "-i" or "-o", with no spaces, or you may place a space between "-i" or "-o" and the filename.) Unless these files are on the current working directory, you will also need to specify directory paths for ODBiC and the files, such as:

    \httpfile\scripts\odbic -i \httpfile\template.html -o \httpfile\pagex.html 

Predefined Variables

Using ODBiC Without a Template File