Open DataBase Scripting Language

---

Version 3

    <!--SET x=1 -->
    <!--IF $y$ > 10 -->
        <!--SET x=2 -->
    <!--ENDIF-->

Beginning with release 2.1, there is an alternate method for marking commands which is more similar to other scripting languages such as ASP and JSP, and which can make your scripts easier to type and to read. The beginning of a command statement can be marked with the <% symbol, and spaces and tabs are ignored between that symbol and the command. The end of the command is marked with the %> symbol. If the next script text or line is also a command, you can use a semi-colon (";") to mark the end of one command and the beginning of another. With this mode (but not the HTML comment-style commands), the SET command is optional; that is, an "implicit SET" is assumed if a command in the form of "variable=value" is recognized. (See SET command.) Here is an example of commands using the "script tag" style:

    <% x=1;
       IF $y$ > 10;
           x=2;
       ENDIF %>

    <%
        x=1;
        IF $y$ > 10;
            x=2;
        ENDIF;
    %>

The above code could also be written on a single line (where the spaces are optional, but make the line more readable):

    <% x=1; IF $y$ > 10; x=2; ENDIF %>

"Code blocks" such as loops and IF / ELSE conditional sections, do not need to be begun and completed inside the same <% and %> "script tags". That is, you can mix the beginning and ending of these sections with ordinary text and HTML output, like this:

    <%
        x=1;
        IF $y$ > 10;
            x=2;
    %>
            <B> Y is greater than 10, so X has been set to 2. </B>

    <% ENDIF %>

The remainder of this User's Guide will use the "script tag" style for marking commands, but in all cases the "HTML comment" style will also work. All future releases will continue to recognize the "comment" style, so you do not need to convert any existing scripts. The two styles can be mixed in the same script, but you must be consistent for each individual command. That is, a command that begins with "<!--" must end with a "-->", and any command or series of commands that begin with <% must be terminated with %>.

