ODBscript
Open DataBase
Scripting Language

Version 3

User's Guide - Part 2


ODBscript Commands

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

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

Here is an exampe of how commands will appear in the script using the "comment" style (the commands are explained below):

    <!--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 %>
Such code sections can be made more readable by placing the <% and %> on separate lines:
    <%
        x=1;
        IF $y$ > 10;
            x=2;
        ENDIF;
    %>
(Note that the last line of code, "ENDIF;", also has a semi-colon terminator. This is not required for any commands that allow multiple-line arguments, since those commands will find the %> on the next line anyway, but some commands that do not have arguments, such as the various "END..." commands, will expect to find a terminator on the same line, so it's safest to always add a semi-colon on the last line. This habit may also help to avoid problems if you come back to this section later and add more code following the last line.)

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.

Quick Command Reference

BREAK Terminate a processing loop (e.g., WHILE, EACHROW)
CLOSE Close a file opened with the OPEN command
DATABASE Define an ODBC database connection string
DEFAULT Define default values for "empty" or undefined variables
DELETE Execute an ODBC SQL DELETE statement
EACHFILE, ENDFILE Define a processing loop for a file directory listing
EACHINPUT, ENDINPUT Define a processing loop for each CGI input variable
EACHMULTI, ENDMULTI Define a processing loop for multiply-defined input variables
EACHROW, ENDROW Define formatting for each result row after a SELECT statement
EXEC Execute a system command or run a DOS program
EXIT Terminate the script file processing
FORM Generate a generic HTML data input form
FORMAT Define output format "masks" for variables
FUNCTION, RETURN Define a string function
HEADER Send an HTTP header to the browser
HIDDEN Generate <INPUT TYPE="hidden"> form fields to pass variables
HTTPGET Send an HTTP request using the "get" method
HTTPPOST Send an HTTP request using the "post" method
IF, ELSE, ENDIF Conditional test of variables
IFNEW Test an "ORDER BY" column in sorted result rows for new group
IMPORT, ENDIMPORT Read and process data variables from a text file
INCLUDE Read and process an additional script file
INSERT Execute an ODBC SQL INSERT statement
INSERTFORM Generate an automatic database insert form and SQL statement
NOTE Script comment, not output to browsers
ONERR, ENDERR Define an "error trap" to be executed if a processing error is encountered
OPEN Open a file for reading or writing
OPTIONLIST Create an HTML "select option" list from an SQL query or a given list of values
OUTPUT Write all following script output to a disk file
QBE Generate and execute a "query by example" SQL statement
REDIRECT Redirect user's browser to a different URL
RETURNFILE Send unprocessed file to the user's browser (e.g. an image file)
SEARCH Generate and execute a "keyword search" query
SENDMAIL, ENDMAIL Send the output as an e-mail message
SESSION Define session "persistent" variables
SET Set variable to value
SETCOOKIE Send a "cookie" to the user's Web browser
SETMULTI Set new instance of a multiply-defined variable (an array)
SETOPTION Set a processing option (e.g. date format, SQL options)
SHOWINPUT Output a list of all CGI input variables
SQL Execute an ODBC SQL statement
SELECT Execute an ODBC SQL SELECT statement
TABLE Format SQL SELECT results as an HTML table
TRACE, TRACEOFF Show SET, IF, and SQL statements as they are executed (for debugging)
TRANSLATE Define an automatic translation table for a variable
UPDATE Execute an ODBC SQL UPDATE statement
UPDATEFORM Generate an automatic database update form and SQL statement
USER Define user "persistent" variables
VALIDATE Verify that input data matches a "regular expression" pattern
WHILE, ENDWHILE Define a processing loop to be repeated while a condition is true
WRITE Write a line to a file opened with the OPEN command

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

<% BREAK %>

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

Example:

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

<% DATABASE "odbc_connection_string" %>

The DATABASE statement specifies the ODBC database connection that will be used for subsequent SQL commands. ODBscript uses the ODBC SQLDriverConnect function to connect to databases, so any of the optional arguments supported by a particular driver are valid in this connection string. Typical arguments are "DSN" (Data Source Name), "DRIVER" (explicit driver specification), "UID" (database-defined user ID), and "PWD" (user password). (Note that the ODBC connection string uses semi-colon delimiters between arguments, which is also the ODBscript "end of command" marker if you are using the "script tag" command style, so you should generally put the DATABASE connection string in quotes.) your

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

You must have a DATABASE statement in your HTML 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$" %>
(Note again that the connection string is quoted because of the semi-colons.) DSN names are defined using the ODBC Manager DSN definition dialog box, which can be accessed from the Control Panel by clicking on the ODBC ico). Note that on Windows NT systems, which associates DSNs with NT user IDs, any DSNs accessed by ODBscript will need to be defined as System DSNs, or they will need to be defined for the user ID that your Web Server uses. (Refer to your server's documentation. Note that some servers run as LocalUser, which cannot have DSNs assigned, so you must use a System DSN.) To define a System DSN, click on the "System DSN" tab on the dialog box before you define the DSN.

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" %>
Note that the "DRIVER={...}" string (which is inside curly braces) must be the exact text that is shown in the ODBC DSN definition window for available drivers. (This is because the ODBC Driver Manager uses this same table to look up the driver.)

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

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

Example Connection Strings Without Using DSN

  Database                Keywords

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

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

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

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

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

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

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

<% DEFAULT variable=value [, variable=value, ...] %>

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

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

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

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

Example:

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

<% EACHFILE [drive:][path][filename] %> ... <% ENDFILE %>

This command allows you to process the results of a directory file listing, which is similar to the MS-DOS "dir" command. All of the script code between the EACHFILE and the ENDFILE is repeated for each file returned by the listing request.

The results of the directory listing are set in four predefined variables: the file name is in $file_name$; the size of the file is in $file_size$; the date and time that the file was last updated is in $file_date$; and $file_is_dir$ is set to 1 if the file is a subdirectory of the current directory, or 0 if it is an ordinary file.

The drive (which is the disk specification such as "c:"), the path (the directory specification), and filename parameters are all optional. Unless otherwise specified, the drive and path are the same as the "current working directory" that is set when ODBscript is executed by the Web server. With most servers, the working directory will be set to the CGI directory where the odb.exe file is located, but some servers will set this to the location of the server's own code.

The directory path parameter can use either a "/" or a "\" as a directory/subdirectory separator. The path should begin with a "/" or "\" to indicate the disk's "root"; otherwise the path will be taken as a subdirectory of the current working directory (which is probably not what is desired).

The filename is also optional, and if it is omitted then all the files on the specified directory path will be listed. The filename can use a "*" as a "wildcard" character.

Example:

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

<% EACHINPUT %> ... <% ENDINPUT %>

This command will let you define a section of script code to process each CGI input variable that has been passed to the script. CGI variables are input to the script with an HTML form or directly in the "query string" of the URL used to invoke the CGI. All of the script between the EACHPUT and the ENDINPUT commands will be executed for each variable. In this code, the special predefined variable $input_variable$ may be used to reference the name of the current variable, and $input_value$ may be used to reference its value.

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

<% EACHMULTI variable [,variable, ...] %> ... <% ENDMULTI %>

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

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

<% EACHROW %> ... <% ENDROW %>

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

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

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

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

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

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

There are certain 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 %>

<% EXEC dos_command [arguments] %>

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

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

If the system command or program writes any "console" output as a result of execution, this output will go directly back to the user's browser. Note that the output does not go through 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$ %>

<% EXIT %>

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

Example:

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

<% FORM [ACTION=cgi,] [SCRIPT=script_file,]
[SUBMIT=text,] [TARGET=label,] [TRACE,] [HIDDEN=(),]
["label"] input_field[:size] [=value]
[,OPTIONLIST=()] [,CHECKBOX=()]
[,PASSWORD=pwd_field[:size]] [,...] %>

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

By default, the FORM command puts ACTION="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) %>

<% FORMAT variable=mask [, variable=mask, ...] %>

The FORMAT command allows you to specify special formatting for variables, such as forcing a certain number of decimal places for numbers, adding commas to mark thousands, or adding a dollar sign or pounds sign in front of money amounts. For values that are recognizable as dates, a formatting mask allows you to reformat the date. For ordinary text values, a formatting mask can be used to insert any special characters at fixed positions.

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
For variables that are not numbers, the formatting is less complicated. Working from left to right in the mask, each successive "#" character in the mask is replaced by the next successive character in the variable. Characters other than the "#" in the mask are simply copied to the output. When there are no characters remaining in the variable, then the formatting is finished, and any remaining characters in the mask are ignored.

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

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:

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>" %>
Whenever $Email$ is referenced in the script, it will be inserted at the "@" positions in the mask, and the "@" can be used multiple times in the same mask, so the above mask both displays the e-mail address and puts it into the "mailto" link.

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(@)" %>
This works for user-defined functions as well, so you can write a function that does any kind or reformatting or translating every time the variable is referenced.

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

With this command, you can define a string function that can be used in the script like any of the built-in string functions. Like those string functions, user functions are "called" (or "invoked") elsewhere in the script by referencing the function name prefixed with a "$" character, followed by optional function arguments enclosed in parentheses and separated by commas (e.g., $name(arg1,arg2,...).

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 HEADER command may be used to send an alternate HTTP header to the user's browser instead of the default "Content-type: text/html" (which is the standard header for ordinary HTML documents), or to send special-purpose headers before sending that HTML header.

Important Notes:


<% HIDDEN variable [,variable, ...] %>

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

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

Example:

    <FORM ... >
    <% HIDDEN uid, pwd, transaction_code %>
    ...
    </FORM>

<% HTTPGET url [,variable, ...] %>

This command will open a socket connection and send an HTTP request using the "get" method. The "get" method uses only the URL, possibly with an HTTP "query string" appended. That is, if the URL is to a CGI script instead of to an HTML file, a query string may follow a "?" character in the URL to pass variables to the script. The query string will usually contain input variable "name=value" pairs (no quotes), with multiple variables separated by "&" characters. Values passed in a query string should always be encoded using the $url( ) function to insure that they contain only legal URL characters.

With the HTTPGET command, you have the option of either directly coding your query string in the URL or specifying a list of variables that you want to pass. These should just be the variable names (with no "$"characters) separated by commas. The URL will be modified to include a "?" and a query string containing each of the specified variables and their current values. (That is, this option assumes that the variable names in ODBscript and their current values are the "name=value" pairs to pass.) For example, if you specify a URL and variables in the command such as:

    <% HTTPGET somedomain.com/scripts/any.cgi, var1, var2 %>
the resulting URL used for the "get" will be the same as if you coded this:
    <% HTTPGET somedomain.com/scripts/any.cgi?var1=$url($var1$)&var2=$url($var2$) %>
The first form is obviously more convenient and readable, but you may need to manually specify the query string in special cases (for example, if a variable name that you need to pass is defined but it doesn't currently contain the value that you need to pass).

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 %>
With these commands, the body of the response, if any, is simply written to the current output, which is either the user's browser or a file opened with the OUTPUT command.

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

<% HTTPPOST url, variable [,variable, ...] %>

This command will open a socket connection and send an HTTP request using the "post" method. The "post" method is typically used by HTML forms to pass more data than can be passed using the "get" method. (The "get" method is limited by the maximum size of a URL, which is server implementation-dependent and may be as low as 2K bytes.)

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

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

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

"Value1" and "value2" can be any variable (referenced by the variable name prefixed and suffixed with "$" signs), a "literal" value (a number or a text string), an arithmetic expression using numeric-valued variables or literals, or a "string function" expression that produces a text string. Arithmetic expression may use "+" for addition, "-" for subtraction, "*" (asterisk) for multiplication, or "/" for division. Parentheses, "(" and ")", may be used to indicate the order of evaluation (i.e., operations inside parentheses are performed first). A "unary" minus sign is allowed to indicate that a variable, constant, or expression inside parentheses is to be negated (e.g., $x$ / -$y$ or -($x$ / $y$)). You may also use any of the 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 %>
The advantage of this form is that the IFs are not "nested" and you only need one <% ENDIF %> to end the entire series.

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

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

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

The condition specified in an IF or ELSE IF statement can span multiple lines, but the "-->" (or the script-tag markers ";" or "%>") must mark the end of the condition.


<% IFNEW variable %>

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

Examples:

The following could be used to list items grouped by category with a category header before each group:

    <% SELECT category, item_number, description FROM item ORDER BY category ;
       EACHROW;
          IFNEW category %>
             <H1> $category$ </H1>
          <% ENDIF %>
          <P> $item_number$ $description$
    <% ENDROW %>
An example of "master/detail" reporting might be the case where you have a table that has one row for each of your customers and another table with many order rows for each customer. The master customer table must have some unique ID that can be used to identify each customer's orders in the detail order table. You need to "join" your master table to the detail table using this ID. The general form would be:
    <% SELECT * FROM master, detail WHERE master.id = detail.id
            ORDER BY master.id, detail.order_date ;
       EACHROW;
          IFNEW id %>
             (... format any data from the master table)
          <% ENDIF %>
          (... format the data from a single detail row)
    <% ENDROW %>
The "join" will produce one result row for each detail row. Every result row for a given customer will actually include all the master table data for that customer. But the IFNEW structure above will allow you to display the master data only once (i.e., whenever a new master ID is encountered in the result set).

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

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

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


<% IMPORT [DELIMITER="chars",][QUOTED,][SKIP=n,] variable_list FROM file %>
...
<% ENDIMPORT %>

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

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

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

The optional SKIP=n specification means to skip "n" lines in the file before beginning input processing. This may be used with any of the four options above. This keyword can be used, for example, to skip a field "header" line in the file by specifying SKIP=1. The skipped lines are not counted in the automatic line counter, $importrow$.

Note that the variable_list specification does not need to specify all the data fields present in each line. If only the first few fields are to be used in the loop, then you can specify just those, and the remainder of the line will be ignored.

Example:

  <TABLE>
  <TR><TH>Name</TH><TH>E-mail</TH></TR>
  <% IMPORT DELIMITER="|", name, email FROM C:/data/maillist.txt %>
       <TR><TD>$name$</TD><TD>$email$</TD></TR>
  <% ENDIMPORT %>
  </TABLE>
Example: Process a standard "CSV" format ("comma-separated values") file that begins with a "field header" line that is skipped, and which has quoted data fields:
  <% 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 %>
Example: Process same "CSV" format file as above, but let the "field header" line define the fields (note that the variable names you use in the loop must match those actually defined in the header line):
  <% 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 %>

<% INSERTFORM TABLE=table, [SCRIPT=script,] [SUBMIT=text,]
[TARGET=label,] [TRACE,] [HIDDEN=(),]
["label"] numeric_field[:size] [=value] |
["label"] 'text_field[:size]' [=value] |
["label"] #date_field[:size]# [=value]
[,OPTIONLIST=()] [,CHECKBOX=()]
[,PASSWORD='pwd_field[:size]' [,...] %>

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

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$ %>
(If you use your own script, note that while these inputs will be encrypted when they are output in the form, they are automatically decrypted when they are passed in. So, if you write your own script to handle the INSERT statement, you do not need to decrypt them -- just use $in_database$ and $in_sql$ as shown above.)

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

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

The NOTE command may be used to add a comment to a script that will not be output to a browser when the script is processed. Ordinary HTML comments inside <!-- and --> markers (that is, those that are not recognized as ODBscript commands) are always copied to the output. The NOTE command simply ignores the enclosed comment, so it doesn't get sent to the browser.

The NOTE command can immediately be followed by a colon, "NOTE:", which may make the statement more readable. (Actually, any command may be followed by a colon, which will be ignored, but NOTE is probably the only case where it's useful to do that.)

Sometimes when you are testing a script, you would like to skip over large sections of the script. The NOTE command probably won't work for that because the "comment" will be terminated by the first script-tag markers ";" or "%>" encountered (or "-->" if you are using the "comment" command style), even if it's really the terminator for another command. The easiest way to temporarily skip over a large section of a script is to put a statement <% IF 0 %> ("if zero," which is never true) at the beginning of the section to be skipped, and an <% ENDIF %> at the end. Since the section will be in a "false" conditional clause, it will not be processed or sent to the broswer.

Example:

   <% NOTE: Verify the user's ID and password before proceeding %>


<% ONERR %> ... <% ENDERR %>

The ONERR command defines an "error trap" that will be executed if any processing error is encountered by subsequent processing in the script. (The ONERR does not affect errors that occur prior to the ONERR definition.) All of the HTML and ODBscript commands between ONERR and ENDERR will be processed.

When the ONERR block is called, the message describing the processing error that occurred will be set in variable $odbic_error$. If you set this variable to empty (e.g., <% SET odbic_error = "" %> inside the ONERR block, then no error message will be output. For example, if you don't want users to see the error messages, you might log $odbic_error$ to a file and then clear the variable to suppress the standard error output. Otherwise, if $odbic_error$ contains text when the ONERR block completes, then it will be output in the standard error message format. Therefore, you might change the output message by resetting $odbic_error$ to a different value.

You can redefine the ONERR block multiple times in a script. As the script is processed sequentially, each block remains in effect until another one is defined. If you have set an ONERR block and want to return to normal error processing, you can simply define an empty block (i.e., <% ONERR ; ENDERR %>).

Examples:

The following will log the error message to a file and substitute a generic message (note: $path_info$ is the current script name):

    <% ONERR %>
       <% OUTPUT ODBscriptErr.log APPEND %>
       $today$ $time24$ $path_info$: $odbic_error$
       <% OUTPUT;
       odbic_error = "Internal processing error occurred.";
    ENDERR %>
The following will save the error message in another variable (e.g., so that it can be tested elsewhere in the script to determine if an error occurred) and then clears $odbic_error$ so no message is output.
    <% ONERR ;
       got_error = $odbic_error$, odbic_error = "";
    ENDERR %>

<% OPEN fileID, filename [APPEND | READ] %>

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

<% OPTIONLIST [size] [MULTIPLE] ["label"] [display,]column [AS var] FROM table [WHERE ...] %>

or

<% OPTIONLIST [size] [MULTIPLE] ["label"] input_field = value1, value2, value3, ... %>

The OPTIONLIST command may be used to generate an HTML "select option" input field, which is a "pull-down" menu of choices on an input form. Selecting the values from a pull-down list informs the user of the available choices and automatically prevents entering values that are not allowed. The OPTIONLIST command allows you to create the list of values from an SQL query (so that the values will automatically change if the database changes), or you can give an explicit list of values if they are predefined and never change.

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

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

In the first form of the command shown above, the options in the list will be generated by an automatic SQL query, which will be "SELECT DISTINCT [display,] column [AS var] FROM table [WHERE ...]". Each "column" value resulting from this query will be inserted as an <OPTION VALUE="..."> on the form being created. The optional "display" that precedes the "column" is another column from the same table, and if this column name is given, then the values for this column will be the actual text that will be shown to the form user in the pull-down list. The "column" will always be the name of the <SELECT> variable, and the database values for this column will be the values that are passed when an option is selected. If "display" is not given, then the "column" values will be the text values shown in the list and passed when an option is selected. (Thus, the "column" values that will be passed can be codes, i.e. numbers or abbreviations, and the "display" can be readable text for the user to see in the pull-down list. The typical way to use this option would be to have a table that pairs the codes with the display text. If the text that the user should see is simply the value that you want passed in the form, then just omit the "display" specification.)

To clarify, the HTML output generated by this command will be a <SELECT NAME="column"> [MULTIPLE] [SIZE=size]> tag, and then for each row selected from the database, a line like this will be generated:

    <OPTION VALUE="column"> display
(where "display" will be the same as "column" if "display" is not given in the OPTIONLIST command), and the list will be terminated with a </SELECT> tag.

If necessary, you can use the SQL "AS" keyword to rename the result column. When you use this option, the <SELECT> input variable name will be the "AS" renamed result column.

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>
In the first command above, the optional "size" specification is given, which produces a scrolling window 10 lines high. The available Categories are selected from the Products database table. In the second OPTIONLIST, three "warehouse" input selections are given explicitly. In "script2.odb" (which is the designated target for the ODBscript ACTION function above), the user's selections will be variables named "Category" and "warehouse" (which are not case-sensitive). In that script, you might use the following form to give the user another choice list for the products in the selected category:
    <FORM METHOD="post" ACTION="/scripts/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 specifies that the processed output is to be written into a file rather than writing the output back to the Web browser. The "filename" given in this command must specify the complete file system directory path, and no mapping of the Web server's root directory is recognized. The file will be created if it does not already exist. Note that a temporary file is actually used for writing. The original file, if any, will be deleted only when the program terminates, and the temporary file will be renamed. (This helps to avoid contention for an HTML file that another browser may be reading.)

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

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

The optional APPEND specification means that the current output is to be added to the end of an existing file. (If the file doesn't already exist, however, then one will be created and the output will be "appended" to this empty file.) Warning: If you use the APPEND, INSERT, or REPLACE options, your Web server's user ID must have both "read" and "write" access to the existing file.

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

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

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

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

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

Examples:

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

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


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

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

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

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

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

Next, fields are scanned for the presence of a percent character, "%", anywhere in the field. The "%" character is the SQL "wild card" character used in a LIKE comparison: Any occurrence of a "%" character matches zero or more occurrences of any character in the database column. For example, the SQL clause, "WHERE last_name LIKE 'harr%' " might select HARRIS, HARRINGTON, and any other names that begins with "harr". A pattern of "%ton" would select any name ending in with "ton". A pattern of "%ing%" would select any name with "ing" anywhere within the name (including the beginning and the end). If a percent character is found in the input form value, ODBscript will automatically use a LIKE comparison in the WHERE clause. (Note: If you need to select on a value that contains an actual "%" in the database, then you can use the "=" at the beginning of the input value to override the automatic LIKE comparison.) P> Next, the entered fields are checked to see if they begin with any of the special SQL comparison operators: LIKE or NOT LIKE (followed by a pattern containing any number of "%" characters; note that the LIKE is not really required if the field contains any "%" character); BETWEEN or NOT BETWEEN (followed by a lower limit, the keyword AND, and an upper limit); or IN or NOT IN (followed by a list of comma-separated values enclosed inside a set of parentheses). (The IN or NOT IN list of values can also be the single-column result rows of a "subquery" SELECT statement.) If any of these operators is found, then it is directly inserted into the WHERE clause instead of an "=" sign. NOTE: When any character-data value (as opposed to a numeric value) is used in any of these special operators, the user must enclose the data inside of single-quote (apostrophe) characters. (This is because the comparison is inserted "as is" into the WHERE clause.)

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

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

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

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

The optional "ROWS=" specification limits the number of rows that will be selected. 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:

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

Example:

    <% QBE TABLE=Employees, EmployeeID, 'LastName', 'FirstName', 'Title',
       'BirthDate', 'HireDate', 'Address', 'City', 'Region', ReportsTo %>
The TABLE to be SELECTed FROM is "Employees". "EmployeeID" and "ReportsTo" are numeric data columns, and all of the rest are character data (as indicated by enclosing the column names in apostrophes). Since no SELECT=( ) option is given in the command, all database columns will be returned, and since no ORDER=( ) option is given, the rows will be in the table's default order.

<% REDIRECT url %>

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

NOTE: This command can only "redirect" the client browser if no output has been produced by the current script file. That is, the only things that can appear before the REDIRECT command in the script file are ODBscript commands that do not produce output. For example, you might have an SQL statement to select the URL from a database table, or you might use an IF statement to conditionally execute the REDIRECT command.

If any output has already been produced by the script file before the REDIRECT command is encountered, then ODBscript cannot automatically redirect the user's browser. In such a case, ODBscript will insert an HTML text link to the specified URL instead. This link line will say "Please click here to continue". If you get this link instead of the intended redirection, check your script for extra spaces before or after the previous command lines.

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

Example:

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


<% RETURNFILE filename %>

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


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

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

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

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

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

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

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

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

The optional "ROWS=" specification limits the number of rows that will be selected. 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 %>
Input examples:
    Keywords            Rows selected
    plastic widget      Both "plastic" and "widget" in either Heading or Description
    plastic and widget  (Same as above, where "and" is implicit)
    plastic or widget   Either "plastic" or "widget" in either Heading or Description
    widget not plastic  "Widget" in either Heading or Description but "plastic" in neither
    "plastic widget"    Exact phase "plastic widget" in either Heading or Description


<% SENDMAIL SERVER=smtp_host, FROM=sender,
TO=recipient | TO=(column FROM table [WHERE ...] [,SUBJECT=subject]
[,CC=courtesy_copy_to] [,BCC=blind_copy_to] [,REPLY=reply_to]
[,ATTACH=([TYPE=mime_type] [,DISPOSITION=disp] file [,file,...])]
[,SUP] [,MIME] [,HTML] %> ... <% ENDMAIL %>

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

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

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

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

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

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

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

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

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 %>
The following method would let you include customer-specific information from your database in an e-mail message (just the first name in this case, but it could be anything):
    <% DATABASE DSN=MyDb ;
       SELECT FirstName, EmailAddr FROM Customers;
       EACHROW;
          SENDMAIL SERVER=mail.my.com, FROM=me@my.com, TO=$EmailAddr$,
                   SUBJECT=New Product Announcement &>
    Dear $FirstName$,

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

    Best regards,
    Me
    <% ENDMAIL;
       ENDROW %>
If you just want to use e-mail addresses in your database, you might do something like this, which specifies an SQL SELECT query to execute to get a list of addresses. (That is, in this case the query "SELECT email FROM customers" will be executed in the currently specified database to get a list of e-mail addresses from the column named "email" in the table named "customers". You might also give any valid WHERE clause to conditionally select the desired addresses):
    <% SENDMAIL SERVER=mail.my.com, FROM=me@my.com, TO=(email FROM customers),
         SUBJECT=New Product Announcement ;
       INCLUDE \mydir\announce.txt ;
       ENDMAIL %>

<% SESSION [TMP=directory,] [LOGIN=login_url,] [TIMEOUT=minutes,] [TAG=id_name,] [NEW,] variable [=value] [, variable [=value], ...] %>

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

<% SETCOOKIE name=value [,EXPIRES=number] [,EXPIREDATE=date] [,DOMAIN=domain] [,PATH=path] [,SECURE] %>

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

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

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

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

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

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

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

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

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

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

There is no "getcookie" command. 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 %>
The IF statement first checks to see if there is already a cookie value passed in for "tracking_id". If not, the SQL SELECT will select the largest tracking_id currently in the "users" table, add one, and assign the result "AS" tracking_id. This number is inserted back into the table. The SETCOOKIE then sets this value in the user's browser. The cookie is set to be retained by the browser for 5 years. No domain or path is specified, so the cookie will be sent to any CGI program in the same domain. Now, any user actions anywhere on the site can be associated to this unique number. In ODBscript, just use the $tracking_id$ variable in any script. If the above code were on a registration page, then additional data might be inserted into the table. On other pages, the statement <% IF NOT $tracking_id$ %> could be used as a test for an unregistered user.

<% SETMULTI variable=value [,variable=value,...] %>

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

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


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

This command allow setting certain internal processing options. The option names and values are not case-sensitive:

DATEThis 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.
MONTHSThis 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.
DECIMALThe 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.
CURRENCYThe 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_ROWSThis 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_MODEThis 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_AUTOCOMMITThe 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_ISOLATIONThe 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]] %>

With the SQL command, you can use any SQL statements acceptable to your ODBC driver. The common SQL functions of SELECT, INSERT, UPDATE, and DELETE are also defined as ODBscript commands that execute those particular SQL statements, so you do not need use "SQL" as the command name for those; just use those SQL statement as commands. Two special statements (which are not standard SQL but are provided for convenience), are "SQL TABLES" and "SQL COLUMNS", which can be used to query for information about the tables in a database and the columns in the 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 %>
After this SQL statement is executed, the variable reference $item_number$ can be used to insert the first result for the column named "item_number" anywhere into the output, and variable $description$ would refer to the value of the column named "description" in the same result row. (See the EACHROW command for formatting multiple result rows.)

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

One non-obvious caution about column names used in SQL statements: Some databases (such as MS Access) allow spaces in column names. (This is why 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;
    %>
The $sql_error$ variable used in this example is another ODBscript-defined variable which is set for each SQL statement. After a SELECT, $sql_error$ may say "No rows selected" if the statement was valid but no data met the selection criteria. If the ODBC driver has any problem executing the SQL statement, then variable $sql_error$ will contain the error message returned by the driver (e.g. the connection failed, the SQL syntax is incorrect, etc.) The IF statement above would cause the $sql_error$ message to be output whenever there were no rows returned, whatever the reason, and the lines following the ELSE would be used to format any normal result rows.

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

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.
Note: For the "SQL TABLES" and "SQL COLUMNS" commands, the result column names above are the ones defined in the ODBC standard, but specific databases may use slightly different names for the same data, and will usually have additional information that is necessary to support specific implementation features. Refer to the database documentation, but remember that you can output any SQL result row set as an HTML table with the TABLE command, without knowing the column names. So, you can execute a command like:
  <% SQL COLUMNS MyTable; TABLE BORDER=1 %>
to output all results for inspection, to determine the exact variable names you need to refer to the data.

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 TABLE command automatically formats all columns and all rows from the previous query into an HTML table. This formatting uses the HTML <TABLE>, <TR>, and <TD> tags. The html_table_opts specifications that can follow the TABLE command are the same as the HTML <TABLE> optional elements. (That is, whatever you put for html_table_opts will simply be included in the generated HTML <TABLE> tag.) For most HTML 2.0 browsers, these options can include BORDER, CELLSPACING, and CELLPADDING. Most newer browsers also allow the BGCOLOR, BACKGROUND, and various other parameters. In each case, use the standard HTML specification format.

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 %>
A common thing to do with tables is to alternate the background color of the rows to make the data easier to follow across the page. To do this with the TABLE command, you can use the table_tr predefined variable and make use of the fact that, if you put the SET value inside of quotes, a variable can contain another variable or expression that will evaluated when the variable is actually used, rather than when it is set:
    <% SET table_tr="BGCOLOR=$if(mod($row$,2),#ddddff,#ffffff)" %>


<% TRACE %> ... <% TRACEOFF %>

The TRACE command is only for debugging scripts. It causes a message to be inserted into the output stream when certain ODBscript statements are executed. The message will show how ODBscript interpreted these statements (that is, all variables and any arithmetic and logical expressions will be resolved), so you can inspect the values and the script's "flow-control logic" for errors and unexpected behavior.

To make the traced statements distinct from your script's normal output, the TRACE output messages are enclosed in <font color=red> HTML tags, inside of square brackets. The current script line number is shown, followed by the traced command statement, such as:

    [123:SET x="1" ][124:IF 1 < 10 (TRUE)]
Not all ODBscript commands are traced -- only the ones that are most useful for debugging:
SET, IF, SQL, BREAK, and EXIT.

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

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

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

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

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

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

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

Example:

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

<% UPDATEFORM TABLE=database_table, KEY=key_field,
[SCRIPT=script_file,] [SUBMIT=text,] [TARGET=label,] [HIDDEN=(),] [TRACE,]
["label"] numeric_field[:size] |
["label"] 'text_field[:size]' |
["label"] #text_field[:size]#
[,OPTIONLIST=()] [,CHECKBOX=()] |
[,PASSWORD='pwd_field[:size]]' [,...] %>

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

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

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

Important Note: You should specify KEY columns that will uniquely identify the row, which may sometimes require more than one column. All rows that satisfy the WHERE clause will be updated, so be careful with your KEY specification!

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$ %>
(If you use your own script, note that while these inputs will be encrypted when they are output in the update form, they are automatically decrypted when they are passed in. So, if you write your own script to handle the update, you do not need to decrypt them -- just use $in_database$ and $in_sql$ as shown above.)

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:

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 %>
The "SCRIPT=" specifies the script file name for ODBscript to use when the form is submitted. The "TABLE=" declares that the database table to be updated is "Employees". The "KEY=" specifies that "EmployeeID" is the record unique key field, which is a numeric field. (The KEY fields may not be updated on the form.) All of the following fields can be updated. Except for "ReportsTo", all of these fields are character data rather than numeric (as indicated by enclosing the column names in apostrophes). The update form INPUT field width for each is shown after the colon. For more examples, specifically the use of labels, OPTIONLIST, CHECKBOX, and HIDDEN, please refer to the examples for FORM or INSERTFORM.

<% USER [TABLE=user_table,] [LOGIN=login_url,] [TIMEOUT=minutes,] [EXPIRES=cookie_expiration,] [TAG=id_name,] variable [, variable, ...] %>

The USER command allows you to automatically associate data to each user visiting your site. These data are stored in a database table keyed by a unique ID, which is assigned by ODBscript and set as a cookie in the user's browser.

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

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

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

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

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

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

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

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

Example:

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

<% WHILE condition %> ... <% ENDWHILE %>

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

Example:

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

<% WRITE fileID, line %>

Writes a line of text to a file opened with the
OPEN command.

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.
Example:
    <%
    OPEN log, $pathTranslated("../logs/myLog.txt") APPEND;
    WRITE log, "$date_short$ $time24_hms$ Record $key$ updated.\n";
    CLOSE log;
    %>


Next Chapter: String Functions


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