ODBiC
Open DataBase
Internet
Connector

Version 1.6

User's Guide - Part 2


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.

Quick Command Reference

BREAK Terminate a processing loop (e.g., WHILE, EACHROW)
DATABASE Define an ODBC database connection string
DEFAULT Define default values for "empty" variables
EACHMULTI, ENDMULTI Define a processing loop for multiply-defined input variables
EACHROW, ENDROW Define formatting for each result row after a SELECT statement
EXEC Execute a system command or run a DOS program
EXIT Terminate the template file processing
FORM Generate a generic HTML data input form
FORMAT Define output format "masks" for variables
HIDDEN Generate <INPUT TYPE="hidden"> form fields to pass variables
IF, ELSE, ENDIF Conditional test of variables
IFNEW Test an "ORDER BY" column in sorted result rows for new group
IMPORT, ENDIMPORT Read and process data variables from a text file
INCLUDE Read and process an additional template file
INSERTFORM Generate a database insert form
NOTE Template comment, not output to browsers
OPTIONLIST Create an HTML "select option" list from a query or a given list of values
OUTPUT Write the output to a disk file
QBE Generate and execute a "query by example" SQL statement
REDIRECT Redirect user's browser to a different URL
SEARCH Generate and execute a "keyword search" query
SENDMAIL, ENDMAIL Send the output as an e-mail message
SET Set variable to value
SETCOOKIE Send a "cookie" to the user's Web browser
SETMULTI Set new instance of a multiply-defined variable (an array)
SHOWINPUT Output a list of all CGI-input variables
SQL Execute an ODBC SQL statement
TABLE Format SQL SELECT results as an HTML table
TRANSLATE Define a translation table for a variable
UPDATEFORM Generate a database update form
VALIDATE Verify that input data matches a "regular expression" pattern
WHILE, ENDWHILE Define a processing loop to be repeated while a condition is true

In the following description of the commands, the square-bracket characters, "[" and "]", will be used to indicate optional command keywords and parameters. The vertical bar character, "|", will be used between two possible choices for a keyword or a parameter, indicating that you may use one or the other. The ellipsis, "..." will be used to indicate that the preceding parameter may be repeated, such as "var [,var,...]" to indicate that a "var" may occur one or more times separated by commas.

<!--BREAK-->

This command causes a loop to terminate immediately. It may be used in any of the looping commands: WHILE, EACHROW, EACHMULTI, and IMPORT. Processing continues after the "loop end" marker (ENDWHILE, ENDROW, ENDMULTI, or ENDIMPORT). Typically, this command would only be used inside a conditional (IF... ENDIF) test to terminate the loop early if some condition were met.

Example:

    <!--SQL SELECT ...  -->
    <!--EACHROW-->
      <!--IF $row$ > 50 -->
        <!--BREAK-->   <!-- (quit processing if more than 50 rows) -->
      <!--ENDIF-->
      ...
    <!--ENDROW-->

 

<!--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.

You must have a DATABASE statement in your HTML template file before any SQL commands. (Optionally, you may pass in a connection string in the variable named "database"; see the section Predefined Variables.) This command does not actually establish a connection, however. Rather, the connection string specified by this statement will be used to connect when an SQL command is executed.

The connection string set by this statement applies to all subsequent SQL commands until a different DATABASE command is encountered. If you need to access another database, just use another DATABASE statement before those SQL statements. (This does not mean that the database connection is re-established for each SQL statement, however. The connection established by the first SQL statement stays open until another SQL statement is executed with a different connection string, or until ODBiC terminates.)

You can use variables anywhere in the DATABASE statement (including the DSN). One common usage would be variables to insert the user's ID and password, which you might get from an input form:

    <!--DATABASE DSN=Employees; UID=$user$; PWD=$password$ -->