NOTE: Any comments that are not recognized as ODBscript 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. (You must use your browser's "View Source" option to see these unrecognized commands, since the browser will not show HTML comments in the normal display. Viewing the script's output with the browser's "View Source" function is an important "debugging" technique.)

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

    <% SELECT ...  ;
       EACHROW;
         IF $row$ > 50;
           BREAK;        note: quit processing if more than 50 rows;
         ENDIF;
         ...
      ENDROW %>

You must have a DATABASE statement in your HTML script 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 ODBscript 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$" %>

On Windows 98 and NT systems, you can bypass all DSN database associations by giving a complete ODBC connection specification in the DATABASE statement. (Important note: The following method for using "DSN-less connections" will not generally work on Windows 2000 and XP systems. This mode requires creating registry keys, and in the default Win2000 and XP configurations, the user ID that CGIs run under do not have permission to create registry keys, so you must use DSNs.) This connection specification 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" %>

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"

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

The following code will produce a listing of all HTML file ("*.htm" and "*.html" file name extensions) on the same directory as the current script (which is set in the predefined variable $path_translated_dir$), and display each file as a clickable link (which must be a link to the server-mapped directory set in $path_info_dir$, i.e., the same specification given in the URL for the script):

    <% EACHFILE $path_translated_dir$/*.htm* %>
      <A HREF="$path_info_dir$/$file_name$"> $file_name$ </A> <BR>
    <% ENDFILE %>

(Note that there is also a SHOWINPUT command that will simply output all input CGI variables in the format of "name: value", which may be all you need for debugging or for a simple "form mailer".)

Example:

    <% EACHINPUT %>
    <P> Variable <B>$input_variable$</b> has the value "$input_value$".
    <% ENDINPUT %>

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

Important note: In the EACHMULTI declaration line itself, do not enclose the variable names inside of "$" characters:

    Right:  <% EACHMULTI var1, var2 %>

    WRONG!  <% EACHMULTI $var1$, $var2$ %>  Don't use $ characters!

Inside the EACHMULTI loop, when you reference the variables, you do need to use the "$" characters around their names as you normally would, but you must use just the names in the EACHMULTI declaration statement.

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 ODBscript 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;
       INSERT INTO Subscribe (mailing_list, email)
             VALUES ('$mailing_list$', '$email$');
    ENDMULTI %>

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

Example:

  <% 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 %>

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 ODBscript, so no ODBscript 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 script. 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/odb -i/httpfile/$script$ -o/httpfile/$output$ %>

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

By default, the FORM command puts ACTION="odb.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 to execute a script on your site, you can use a "relative URL" such as ACTION="/cgi-bin/program.exe". Otherwise, specify a full URL beginning with "http://..." and the domain name.

The SCRIPT option lets you specify the file that ODBscript (or another CGI, if it uses the server variable PATH_INFO) is to execute. In the SCRIPT parameter, specify only the path to the script file itself; do not include the path to the odb.exe CGI (which will be added automatically). Whatever you specify here will simply be added to the ACTION URL, so the SCRIPT parameter is primarily intended for cases where you are using the default, ODBscript. (Otherwise, you could just put the full specification for both the CGI and the extra PATH_INFO in the ACTION parameter.) You must specify either the ACTION or the SCRIPT parameter to use this command.

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

The optional TARGET parameter can be used to define an HTML "target" label for the form submission result, which can be a frame name or a window name. (Note: If your form is already in a frame, the default behavior is to open the submission in the same frame, so you don't need the TARGET parameter unless you want the form submission result in a different frame or window.) For example, TARGET="_blank" can be used to open the result page in a new window.

The optional TRACE keyword will enable the ODBscript trace function when the form is submitted to the script.

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.

Following any of these optional parameters that you wish to use, 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, ODBscript uses a default size of 50 characters. If you give a field size larger than 99, then ODBscript 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, ODBscript 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 ODBscript 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 script in a hidden variable named "default" -- one of the predefined input variables that ODBscript always processes -- with a value of "input_field=unchecked_val". Like a DEFAULT statement in a script, this value will be used if the user doesn't check the box. Therefore, if you use the FORM CHECKBOX with your own script, don't specify a DEFAULT for the checkbox variable in the next script.)

The PASSWORD keyword can be used to define a TYPE="password" input field. Browsers do not display the values typed into "password" fields; instead, they show an asterisk (*) for each character typed. Other than this special processing in the browser, a password field is like any other text input field.

Example:

The following example uses all optional parameters except ACTION (which defaults to odb.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 SCRIPT="/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) %>

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

Date formats are explained below. For numeric and ordinary text values, 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 ($), the pound sign character (£), or any currency symbol set using SETOPTION currency 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 the currency symbol to be added to the front of a numeric value. You can set an alternate currency character using the SETOPTION CURRENCY="character" command.

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.

By default, format masks expect that the period character "." is used in masks to denote a decimal point (i.e. the separator between the whole-number digits and the fractional digits). To use another character as the decimal point marker, such as a comma, you can use the SETOPTION DECIMAL="," command. If you set that, then you can use the period character as the thousands-separator, such as "#.###.##0,00". (Actually, you can use any character except the current decimal point character as the thousands-separator, so you could use a mask such as "# ### ###" to have spaces separating each three-digit group.)

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

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.

Date format masks are different from numeric and ordinary text format masks. A date format mask should not have any "#" characters. Instead, you can use "y", "m", and "d" in various ways to specify year, month, and day, respectively. All other characters in the mask (such as spaces, commas, dashes, or slashes) are simply copied into the output. When date formatting is specified, the input value to be formatted is an ordinary character string, but it must be recognizable as a valid date by the following rules:

• The year, month and day must be separated from each other by any combination of spaces, commas ",", slashes "/", dots ".", or dashes "-".
• If the input fields are all numeric values, then they are assumed to be in month-day-year order, unless the SETOPTION DATE="..." command has set some other order.
• Month names or abbreviations are recognized. English month names are assumed unless the SETOPTION MONTHS="..." command has set some other list of names.
• If a month name is recognized where a day was expected according to the current DATE="..." order, then a valid format will be assumed by simply switching the day and the month fields. (For example, the default DATE order is "mdy", but "05-JAN-00" will be recognized as if it were "January 5, 2000" or "01/05/2000", in "mdy" order.)
• Two-digit years are assumed to be in the range of 1970 to 2069.
• The month must be between 1 and 12, and the day must be within the valid range for that month (with leap-year checking for February).

If the input value is recognized as a date, then a format mask simply allows you to reformat the date. In a date mask, the single characters "y", "m" and "d" mean to output the numeric value of the year, month, and day, respectively. A single-digit month or day will be output as a single digit. For example, with a mask of "m/d/y", the date March 5, 2001 would be formatted as "3/5/2001". Alternatively, you can use "yy", "mm" or "dd" to force a two-digit result, with a leading zero added if necessary. That is, the mask "mm/dd/yy" would output "03/05/01" for March 5, 2001. Two-digit years are assumed to be in the range of 1970 to 2069.

For months only, there are two more options: "mmm" means a three-character abbreviation of the month name, and "mmmm" means the full month name. When you use these options, you can also control the character-case of the output by specify either "M" or "m" for each character of the mask. For example, the mask "Mmm" means that you want to capitalize just the first character of the three-character month abbreviation, such as Jan, Feb, etc. "Mmmm" would mean that the first character of the full month name should be capitalized, such as January. "MMM" or "MMMM" would mean that you want the abbreviation or full month name all upper-case letters (JAN or JANUARY). Lower-case "mmm" or "mmmm" would output only lower-case month abbreviations or names.

A special option in format masks is to use the "@" character to simply insert the value being formatted. That is, any "@" characters in the mask will be replaced by the entire value to be formatted. For example, suppose that you have a database column named Email that is being displayed in a result table and you want to make it a "clickable mailto" link. You can do that with format command like this at the top of the script:

    <% FORMAT Email="<a href='mailto:@'>@</a>" %>

This type of format mask also allows function calls where the "@" is one of the function arguments. For example, suppose you have a field or database column called Name, containing both first and last name, and you want to insure that whenever it's used, it's displayed with an uppercase initial character for each name and the rest of the characters in lower case. You can do that with a $wcase( ) function in the format mask:

    <% FORMAT Name="$wcase(@)" %>

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 (or the script-tag markers ";" or "%>") after the last variable on the last line.

<% FUNCTION name ( [arg_variable, ...] ) [local_variable, ...] %>
...
<% RETURN [expression] %>

All of the commands and text between the <% FUNCTION ...%> statement and the <% RETURN %> statement will be executed each time the function is called from the script. The function can directly output text and HTML, much like an included file, but the RETURN statement can also specify an expression that is evaluated to produce a character-string value that is returned as the "value of the function". This returned value allows user string functions to be used in variable expressions such as those allowed in IF and SET statements, or as arguments for other functions: the value of the RETURN expression is inserted into the referencing expression. Like the built-in string functions, if you call a user string function in ordinary text or HTML output, then the evaluated RETURN expression (as well as any direct output produced by the function) is simply inserted into the output at the point that the function is called. Like other string functions, you can use the returned value of a user function in an arithmetic expression, provided that the returned string is a valid number.

The "arg_variable" or comma-separated list of arg_variables defined in the FUNCTION command must be enclosed within "(" and ")" characters. These variable names are used within the function body to reference the values passed when the function is called. For example, if you define a function such as <% FUNCTION myFunc (arg) %> and then call the function from the script with a reference such as $myFunc(123), then inside the function body, the variable $arg$ will have the value "123". Like any other function, the arguments passed to the function can be any combination of variables, literal character strings, or arithmetic/logical expressions. Each argument expression in the call from the script will be fully evaluated and the result will be assigned to a corresponding argument variable in the function definition. Those values can then referred to by those variable names within the function. These function argument variables are optional and can be omitted from the FUNCTION declaration, but when the function is called from a script, the parentheses are required even if no arguments are passed. That is, if the function does not require any arguments, call it with a reference such as $name( ). In that case, you can also use an empty set of parentheses in the function definition, such as "FUNCTION name ( )" -- and you must do so if you also have a list of "local" variables as explained below -- but if the function does not require any arguments or local variables, the empty "( )" is optional in the function definition.

Important note: In the FUNCTION declaration line itself, do not prefix the function name with a "$" character, and do not enclose the argument variable names or local variable names inside of "$" characters:

    Right:  <% FUNCTION name (arg1, arg2) %>

    WRONG!  <% FUNCTION $name ($arg1$, $arg2$) %>   Don't use $ characters!

When you use the function in the script, you will need the "$" name prefix, and when you use the function arguments in the function body, you will need the "$" characters around the names, but these must not be used in the FUNCTION declaration statement.

Important note: The number of arguments passed when a function is called from the script must always exactly match the number of arguments defined within the "(" and ")" characters in the FUNCTION declaration statement. (If you don't necessarily need a particular argument for a particular call, you can pass a zero-length string, "", as an argument, and the function can test whether any argument is empty in the normal manner, e.g., <% IF $arg$ %>.)

Following the list of argument variables enclosed in "(" and ")" characters, you can optionally specify a comma-separated list of "local" variables. These are variables that will be used within the function but which should not conflict with any variables that may have the same names outside of the function, and which should not retain any values outside of the function after the call is completed. That is, these variables will be strictly local within the function, with no effect outside the function. This feature is provided because ordinarily, in addition to the passed arguments, functions also have access to all of the variables used elsewhere in the script, and any variables set or created within the function are also accessible from the script after the function is called. However, it is generally considered poor programming practice to set externally-used variables within a function or to use variables in addition to those passed as arguments. You can do those things if you chose to, because functions do have access to the full set of variables in the script, but doing so will limit the generality of the function, and it can also create some hard-to-find bugs if the wrong variables are inadvertantly altered within a function. Specifying the list of local variables will insure that no such problems occur, whether or not variables with the same names exist outside of the function. Temporary variables will be set up for these local variables, initialized to be empty strings, and you can set them within the function like any other variable. But any variables outside the function that happen to have the same names are unaffected, and these temporary variables are deleted when the function completes.

A function does not necessarily need to have any statements in its body (i.e., between the FUNCTION and RETURN statements), if all the required work of the function can be performed by the RETURN expression itself. Therefore, a function can be used simply as a "shorthand" for a long, complicated expression that is specified completely in the RETURN statement. This is useful if the function simply performs a calculation using the passed arguments, or just reformats the arguments in some special manner.

A function can call itself "recursively". That is, the function body can use a reference to the same function. However, you should take some care that the recursion has a specific limit, or ODBscript will quickly use up all available memory and terminate abnormally. For example, you might define a function that outputs a directory listing using the EACHFILE command. This command will only list the files at one level of the directory hierarchy. However, if a file is a subdirectory (which is indicated by setting $file_is_dir$ to "1" or "true"), you could have the function call itself recursively to list the files in that subdirectory. (In this case, the recursion will be limited automatically by the depth of the directory tree.)

<% HEADER http-header-type: value %>

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>

    <% HTTPGET somedomain.com/scripts/any.cgi, var1, var2 %>

    <% HTTPGET somedomain.com/scripts/any.cgi?var1=$url($var1$)&var2=$url($var2$) %>

The response to the request (i.e., the first header returned by the request) will be stored in stored in a predefined variable named $http_response$. This request response contains a 3-digit status code and usually a text decription. A normal, successful request will have a response of "200 OK".

All other headers returned by the request are stored as a single value in the predefined variable named $http_headers$, with their original "new line" character separators. If you need to process these headers individually, you may use the $split( ) function in a loop like this:

    <% WHILE $http_headers$ ;
       a_header = $split($http_headers$, $asc(10)) %>
       .... (process $a_header$)
    <% ENDWHILE %>

(See also the string functions $httpGet( ) and $httpPost( ), which are similar to these commands except that the response can be assigned to a variable. The limitations of these string functions, however, are that the maximum response size must fit in a variable, which is 8 KB in the standard version or 32 KB in the special "large variable" version, and also that the response must be ASCII text or HTML only -- no image files, for example -- because a binary 0 will terminate a character string.)

If you need to simply output the response to the user's browser, these commands should be used instead of the string functions. If you need to process the response in any way, you can either use the $httpGet( ) or $httpPost( ) functions to assign the entire response to a single variable (subject to the restrictions stated above), or if the size restriction prevents that, you can use the OUTPUT to open a file immediately before the HTTPGET or HTTPPOST command, then another OUTPUT command with no file specified to close the file (and reset to writting to the browser). Then you can use the IMPORT command to read the file back into a variable, line by line. (Note that this method still has the restriction that the response must be ASCII text or HTML, because the IMPORT command will not work properly for binary files, since lines are read into character string variables.) To insure that multiple simultaneous request do not interfere with each other, you should use the $newFileName( ) function to get a unique file name to use for the output. For example:

    <% responseFile = $newFileName("/temp") ;
       OUTPUT $responseFile$ ;
       HTTPGET www.anotherdomain.com/scripts/some.cgi, var1, var2 ;
       OUTPUT ;    note: this closes output file;
       IMPORT line from $responseFile$ %>
        ... (loop to process the response data in $line$, one line at a time)
    <% ENDIMPORT ;
       status = deleteFile($responseFile$) %>

The only difference between using this command and the HTTPGET command above is that with this command you should always specify a list of variables to be passed, and you usually should not use a query string in the URL. (Actually, many Web servers will accept a query string in a "post" URL and will combine the two sets of variables, but you might run into compatibility issues some day if you depend on that.) See the HTTPGET command above for details and examples.

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

"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 numeric 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. Four of the numeric functions are intended to be used in this way to validate input data: isNumber( ), isAlpha( ), isAlphaNum( ), and isCreditCard( ). 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 %>

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 "-->" (or the script-tag markers ";" or "%>") must mark the end of the condition.

<% IFNEW variable %>

    <% SELECT category, item_number, description FROM item ORDER BY category ;
       EACHROW;
          IFNEW category %>
             <H1> $category$ </H1>
          <% ENDIF %>
          <P> $item_number$ $description$
    <% ENDROW %>

    <% 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 %>

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 [DELIMITER="chars",][QUOTED,][SKIP=n,] variable_list FROM file %>
...
<% ENDIMPORT %>

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

  <% IMPORT DELIMITER=",", QUOTED, SKIP=1, field1, field2, field2 FROM /data/data.csv %>
     ... (any processing of $field1$, $field2$, and $field3$ from a single line)
  %lt;$ ENDIMPORT %>

  <% IMPORT DELIMITER=",", QUOTED, * FROM /data/data.csv %>
     ... (any processing of $field1$, $field2$, and $field3$ from a single line)
  %lt;$ ENDIMPORT %>

NOTE: The referenced filename must have the full file system 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. Please read the NOTE for the following INCLUDE command for more details.

<% INCLUDE filename [variable=value, ...] %>

The INCLUDE command will read and process the specified file. The INCLUDE file can contain any HTML and ODBscript commands, and it is processed as if the text from that file were "pasted" into the script file at the point of the INCLUDE command. (If you need to output a file without any processing, use the RETURNFILE command.) INCLUDED files may also INCLUDE additional files.

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. It may also be used to define "subroutines" that are used more than once in a script.

NOTE: The referenced "filename" must have the full file system 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 given, then the file is assumed to be on the "current directory", which is set by the Web server before executing the CGI. Unfortunately, different servers set this differently, e.g. some set it to the Web site root and some set it to the CGI program subdirectory. You can, however, generate a file system path that's on, or relative to, the current script's subdirectory using the $pathTranslated(filename) string function. This function allows using "../" to represent the "parent" directory of the script subdirectory (e.g. "../subdir/filename" would refer to a file on a different subdirectory under the same parent directory), or "subdir/filename" (without a leading "/") to represent a subdirectory of the current script directory. Using this function makes it easier to move scripts around, as long as the subdirectory structure remains the same, because you don't need to "hard code" the full file system path of the script directory.

If the file name or directory path contains any spaces, then the file name must be enclosed inside double-quote marks (").

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 %>

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

Note that before using the INSERTFORM command, you must have a DATABASE command. This is because the form HTML will also include a TYPE="hidden" encrypted input field to specify the database connection to use for the INSERT statement, 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 INSERTFORM command.

If given, the optional "SCRIPT=..." specification will be included on the form as a "hidden" field to tell ODBscript what script file to use when the form is submitted. (This is not required; see below.) In the SCRIPT parameter, specify only the path to the script file itself; do not include the path to the odb.exe CGI (which will be added automatically). Also note that the SCRIPT parameter must be a full file system path, starting at the disk root, and that no Web server URL directory mapping will be applied. (However, if the script in on the same directory as the script containing the INSERTFORM command, you can use the $pathTranslated(scriptname) string function, with the name of your insertion script, to get the full file system path to that file.)

If you use the "SCRIPT" keyword to define a script file to process the INSERT statement, then that script can reference two variables to perform the SQL: The database string will be passed in with the name "in_database" and the generated SQL INSERT statement will be passed in as "in_sql". Therefore, the database insertion can be performed with these statements:

    <% DATABASE $in_database$ ;
       SQL $in_sql$ %>

However, a script file is not necessarily required. Since this command generates encrypted "in_database" and "in_sql" hidden input fields, in will operate in "no script mode" if no script is specified. (See "Using ODBscript Without a Script File" for complete details. Anytime that you do not specify a script file, ODBscript will expect the variables "in_database" and "in_sql" to be passed in, and it will effectively execute the code shown above, with some error checking, or it will use your "default.odb" script if you have installed one on your system.)

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

The optional TARGET parameter can be used to define an HTML "target" label for the form submission result, which can be a frame name or a window name. (Note: If your form is already in a frame, the default behavior is to open the submission in the same frame, so you don't need the TARGET parameter unless you want the form submission result in a different frame or window.) For example, TARGET="_blank" can be used to open the result page in a new window.

The optional TRACE keyword will enable the ODBscript trace function when the form is submitted to the script.

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.

The PASSWORD keyword can be used to define a TYPE="password" input field. Browsers do not display the values typed into "password" fields; instead, they show an asterisk (*) for each character typed. Other than this special processing in the browser, a password field is like any other text input field. Password fields are typically text fields in a database, and if so, you should enclose the column name in apostrophes (as with other text columns, which is explained below).

The TRACE keyword can be used to turn on TRACE mode, for debugging. TRACE will output the SQL statement that is generated by the INSERTFORM command, and it will also output the value of $sql_status$, so you can use this if you are getting an SQL error when the statement is executed.

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

• When ODBscript generates the SQL statement to be used for the subsequent INSERT, it needs to distinguish between numeric fields and text fields, and for certain databases such as MS-Access, date fields. This is because, in SQL statements, numeric fields are given as plain digits but text fields must be enclosed in single-quotes (apostrophes). MS-Access uses "#" characters around data vales. Therefore, you MUST tell ODBscript that a key field or update field is to be treated as a text field by enclosing its INSERTFORM name in apostrophes (e.g., 'name'), or that it is to be treated as a date field by enclosing its name in '#' characters (e.g., #startdate#). If you fail to do this, the generated SQL statement will not "quote" those values, and the statement will be rejected when it is executed.
• As noted above, the input_field names must be exactly the same as database column names. This is because ODBscript uses the field names, as they are given in the INSERTFORM list, as the NAME="..." parameters in the generated HTML form and as the column names in the generated SQL INSERT statement.

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, SCRIPT="/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: Verify the user's ID and password before proceeding %>

    <% ONERR %>
       <% OUTPUT ODBscriptErr.log APPEND %>
       $today$ $time24$ $path_info$: $odbic_error$
       <% OUTPUT;
       odbic_error = "Internal processing error occurred.";
    ENDERR %>

    <% ONERR ;
       got_error = $odbic_error$, odbic_error = "";
    ENDERR %>

This command opens a file for writing with the WRITE command or for reading with the $read( ) string function. (Reading is performed with a string function rather than a command because it returns a single line from the file, so $read( ) allows that line to be assigned to a variable or to be output directly.)

The "fileID" is simply a label -- any name or number that you wish to use -- that must be used to reference this open file in a later WRITE command or $read( ) function. It can be a variable, such as $file$ if it needs to be "dynamic", but ordinarily it can be plain text. Using this file ID allows having several files open at once, and the WRITE commands and $read( ) functions can access the proper file by referencing the same ID as the appropriate OPEN command.

If the READ keyword is specified, then the file is expected to already exist, and the file is opened for reading only. If the file does not exist, an error message will be set in the special predefined variable $file_error$, so you can test for an error after the OPEN command with a statement such as <% IF $file_error$ %> (or test for a successful open with <% IF NOT $file_error$ %>).

Otherwise, if READ is not specified, the file will always be created if it does not already exist. If the file does exist, it will be opened for either reading or writing. If the optional APPEND keyword is specified, then writing will begin after any lines already in the file. Otherwise, without the APPEND keyword, writing will overwrite any lines already in the file. (The APPEND keyword can be used even if the file might not exist; a new file will be created if necessary.)

NOTE: The referenced "filename" can use either "/" or "\" as the directory character. The "filename" must have the full file system directory path specification, starting at the disk root directory, 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 given, then the file is assumed to be on the "current directory", which is set by the Web server before executing the CGI. Unfortunately, different servers set this differently, e.g. some set it to the Web site root and some set it to the CGI program subdirectory. You can, however, generate a file system path that's on, or relative to, the current script's subdirectory using the $pathTranslated(filename) string function. This function allows using "../" or "..\" to represent the "parent" directory of the script subdirectory (e.g. "../subdir/filename" would refer to a file on a different subdirectory under the same parent directory), or "subdir/filename" (without a leading "/" or "\") to represent a subdirectory of the current script directory. Using this function makes it easier to move scripts around, as long as the subdirectory structure remains the same, because you don't need to "hard code" the full file system path of the script directory.

If the file name or directory path contains any spaces, then the file name must be enclosed inside double-quote marks (e.g., "\path\file name"). Otherwise, quotes are not required.

Files can be closed with the CLOSE command by referencing the same file ID, but any files remaining open when the script terminates will automatically be closed.

Example:

The following command opens a file for appending (i.e. writing after any existing lines already in the file). It uses the arbitrary file ID "log", which simply allows WRITE and CLOSE to refer to the same file. It uses $pathTranslated( ) to specify a file that is on a subdirectory named "logs" under the same parent directory as the current script subdirctory. (For example, if the current script is on a subdirectory named "c:/webroot/mysite/odb", then the opened file will be on "c:/webroot/mysite/logs".) This example then writes a single line (with a couple of variables) at the end of the file and closes it.

    <%
    OPEN log, $pathTranslated("../logs/myLog.txt") APPEND;
    WRITE log, "$date_short$ $time24_hms$ Record $key$ updated.\n";
    CLOSE log;
    %>

    <OPTION VALUE="column"> display

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.) When this form is used, the given values will be the text displayed in the list and the values passed when a selection is made. (That is, the displayed text and the passed values cannot be different when you use this form of the command. However, since the values are predefined, you may want to use a TRANSLATE command in the target script to translate the passed text values into codes.)

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

The generated HTML <SELECT> form field acts very much like an <INPUT> variable to the form's ACTION function: The name of the variable will be either "column" or "input_field", depending on the form used, and the value for this variable will be the <OPTION VALUE="..."> (as described above) corresponding to the user's highlighted selection. Thus, to the ACTION function (e.g, ODBscript processing a second script file), there will simply be a variable with a value, just as if the user had typed the value into a standard <INPUT> field with that name. Therefore, you do not need any special processing in the target script to handle OPTIONLIST input, unless you want to allow users to select more than one value from the list.

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 script that processes the form input, you can use the EACHMULTI loop to process each selection.

Examples:

    <FORM METHOD="post" ACTION="/scripts/odb.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>

    <FORM METHOD="post" ACTION="/scripts/odb.exe/your_dir/script3.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 script must reference this input variable as $Employee_Name$.

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

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 %>

    <% 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 %>

The optional "ROWS=" specification limits the number of rows that will be selected. If no limit is set, then all rows matching the selection criteria will be returned. NOTE:This limit is set by using the SQL keyword TOP in the query, which may not be supported by your database (e.g.,it is supported by MS-Access but not by SQL Server.) If not, you can use the SETOPTION SQL_MAX_ROWS to set the number of rows before executing the QBE. (If you set that option, however, it will remain in effect for all subsequent queries until you set some other number, or reset it to return all rows with the command SETOPTION SQL_MAX_ROWS=0.)

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

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

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

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

Example:

    <% QBE TABLE=Employees, EmployeeID, 'LastName', 'FirstName', 'Title',
       'BirthDate', 'HireDate', 'Address', 'City', 'Region', ReportsTo %>

The following code could be used at the beginning of a script 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 %>
    ...

This command may be used to send an unprocessed file to the user's browser. Unlike the INCLUDE command, this command will send the file to the browser without attempting to execute any commands or replace any variable references that may be contained in the file. It is most useful for returning an image file, for example, so ODBscript can be used as the SRC URL for an <IMG> tag.

NOTE: The referenced filename must have the full directory path specification, such as would be used to open the file with a file 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.)

When this command is used to return any file that is not an HTML file, then you should use the HEADER command to specify the proper Content-type. (If you do not use the HEADER command, then the standard "Content-type: text/html" header will be sent before sending the file.)

Example:

    <% HEADER Content-type: image/gif %>
       RETURNFILE /images/banner.gif %>

The optional "ROWS=" specification limits the number of rows that will be selected. If no limit is set, then all rows matching the selection criteria will be returned. NOTE:This limit is set by using the SQL keyword TOP in the query, which may not be supported by your database (e.g.,it is supported by MS-Access but not by SQL Server.) If not, you can use the SETOPTION SQL_MAX_ROWS to set the number of rows before executing the QBE. (If you set that option, however, it will remain in effect for all subsequent queries until you set some other number, or reset it to return all rows with the command SETOPTION SQL_MAX_ROWS=0.)

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

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

    Keywords            Rows selected
    plastic widget      Both "plastic" and "widget" in either Heading or Description
    plastic and widget  (Same as above, where "and" is implicit)
    plastic or widget   Either "plastic" or "widget" in either Heading or Description
    widget not plastic  "Widget" in either Heading or Description but "plastic" in neither
    "plastic widget"    Exact phase "plastic widget" in either Heading or Description

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

The REPLY keyword allows specifying a "Reply-to" e-mail address. Most e-mail reader programs will use this address if the recipient replies to the message. (Note, however, that some e-mail readers do not recognize the "Reply-to" address and will send replies to the FROM address.) The default for this parameter is the FROM address, so you do not need to use REPLY unless you want replies to go to a different address.

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). Attachments are typically "transfer encoded" in Base64, which allows binary files, but for ordinary text files you can use the specification TYPE=text/plain, and the attachment will not be Base64 encoded (which some mail readers do not recognize). The specification would be given something like: ATTACH=(TYPE=text/plain,afile.txt). 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/(exact type) to specify the image type, such as "image/gif" or "image/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.) Note: The TYPE and DISPOSTION keywords affect all files that follow in the attachment list, until another specification is given to change it. For example, ATTACH=(TYPE=text/plain, file1.txt, file2.txt, TYPE=application/octet-stream, file3.doc, file4.doc) means that file1.txt and file2.txt are both type "text/plain", but then the type is changed for file3.doc and file4.doc.

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

The HTML option can be used if the e-mail message body contains HTML tags for formatting. If the recipient's mail reader software supports this type of content, then the message will be formatted similar to the way a browser formats a Web page. (If the recipient's mail reader does not support HTML, then the tags will appear as ordinary text.)

Examples:

When used with the SHOWINPUT or EACHINPUT commands, the SENDMAIL command might be used as a "form e-mailer". (SHOWINPUT simply outputs the list of variables input from a form or in the URL, while EACHINPUT loops through each of the input variables so you can define a script to do any necessary formatting or processing.)

    <% SENDMAIL SERVER=mail.my.com, FROM=$email$, TO=me@my.com,
          SUBJECT=Web Page Form Input %>
       SHOWINPUT;
       ENDMAIL %>

    <% DATABASE DSN=MyDb ;
       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 %>

    <% SENDMAIL SERVER=mail.my.com, FROM=me@my.com, TO=(email FROM customers),
         SUBJECT=New Product Announcement ;
       INCLUDE \mydir\announce.txt ;
       ENDMAIL %>

The SESSION command allows you to automatically associate temporary data to each user visiting your site. These data are stored in a temporary file named with a unique "session ID," which is assigned by ODBscript and set as a cookie in the user's browser. These data are only available for the duration of the session, which is controlled by the TIMEOUT parameter described below. (That is, a session allows you to retain data from one page to the next during the span of an arbitrarily defined "site visit." To associate data to users that is retained from one visit to the next, by keeping it in a database, see the USER command.)

The TMP keyword is optional, but its use is recommended. This parameter specifies the file system directory where the session temporary files will be stored. (If you do not set TMP, there will be some temporary file directory set by the Web server, but you will not have any control over which directory will be used.) This parameter should be given as a file system path, starting at the disk's "root" directory. (It is not relative to the Web server's root directory, and it does not need to be a directory used as an HTML directory. However, the Web server's own system user ID must have "read and write" access privileges for this directory.) If you are unsure of a "safe" directory to use, then omit the TMP specification and allow the Web server to set the temporary directory.

The LOGIN defines a URL, which should be the URL of a session login script. If the user does not have a session ID cookie set, or if the user's last visit was not within the TIMEOUT period, then the user will be automatically redirected to this URL. Like a URL used as the ACTION for a <FORM>, this can be a full URL (with domain) or a site-relative URL. The URL can pass parameters to the login script by adding them to the URL following a "?" mark, in the form of "variable=value", with multiple variables separated by "&" characters. (For an example of using this format to pass the name of the current script to the login script so that it can automatically return to the current script after the login, please see the testsess.odb example included in the ODBscript download file.)

If the LOGIN parameter is not given, then the user will not be required to log in. Even so, users will be assigned session ID cookies (if none is already set), and session temporary file will be read and updated as described below. Therefore, the LOGIN is not required unless you want to validate the user's password or to require some type of registration process before proceeding.

The TIMEOUT parameter specifies how long a user can be "away" from your site (i.e., not link to any scripts) before the LOGIN must be performed again. With this parameter, you can define a "session" as being any number of requests where the time between any two ODBscript requests does not exceed the TIMEOUT period. The TIMEOUT must be specified in minutes.

The NEW keyword forces a new session ID and file to be created, even if the user already has one assigned. This keyword is never required, but it is intended to be used in a LOGIN script to insure that any previous session data is discarded. (If all the session data gets reset anyway, then you may as well reuse any ID and session file that happens to exist, so you don't need to use this keyword. It is intended for specific cases where it's important to start each session without any previous data set, but you don't want to be concerned with explicitly resetting each possible session variable.)

Important note: Only requests to scripts that have a SESSION command will be considered in checking this TIMEOUT. That is, if a user requests a page generated by a script with a SESSION command, then visits other HTML pages or scripts that do not have a SESSION command, then goes to a script that does have a SESSION command, the TIMEOUT covers the full time between the first script and the last. (This is because ODBscript will not have updated the session's "last use" time stamp for any regular HTML files or for any scripts that do not declare a SESSION command.) This means that when you use the SESSION functionality, you will probably want to use scripts with SESSION commands for all pages (whether or not the script needs any session data), or if that is not possible or desireable, then you will need to set the TIMEOUT high enough to allow for visits to non-SESSION pages.

The TIMEOUT parameter should be used only if you also specify a LOGIN. If you declare a LOGIN but do not specify a TIMEOUT, then a default value of 60 minutes will be used.

Note: If you use both the USER command and the SESSION command, you can specify LOGIN and TIMEOUT in either command, but you do not need to set these for both. If you use both USER and SESSION, the most efficient approach is to put the SESSION command first in the script and declare the TIMEOUT and LOGIN in this command, then use the USER command without these parameters. (This approach will avoid a database query for the Users table that would be wasted if the user is redirected to the login script because the timeout period has been exceeded.) Alternatively, if you do put the USER command first, then you should declare the TIMEOUT and LOGIN in that command. (However, note that these are only suggestions; there could possibly be cases where you might want to use different TIMEOUT and LOGIN parameters with the USER and SESSION commands, which would be allowed.)

The TAG keyword allows you to have different session IDs for different applications running on the same site. The default name for the session ID is odbic_sid. The TAG parameter allows you to use something other than "odbic" as the ID name prefix. For example, if you use TAG=MyApp in the SESSION command, then the cookie will be named MyApp_sid, so there will be no conflict with another application's session.

All other parameters in the SESSION command are assumed to be variable names for the session data that you want to set or update in the current script. That is, whenever a script needs to add a new variable or update an existing variable in the session file, it must be included in this list. Important note: You can use any other variables that were already set in the session file by previous scripts -- they will all be automatically defined in your script much like CGI input variables and cookies -- but you cannot update a variable in the session file unless you explicitly specify it in the SESSION command. This is an important principle for proper use of the SESSION command, so please read the following paragraphs carefully:

When the SESSION command is executed, the data variables currently in the temporary session file (if any) will be read. Any of these variables can referenced in the script like any other ODBscript variable (i.e., the variable names enclosed in "$" characters). You can also change the value of any of these variables anywhere in the script, and that new value will be used for the remainder of the script. The session file will be updated automatically when the script completes, but only those variables explicitly declared in the SESSION command will added or updated. All other variables that were in the session file before the script was executed, but which were not declared in the SESSION command's list of variables, will retain their original value.

This feature is primarily intended for security and for maintaining the integrity of session data that should not be changed except in certain scripts. As explained in the next paragraph, a session variable's current value can be overridden by a CGI input value with the same name. (For example, you may need to allow the user to change one of those values with an update form, or you may need to change the state of the session if a particular request is submitted.) But resetting a session variable with a CGI input value will only be allowed for variables that are declared in the SESSION command variable list. Consider this example: Suppose you have a login script that looks up a user name and verifies a password, and then you want to keep a variable named user_ID in the session file for subsequent access to the user database. You wouldn't want to allow a user to be able to change this user_ID just by typing it directly in a URL or by modifying an input form, and you wouldn't want the ID to be changed accidentally by the script (which might happen, say, if you did a database query that had that variable name as a result column, but no row was returned, so the variable would now be null). To prevent that, you should put the variable user_ID in the SESSION list only in the script that verifies the login. Then, all other scripts in your application will be able to use that ID, but no other script will be able to change it.

If you pass any variable into the script with the same name as a SESSION variable (e.g., an <INPUT> from a <FORM>), and the variable is in the list of variables in the SESSION command, then the CGI input data will override the current SESSION data. The session file will be updated when the script completes to have this new value. (As noted above, within the script itself, you can explicitly change the value of any variable including session variables -- for example with a SET command or a database query -- but only those variables in the SESSION list will be updated in the session file when the script completes.)

Optionally, you can specify an initial value for a SESSION variable by declaring variable=value in this command. If the SESSION variable already has a value, then the "=value" has no effect. If you do not set an initial value for a variable, then it will be defined but it will not have any value until you set one in the script.

Note that all of the scripts using the same SESSION will use the same temporary file, but it isn't necessary to declare all of your SESSION variables in each of the scripts. You only need to declare the variables the given script needs.

For an example of using the SESSION and USER commands and an example LOGIN script, please see the testsess.odb and login.odb scripts included in the ODBscript download file.

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

Note that with the "script tag" command style only (but not with the HTML comment style) a SET command is assumed whenever a command in the form of "variable=value" is recognized. For example:

    <% SET x = 1 %>    Okay, the SET can be used in "script tag" mode.
    <% x = 1 %>        Also okay! In "script tag" mode, this is the same as above.

    <!--x = 1 -->      WRONG! The SET is required for comment-style commands.
    <!--SET x = 1 -->  Correct

However, when using the "implicit SET" style, be careful not to use any variable names that are the same as command names. (For example, break=1 will be misinterpreted as an illegal BREAK command, not a SET, but breakFlag=1 would be allowed.) If you need to set a variable that is the same name as a command, use the explicit SET command in front of the variable.

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

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

Example:

    <%
    SET x = y + 1;    WRONG! The "y" will be taken as an ordinary character.
    SET x = $y$ + 1;  Correct! The current value of variable "y" will be used.
    %>

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$)" %>

There is no "getcookie" command. ODBscript 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$ ;
      SELECT max(tracking_id)+1 AS tracking_id FROM users;
      INSERT INTO users (tracking_id) VALUES ($tracking_id$);
      SETCOOKIE tracking_id = $tracking_id$, EXPIRES = 5y;
   ENDIF %>

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

<% SETOPTION option=value [,option=value,...] %>

DATE	This option controls the expected format of dates. Specifically, it is used by the $dateAdd( ) and $dateDiff( ) functions to determine the order of year, month, and day in date-field operands, and it also controls the order of year, month, and day in the predefined date variables such as $today$. There are three valid values: DATE="mdy" which indicates month-day-year order (the default value); DATE="dmy" would indicate day-month-year order; and DATE="ymd" specifies year-month-day order.
MONTHS	This option can be used to specify a list of month names to be used in dates. It must be given as a comma-separated list of 12 month names, in either upper- or lower-case, such as MONTHS="Januar,Februar,..." (12 values). (Because the SETOPTION command allows multiple comma-separated options to be set in the same statement, the list of month names must be enclosed in quotes.) These month names will be used for interpreting dates used by the $dateAdd( ) and $dateDiff( ) functions, and for output of month names in format masks. The month names in the predefined date variables (such as $date$) will also be set when this option is set.
DECIMAL	The value of this option should be a single character, and it specifies the "decimal point" (or "radix point") character that will be used in format masks. For example, after setting DECIMAL="," the format routine will expect that commas are used in format masks to separate the frational digits from the whole-number digits, so you can use a format mask like "#.###.##0,00" to produce a European-style representation of numbers.
CURRENCY	The value of this option should be a single character, and it specifies the currency symbol used in format masks. The characters "$" and "£" are always taken as currency symbols in format masks, but you can use this option to set an alternate single-character symbol. For example, after the command: <% SETOPTION CURRENCY="€", DECIMAL="," %> the format mask "€#.###.##0,00" would "float" the € currency character to the first digit of the result and use a comma for the decimal point, such as €10,00.
SQL_MAX_ROWS	This option specifies the maximum number of rows to return for SQL SELECT statments, even if there are more rows that satisfy the selection criteria. The specific rows returned will be determined by the ORDER BY clause or by the primary key if no ORDER BY is specified. That is, the top "max_rows" number of rows in the sorted order will be returned. This maximum number of rows remains in effect for all subsequent queries, until another value is set or it is reset to return all rows by specifying a value of 0.
SQL_ACCESS_MODE	This option lets you set the database access mode that will be used for subsequent SQL statements. The value "read_only" means that no updates or inserts will be performed in those statements, which may help to avoid unnecessary database locking. The value "read_write" means that updates and inserts may be performed, and it is the default access mode.
SQL_AUTOCOMMIT	The SQL "autocommit" mode means that each statement is committed (i.e., permanently applied to the database) immediately after exectution. The default autocommit mode is "on". If you set the autocommit mode to "off", then you can execute a series of statements as a "transaction" before executing either a COMMIT or ROLLBACK statement. (Rollback means that none of the updates previously executed will be applied to the database.) If you set autocommit to "off" (SETOPTION sql_autocommit=off), then you must execute either a SQL COMMIT or SQL ROLLBACK statement. ("Abandonded" transactions are automatically rolled back when the connection is closed.) Note that not all ODBC databases support transactions; check your documentation.
SQL_TXN_ISOLATION	The SQL "transaction isolation level" controls how concurrent database access is to be handled for a transaction. Using appropriate isolation levels can allow maximum concurrency without causing unnecessary overhead, but without jeopardizing transaction integrity when it's necessary. The options are specified by one of the following (case-insensitive) text values: read_uncommitted - A query returns all database rows matching the selection criteria, including updated or inserted rows in transactions that have not yet been committed (i.e., it allows "dirty reads"). read_committed - A query returns only database rows from transactions that have been fully committed (by waiting for any write locks). repeatable_read - A query waits for any write locks (no "dirty reads") and locks the table for any updates that would match the selection criteria (a read lock), thereby guaranteeing that the query returns a result set that can be duplicated with a subsequent query using the same selection criteria. serializable - Forces transactions to be occur one at a time: A query reads only committed data, and both queries and updates lock the rows affected by the WHERE clause. Because the transaction waits for any read or write locks, and other transactions cannot update or delete the rows in the affected range or insert rows that would match the WHERE clause of any queries or updates in the current transaction, the transaction avoids any "dirty" reads, unrepeatable reads, and "phantom" rows from partially completed concurrent transactions. (This is the highest level of transaction isolation, but it also has the most overhead, and it can cause a "bottleneck" under heavy concurrent usage.) Note that not all ODBC databases support transaction isolation levels, or may not support all of the options shown above, or may not support them precisely as described; check your documentation.

<% SHOWINPUT [endline] %>

The SHOWINPUT command lists out the name and value of all CGI-input variables, including cookies. 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. See also the EACHINPUT command, which will loop through the variables one at a time and you can do any necessary processing or formatting.

The optional "endline" specifies a tag to add to the end of each output "name: value" line. For example, if the output is HTML format, you might specify <BR> to force a new line after each value, or <P> to make a paragraph break between values. If the output is to a text file or to an e-mail message (also text), then there is a special code you can use to add a "linefeed" character: \n. A single \n would cause a new line after each value, and \n\n would cause an extra blank line between the values.

<% SQL sql_statement %>
or <% [SELECT | INSERT | UPDATE | DELETE] sql_statement %>
or <% SQL [TABLES [tables] | COLUMNS [tables]] %>

The SQL statement is issued immediately at the point that it is encountered in the HTML script file. If there is not already an open connection to the database specified by the most recent DATABASE command, then a connection is opened before executing the statement.

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:

    <% SELECT item_number, description FROM items %>

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

    <% SELECT item_nbr AS item, qty AS quantity FROM ... %>

    <% SELECT (quantity * unit_price) AS total_price ... %>

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 ODBscript 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. (ODBscript-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, ODBscript can be used without an HTML script 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 script file, ODBscript automatically executes the equivalent of a command: <% SQL $sql$ %>. However, in a script file, you can specify any variable for the SQL command, such as <% SQL $user_statement$ %>.

After each SQL statement, ODBscript 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:

    <%
    SELECT item, description FROM items ;
    IF $row$ = 0 %>
        $sql_error$
    <% ELSE %>
        . . . (format results, e.g., with an <%EACHROW%> command)
    <% ENDIF;
    %>

Another ODBscript 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:

$sql_status$ = -2 means "No rows selected/affected"
$sql_status$ = -1 means "SQL execution error"
$sql_status$ = 0 means "SQL SELECT or DDL statement successful"
$sql_status$ = 1 means "One or more rows affected by INSERT, UPDATE, or DELETE"

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 special statement "SQL TABLES [tables]" can be used to get information from database system internal tables about the tables in the current database (i.e., the one most recently referenced by a DATABASE command). For some (but not all) ODBC drivers, the optional table specification can be a search term that will be used in a LIKE comparison, in which a "%" (percent sign) character can be used as a "wildcard" to match zero or more unspecified characters, and a "_" (underscore) character can be used to match any single (exactly one) unspecified character. For example, the statement "SQL TABLES User%" would return information about all tables with names beginning with "User". The results of this statement are returned as a set of result rows, the same as a "SQL SELECT" statement, which can then be processed with an EACHROW, ENDROW loop.

Note that not all ODBC database drivers implement the function used for this query, and for those that do implement it, different database systems return somewhat different results depending on the supported features. Also, the specific meaning of some information, such as table_type, is database-dependent. The easiest way to see if the function is supported and what results are returned is to execute a test script such as <% SQL TABLES; TABLE %> which will execute the query and format all result rows (with column headers; see TABLE command). The column names that may be returned (and hence, the variable names that you can use in the script to reference the data, such as $table_name$) are:

TABLE_CAT	Table catalog (database-dependent, e.g. for MS-ACCESS, the path to the *.mdb file containing the database).
TABLE_SCHEM	Table scheme name, if supported and defined.
TABLE_QUALIFIER	Table qualifier, if supported and defined.
TABLE_OWNER	Table owner user name, if supported and defined.
TABLE_NAME	Name of the table.
TABLE_TYPE	One of: TABLE, VIEW, SYSTEM TABLE, GLOBAL TEMPORARY, LOCAL TEMPORARY, ALIAS, SYNONYM, or database-specific identifier.
REMARKS	Comment by table creator, if supported and defined.

The special command "SQL COLUMNS" is similar to "SQL TABLES" but it also returns information about the columns in the tables. (Like SQL TABLES, note that not all ODBC database drivers implement the function used for this query, and that different database systems return somewhat different results.) You can get information about a single table by specifying the exact name, such as "SQL COLUMNS MyTable." Some (but not all) ODBC drivers allow you to use a search term term that will be used in a LIKE comparison, such as "SQL COLUMNS User%" to return information about all tables with names beginning with "User". The results of this command are also returned as a set of result rows, the same as a "SQL SELECT" and "SQL TABLES" statements, which can then be processed with an EACHROW, ENDROW loop or a TABLE command. The column names (and variable names) that may be returned (along with other database-specific columns) are:

TABLE_CAT	Table catalog (database-dependent, e.g. for MS-ACCESS, the path to the *.mdb file containing the database).
TABLE_SCHEM	Table scheme name, if supported and defined.
TABLE_QUALIFIER	Table qualifier, if supported and defined.
TABLE_OWNER	Table owner user name, if supported and defined.
TABLE_NAME	Name of the table.
COLUMN_NAME	Name of the column.
DATA_TYPE	Numeric code for ODBC standard data type or database-specific type. (Refer to database documentation.)
TYPE_NAME	Data type name, such as CHAR, VARCHAR, INTEGER, MONEY, etc. (Database-dependent, refer to documentation).
PRECISION	The maximum number of digits of a numeric type, or the length of a character type.
LENGTH	Data size in bytes if the data is represented as characters (which is always the case in ODBscript variables).
SCALE	The number of digits to the right of the decimal point in a numeric type.
RADIX	Generally, either "10" for a base 10 number or "2" for a binary number.
NULLABLE	Column can be null: "Yes", "No", or "Unknown"
REMARKS	Comment by table creator, if supported and defined.

  <% SQL COLUMNS MyTable; TABLE BORDER=1 %>

The SQL statement, which must be terminated with the "-->" or the script-tag markers ";" or "%>", 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 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, ODBscript 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". You can specify the parameters used in the <TH> tags (such as BGCOLOR and ALIGN) by setting the predefined variable named table_th before using the TABLE command. You can define <FONT> tag parameters for the text by setting the variable named table_font (which is used for the data cells) and table_th_font (which is used for the column headers, if defined; otherwise the column headers will be the same as the table_font specification).

Two other predefined variables that you can set to control table formatting are table_tr and table_td, which are HTML parameters for the <TR> and <TD> tags, respectively.

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:

    <% SET table_font="arial", table_th="BGCOLOR=#cccccc" ;
       SELECT * FROM Products ;
       TABLE BORDER=1 CELLPADDING=5 %>

    <% SET table_tr="BGCOLOR=$if(mod($row$,2),#ddddff,#ffffff)" %>

    [123:SET x="1" ][124:IF 1 < 10 (TRUE)]

The conditional test in a WHILE statement will also be traced, but it is displayed as an IF test. An IF statement that tests a single value (e.g., <% IF $x$ %>) will display a "1" if the test is "true" (the variable has some value) or "0" if the test if "false" (the variable is undefined, or it currently has a zero-length string value, or it has a numeric value of 0). The SQL trace will show both the executed SQL statement and the subsequent value of $sql_error$.

The TRACEOFF command turns off tracing. If TRACE produces too much output to be easily interpreted, you can enclose just one section of the script inside TRACE and TRACEOFF commands.

There is also a predefined variable named "trace" that can be passed in to ODBscript with a value of "on" to turn on the TRACE mode. This variable can be passed as a hidden variable in the UPDATEFORM and INSERTFORM commands by adding the keyword TRACE to those commands.

<% TRANSLATE variable value=newvalue [, value=newvalue, ...] %>

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

Note that before using the UPDATEFORM command, you must have a DATABASE command. This is because the form HTML will also include a TYPE="hidden" encrypted input 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 given, the optional "SCRIPT=..." specification will be included on the form as a "hidden" field to tell ODBscript what script file to use when the form is submitted. (This is not required; see below.) In the SCRIPT parameter, specify only the path to the script file itself; do not include the path to the odb.exe CGI (which will be added automatically). Also note that the SCRIPT parameter must be a full file system path, starting at the disk root, and that no Web server URL directory mapping will be applied. (However, if the script in on the same directory as the script containing the INSERTFORM command, you can use the $pathTranslated(scriptname) string function, with the name of your update script, to get the full file system path to that file.)

If you use the "SCRIPT" keyword to define a script file to process the UPDATE statement, then that script can reference two variables to perform the SQL: The database string will be passed in with the name "in_database" and the generated SQL UPDATE statement will be passed in as "in_sql". Therefore, the update can be performed with these statements:

    <% DATABASE $in_database$ ;
       SQL $in_sql$ %>

However, a script file is not necessarily required. Since this command generates encrypted "in_database" and "in_sql" hidden input fields, in will operate in "no script mode" if no script is specified. (See "Using ODBscript Without a Script File" for complete details. Anytime that you do not specify a script file, ODBscript will expect the variables "in_database" and "in_sql" to be passed in, and it will effectively execute the code shown above, with some error checking, or it will use your "default.odb" script if you have installed one on your system.)

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

The optional TARGET parameter can be used to define an HTML "target" label for the form submission result, which can be a frame name or a window name. (Note: If your form is already in a frame, the default behavior is to open the submission in the same frame, so you don't need the TARGET parameter unless you want the form submission result in a different frame or window.) For example, TARGET="_blank" can be used to open the result page in a new window.

The optional TRACE keyword will enable the ODBscript trace function when the form is submitted to the script.

Following the TABLE, the KEY field or fields, and any optional parameters, 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 ODBscript, 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, ODBscript 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 ODBscript 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, ODBscript 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:

• When ODBscript generates the SQL statement to be used for the subsequent update, it needs to distinguish between numeric fields and text fields, and for certain databases such as MS-Access, date fields. This is because, in SQL statements, numeric fields are given as plain digits but text fields must be enclosed in single-quotes (apostrophes). MS-Access uses "#" characters around data vales. Therefore, you MUST tell ODBscript that a key field or update field is to be treated as a text field by enclosing its UPDATEFORM name in apostrophes (e.g., 'name'), or that it is to be treated as a date field by enclosing its name in '#' characters (e.g., #startdate#). If you fail to do this, the generated SQL statement will not "quote" those values, and the statement will be rejected when it is executed.
• As noted above, the database column names must be exactly the same as ODBscript variable names. This is because ODBscript uses the field names, as they are given in the UPDATEFORM list, in the generated SQL statement, and it also uses the current values for those fields from its variable list to initialize the INPUT fields. This is usually not a problem, since the intended application for this command is to select data with one SQL statement, then write a form to allow the user to update the current data. (This does not mean that the variables necessarily got their values from a previous SQL select; they might be input variables or defaults. The only requirement is that the variable names are the same as the column names.) If you use a script file to process the form when it is submitted, remember that the input field names from the form will be the same as the database column names.
• Although ODBscript will expect to find variables for each of the fields, do not put the $ prefixes and suffixes on the variable names in the UPDATEFORM list.

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, ODBscript uses a default size of 50 for text fields and 12 for numeric fields. If you give a field size larger than 99, then ODBscript 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 ODBscript 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 script in a hidden variable named "default" -- one of the predefined input variables that ODBscript always processes -- with a value of "column=unchecked_val". Like a DEFAULT statement in a script, this value will be used if the user doesn't check the box. Therefore, if you use the FORM CHECKBOX with your own script, don't specify a DEFAULT for the checkbox variable in the next script.)

The PASSWORD keyword can be used to define a TYPE="password" input field. Browsers do not display the values typed into "password" fields; instead, they show an asterisk (*) for each character typed. Other than this special processing in the browser, a password field is like any other text input field. Password fields are typically text fields in a database, and if so, you should enclose the column name in apostrophes, as with other text columns.

The TRACE keyword can be used to turn on TRACE mode, for debugging. TRACE will output the SQL statement that is generated by the UPDATEFORM command, and it will also output the value of $sql_status$, so you can use this if you are getting an SQL error when the statement is executed.

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 SCRIPT=/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 %>

You must declare a DATABASE connection string before using this command, which specifies the database that contains the Users table. (Note that this does not need to be the same database that your application is using, since you can set another DATABASE immediately after the USER command.)

The TABLE keyword is optional, and it specifies the database table that contains the user data. The default for this table is Users. In the ODBscript download file, there is an MS-Access database, users.mdb, that contains a Users table. You can modify that table to suit your user data, or add a similar table to your own database, or simply use any table that you may already have. If the table is named Users, then you do not need to specify the TABLE keyword.

If you use your own table, there are two required columns: a "text" column for the user ID and a "date/time" column for logging the time of the user's last site usage. The default name for the user ID column should be odbic_uid (but see the TAG keyword below), and the usage timestamp column should be named last_use. All other columns can be whatever you need for your LOGIN script (if any) and your application.

The LOGIN defines a URL, which should be the URL of a user login script. If the user does not have a user ID cookie set, or if the user's last visit was not within the TIMEOUT period, then the user will be automatically redirected to this URL. Like a URL used as the ACTION for a <FORM>, this can be a full URL (with domain) or a site-relative URL. The URL can pass parameters to the login script by adding them to the URL following a "?" mark, in the form of "variable=value", with multiple variables separated by "&" characters. (For an example of using this format to pass the name of the current script to the login script so that it can automatically return to the current script after the login, please see the testsess.odb example included in the ODBscript download file.)

If the LOGIN parameter is not given, then the user will not be required to log in. Even so, users will be assigned user ID cookies (if none is already set), and the Users table data will be read and updated as described below. Therefore, the LOGIN is not required unless you want to validate the user's password or to require some type of registration process before proceeding.

The TIMEOUT parameter specifies how long a user can be "away" from your site (i.e., not link to any scripts) before the LOGIN must be performed again. With this parameter, you can define a "user session" as being any number of requests where the time between any two ODBscript requests does not exceed the TIMEOUT period. The TIMEOUT must be specified in minutes.

Important note: Only requests to scripts that have a USER command will be considered in checking this TIMEOUT. That is, if a user requests a page generated by a script with a USER command, then visits other HTML pages or scripts that do not have a USER command, then goes to a script that does have a USER command, the TIMEOUT covers the full time between the first script and the last. (This is because ODBscript will not have updated the user's "last use" time stamp for any regular HTML files or for any scripts that do not declare a USER command.) This means that when you use the USER functionality, you will probably want to use scripts with USER commands for all pages (whether or not the script needs any data from the Users table), or if that is not possible or desireable, then you will need to set the TIMEOUT high enough to allow for visits to non-USER pages.

The TIMEOUT parameter should be used only if you also specify a LOGIN. If you declare a LOGIN but do not specify a TIMEOUT (or specify TIMEOUT=0), then the LOGIN will be performed only if the user does not already have a user ID cookie set. In other words, a TIMEOUT of 0 means that the user will be directed to the LOGIN script only on his first visit. Therefore, you can use this type of LOGIN as a one-time registration process.

Note: If you use both the USER command and the SESSION command, you can specify LOGIN and TIMEOUT in either command, but you do not need to set these for both. If you use both USER and SESSION, the most efficient approach is to put the SESSION command first in the script and declare the TIMEOUT and LOGIN in this command, then use the USER command without these parameters. (This approach will avoid a database query for the Users table that would be wasted if the user is redirected to the login script.) Alternatively, if you do put the USER command first, then you should declare the TIMEOUT and LOGIN in that command. (However, note that these are only suggestions; there could possibly be cases where you might want to use different TIMEOUT and LOGIN parameters with the USER and SESSION commands, which would be allowed.)

The EXPIRES keyword can be used to specify how long the user's browser should retain the user ID 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. (Although "m" and "h" are probably not useful for this purpose, they are allowed because the SETCOOKIE command allows them.) For example, "EXPIRES = 60d" would mean that the browser should retain the cookie for sixty days. If you don't specify an EXPIRES time, the default will be 90 days. Note that each time the user executes a script containing a USER command , the cookie will be reset with this retention value, so frequent visitors will retain the cookie indefinitely.

The TAG keyword allows you to have different user IDs (and different database tables) for different applications running on the same site. The default name for the user ID in the Users table is ODBscript_uid. (This is also the name of the cookie sent to the user's browser.) The TAG parameter allows you to use something other than "ODBscript" as the ID name prefix. For example, if you use TAG=MyApp in the USER command, then the database column should be named MyApp_uid, and the cookie will have this same name, so there will be no conflict with applications running on the same site.

All other parameters in the USER command are assumed to be database column names for the user data that you want to retain. When the USER command is executed, these data columns will be read from the database (using the user ID as the key), so these data can referenced in the script like any other ODBscript variable (i.e., the column names enclosed in "$" characters). If you change the value of these variables anywhere in the script, then the database will be updated automatically when the script completes. Also, if you pass any variables into the script with the same name as a USER column (e.g., an <INPUT> from a <FORM>), then the input data will override the database data, and the database table will be updated when the script completes.

Note that all of the scripts using the same USER ID will use the same database table, but it isn't necessary to declare all of your USER variables in each of the scripts. You only need to declare the variables the given script needs.

The USER command is designed to allow users to register themselves at your site, through processing that you provide in your LOGIN script. In this case, ODBscript will assign the "ODBscript_uid" values (or whatever TAG you are using) with a string composed of the current date and time, plus the REMOTE_ADDR (IP address) of the user's browser. (Note that this may be useful information in some cases, so you know when and from where a visitor first registered, but its real functional purpose is uniqueness.) However, USER can also be used with a predefined Users table that is maintained by the site administrator. In this case, you can simply assign the user IDs in the table whenever a new user is added. (The database "auto-increment" number type can be used for this purpose.) The ID column should always be an indexed column, with no duplicates allowed.

For an example of using the SESSION and USER commands and an example LOGIN script, please see the testsess.odb and login.odb scripts included in the ODBscript download file. This example allows users to register on their first visit.

<% VALIDATE variable [= pattern], ... %>

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}$",
               email="[a-zA-Z0-9].+@[a-zA-Z0-9].+\.[a-zA-Z0-9].+" %>

Example:

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

The "fileID" is simply a label -- any name or number that you wish to use -- which must match the fileID used in the OPEN command.

The "line" to be written can contain variables and functions, exactly like any line that is output to the browser. (Note that the line is not evaluated as an expression; it is treated as ordinary output text.) However, you can enclose the entire line in single- or double-quotes, if you wish, and those quotes will be removed.

Important note: The entire "line" is normally written to the file as a single string, even if the WRITE command spans two or more lines. Any line break in the WRITE command statement will be replaced with a single space character in the output line. Also note that the output line is not automatically written to the output file with a line break at the end. However, you can use the following special characters anywhere in the line:

\n	- New line (or "linefeed") character. You must use this character at the end of any lines to force an end-of-line in the output file. (Otherwise, if the WRITE line does not end with a \n, the next WRITE command will continue writing on the same output line.) A single WRITE command line can contain multiple \n characters to write several output lines with one command.
\r	- Carriage return character. Ordinarily, you should not need to use this character: On Windows (MS-DOS) machines, when writing a text file, any linefeed (new line, \n) characters will automatically be replaced with a both a carriage return and a linefeed character (referred to as a CR/LF pair).
\t	- Tab character. Important note: If you have any tab character in your WRITE command line, it will be replaced with single space character. You must use \t to force a tab character in the output.

    <%
    OPEN log, $pathTranslated("../logs/myLog.txt") APPEND;
    WRITE log, "$date_short$ $time24_hms$ Record $key$ updated.\n";
    CLOSE log;
    %>