 |
Scripting Language |
Version 3
|
Scripting Language |
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.
Example:
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:
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:
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.
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:
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):
(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:
"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:
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:
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:
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:
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:
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
<% 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.
<% SELECT ... ;
EACHROW;
IF $row$ > 50;
BREAK; note: quit processing if more than 50 rows;
ENDIF;
...
ENDROW %>
<% DATABASE "odbc_connection_string" %>
<% 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.
<% 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.)
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, ...] %>
<% 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.
<% 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.
<% 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).
Right: <% EACHMULTI var1, var2 %>
WRONG! <% EACHMULTI $var1$, $var2$ %> Don't use $ characters!
<% EACHMULTI mailing_list;
INSERT INTO Subscribe (mailing_list, email)
VALUES ('$mailing_list$', '$email$');
ENDMULTI %>
<% EACHROW %> ... <% ENDROW %>
<% 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] %>
<% EXEC /httpfile/cgi-bin/odb -i/httpfile/$script$ -o/httpfile/$output$ %>
<% EXIT %>
<% 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,]
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.)
[SUBMIT=text,] [TARGET=label,] [TRACE,] [HIDDEN=(),]
["label"] input_field[:size] [=value]
[,OPTIONLIST=()] [,CHECKBOX=()]
[,PASSWORD=pwd_field[:size]] [,...] %>
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) %>
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, ...] %>
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,...).
...
<% RETURN [expression] %>
All of the commands and text between the <% FUNCTION ...%> statement and the <% RETURN %> statement will be executed each time the function is called from the script. The function can directly output text and HTML, much like an included file, but the RETURN statement can also specify an expression that is evaluated to produce a character-string value that is returned as the "value of the function". This returned value allows user string functions to be used in variable expressions such as those allowed in IF and SET statements, or as arguments for other functions: the value of the RETURN expression is inserted into the referencing expression. Like the built-in string functions, if you call a user string function in ordinary text or HTML output, then the evaluated RETURN expression (as well as any direct output produced by the function) is simply inserted into the output at the point that the function is called. Like other string functions, you can use the returned value of a user function in an arithmetic expression, provided that the returned string is a valid number.
The "arg_variable" or comma-separated list of arg_variables defined in the FUNCTION command must be enclosed within "(" and ")" characters. These variable names are used within the function body to reference the values passed when the function is called. For example, if you define a function such as <% FUNCTION myFunc (arg) %> and then call the function from the script with a reference such as $myFunc(123), then inside the function body, the variable $arg$ will have the value "123". Like any other function, the arguments passed to the function can be any combination of variables, literal character strings, or arithmetic/logical expressions. Each argument expression in the call from the script will be fully evaluated and the result will be assigned to a corresponding argument variable in the function definition. Those values can then referred to by those variable names within the function. These function argument variables are optional and can be omitted from the FUNCTION declaration, but when the function is called from a script, the parentheses are required even if no arguments are passed. That is, if the function does not require any arguments, call it with a reference such as $name( ). In that case, you can also use an empty set of parentheses in the function definition, such as "FUNCTION name ( )" -- and you must do so if you also have a list of "local" variables as explained below -- but if the function does not require any arguments or local variables, the empty "( )" is optional in the function definition.
Important note: In the FUNCTION declaration line itself, do not prefix the function name with a "$" character, and do not enclose the argument variable names or local variable names inside of "$" characters:
Right: <% FUNCTION name (arg1, arg2) %>
WRONG! <% FUNCTION $name ($arg1$, $arg2$) %> Don't use $ characters!
When you use the function in the script, you will need the "$" name prefix, and when you use the function arguments in the function body, you will need the "$" characters around the names, but these must not be used in the FUNCTION declaration statement.
Important note: The number of arguments passed when a function is called from the script must always exactly match the number of arguments defined within the "(" and ")" characters in the FUNCTION declaration statement. (If you don't necessarily need a particular argument for a particular call, you can pass a zero-length string, "", as an argument, and the function can test whether any argument is empty in the normal manner, e.g., <% IF $arg$ %>.)
Following the list of argument variables enclosed in "(" and ")" characters, you can optionally specify a comma-separated list of "local" variables. These are variables that will be used within the function but which should not conflict with any variables that may have the same names outside of the function, and which should not retain any values outside of the function after the call is completed. That is, these variables will be strictly local within the function, with no effect outside the function. This feature is provided because ordinarily, in addition to the passed arguments, functions also have access to all of the variables used elsewhere in the script, and any variables set or created within the function are also accessible from the script after the function is called. However, it is generally considered poor programming practice to set externally-used variables within a function or to use variables in addition to those passed as arguments. You can do those things if you chose to, because functions do have access to the full set of variables in the script, but doing so will limit the generality of the function, and it can also create some hard-to-find bugs if the wrong variables are inadvertantly altered within a function. Specifying the list of local variables will insure that no such problems occur, whether or not variables with the same names exist outside of the function. Temporary variables will be set up for these local variables, initialized to be empty strings, and you can set them within the function like any other variable. But any variables outside the function that happen to have the same names are unaffected, and these temporary variables are deleted when the function completes.
A function does not necessarily need to have any statements in its body (i.e., between the FUNCTION and RETURN statements), if all the required work of the function can be performed by the RETURN expression itself. Therefore, a function can be used simply as a "shorthand" for a long, complicated expression that is specified completely in the RETURN statement. This is useful if the function simply performs a calculation using the passed arguments, or just reformats the arguments in some special manner.
A function can call itself "recursively". That is, the function body can use a
reference to the same function. However, you should take some care that the
recursion has a specific limit, or ODBscript will
quickly use up all available memory and terminate abnormally. For example, you
might define a function that outputs a directory listing using the
EACHFILE command. This command will only list the files
at one level of the directory hierarchy. However, if a file is a subdirectory
(which is indicated by setting $file_is_dir$ to "1" or "true"), you could have
the function call itself recursively to list the files in that subdirectory.
(In this case, the recursion will be limited automatically by the depth of the
directory tree.)
Important Notes:
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:
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:
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:
<% 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.
<% 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.
<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.
<% 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).
<% 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$) %>
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.
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:
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):
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.
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:
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.)
The variables to be extracted are identified by the "variable_list" parameter. There
are four options for this list:
The "QUOTED" keyword indicates that fields are enclosed in either single-
or double-quote characters (which allows fields to contain commas, for example).
These quotes will be removed from the values. A
single-quoted field can freely contain double-quote characters, or a double-quoted
field can contain single-quotes. Otherwise, two consecutive characters of the
same type as the outer quote characters will be replaced with a one character
of that same type. (For example, a value such as "a ""quoted"" word" will
be taken as a single field, the beginning and ending quote characters (") will
be removed, and the pairs of qoutes ("") in the middle will be replaced by one
(") character, so the final value will be: a "quoted" word.) Quoted fields can
contain end-of-line characters (for example, a mulitiple-line address can be
contained in a single quoted field), and the extracted value will span to the
ending quote. (Otherwise, any end-of-line character not inside of quotes
will mark the end of the data line.)
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:
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.
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:
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:
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.)
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:
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):
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.
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:
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:
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$.
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:
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:
<% IF [NOT] value1 condition value2 [AND | OR ...] %> ... <% ELSE %> ...
<% ENDIF %>
<% 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.
<% 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 %>
<% IFNEW variable %>
<% 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).
<% IMPORT [DELIMITER="chars",][QUOTED,][SKIP=n,] variable_list FROM file %>
This command allows you to read a text file and extract data values as variables.
...
<% ENDIMPORT %>
This command automatically loops through the import file one line at a time,
much like EACHROW, and on each iteration, each variable in the variable_list
will have a "current value" as read from the current line of the inport file.
The end of the processing loop must be marked with the
ENDIMPORT command. Inside this loop, you can use the variable $importrow$ as
the current line number in the input file, and when the loop terminates
$importrow$ will be the total lines read.
Example:
<% IMPORT a_line FROM d:/temp/afile.txt %>
Example:
<% IMPORT DELIMITER=",", QUOTED, var1, var2, var3 FROM c:/data/dfile.dat %>
Example:
<% IMPORT varA:24, varB:12, varC:1 FROM d:/temp/fixedData.txt %>
Example: (The file must begin with a "header" line to define the fields)
<% IMPORT * FROM d:/temp/data.txt %>
<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 %>
<% INCLUDE filename [variable=value, ...] %>
<% INCLUDE D:\httpfile\standard_header.htm %>
<% INSERTFORM TABLE=table, [SCRIPT=script,] [SUBMIT=text,]
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.
[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]' [,...] %>
<% 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.)
<% 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.
<% 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.
<% 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] %>
<%
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, ... %>
<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.
<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>
<% OUTPUT filename [APPEND | INSERT AFTER marker | INSERT BEFORE marker |
REPLACE BETWEEN marker1, marker2] %>
<% 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# [, ...]
%>