DSN names are defined using the ODBC DSN definition dialog box (which can be accessed from the Control Panel by clicking on the ODBC icon). Note that on Windows NT systems, which associates DSNs with NT user IDs, any DSNs accessed by ODBiC will need to be defined as System DSNs, or they will need to be defined for the user ID that your Web Server uses. (Refer to your server's documentation. Note that some servers run as LocalUser, which cannot have DSNs assigned, so you must use a System DSN.) To define a System DSN, click on the "System DSN" button on the dialog box before you define the DSN.

However, you can bypass all DSN database associations by giving a complete ODBC connection specification in the DATABASE statement. This would include a driver and file type specification, file location path, and various options. Refer to the ODBC documentation for complete details, but here is an example of a connection string for an MS Access database contained in the file C:\httpfile\db\products.mdb:

    <!--DATATBASE DRIVER={Microsoft Access Driver (*.mdb)};
        DBQ=c:\httpfile\db\products.mdb; FIL=MS Access-->
Note that the "DRIVER={...}" string (which is inside curly braces) must be the exact text that is shown in the ODBC DSN definition window for available drivers. (This is because the ODBC Driver Manager uses this same table to look up the driver.)

Depending on the database that you are using, the DBQ specification may need to be a complete file path and file name, or it may just be a directory. Refer to the examples in the chart below. For example, the MS-Access DBQ gives the file name of the database, "c:\temp\sample.mdb", but dBASE puts each database on a separate directory, so the DBQ just indicates this directory, "c:\temp".

If you are using ODBC 3.0 (which is shipped with Office97), you may also need to use the DRIVERID keyword. In the chart below, if you are using ODBC 2.x do not use the DRIVERID keyword.

Example Connection Strings Without Using DSN

  Database                Keywords

  Microsoft Access        DRIVER={Microsoft Access Driver (*.mdb)};
                          DBQ=c:\temp\sample.mdb;
                          FIL=MS Access

  dBASE                   DRIVER={Microsoft dBASE Driver (*.dbf)};
                          DBQ=c:\temp;
                          DRIVERID=277;
                          FIL=DBASE2   (or DBASE3, DBASE4)

  Microsoft Excel 3/4     DRIVER={Microsoft Excel Driver (*.xls)};
                          DBQ=c:\temp;
                          DRIVERID=278;
                          FIL=EXCEL

  Microsoft Excel 5/7     DRIVER={Microsoft Excel Driver (*.xls)};
                          DBQ=c:\temp\sample.xls;
                          DRIVERID=22;
                          FIL=EXCEL

  Microsoft FoxPro        DRIVER={Microsoft FoxPro Driver (*.dbf)};
                          DBQ=c:\temp;
                          DRIVERID=536;
                          FIL=FOXPRO 2.0   (or FOXPRO 2.5, FOXPRO 2.6)

  Paradox                 DRIVER={Microsoft Paradox Driver (*.db );
                          DBQ=c:\temp;
                          DRIVERID=26;
                          FIL=PARADOX

  Text                    DRIVER={Microsoft Text Driver (*.txt;*.csv)};
                          DEFAULTDIR=c:\temp;
                          FIL=TEXT
For other databases, refer to your ODBC driver's documentation for keywords required or allowed with the ODBC "SQLDriverConnect" function call.

 

<!--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).

You may define DEFAULT values for up to 100 different variables. You may also redefine the default value for a variable that was previously used in the template file. (The default is in effect from the point of the DEFAULT command until another DEFAULT is specified for the same variable.) And you may define a default value that is another variable (for example, "<!--DEFAULT var1 = $var2$, ... -->"). (Note that this variable substitution will be done when the defaulted variable is actually referenced, not when the DEFAULT value is set by this command. This allows the DEFAULT value to change as "var2" changes.)

Example:

    <!--DEFAULT quantity=1, phone="(none)" -->

 

<!--EACHMULTI variable [,variable, ...]--> ... <!--ENDMULTI-->

All the template code between the EACHMULTI and the ENDMULTI is repeated for each instance of multiply-defined variables (i.e., multiple instances of values with the same name).

"Multi-variables" can be sent from an HTML form <SELECT> "pull-down menu" if the MULTIPLE keyword is added to the tag (e.g., <SELECT MULTIPLE name="...">). When this keyword is specified, the user can highlight more than one of the selections in the list. You can also create multiple instances of a variable simply by having multiple <INPUT> statements in an HTML form with the same name. (Typically, browsers will only send those fields that actually have data entered in them, so you can supply several input fields, and a user can enter data in as many as required.) Of course, hidden <INPUT>s with the same name can also be repeated. Another way to create multiple occurrences of a variable is by using the SETMULTI command. In each case, the EACHMULTI loop allows the ODBiC template to process each value in turn. Inside the loop, $variable$ refers to the "current" value, which changes with each iteration.

You can specify a list of variable names in the EACHMULTI command, with the variable names separated by commas. In this case, "parallel" sets of multi-variables are processed, much like a row returned from an SQL query. Parallel sets of multi-variables can be created with the SETMULTI command or with parallel sets of fields on a form. (That is, your input form could be a table with several columns of variables, and several rows with the same variable names.)

If you specify a list of variables, the EACHMULTI loop will continue for as many iterations as the maximum number of any one of the variables. If you "run out" of any of the other variables before this maximum number is reached, those variables become "undefined" (unless you set a DEFAULT for them.)

Inside an EACHMULTI loop, you can use the internal variable $multirow$ as the current multi-variable instance number, similar to the $row$ variable in the EACHROW loop.

If you SET one of the EACHMULTI variables inside the loop, you will be resetting that specific instance of the variable (which would be useful only if you're going to reprocess that multi-variable later in another EACHROW loop). Important Note: Do not use SETMULTI inside an EACHMULTI loop to set the same variables that you're looping on! SETMULTI always creates a new instance of the variable, so you would be creating an infinite loop. Use a regular SET statement.

There are certain ODBiC commands that may not be used inside of the EACHMULTI loop because they should not be used repetitively. These commands are not allowed: DEFAULT, FORMAT, TRANSLATE, and VALIDATE.

Example:

Suppose that a form has an entry for an e-mail address and a <SELECT MULTIPLE> list of mailing lists that a user could subscribe to. The following will insert the e-mail address into the database for each selected mailing list:

    <!--EACHMULTI mailing_list -->
      <!--SQL INSERT INTO Subscribe (mailing_list, email)
             VALUES ('$mailing_list$', '$email$') -->
    <!--ENDMULTI-->

 

<!--EACHROW--> ... <!--ENDROW-->

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 text or HTML tags 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.)

Bear in mind, however, that "nested" SQL statements create a great deal of overhead, and that nested SELECTs are rarely necessary. Instead, you can usually use a "join" operation to get data from two tables with a single query, then use the IFNEW test to do "master/detail" grouping. (See the IFNEW examples.)

There are certain ODBiC commands that may not be used inside of the EACHROW loop because they should not be used repetitively. These commands are not allowed: DEFAULT, FORMAT, TRANSLATE, and VALIDATE.

Inside an EACHROW specification, you may use the ODBiC variable $row$ to reference the current row number. The $row$ variable is initialized after each SQL statement (see the SQL command) and it is incremented for each fetched result. You might use this variable to enumerate the results, or you might want to test for particular row numbers. For example, you could use a conditional statement <!--IF $row$ = 1 --> to do some special output, such as a table header, before any results are output. (But it is generally easier to put such "first row" formatting between the SQL statement and the EACHROW command: Any output after the SQL but before the EACHROW will be done for the first row only.) The $row$ variable is most useful when you want to limit the number of rows displayed.

Since the EACHROW command always loops through all of the result rows, you should not use EACHROW combined with any of the other result looping commands (TABLE, UPDATEFORM, or OPTIONLIST), unless you have another SQL SELECT statement nested in the EACHROW loop.

The formatting text between the EACHROW and the ENDROW can span multiple lines up to a maximum size of 16K bytes (which is about 200 full 80-character lines).

Example:

  <!--SQL SELECT name, phone FROM Employees WHERE dept = '$dept$' -->
  <!--IF $row$ -->
    <H3> Employees for Department $dept$ </H3>
    <TABLE>
    <TR><TH>Name</TH><TH>Phone</TH></TR>
    <!--EACHROW-->
      <TR><TD>$name$</TD><TD>$phone$</TD></TR>
    <!--ENDROW-->
    </TABLE>
  <!--ELSE-->
    <H3> No Employes Found for Department $dept$ </H3>
  <!--ENDIF-->

 

<!--EXEC dos_command [arguments] -->

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.

If the system command or program writes any "console" output as a result of execution, this output will go directly back to the user's browser. Note that the output does not go through ODBiC, so no ODBiC processing on the output is performed. However, it is possible to "redirect" the console output to a file by using the ">" character, (e.g. <!--EXEC program > file -->) then INCLUDE the file in the current template. You may also wish to use the redirection to prevent any output from going to the user's browser.

For security reasons, the command string cannot contain a "$T" command separator (which, on a command line in some versions of DOS, can be used to issue multiple commands on a single line). Also for security reasons, the EXEC command cannot be used inside an EACHROW loop (since the EACHROW specification can be passed in from a form).

Example:

  <!--EXEC /httpfile/cgi-bin/odbic -i/httpfile/$template$ -o/httpfile/$output$ -->

 

<!--EXIT-->

The EXIT command causes ODBiC to stop processing the input template file. It is most useful inside an IF statement, such as after output of an error message. In such cases, an "early exit" can avoid some complicated and heavily nested IF/THEN/ELSE clauses that would be necessary to skip the rest of the file.

Example:

  <!--IF NOT $email$ -->
    You must enter your e-mail address. Please go back and fill in that box.
    <!--EXIT-->
  <!--ENDIF-->

 

<!--FORM [ACTION=cgi,] [SUBMIT=text,]
["label"] input_field[:size] [=value]
[,OPTIONLIST=()] [,CHECKBOX=()] [,HIDDEN=()] [,...] -->

This command will generate a "generic" HTML data input FORM with an ACTION link to ODBiC. For example, the generated form might be used as input to a query template. (This is command is similar to the
UPDATEFORM and INSERTFORM commands, except that no SQL statement is generated.)

By default, the FORM command puts ACTION="odbic.exe" in the HTML <FORM> declaration. If you want to send the form input data to a different CGI program, you can give an ACTION="cgi" parameter to this command to specify a different CGI program. If you use this parameter, you should also give the CGI URL path, such as ACTION="/cgi-bin/program.exe".

The optional SUBMIT parameter can be used to define the text to be used for the form's "submit" button. The default is "Submit".

You can specify one or more input_fields separated by commas. For each, an HTML <INPUT NAME="..."> is added to the form, using the given field name. An input_field box can be initialized to a specific value by using the format "input_field=value". (The "value" does not need to be enclosed in double-quotes unless in contains any commas.) The user will be able to edit this value, if desired.

You can control the size of each <INPUT> field by using the optional ":size" specification (i.e., a colon followed by a number) after the input_field name in the FORM list. For example, "first_name:24" would produce <INPUT TYPE="text" NAME="first_name" SIZE="24"> which would be an input box named "first_name" that is 24 characters wide. If no size is specified, ODBiC uses a default size of 50 characters. If you give a field size larger than 99, then ODBiC automatically uses an HTML <TEXTAREA> input, which is a multiple-line scrolling window. This window will be at most 64 characters wide and as many lines as it takes to hold your specified field size. For example, a specified size of 250 would produce a 50-character, 5-line textarea window. However, you can directly specify the size of a <TEXTAREA> by giving the "size" specification as two numbers separated by an "x" (for example, 'Description:64x4'), where the two numbers are to be the number of columns and the number of rows. (The numbers can be given in either order; the larger number will always be used as the field width and the smaller number will be the number of lines.)

By default, the input_field name is also displayed on the form, immediately in front of the input box, to identify the requested data. For improved appearance and readability, ODBiC capitalizes the first letter of this "label", converts any underscore characters to spaces, and capitalizes any letter following an underscore. For example, "customer_name" would have a label of "Customer Name".

If you want to have a different input box label, something other than the input_field name, you can specify a label in double-quotes immediately in front of the input_field name. Note: You must have a space, not a comma, between the quoted label and the input_field name.

Anywhere in the list of input_fields for this command, you may specify "OPTIONLIST=(...)", and the arguments inside the parentheses can be the same as the OPTIONLIST command. Specifically, you can have "OPTIONLIST=(column from table)" to select the options from the database, or you can give a comma-separated list of literal values in the form of "OPTIONLIST = (input_field = value1, value2, ...)". In either case, if the given input_field is already defined as an ODBiC variable and it has a current value that is in the list of options shown by this command, then that option will be "SELECTED" in the list. (That is, the current value will already be highlighted in the list the user sees). Therefore, you can use this feature to preset a particular option to SELECTED by using a SET input_field=value before using input_field in the FORM command.

You can also specify a CHECKBOX variable in the FORM command. Again, this may be anywhere in the list of input_fields, and the format is "CHECKBOX=(input_field, checked_val, unchecked_val)". The "checked_val" will the variable's value if the user checks the box; otherwise the variable will have the "unchecked_val". Similar to the OPTIONLIST, if the specified input_field is already defined as a variable and it has a current value equal to the "checked_val", then the user will see the box as already checked. Otherwise it will be unchecked. (NOTE: Browser's only send a value if a checkbox is checked, and send nothing for that variable if the box is unchecked. Therefore, the "unchecked_val" is passed to the next template in a hidden variable named "default" -- one of the predefined input variables that ODBiC always processes -- with a value of "input_field=unchecked_val". Like a DEFAULT statement in a template, this value will be used if the user doesn't check the box. Therefore, if you use the FORM CHECKBOX with your own template, don't specify a DEFAULT for the checkbox variable in the next template.)

If you need to pass "hidden" variables in the form, you can use the HIDDEN parameter anywhere in the list of input_fields. The format is "HIDDEN=variable" for a single variable or "HIDDEN=(var1, var2, ...)" for a list of variables. This parameter works like the HIDDEN command to generate <INPUT TYPE="hidden"> fields for the given variable or list of variables. Note that only variables can be used in the HIDDEN parameter list, so you may need to SET these variables before using them in the FORM.

Example:

The following example uses all optional parameters except ACTION (which defaults to odbic.exe). Many fields have sizes declared (which defaults to 50 characters if ":size" is not given). This example also provides a double-quoted "display label" for each field, which is different from the input_field variable name, to show the proper syntax for each type of option. (If these quoted labels are not used for a field, then the display label would be the same as the input_field name that immediately follows the quoted label.) Unlike INSERTFORM and UPDATEFORM, the FORM command does not need to have apostrophes (single-quotes) around text-data fields. (Those commands need the single-quotes to generate properly quoted values in SQL statements, but FORM does not generate any SQL.)

 <!--FORM TEMPLATE="/httpfile/query.odb",
     "First Name" firstname:24, "Last Name" lastname:24,
     "Home Address" address, "City, State, and Zip" csz,
     OPTIONLIST=("Department" dept from Departments),
     CHECKBOX=("Hourly?" hour, Y, N),
     "Salary or hourly rate" rate:8,
     HIDDEN=(uid, pwd) -->

 

<!--FORMAT variable=mask [, variable=mask, ...] -->

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.

Note that the FORMAT command does not cause formatting at the point that it is issued; it defines a mask that will be used anytime that the variable is referenced. Therefore, the FORMAT command can appear anywhere in the template before the point that the variable will be referenced for output. (Specifically, the FORMAT command should not, and cannot, be used inside an EACHROW loop. Specify the FORMAT mask before the EACHROW.) Note, however, that there is a string function, $format( ), that does perform this same formatting function at the point that it is encountered.

The formatting mask uses the pound sign character (#) to indicate a position that can be filled by a character or digit from the variable. For numeric values, the zero character (0) also represents a position that can be filled by a digit from the variable, or a "0" if there is no digit at that position. Other characters (except as noted below) are copied to the formatted output.

For variables that have a numeric value (i.e., a variable containing only digits, plus or minus sign, or a decimal point), you may use the minus sign (-) as the first character of the mask to indicate that negative numbers should be formatted with a "-" sign in front, but positive numbers are to have no sign. A plus sign (+) as the first mask character causes both positive and negative numbers to be shown with a plus sign or a minus sign, respectively. If the mask does not use either the plus or minus signs as the first character, then negative numbers will be shown without a sign.

You may also use the dollar sign character ($) or pound sign character (£) as the first mask character (or as the second character if you have a plus or minus sign as the first character.) This will cause a dollar sign or pound sign to be added to the front of a numeric value.

For numeric variables, the explicit or implicit decimal points of the mask and the number are aligned. The result will have a decimal point only if the mask does. Working toward both the left and right of the decimal point, digits from the variable replace any "#" characters in the mask, but only if there is a digit at a given position. If there is no digit at that position, then the formatting process stops. That is, as long as there are digits remaining in the value, then "#" characters are replaced by digits and special characters such as commas are copied to the output, but when there are no digits left, then the formatting is finished and special characters past that point are ignored. A "0" character in the mask will be replaced by the digit from the variable at that corresponding position, if there is one, or the "0" will remain in the output if there is no digit.

If a numeric value has more fractional digits than the format mask specifies, then the value will be rounded. If the mask does not specify any fractional digits, then the numeric value will be rounded to a whole number.

Here are some examples:

    <!--FORMAT price="$#,###,##0.00" -->

    If price is:    the output will be:
     10.00                 $10.00
     1250.00               $1,250.00
     1250.999              $1,251.00
     6.0000                $6.00
     .501                  $0.50
     .509                  $0.51
     -1                    $1.00

    <!--FORMAT price="-$#,###,##0.00" -->

    If price is:    the output will be:
      235000               $235,000.00
      -10.999              -$10.99

    <!--FORMAT value="+#####0.0###" -->

    If value is:    the output will be:
      1                    +1.0
      505.505              +505.505
      -23.123456           -23.1235
      .5                   +0.5
For variables that are not numbers, the formatting is less complicated. Working from left to right in the mask, each successive "#" character in the mask is replaced by the next successive character in the variable. Characters other than the "#" in the mask are simply copied to the output. When there are no characters remaining in the variable, then the formatting is finished, and any remaining characters in the mask are ignored.

NOTE: If your mask contains any commas, then you must enclose the mask in double-quotes (") in the FORMAT command. This is because commas separate the "variable=mask" pairs in the command. If the actual mask contains any double-quote characters ("), you must use single-quotes (') around the mask, such as '"mask"'. Quotes are optional if there are no commas or double-quotes in the mask.

You may define up to 50 format masks. You might use separate FORMAT commands for each variable or declare several in the same command, separated by commas. The list of "variable=mask" pairs can span multiple lines with the command-terminating "-->" mark after the last variable on the last line.

 

<!--HIDDEN variable [,variable, ...] -->

This command is simply a convenience to generate <INPUT TYPE="hidden" NAME="variable" VALUE="$variable$"> form fields for the given list of variables. (That is, the VALUEs will have each variable's current value.) This command may be used to pass the current values for variables to the next template without showing the values on the current form.

The HIDDEN command should only be used within a <FORM> ... </FORM> declaration. Only variable names may be used in the list, without the "$" enclosing marks, but you could SET a variable to a value immediately before using it in this command.

Example:

    <FORM ... >
    <!--HIDDEN uid, pwd, transaction_code >
    ...
    </FORM>

 

<!--IF [NOT] value1 condition value2 [AND | OR ...] --> ... <!--ELSE--> ... <!--ENDIF-->

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).

"Value1" and "value2" can be any variable (referenced by the variable name prefixed and suffixed with "$" signs), a "literal" value (a number or a text string), an arithmetic expression using numeric-valued variables or literals, or a "string function" expression that produces a text string. Arithmetic expression may use "+" for addition, "-" for subtraction, "*" (asterisk) for multiplication, or "/" for division. Parentheses, "(" and ")", may be used to indicate the order of evaluation (i.e., operations inside parentheses are performed first). A "unary" minus sign is allowed to indicate that a variable, constant, or expression inside parentheses is to be negated (e.g., $x$ / -$y$ or -($x$ / $y$)). You may also use any of the math functions in an expression.

NOTE: Variables used in IF statements must be enclosed in "$" characters. That is, the program does not assume that any operands in an IF comparison are variables. Like output text, you must enclose the variable names in "$" characters to cause the variable's values to be substituted into the expression.

The "condition" specifies a test between the two values: "=" (equal), "<>" or "!=" (not equal), ">" (greater than), "<" (less than), ">=" (greater than or equal), or "<=" (less than or equal). If the specified relationship between the two values is true, then all text and statements following the IF, up to an ELSE or ENDIF statement, will be processed. The ELSE reverses the sense of the test, and any text and statements up to the ENDIF will be processed only if the test specified in the IF statement is false.

You may combine conditional tests using AND (i.e., conditions on both sides of the AND must be true) or OR (either side may be true), or use NOT in front of a condition expression to reverse its sense. You may use parentheses to indicate the order of the compounded tests. The default is that NOT is performed first, AND is performed next, and OR has the lowest precedence. For example, "NOT $a$=1 AND $b$=2 OR $c$=3 AND $d$=4" is the same as "((NOT $a$=1) AND $b$=2) OR ($c$=3 AND $d$=4)".

Actually, you can test the "condition" of a single variable. When only one value is given in a conditional expression (or a single value is compounded with NOT, AND, or OR), then the test produces a "true" result if the value is any non-empty string or any non-zero number. For example, you can say <!--IF NOT $name$ --> to test if the variable "name" has no value, or <!--IF $opt1$ AND $opt2$ --> to test for having values for both variables. Three of the math functions are intended to be used in this way to validate input data: isNumber ( ), isAlpha ( ), and isAlphaNum ( ). For example, you can say <!--IF NOT isNumber($price$) --> to check for an invalid number in the "price" variable.

An IF statement comparison is assumed to be a numeric comparison whenever both values are numeric values or arithmetic expressions. Otherwise, if either value is non-numeric, then a text string comparison will be used. You can use quote marks around or in a value expression to force the entire value to be treated as a text string, but the quotes are optional if the string expression contains any non-numeric characters. For these "implicit" text string comparisons, leading and trailing space on values are ignored. (For example, <!--IF $var$ = this value --> is the same as <!--IF $var$="this value" -->.) You can, however, include spaces inside quotes if you need to have them as part of the comparison, such as <!--IF $var$ = " " -->. You may use single-quote characters (') if a value contains any actual double-quotes; for example, '"value"' would be "value" with the double-quotes included.

You may include another IF statement in the ELSE statement, such as:

    <!--IF $type$=A -->
        ...
    <!--ELSE IF $type$=B -->
        ...
    <!--ELSE IF $type$=C -->
        ...
    <!--ELSE-->
        ... (not A, B, or C)
    <!--ENDIF-->
The advantage of this form is that the IFs are not "nested" and you only need one <!--ENDIF--> to end the entire series.

Note that if you test a variable that has a TRANSLATE table defined for it, you must test for the translated value rather than the original value. In general, remember that the "value" expressions in an IF statement are processed like normal output before any arithmetic or the comparison itself is performed.

Example (indentation helps to pair IFs with ELSEs and ENDIFs):

    <!--IF $Discontinued$ = Yes -->
        This is a discontinued item.
    <!--ELSE-->
        <!--IF $UnitsOnHand$ -->
            We have $UnitsOnHand$ units in stock,
            <!--IF $UnitsOnHand$ < $UnitsOrdered$ -->
                which is insufficient to fill this order.
            <!--ELSE IF $UnitsOnHand$ - $UnitsOrdered$ < $ReorderLevel$ -->
                so we can fill this order, but it is time to reorder.
            <!--ELSE-->
                so we can fill this order.
            <!--ENDIF-->
        <!--ELSE-->
            We have no units in stock. Time to reorder.
        <!--ENDIF-->
    <!--ENDIF-->

The condition specified in an IF or ELSE IF statement can span multiple lines, but the "-->" must mark the end of the condition.

 

<!--IFNEW variable -->

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.)

Examples:

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-->
An example of "master/detail" reporting might be the case where you have a table that has one row for each of your customers and another table with many order rows for each customer. The master customer table must have some unique ID that can be used to identify each customer's orders in the detail order table. You need to "join" your master table to the detail table using this ID. The general form would be:
    <!--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-->
The "join" will produce one result row for each detail row. Every result row for a given customer will actually include all the master table data for that customer. But the IFNEW structure above will allow you to display the master data only once (i.e., whenever a new master ID is encountered in the result set).

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".)

Note that the only argument in the IFNEW statement is a single variable. Since this argument must be a variable, you can use just the variable name without the "$" signs around it. (However, any "$" signs will be ignored.)

 

<!--IMPORT variable_list FROM file--> ... <!--ENDIMPORT-->

This command allows you to read a text file and extract data values as variables.

The variables to be set are identified by the "variable_list" parameter. There are three options for this list:

This command automatically loops through the import file one line at a time, much like EACHROW, and on each iteration, each variable in the variable_list will have a "current value" as read from the current line of the inport file. The end of the processing loop must be marked with the ENDIMPORT command. Inside this loop, you can use the variable $importrow$ as the current line number in the input file, and when the loop terminates $importrow$ will be the total lines read.

Example:

  <TABLE>
  <TR><TH>Name</TH><TH>E-mail</TH></TR>
  <!--IMPORT DELIMITER=",", name, email FROM C:/data/maillist.txt -->
    <TR><TD>$name$</TD><TD>$email$</TD></TR>
  <!--ENDIMPORT-->
  </TABLE>

 

<!--INCLUDE filename [variable=value, ...] -->

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.

NOTE: The referenced filename must have the full directory path specification, such as would be used to open the file with a text editor, and no Web-server directory mapping will be applied. (If there is no directory path, then the file is assumed to be on the "current directory", which would be the CGI directory.)

You can also set variables in the INCLUDE command by adding a list of "variable=value" specifications, similar to the SET command. This is useful if the included file requires certain variables to be set. The first "variable=value" should be separated from the INCLUDE file name by a space, and additional variables should be separated from each other with commas, exactly like the SET command. (Note that this is not necessary for any variables that already have values, because the included file automatically has full access to all currently defined variables.)

Example:

    <!--INCLUDE D:\httpfile\standard_header.htm -->

 

<!--INSERTFORM TABLE=table, [TEMPLATE=template,] [SUBMIT=text,]
["label"] numeric_field[:size] [=value] |
["label"] 'text_field[:size]' [=value] |
["label"] #date_field[:size]# [=value]
[,OPTIONLIST=()] [,CHECKBOX=()] [,HIDDEN=()] [,...] -->

This command generates an HTML <FORM> to insert rows into a database table. The generated <FORM> will have an ACTION="odbic.exe". Like the
FORM command, INSERTFORM generates HTML <INPUT> boxes for each of the specified input_fields, but it also generates an SQL INSERT statement to be executed when the form is submitted. This INSERT statement will specify database columns corresponding to the input_fields and a VALUES clause to insert the data entered by the user.

NOTE: You must define a database connection before using INSERTFORM, even if the current template does not use any SQL commands. As described below, the form will include a "hidden" HTML field to specify the "database" to use for the SQL INSERT statement. This will be the same as the DATABASE connection in effect when the INSERTFORM command is used. If you haven't executed any SQL commands prior to using INSERTFORM, it will still be necessary to provide a DATABASE command somewhere before the INSERTFORM.

You must specify a TABLE="..." parameter, which will be the database table used in the generated SQL INSERT statement.

You can optionally specify a TEMPLATE="..." parameter, which will be the ODBiC template to use when the form is submitted. If you do not specify a TEMPLATE, ODBiC will use its "no template" processing, which will execute the SQL statement using the "default.odb" template. If you define your own template to process the INSERTFORM input, you should have at least these two commands to execute the SQL INSERT statement that will be passed in:

  <!--DATABASE $database$ -->
  <!--SQL $sql$ -->
(As noted above, you must have a DATABASE command in your template prior to using INSERTFORM, and this connection string will be passed as the "hidden" variable named "database".)

The optional SUBMIT parameter can be used to define the text to be used for the form's "submit" button. The default is "Insert".

Like the FORM command, you can specify display labels for any input_field, and you can control the size of each input box. You can specify an initial value to show in the input box as "input_field=value", which the form user may edit, if desired. You can also use OPTIONLIST, CHECKBOX, and HIDDEN inputs. See the FORM command for complete input_field details.

NOTE: There are some special considerations for specifying the database column names in INSERTFORM (and in UPDATEFORM):

Example:

The following example uses all optional parameters. Many fields have sizes declared, which defaults to 50 characters if ":size" is not given. This example also provides a double-quoted "display label" for each field, which is different from the input_field variable name, to show the proper syntax for each type of option. (If these quoted labels are not used for a field then the display label would be the same as the input_field name that immediately follows the quoted label.)

 <!--INSERTFORM TABLE=Employees, TEMPLATE="/httpfile/insert.odb",
     "First Name" 'firstname:24', "Last Name" 'lastname:24',
     "Home Address" 'address:50x2', "City, State, and Zip" 'csz',
     OPTIONLIST=("Department" dept from Departments),
     CHECKBOX=("Hourly?" 'hour', Y, N),
     "Salary or hourly rate" rate:8,
     HIDDEN=(uid, pwd) -->

 

<!--NOTE comment -->

The NOTE command may be used to add a comment to a template that will not be output to a browser when the template is processed. Ordinary HTML comments inside <!-- and --> markers that are not recognized as ODBiC commands are always copied to the output. The NOTE command simply ignores the enclosed comment.

Example:

   <!--NOTE Verify the user's ID and password before proceeding -->

 

<!--OPTIONLIST [size] [MULTIPLE] ["label"] column [AS var] FROM table [WHERE ...] -->
or
<!--OPTIONLIST [size] [MULTIPLE] ["label"] input_field = value1, value2, value3, ... -->

The OPTIONLIST command may be used to generate an HTML form "select option" input field from a given list of acceptable values or from a set of values selected from a database table with an SQL query. A "select option" list is a "pull-down" menu of choices Selecting the values from a "pull-down" list informs the user of the available choices and automatically prevents entering values that are not allowed.

This command should only be used when outputting an HTML form, and it should appear inside the <FORM> declaration, before the </FORM> (end of form).

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 (a number). (Note: Some browsers do not support the "size" option.) The default for "size" is one (1), which produces a single-line box with a "pull-down" arrow on the side, 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.)

In the first form of the command shown above, the options in the list will be generated by an automatic SQL query, which will be "SELECT DISTINCT column [AS var] FROM table [WHERE ...]". Each column value resulting from this query will be inserted as an <OPTION> value on the form being created.

In the second form of the command shown above, a specific list of <OPTION> values may be given directly, separated by commas. (The values do not need to be enclosed in double-quotes unless a value contains a comma.)

The generated HTML <SELECT> form field acts like an INPUT variable to the form's ACTION function. In the first form of the OPTIONLIST command shown above, which specifies an SQL query, the name of this input variable will be the same as the column name. (That is, the HTML tag will be <SELECT NAME="column">.) If necessary, you can use the SQL "AS" keyword to rename the result column, in which case the <SELECT> input variable name will be the "AS" renamed result column.

In the second form of the command shown above, the name of the input_field will be the name of the <SELECT> variable.

The value passed in the <FORM> input will be the user's highlighted selection. To the ACTION function (e.g, ODBiC processing a second template file), there will simply be a variable with the same name as the column or input_field, just as if the user had typed the value into a standard <INPUT> field with that name.

If you want to allow users to select multiple values for a given input, you can add the optional MULTIPLE keyword in the OPTIONLIST command before the column or input_field specification. This will generate an HTML <SELECT MULTIPLE> tag, and the user will be able to highlight more than one selection in the list. (Multiple selections are made by holding down the "Ctrl" key while clicking additional selections or by holding down the "Shift" key while clicking two selections, which selects everything in between the clicks.) In the template that processes the form input, you can use the EACHMULTI loop to process each selection.

Examples:

    <FORM METHOD="post" ACTION="/scripts/odbic.exe/your_dir/getproducts.odb" >
    Select category: <!--OPTIONLIST 10 Category from Products --> <BR>
    Select warehouse: <!--OPTIONLIST warehouse = Newark, St. Louis, Oakland --> <BR>
    <INPUT TYPE="submit" VALUE="Get Products">
    </FORM>
In the first command above, the optional "size" specification is given, which produces a scrolling window 10 lines high. The available Categories are selected from the Products database table. In the second OPTIONLIST, three "warehouse" input selections are given explicitly. In "template2.odb" (which is the designated target for the ODBiC ACTION function above), the user's selections will be variables named "Category" and "warehouse" (which are not case-sensitive). In that template, you might use the following form to give the user another choice list for the products in the selected category:
    <FORM METHOD="post" ACTION="/scripts/odbic.exe/your_dir/template3.odb" >
    Select product: <!--OPTIONLIST 25 Product from Products 
                    WHERE Category = '$category$' AND Warehouse = '$warehouse$' --> <BR>
    <INPUT TYPE="submit" VALUE="Get Product Description">
    </FORM>

If the name of the column to be selected contains a space, then the column name must be enclosed in double-quotes (e.g.,, OPTIONLIST "Employee Name") when it is used in this command. This column name will be quoted in the generated SQL statement, but in the name used for the HTML INPUT variable, spaces will be replaced by underscores (e.g.,, Employee_Name), so the target template must reference this input variable as $Employee_Name$.

 

<!--OUTPUT filename [APPEND | INSERT AFTER marker | INSERT BEFORE marker | REPLACE BETWEEN marker1, marker2] -->

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.) Warning: If you use the APPEND, INSERT, or REPLACE options, your Web server's user ID must have both "read" and "write" access to the existing 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.odb 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:

The OUTPUT command APPEND option is useful for creating log files of activity. For example, you might log $sql_statement$ after ever database operation initiated by FORM input, so that you could track all updates. Or you might log the full FORM input by using the SHOWINPUT command. Another use might be to allow users to add their e-mail addresses to a mailing list to be used with the SENDMAIL command. Here's an example of logging all the information from an input FORM:

    <!--OUTPUT \mydir\formlog.txt APPEND -->
    $today$ $time$
    <!--SHOWINPUT-->
    <!--OUTPUT-->
The following could be used to insert an entry into a "guestbook" which is an HTML file. Assume that there is a comment marker in the file, <!-- insert_messages_here -->, and that the input FORM has fields called "name", "email", and "message":
    <!--OUTPUT \mydir\guestbook.htm INSERT BEFORE insert_messages_here -->
    <HR>
    From: $name$ (<A HREF="mailto:$email$">$email$</A>) on $today$ at $time$ 
    <P> $message$
    <!--OUTPUT-->

 

<!--QBE TABLE=database_table, [SELECT=column(s),] [ORDER=column(s),] [ROWS=number,] numeric_field | 'text_field' | #date_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 form user, rather than the template designer, controls which columns are actually included in the selection criteria for any given query. 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 by the form user 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.) P> 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:

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 -->
The TABLE to be SELECTed FROM is "Employees". "EmployeeID" and "ReportsTo" are numeric data columns, and all of the rest are character data (as indicated by enclosing the column names in apostrophes). Since no SELECT=( ) option is given in the command, all database columns will be returned, and since no ORDER=( ) option is given, the rows will be in the table's default order.

 

<!--REDIRECT url -->

The REDIRECT command may be used to "transfer" the client browser to a different URL. To insure proper functioning with all browsers, the URLs used with the REDIRECT command should be full URLs, including the "http://www.domain.com/" at the beginning. Like all ODBiC commands, you can give a variable as the URL or as any part of the URL string (e.g, http://www.domain.com/$file$).

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.

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". If you get this link instead of the intended redirection, check your template for extra spaces before or after the previous command lines.

When a REDIRECT command is encountered, the remainder of the HTML template file is not processed.

Example:

The following code could be used at the beginning of a template to check for a "cookie" named "userID" and transfer to a login page if there is none.

    <!--IF NOT $userID$ -->
    <!--REDIRECT http://www.ourdomain.com/login.htm -->
    <!--ENDIF-->
    ...

 

<!--SEARCH TABLE=database_table, KEYWORDS=keywords, [SELECT=column(s),] [ORDER=columns,] [ROWS=number,] column_1 [,column_2, ...] -->

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 -->
Input examples:
    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 | TO=(column FROM table [WHERE ...] [,SUBJECT=subject]
[,CC=courtesy_copy_to] [,BCC=blind_copy_to]
[,ATTACH=([TYPE=mime_type] [,DISPOSITION=disp] file [,file,...])]
[,SUP] [,MIME] --> ... <!--ENDMAIL-->

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. (ISP's often have a special node for mail such as "mail.ispdomain.com".)

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.

The TO specification may also be an address file: Put an "@" in front of the file path and name, such as "TO=@\mydir\mailinglist.txt". (You must use the full file system path to the file, since Web-server root directory mapping will not be applied.) This file should be a standard text file with one e-mail address per line, or a comma-separated list of addresses on one or more lines. (NOTE: You can use the OUTPUT APPEND option to allow users to add an address to a mailing list from a Web form.)

The TO address or address list can also be selected from a database table. To use this option, put an SQL query (with "SELECT" implied, not given) with an optional WHERE clause inside parentheses: TO=(column FROM table [WHERE ...]). The message will be sent to each selected "column" e-mail address.

The SUBJECT specification, if given, will be sent as the subject line of the e-mail message. If this is omitted, the subject line will be "(No subject)". The subject may be enclosed in double-quote marks, "...", but it does not need to be enclosed unless it contains a comma (which separates option keywords).

The CC and BCC keywords may be used to specify "courtesy copy" and "blind courtesy copy" addresses. ("Blind" copy recipient addresses are not shown in the message received by "TO" and "CC" recipients.) Like TO, CC and BCC may be single addresses, multiple addresses separated by commas, or address files with the file name prefixed with "@". (Note: The SQL SELECT from a database table is not supported for CC and BCC.)

E-mail attachment files may be specified with the ATTACH keyword. A single attachment file may be given as "ATTACH=file", or a list of file may be given inside parentheses, separated by commas: "ATTACH=(file1,file2,...)". The TYPE keyword may be added before a file name to define a MIME content type, which some e-mail programs can use to know how to display the file (e.g., TYPE=image/gif for a GIF image file). The DISPOSITION keyword may be added to cause some e-mailer programs to do something with the file other than save it to disk (e.g, DISPOSITION=inline to show this attached file when the main message is viewed). (Note: If you use DISPOSITION=inline for an image file, you should also use a TYPE=image/(type) to specify the image type, such as "gif" or "jpeg".) TYPE and DISPOSITION are not required if the attached file is to be saved to the recipient's local disk, even if the file is any kind of binary file. (That is, you can attach images, programs, word processor documents, spreadsheets, etc., as well as ordinary text files, and they will be saved to the user's disk. The TYPE and DISPOSITION are only useful for e-mail programs that can display attachments directly.)

The SUP keyword may be added to suppress the TO list of addresses in the message that each recipient sees. That is, if you do not want recipients to see the address list, then adding SUP will cause the message header to say "TO: (Multiple Addresses Suppressed)".

The MIME option specifies that the e-mail message should use 8-bit MIME 1.0 encoding for special characters (i.e., characters above the standard 7-bit ASCII codes). This option allows for the ISO-8859-1 European character set. The default is that messages are not encoded and the specified character set will be "us-ascii".

Examples:

When used with the SHOWINPUT command, the SENDMAIL command might be used as a "form e-mailer". (SHOWINPUT simply outputs the list of variables input from a form.)

    <!--SENDMAIL SERVER=mail.my.com, FROM=$email$, TO=me@my.com, 
        SUBJECT=Web Page Form Input-->
    <!--SHOWINPUT-->
    <!--ENDMAIL-->
The following method would let you include customer-specific information from your database in an e-mail message (just the first name in this case, but it could be anything):
    <!--DATABASE DSN=MyDb -->
    <!--SQL SELECT FirstName, EmailAddr FROM Customers -->
    <!--EACHROW-->
    <!--SENDMAIL SERVER=mail.my.com, FROM=me@my.com, TO=$EmailAddr$, 
        SUBJECT=New Product Announcement-->
    Dear $FirstName$,

    Blah blah blah new product blah blah blah and on and on and on.

    Best regards,
    Me
    <!--ENDMAIL-->
    <!--ENDROW-->
If you just want to use e-mail addresses in your database, you might do something like this:
    <!--SENDMAIL SERVER=mail.my.com, FROM=me@my.com, TO=(email FROM customers),
         SUBJECT=New Product Announcement-->
    <!--INCLUDE \mydir\announce.txt -->
    <!--ENDMAIL-->

 

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

The SET command explicitly creates and assigns a value to a variable. Unlike the DEFAULT command, a SET variable will override any other definition for the variable. Also, you may use other variables and arithmetic expressions in SET commands, and they are evaluated at the point that the SET command is used.

Arithmetic expressions may use "+" for addition, "-" for subtraction, "*" (asterisk) for multiplication, or "/" for division. Parentheses, "(" and ")", may be used to indicate the order of evaluation (i.e., operations inside parentheses are performed first). A "unary" minus sign is allowed to indicate that a variable, constant, or expression inside parentheses is to be negated. You may also use any of the math functions and the string functions in the expression. You can use logical expressions in a SET command: A "true" value will be represented by a "1" and a "false" value will be represented by a "0". You may use these "1" and "0" logic values in arithmetic expressions, such as <!--SET $count$ = $count$ + ($type$ = A)-->. (A "1" will be added to $count$ only if the $type$ is "A", which saves using an IF/ENDIF statement around the SET.)

NOTE: If you use any variables in the "value" expression of a SET command, you must enclose the variable names in "$" characters. That is, the program does not assume that any part of the "value" is a variable.

If the string value contains any arithmetic operation characters that you don't want evaluated as arithmetic, then you must put the value inside double-quote (") marks. You must also use quotes if the value contains any commas, since commas are used to separate multiple variable specifications in the SET command. (The exception to this rule is that commas inside parentheses, e.g. commas separating function arguments, are recognized as being part of the expression for one variable.) If the actual text value contains any double-quote characters ("), you must use single-quotes (') around the string, such as '"value"'. Otherwise, quotes are not required, and any leading or trailing space characters will be ignored.

You can cause "string concatenation" by giving multiple variables in the "value", such as "var = $a$$b$" or "var = $a$ plus $b$". You may use quotes around any part of such a concatenated expression or around the entire expression. Variable references and arithmetic that are inside quotes, such as "$val$ + 1", will not be evaluated when the SET command sets the value. When the variable is actually used for output, however, any variables embedded in the SET expression will be replaced by their current value.

Since the target of a SET assignment is necessarily a variable, you do not need to enclose that variable name in "$" characters. (However, they will be ignored if you do.)

Examples:

    <!--SET count = $count$ + 1, done = Yes,
       value = ($number1$ + $number2$) * $price$, tax = $price$ * $rate$ -->

    <!--SET hypotenuse = sqrt($x$*$x$ + $y$*$y$), prefix = $left($string$,3) -->

    <!--SET total = "$eval($UnitPrice$ * $Quantity$)" -->
The last example above shows one way to use the $eval( ) string function. Because the expression is in quotes, it is not evaluated when the "total" variable is set. However, the quotes are not set as part of the value, so anywhere $total$ is used subsequently in the template, (for example, in a table produced by an EACHROW loop), the $eval( ) function will cause the expression to be evaluated, using the current values for $UnitPrice$ and $Quantity$, and the result will be inserted into the output.

 

<!--SETCOOKIE name=value [,EXPIRES=number] [,EXPIREDATE=date] [,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.

EXPIREDATE can be used to specify an explicit expiration date (instead of a retention period). The expiration date must be given in this format: "day, dd-mon-yyyy hh:mm:ss tz", where "day" is the three-character day-of-week abbreviation (e.g., Sun, Mon, Tue, ...), "dd" is the two-digit day-of-month, "mon" is the three-digit month abbreviation (e.g., Jan, Feb, Mar, ...), "yyyy" is the four-digit year, "hh:mm:ss" is the hours:minutes:seconds time-of-day, and "tz" is a time zone (which can be either "GMT" or something relative to GMT, such as "-7:00 MST" meaning 7 hours earlier than GMT, "Mountain Standard Time".

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.

There is no GETCOOKIE command. ODBiC will always check to see if any cookies have been sent by a browser, and if there are any, they will be set up as named variables, similar to form INPUT variables. In exchange for this convenience, you will need to be careful about naming cookies so that they don't conflict with form INPUT variables. To be more precise, cookies are treated exactly like DEFAULT values for variables, so if you do have an INPUT variable with the same name, the INPUT value will override the cookie value.

Example:

<HEAD>
<!--IF NOT $tracking_id$ -->
  <!--SQL SELECT max(tracking_id)+1 AS tracking_id FROM users -->
  <!--SQL INSERT INTO users (tracking_id) VALUES ($tracking_id$) -->
  <!--SETCOOKIE tracking_id = $tracking_id$, EXPIRES = 5y -->
<!--ENDIF-->
The IF statement first checks to see if there is already a cookie value passed in for "tracking_id". If not, the SQL SELECT will select the largest tracking_id currently in the "users" table, add one, and assign the result "AS" tracking_id. This number is inserted back into the table. The SETCOOKIE then sets this value in the user's browser. The cookie is set to be retained by the browser for 5 years. No domain or path is specified, so the cookie will be sent to any CGI program in the same domain. Now, any user actions anywhere on the site can be associated to this unique number. In ODBiC, just use the $tracking_id$ variable in any template. If the above code were on a registration page, then additional data might be inserted into the table. On other pages, the statement <!--IF NOT $tracking_id$--> could be used as a test for an unregistered user.

 

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

The SETMULTI works like the
SET command except that multiple occurrences of the variable are created (i.e. an array of values), one occurrence for each instance of a SETMULTI command. This command could be used in a loop (an EACHROW loop, for example), and the variables can be processed in a subsequent EACHMULTI loop.

You can specify a list of "variable=values" to be set in the SETMULTI command, separated by commas. You can then use the same list of variables in the EACHMULTI command, and the EACHMULTI loop will process the "parallel" rows of these variables. This allows you to save rows (or just certain columns) of data from an SQL SELECT and reprocess the rows with an EACHMULTI loop. (Keep in mind, however, that the memory available to a CGI program is not unlimited, so you should not attempt to save huge amounts of data this way. For large amounts of data it would be better to OUTPUT to a file, then IMPORT the file in a subsequent loop.)

 

<!--SHOWINPUT-->

The SHOWINPUT command lists out the name and value of all CGI-input variables. The format is "name: value", with one variable per line. This command is most useful when used with SENDMAIL to create a "form e-mailer" or with the OUTPUT APPEND option to log FORM input to a disk file.

 

<!--SQL sql_statement -->

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 -->
After this SQL statement is executed, the variable reference $item_number$ can be used to insert the first result for the column named "item_number" anywhere into the output, and variable $description$ would refer to the value of the column named "description" in the same result row. (See the
EACHROW command for formatting multiple result rows.)

ODBiC supports the keyword "AS" (upper or lowercase) which may be used to "rename" a result column. For example:

    <!--SQL SELECT item_nbr AS item, qty AS quantity FROM ... -->
After the above statement, the variables $item$ and $quantity$ would be used to reference the results instead of $item_nbr$ and $qty$. (Note that this feature is supported within ODBiC, so you may use AS even if your ODBC driver does not support it.) This feature is most useful (perhaps necessary) when an SQL calculated field is used, such as:
    <!--SQL SELECT (quantity * unit_price) AS total_price ... -->
In ODBiC, this column renaming is also useful when the TABLE command is used. That command uses the result column names as the HTML table headers, and you may wish to make the table headers less cryptic than the column names used in the database. Using the AS modifier for the SQL SELECT column names, you can specify the table headers that will be displayed in the HTML output. Renaming columns might also be useful or necessary to avoid conflict with other variables of the same name.

One non-obvious caution about column names used in SQL statements: Some databases (such as MS Access) allow spaces in column names. (This is why ODBiC allows spaces in variable names, even though this author discourages the practice.) Remember to enclose such column names inside double-quote marks (") when you use them in SQL statements. (ODBiC-generated SQL statements, such as the QBE query and UPDATEFORM, will also have double-quotes around any column names that contain spaces.)

Variables can be used anywhere within SQL statements. The most common usage would be as column values, such as in the WHERE clause of a SELECT statement or the SET clause of an UPDATE statement. (Remember, however, to enclose variable references inside apostrophes for any character-data values, such as "... WHERE name = '$name$' ...".) But you might also use a variable for a table name or column name. In fact, the entire SQL statement can be referenced as a variable, and that variable may also contain variable references. This is useful when you want to pass SQL statements in from forms.

For example, ODBiC can be used without an HTML template file. To do so, it is necessary to pass in a CGI variable named "sql" containing an SQL statement to execute. If there is no template file, ODBiC automatically executes the equivalent of a command: <!--SQL $sql$ -->. However, in a template file, you can specify any variable for the SQL command, such as <!--SQL $user_statement$ -->.

After each SQL statement, ODBiC sets several internally maintained variables that you may access. One of these is $row$, which is set to 0 if there are no rows returned by the SQL statement, or is set to 1 if at least one result row has been "fetched". Therefore, you can test the $row$ variable after a SELECT to see if any results were returned. For example:

    <!--SQL SELECT item, description FROM items -->
    <!--IF $row$ = 0 -->
      $sql_error$
    <!--ELSE-->
      . . . (format results, e.g., with an <!--EACHROW--> command)
    <!--ENDIF-->
The $sql_error$ variable used in this example is another ODBiC-defined variable which is set for each SQL statement. After a SELECT, $sql_error$ may say "No rows selected" if the statement was valid but no data met the selection criteria. If the ODBC driver has any problem executing the SQL statement, then variable $sql_error$ will contain the error message returned by the driver (e.g. the connection failed, the SQL syntax is incorrect, etc.) The IF statement above would cause the $sql_error$ message to be output whenever there were no rows returned, whatever the reason, and the lines following the ELSE would be used to format any normal result rows.

Another ODBiC variable set after an SQL statement is $rows_affected$, but it takes on a meaningful value only for UPDATE, INSERT, and DELETE statements. Again, for these statements, $sql_error$ is set if there is an error, but $sql_error$ is also set to "### rows affected" for successful statements, where the ### will be equal to $rows_affected$. (This means that, unless you want to do some special error handling, you might always just print out $sql_error$ after an UPDATE, INSERT, or DELETE statement, without testing $rows_affected$ for being 0. Remember that for UPDATE and DELETE, $rows_affected$ might be 0 even if the statement was valid, because no rows met the WHERE clause criteria.)

There is a status variable that can be used to determine directly the success of the SQL operation, but its interpretation depends on the type of statement. The variable is $sql_status$, and its possible values are:

For example, immediately after a SELECT, a "0" status will mean that at least one row has been selected, whereas a "-2" means that there were no rows that met the WHERE clause selection criteria. However, after any of the result-looping commands (TABLE, EACHROW, UPDATEFORM, and OPTIONLIST), the status will always be "-2" because those commands loop through the results until there are no more rows. A status of "1" is the normal return for a successful INSERT, UPDATE, or DELETE, and a "-2" status would indicate that no rows met the WHERE clause selection criteria. A "-1" status always means that there was some error encountered by the ODBC driver in executing the SQL statement.

The SQL statement, which must be terminated with the "-->" marker, can span multiple lines up to a maximum size of 8K bytes (which is about 100 full 80-character lines). Also, there is a limit of 8K bytes for any column returned by a SQL SELECT statement, and memo-type columns that are longer than that will be truncated.

 

<!--TABLE [html_table_opts] -->

The TABLE command automatically formats all columns and all rows from the previous query into an HTML table. This formatting uses the HTML <TABLE>, <TR>, and <TD> commands. The database column names (or the "AS" renamed columns; see the example in the SQL command) are used as the column headers. The table headers are formatted with HTML <TH> commands. For improved appearance and readability, ODBiC capitalizes the first letter of the column headers, converts any underscore characters to spaces, and capitalizes any letter following an underscore. Thus, the database column "customer_name" would have a table header of "Customer Name".

The html_table_opts specifications are the same as the HTML <TABLE> optional elements. That is, whatever you put for html_table_opts will simply be included in the generated HTML <TABLE> command. For most HTML 2.0 browsers, these options can include BORDER, CELLSPACING, and CELLPADDING. Most newer browsers also allow the BGCOLOR command. In each case, use the standard HTML specification format.

Since the TABLE command always loops through all of the result rows, you should not use TABLE combined with any of the other result looping commands, EACHROW, UPDATEFORM, and OPTIONLIST.

Example:

    <!--SQL SELECT * FROM Products -->
    <!--TABLE BORDER=1 CELLPADDING=5 -->

 

<!--TRANSLATE variable value=newvalue [, value=newvalue, ...] -->

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"'.

You may define up to 32 different translation tables (i.e., tables for 32 different variables), and each table may have up to 100 translation value pairs. You may also redefine a translation table for a variable that was previously used in the template file. (The translation is in effect from the point of the TRANSLATE command until another TRANSLATE is given for the same variable.) And you may define a translation "newvalue" that is another variable (for example, "<!--TRANSLATE var1 value1=$var2$, ... -->").

Example:

    <!--TRANSLATE state 1=open, 2=closed, 3=pending, 4=unknown -->

 

<!--UPDATEFORM [TEMPLATE=html_template,] TABLE=database_table, KEY=key_field, [SUBMIT=text,]
["label"] numeric_field[:size] |
["label"] 'text_field[:size]' |
["label"] #text_field[:size]#,
[,OPTIONLIST=()] [,CHECKBOX=()] [,HIDDEN=()] [,...] -->

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.

If given, the optional "TEMPLATE=..." specification will be included on the form as a "hidden" field to tell ODBiC what HTML template file to use when the form is submitted. (However, a template file is not necessarily required; see "Using ODBiC Without a Template File".) The form will also include a "hidden" HTML field to specify the database connection to use for the update, which will be the same as the database in effect when the UPDATEFORM command is used. (If necessary, use a DATABASE command immediately before the UPDATEFORM command, but if you have just selected data from a database, then that same database will be used for the update form.) If you do use the "TEMPLATE" keyword to define an HTML template file to process the UPDATE statement, then that template will need to reference two of the predefined variables: The database string will be passed in with the name "database" and the generated SQL UPDATE statement will be passed in as "sql". Therefore, the update can be performed with these statements:

    <!--DATABASE $database$ -->
    <!--SQL $sql$ -->
(Anytime that you do not specify a template file, ODBiC will expect the variables "database" and "sql" to be passed in, and it will effectively execute the above code. This is why you do not necessarily need to have a template file to execute the UPDATEFORM output.)

The "TABLE=..." specification for this command is required. It specifies the database table that the generated SQL statement will update.

One or more "KEY=..." specifications are also required. A single key column can be specified as "KEY=col" and multiple key columns can be specified inside parentheses, "KEY=(col_1, col_2, ...)". These columns will be included on the generated form, but they will be shown simply as text, not as modifiable fields. They will also be used in the WHERE clause of the generated SQL (i.e., "UPDATE database_table SET ... WHERE key_field=(current value), ...").

IMPORTANT NOTE: You should specify KEY columns that will uniquely identify the row. All rows that satisfy the WHERE clause will be updated!

The optional SUBMIT parameter can be used to define the text to be used for the form's "submit" button. The default is "Update".

Following the KEY field or fields, one or more modifiable data fields may be specified. These are included on the form with their current values filled in, and the value may be changed by the user. The field names given in the UPDATEFORM list will be used as the HTML names for the generated <INPUT> fields, and they must be the same as the database column names (see below). Moreover, they should be currently-defined variable names in ODBiC, or they will not have any "current value" on the update form. Each of these fields will be included as SET column specifications in the generated SQL statement. The complete SQL statement would therefore be: "UPDATE database_table SET field=$field$, ... WHERE key_field=(current value), ...". When the user has made changes to any fields and submits the form, ODBiC will insert the new values from the update form into this SQL statement and then execute it. (This is the reason that the database column name, the ODBiC variable name, and form variable name must all be the same for any given field.)

By default, the database column name is also displayed on the form, immediately in front of the input box, to identify the data. For improved appearance and readability, ODBiC capitalizes the first letter of this "label", converts any underscore characters to spaces, and capitalizes any letter following an underscore. For example, "customer_name" would have a label of "Customer Name".

If you want to have a different input box label, something other than the database column name, you can specify a label in double-quotes immediately in front of the column name. Note: You must have a space, not a comma, between the quoted label and the input_field name.

NOTE: There are several considerations for specifying the key fields and update fields:

You can control the size of each update <INPUT> field by using the optional ":size" specification after the field name in the UPDATEFORM list. If no size is specified, ODBiC uses a default size of 50 for text fields and 12 for numeric fields. If you give a field size larger than 99, then ODBiC automatically uses an HTML <TEXTAREA> input, which is a multiple-line scrolling window. This window will be at most 64 characters wide and as many lines as it takes to hold your specified field size. (For example, a specified size of 250 would produce a 50-character, 5-line textarea window.) However, you can directly specify the size of a <TEXTAREA> by giving the "size" specification as two numbers separated by an "x" (for example, 'Description:64x4'), where the two numbers are to be the number of columns and the number of rows. (The numbers can be given in either order; the larger number will always be used as the field width and the smaller number will be the number of lines.)

Anywhere in the list of input_fields for this command, you may specify "OPTIONLIST=(...)", and the arguments inside the parentheses can be the same as the OPTIONLIST command. Specifically, you can have "OPTIONLIST=(column from table)" to select the options from the database, or you can give a comma-separated list of literal values in the form of "OPTIONLIST = (input_field = value1, value2, ...)". In either case, if the given database column is already defined as an ODBiC variable and it has a current value that is in the list of options shown by this command (which should always be the case for columns used in UPDATEFORM), then that option will be "SELECTED" in the list. (That is, the current value will already be highlighted in the list the user sees).

You can also specify a CHECKBOX variable in the FORM command. Again, this may be anywhere in the list of database columns, and the format is "CHECKBOX=(column, checked_val, unchecked_val)". The "checked_val" will the variable's value if the user checks the box; otherwise the variable will have the "unchecked_val". Similar to the OPTIONLIST, if the specified column is already defined as a variable and it has a current value equal to the "checked_val", then the user will see the box as already checked. Otherwise it will be unchecked. (NOTE: Browser's only send a value if a checkbox is checked, and send nothing for that variable if the box is unchecked. Therefore, the "unchecked_val" is passed to the next template in a hidden variable named "default" -- one of the predefined input variables that ODBiC always processes -- with a value of "column=unchecked_val". Like a DEFAULT statement in a template, this value will be used if the user doesn't check the box. Therefore, if you use the FORM CHECKBOX with your own template, don't specify a DEFAULT for the checkbox variable in the next template.)

NOTE: If you use an UPDATEFORM command after a SELECT statement that returns several result rows, you will automatically get a separate update form for each result row. Each of these forms will have its own SQL UPDATE statement and "submit" button, but the user will only be able to update one row at a time. (The user could, however, use the browser "Back" button to go back and update another row.)

Since the UPDATEFORM command always loops through all of the result rows, you should not use UPDATEFORM combined with any of the other result looping commands, EACHROW, TABLE, and OPTIONLIST.

Example:

    <!--UPDATEFORM TEMPLATE=/htmlroot/your_dir/upd_empl.odb, TABLE=Employees, 
       KEY=EmployeeID, 'LastName:20', 'FirstName:10', 'Title:30', 'BirthDate:9',
       'HireDate:9', 'Address:60', 'City:15', 'Region:15', ReportsTo:8 -->
The "TEMPLATE=" specifies the template file for ODBiC to use when the form is submitted. The "TABLE=" declares that the database table to be updated is "Employees". The "KEY=" specifies that "EmployeeID" is the record unique key field, which is a numeric field. (The KEY fields may not be updated on the form.) All of the following fields can be updated. Except for "ReportsTo", all of these fields are character data rather than numeric (as indicated by enclosing the column names in apostrophes). The update form INPUT field width for each is shown after the colon. For more examples, specifically the use of labels, OPTIONLIST, CHECKBOX, and HIDDEN, please refer to the examples for FORM or INSERTFORM.

 

<!--VALIDATE variable [= pattern], .. -->

This command validates the given variable against a UNIX-style "regular expression" pattern. If no pattern is specified, this command simply checks that the variable has some value (i.e., that the form user has not left the field empty).

This command can be used to validate form input. Each validation failure causes an error message to be output to the browser, and if any errors are detected, the user will be asked to return to the form and correct them. If there are any validation errors, the remainder of the template is not processed.

If a pattern match fails, the message will be "<entered data> is not a valid entry for <field name>" (with the user's actual data and the field name inserted).

If no pattern is specified, then the validation is simply a check for a required field. If the variable has no value, the error message will be "<field name> must be entered."

You can give a list of variables to be validated in this command, separated by commas. You could also use this command once for each variable to be validated, but it is better to specify all validations in the same command because all of the validations will checked before telling the user to go back and make corrections. Therefore, there will be an error message for each validation failure, and the user can correct all the errors at one time rather than re-submitting the form only to get another error message.

This command internally uses the functionality of the match( ) function. The match( ) function can be used in an IF statement if you want to validate input values yourself (for example if you want to handle errors differently).

The patterns given in the VALIDATE command or the match( ) function can use the "or" pattern separator (the "|" character) to specify multiple patterns that might be matched, so you can allow several different formats for a field.

The "$" (the "end of value" meta-character) can be used as a single-character pattern to match an empty input variable, so you can add that to a pattern if it's valid to omit that input. For example, if a certain input were optional but, if given, needed to be exactly four digits, you could use a VALIDATE pattern of "$|[0-9]{4}", which would mean "immediate end of value (i.e., empty), or four digits".

Example:

  <!--VALIDATE phone="^(?[0-9]{3})?[- ]?[0-9]{3}[- ]?[0-9]{4}$",
               eamil="[a-zA-Z0-9].+@[a-zA-Z0-9].+\.[a-zA-Z0-9].+" -->

 

<!--WHILE condition --> ... <!--ENDWHILE-->

All the code between the WHILE and the ENDWHILE will be repeated as long as the given condition is true. The condition can be any test allowed in an
IF statement. The code is not executed at all if the condition is already false. To avoid a "run-away" CGI program, the loop will automatically terminate after 1000 iterations.

Example:

  <!--SET i = 1 -->
  <!--WHILE $i$ <= 10 -->
    [<A HREF="page$i$.htm">$i$</A>]
    <!--SET i = $i$ + 1 -->
  <!--ENDWHILE-->


Next Chapter: String Functions


Copyright ©1997-1999, Roger Harris. All rights reserved.