 |
Internet Connector |
Version 2.2
|
Internet Connector |
For all versions of the program prior to Release 2.1, ODBiC commands are embedded in the template files by using the standard HTML "comment" notation: The start of an HTML comment is marked by the characters "<!--" and the end of the comment is marked by the characters "-->". Browsers ignore these comments, but ODBiC will look for command names beginning immediately after the characters "<!--". NOTE: There must not be any spaces between the "<!--" character sequence and the first character of the command name, so the "<!--" is effectively part of the command name. Command names may be typed in uppercase or lowercase characters.
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 template 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 templates 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 template 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 templates. The two styles can be mixed in the same template, 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 ODBiC commands are simply copied to the output. If you see that a "command" is being output as a comment instead of being executed, check the spelling and syntax carefully. (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 template's output with the browser's "View Source" function is an important "debugging" technique.)
For security reasons, ODBiC commands cannot
be embedded in variables. That is, only commands that are actually in the
template file, before variable text substitution, will be recognized.
Example:
The DATABASE statement specifies the ODBC database connection that will be used
for subsequent SQL commands. ODBiC uses the
ODBC SQLDriverConnect function to connect to databases, so any of the
optional arguments supported by a particular driver are valid in this
connection string. Typical arguments are "DSN" (Data Source Name), "DRIVER"
(explicit driver specification), "UID" (database-defined user ID), and
"PWD" (user password). (Note that the ODBC connection string uses semi-colon
delimiters between arguments, which is also the ODBiC
"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 template file before any
SQL commands. (Optionally, you may pass in a connection string in the
variable named "database"; see the section
Predefined Variables.) This command
does not actually establish a connection, however. Rather, the connection
string specified by this statement will be used to connect when an SQL
command is executed.
The connection string set by this statement applies to all subsequent SQL
commands until a different DATABASE command is encountered. If you
need to access another database, just use another DATABASE statement before
those SQL statements. (This does not mean that the database connection is
re-established for each SQL statement, however. The connection established
by the first SQL statement stays open until another SQL statement is
executed with a different connection string, or until
ODBiC terminates.)
You can use variables anywhere in the DATABASE statement (including the DSN).
One common usage would be variables to insert the user's ID and password,
which you might get from an input form:
However, you can bypass all DSN database associations by giving a complete
ODBC connection specification in the DATABASE statement. This would include a
driver and file type specification, file location path, and various options.
Refer to the ODBC documentation for complete details, but here is an example
of a connection string for an MS Access database contained in the file
C:\httpfile\db\products.mdb:
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
template file and there is a DEFAULT value defined, then the DEFAULT value will
be inserted. (Otherwise, of course, the variable's actual value will be
inserted.) NOTE: An "empty-valued" variable is one that is not defined
by an INPUT field or a database column, or a defined variable that
contains no data.
You can give a list of variable=value pairs in the DEFAULT statement. All
values are treated as text strings. Quotes around values (e.g.
variable="value" or variable='value') are not required unless the value
contains a comma (which separates variable specifications in the statement),
but you may use quotes if you like. If you don't use any quote marks around a
value text string, no leading or trailing spaces for the value will be included.
(For example, if the command were "DEFAULT var1 = value one , var2 = value 2
, ...", the actual text values used would be "value one" and "value 2".) If
the actual text value contains any double-quote characters ("), you must use
single-quotes (') around the string, such as '"value"'.
You can use multiple DEFAULT statements, or you can use a single DEFAULT
statement that spans multiple lines (with the "-->" 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 template file. (The default is in effect from the point of the
DEFAULT command until another DEFAULT is specified for the same variable.)
And you may define a default value that is another variable (for example,
"<% DEFAULT var1 = $var2$, ... %>"). (Note that this variable
substitution will be done when the defaulted variable is actually
referenced, not when the DEFAULT value is set by this command. This allows
the DEFAULT value to change as "var2" changes.)
Example:
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 ODBiC is
executed by the Web server. With most servers, the working directory will
be set to the CGI directory where the odbic.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 template
(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 template):
(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 ODBiC template to process each value in turn. Inside the loop,
$variable$ refers to the "current" value, which changes with each iteration.
You can specify a list of variable names in the EACHMULTI command, with the
variable names separated by commas. In this case, "parallel" sets of
multi-variables are processed, much like a row returned from an SQL query.
Parallel sets of multi-variables can be created with the
SETMULTI command or with parallel sets of
fields on a form. (That is, your input form could be a table with several
columns of variables, and several rows with the same variable names.)
If you specify a list of variables, the EACHMULTI loop will continue for as
many iterations as the maximum number of any one of the variables.
If you "run out" of any of the other variables before this maximum number is
reached, those variables become "undefined" (unless you set a DEFAULT for
them.)
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 ODBiC commands that may not be
used inside of the EACHMULTI loop because they should not be used repetitively.
These commands are not allowed: DEFAULT, FORMAT, TRANSLATE, and VALIDATE.
Example:
Suppose that a form has an entry for an e-mail address and a <SELECT
MULTIPLE> list of mailing lists that a user could subscribe to. The
following will insert the e-mail address into the database for each selected
mailing list:
The EACHROW command marks the beginning of formatting that is to be applied to
each result row after a SELECT query. The end of the formatting is marked by the
ENDROW command. (The ENDROW is required if you use an EACHROW command.)
You can use any text or HTML tags in the EACHROW formatting, and any
reference to a database variable (that is, a result column name prefixed and
suffixed with $ signs) will be replaced by the value of that column in the
current result row.
The EACHROW command does not need to be immediately after the SQL SELECT
statement -- for example, you may output a table header before you begin
formatting the results -- but EACHROW always refers to the last SQL SELECT
statement that was executed.
As noted previously, there is no "fetch" command. An SQL statement that is a
SELECT causes an immediate, automatic fetch of the first result row. Additional
"fetches" are automatically performed at the bottom of the EACHROW
processing, until there are no more rows. Therefore, if you are selecting a
single row from a table, you do not need to use the EACHROW command.
You may use additional SQL statements inside an EACHROW loop, but you can only
"nest" SQL statements three (3) levels deep. (This includes the implicit SQL
statements generated by the OPTIONLIST, QBE, and SEARCH commands.) An SQL
statement inside an EACHROW loop can connect to a different database. If you use
an SQL statement inside an EACHROW loop, you can use EACHROW, TABLE, or UPDATEFORM
to format the results, if necessary. (If you SELECT a single row from inside an
EACHROW loop, you don't need any of those looping commands because the result
columns from that row are immediately available as variables, just as with an SQL
statement outside an EACHROW loop.)
Bear in mind, however, that "nested" SQL statements create a great deal of
overhead, and that nested SELECTs are rarely necessary. Instead, you can usually
use a "join" operation to get data from two tables with a single query, then use
the IFNEW test to do "master/detail" grouping. (See the
IFNEW examples.)
There are certain ODBiC commands that may not be
used inside of the EACHROW loop because they should not be used repetitively.
These commands are not allowed: DEFAULT, FORMAT, TRANSLATE, and VALIDATE.
Inside an EACHROW specification, you may use the
ODBiC
variable $row$ to reference the current row number. The $row$ variable is
initialized after each SQL statement (see the SQL command)
and it is incremented for each fetched result. You might use this variable to
enumerate the results, or you might want to test for particular row numbers.
For example, you could use a conditional statement <% IF $row$ = 1 %>
to do some special output, such as a table header, before any results are output.
(But it is generally easier to put such "first row" formatting between the SQL
statement and the EACHROW command: Any output after the SQL but before
the EACHROW will be done for the first row only.) The $row$ variable is most
useful when you want to limit the number of rows displayed.
Since the EACHROW command always loops through all of the result rows, you should
not use EACHROW combined with any of the other result looping commands (TABLE,
UPDATEFORM, or OPTIONLIST), unless you have another SQL SELECT statement nested in
the EACHROW loop.
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 ODBiC variables) to the system
command or program.
One useful purpose for this command is to execute additional
ODBiC processes. For example, in a template file
that has made a change to a database, you might execute
ODBiC to regenerate a "fixed" page. Using this
technique, rather than always querying the database in "real time", can save
on document access time.
If the system command or program writes any "console" output as a result of
execution, this output will go directly back to the user's browser. Note that
the output does not go through ODBiC, so no
ODBiC processing on the output is performed.
However, it is possible to "redirect" the console output to a file by using the
">" character, (e.g. <% EXEC program > file %>) then
INCLUDE the file in the current template. You may also
wish to use the redirection to prevent any output from going to the
user's browser.
For security reasons, the command string cannot contain a "$T" command
separator (which, on a command line in some versions of DOS, can be used to
issue multiple commands on a single line). Also for security reasons, the EXEC
command cannot be used inside an EACHROW loop (since the EACHROW specification
can be passed in from a form).
Example:
The EXIT command causes ODBiC to stop processing
the input template file. It is most useful inside an IF statement, such as
after output of an error message. In such cases, an "early exit" can avoid
some complicated and heavily nested IF/THEN/ELSE clauses that would be
necessary to skip the rest of the file.
Example:
BREAK
Terminate a processing loop (e.g., WHILE, EACHROW) 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 template 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 template file INSERT
Execute an ODBC SQL INSERT statement INSERTFORM
Generate an automatic database insert form and SQL statement NOTE
Template comment, not output to browsers ONERR, ENDERR
Define an "error trap" to be executed if a processing error is encountered OPTIONLIST
Create an HTML "select option" list from an SQL query or a given list of values OUTPUT
Write the output to a disk file QBE
Generate and execute a "query by example" SQL statement REDIRECT
Redirect user's browser to a different URL 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
<% 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 DSN definition dialog box (which can be
accessed from the Control Panel by clicking on the ODBC icon). Note that on
Windows NT systems, which associates DSNs with NT user IDs, any DSNs accessed
by ODBiC will need to be defined as System DSNs,
or they will need to be defined for the user ID that your Web Server uses.
(Refer to your server's documentation. Note that some servers run as
LocalUser, which cannot have DSNs assigned, so you must use a System DSN.) To
define a System DSN, click on the "System DSN" button on the dialog box before
you define the DSN.
<% 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 template 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 template code to process
each CGI input variable that has been passed to the template. CGI
variables are input to the template with an HTML form or directly in the
"query string" of the URL used to invoke the CGI. All of the template
between the EACHPUT and the ENDINPUT commands will be executed for each
variable. In this code, the special predefined variable $input_name$
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_name$</b> has the value "$input_value$".
<% ENDINPUT %>
<% EACHMULTI variable [,variable, ...] %>
... <% ENDMULTI %>
All the template code between the EACHMULTI and the ENDMULTI is repeated for
each instance of multiply-defined variables (i.e., multiple instances
of values with the same name).
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/odbic -i/httpfile/$template$ -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,] [SUBMIT=text,]
This command will generate a "generic" HTML data input FORM with an ACTION link
to ODBiC. For example, the generated form might be
used as input to a query template.
(This is command is similar to the UPDATEFORM and
INSERTFORM commands, except that no SQL statement is
generated.)
["label"] input_field[:size] [=value]
[,OPTIONLIST=()] [,CHECKBOX=()] [,HIDDEN=()]
[,PASSWORD=pwd_field[:size]] [,...] %>
By default, the FORM command puts ACTION="odbic.exe" in the HTML <FORM> declaration. If you want to send the form input data to a different CGI program, you can give an ACTION="cgi" parameter to this command to specify a different CGI program. If you use this parameter, you should also give the CGI URL path, such as ACTION="/cgi-bin/program.exe".
The optional SUBMIT parameter can be used to define the text to be used for the form's "submit" button. The default is "Submit".
You can specify one or more input_fields separated by commas. For each, an HTML <INPUT NAME="..."> is added to the form, using the given field name. An input_field box can be initialized to a specific value by using the format "input_field=value". (The "value" does not need to be enclosed in double-quotes unless in contains any commas.) The user will be able to edit this value, if desired.
You can control the size of each <INPUT> field by using the optional ":size" specification (i.e., a colon followed by a number) after the input_field name in the FORM list. For example, "first_name:24" would produce <INPUT TYPE="text" NAME="first_name" SIZE="24"> which would be an input box named "first_name" that is 24 characters wide. If no size is specified, ODBiC uses a default size of 50 characters. If you give a field size larger than 99, then ODBiC automatically uses an HTML <TEXTAREA> input, which is a multiple-line scrolling window. This window will be at most 64 characters wide and as many lines as it takes to hold your specified field size. For example, a specified size of 250 would produce a 50-character, 5-line textarea window. However, you can directly specify the size of a <TEXTAREA> by giving the "size" specification as two numbers separated by an "x" (for example, 'Description:64x4'), where the two numbers are to be the number of columns and the number of rows. (The numbers can be given in either order; the larger number will always be used as the field width and the smaller number will be the number of lines.)
By default, the input_field name is also displayed on the form, immediately in front of the input box, to identify the requested data. For improved appearance and readability, ODBiC capitalizes the first letter of this "label", converts any underscore characters to spaces, and capitalizes any letter following an underscore. For example, "customer_name" would have a label of "Customer Name".
If you want to have a different input box label, something other than the input_field name, you can specify a label in double-quotes immediately in front of the input_field name. Note: You must have a space, not a comma, between the quoted label and the input_field name.
Anywhere in the list of input_fields for this command, you may specify "OPTIONLIST=(...)", and the arguments inside the parentheses can be the same as the OPTIONLIST command. Specifically, you can have "OPTIONLIST=(column from table)" to select the options from the database, or you can give a comma-separated list of literal values in the form of "OPTIONLIST = (input_field = value1, value2, ...)". In either case, if the given input_field is already defined as an ODBiC variable and it has a current value that is in the list of options shown by this command, then that option will be "SELECTED" in the list. (That is, the current value will already be highlighted in the list the user sees). Therefore, you can use this feature to preset a particular option to SELECTED by using a SET input_field=value before using input_field in the FORM command.
You can also specify a CHECKBOX variable in the FORM command. Again, this may be anywhere in the list of input_fields, and the format is "CHECKBOX=(input_field, checked_val, unchecked_val)". The "checked_val" will the variable's value if the user checks the box; otherwise the variable will have the "unchecked_val". Similar to the OPTIONLIST, if the specified input_field is already defined as a variable and it has a current value equal to the "checked_val", then the user will see the box as already checked. Otherwise it will be unchecked. (NOTE: Browser's only send a value if a checkbox is checked, and send nothing for that variable if the box is unchecked. Therefore, the "unchecked_val" is passed to the next template in a hidden variable named "default" -- one of the predefined input variables that ODBiC always processes -- with a value of "input_field=unchecked_val". Like a DEFAULT statement in a template, this value will be used if the user doesn't check the box. Therefore, if you use the FORM CHECKBOX with your own template, don't specify a DEFAULT for the checkbox variable in the next template.)
If you need to pass "hidden" variables in the form, you can use the HIDDEN parameter anywhere in the list of input_fields. The format is "HIDDEN=variable" for a single variable or "HIDDEN=(var1, var2, ...)" for a list of variables. This parameter works like the HIDDEN command to generate <INPUT TYPE="hidden"> fields for the given variable or list of variables. Note that only variables can be used in the HIDDEN parameter list, so you may need to SET these variables before using them in the FORM.
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 odbic.exe). Many fields have sizes declared (which defaults to 50 characters if ":size" is not given). This example also provides a double-quoted "display label" for each field, which is different from the input_field variable name, to show the proper syntax for each type of option. (If these quoted labels are not used for a field, then the display label would be the same as the input_field name that immediately follows the quoted label.) Unlike INSERTFORM and UPDATEFORM, the FORM command does not need to have apostrophes (single-quotes) around text-data fields. (Those commands need the single-quotes to generate properly quoted values in SQL statements, but FORM does not generate any SQL.)
<% FORM TEMPLATE="/httpfile/query.odb",
"First Name" firstname:24, "Last Name" lastname:24,
"Home Address" address, "City, State, and Zip" csz,
OPTIONLIST=("Department" dept from Departments),
CHECKBOX=("Hourly?" hour, Y, N),
"Salary or hourly rate" rate:8,
HIDDEN=(uid, pwd) %>
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 template before the point that the variable will be referenced for output. (Specifically, the FORMAT command should not, and cannot, be used inside an EACHROW loop. Specify the FORMAT mask before the EACHROW.) Note, however, that there is a string function, $format( ), that does perform this same formatting function at the point that it is encountered.
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 ($) or pound sign character (£) as the first mask character (or as the second character if you have a plus or minus sign as the first character.) This will cause a dollar sign or pound sign to be added to the front of a numeric value. 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.
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[, arg_variable, ...])] %>
With this command, you can define a string function that can be used in the
template like any of the built-in string functions.
Like the string functions, user functions are "called" (or "invoked") elsewhere
in the template 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 template. 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 argument variables defined in the FUNCTION command 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 func (arg) %> and then call the function from the template with a reference such as $func(123), then inside the function body, the variable $arg$ will have the value "123". These function argument variables are optional and can be omitted from the FUNCTION declaration, but when the function is called from a template, 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( ).
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 inside of "$" characters:
Right: <% FUNCTION name (arg1, arg2) %>
WRONG! <% FUNCTION $name ($arg1$, $arg2$) %> Don't use $ characters!
When you use the function in the template, 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 template must always exactly match the number of arguments defined in the FUNCTION declaration statement. (You can, however, 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$ %>.)
A function does not 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.
In addition to the passed arguments, functions also have access to all of the variables used elsewhere in the template, and any variables set within the function are also accessible from the template after the function is called. Although 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. Doing so, however, will necessarily limit the generality of the function, and it also can create some hard-to-find bugs if the wrong variables are inadvertantly altered within a function. Therefore, you should take care with all variable names used in a function, and in particular you must be careful to make sure to use unique names for any variables that are intended to be "working" variables only within the function.
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 ODBiC 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 ODBiC 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 template 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 ODBiC commands only if the
specified conditions are true.
Every occurrence of an IF statement must have a matching ENDIF to mark
the end of the conditional processing. You can "nest" IF statements (that is,
you can have another IF statement in the conditional part of an IF or ELSE
section).
"Value1" and "value2" can be any variable (referenced by the variable name
prefixed and suffixed with "$" signs), a "literal" value (a number or a text
string), an arithmetic expression using numeric-valued variables or literals,
or a "string function" expression that
produces a text string. Arithmetic expression may use "+" for addition, "-"
for subtraction, "*" (asterisk) for multiplication, or "/" for division.
Parentheses, "(" and ")", may be used to indicate the order of evaluation
(i.e., operations inside parentheses are performed first). A "unary"
minus sign is allowed to indicate that a variable, constant, or expression
inside parentheses is to be negated
(e.g., $x$ / -$y$ or -($x$ / $y$)). You may also use any of
the 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,
ODBiC assumes that you are using multi-level
grouping and resets all "lower" level variables whenever any "higher" level
produces a "new" result. (That is, they will also return a "new" result the
next time they are tested, without actually testing the old values. This is
to force a "new detail" break when the "master" changes, even if the actual
value of the "detail" column happens to be the same as the previous "master".)
Note that the only argument in the IFNEW statement is a single variable. Since
this argument must be a variable, you can use just the variable name
without the "$" signs around it. (However, any "$" signs will be ignored.)
The variables to be extracted are identified by the "variable_list" parameter. There
are four options for this list:
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 for specifying the input variables. 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:
The INCLUDE command will read and process the specified file. The INCLUDE file
can contain any HTML and ODBiC commands, and it is
processed as if the text from that file were "pasted" into the template file at
the point of the INCLUDE command. (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 template.
NOTE: The referenced filename must have the full directory path
specification, such as would be used to open the file with a text editor, and
no Web-server directory mapping will be applied. (If there is no directory path,
then the file is assumed to be on the "current directory", which would be the
CGI directory.) 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:
NOTE: You must define a database connection before using INSERTFORM,
even if the current template does not use any SQL commands.
As described below, the form will include a "hidden" HTML field to specify the
"database" to use for the SQL INSERT statement. This will be the same as the
DATABASE connection in effect when the INSERTFORM command
is used. If you haven't executed any SQL commands prior to using INSERTFORM, it
will still be necessary to provide a DATABASE command somewhere before the
INSERTFORM.
You must specify a TABLE="..." parameter, which will be the database table
used in the generated SQL INSERT statement.
You can optionally specify a TEMPLATE="..." parameter, which will be the
ODBiC template to use when the form is submitted.
If you do not specify a TEMPLATE, ODBiC will use
its "no template" processing, which will
execute the SQL statement using the "default.odb" template.
If you define your own template to process the INSERTFORM input, you should
have at least these two commands to execute the SQL INSERT statement that will
be passed in:
The optional SUBMIT parameter can be used to define the text to be used for
the form's "submit" button. The default is "Insert".
Like the FORM command, you can specify display labels
for any input_field, and you can control the size of each input box.
You can specify an initial value to show in the input box as
"input_field=value", which the form user may edit, if desired.
You can also use OPTIONLIST, CHECKBOX, and HIDDEN inputs. See the
FORM command for complete input_field details.
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 template, you would like to skip over large
sections of the template. 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 template
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 template. As the template
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 template name):
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 template 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, ODBiC processing a second
template 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 template
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 template 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 template 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 template file. Therefore, if you want all output to go into the file,
you must make the OUTPUT command the first command in the file.
An OUTPUT command without any file name (i.e., <% OUTPUT %>) causes
output to revert back to the browser. Thus, you can embed an OUTPUT command in
the file after writing some browser output, write some data into the file,
and use <% OUTPUT %> to revert back to normal browser output.
The optional APPEND specification means that the current output is to be added to
the end of an existing file. (If the file doesn't already exist, however,
then one will be created and the output will be "appended" to this empty file.)
Warning: If you use the APPEND, INSERT, or REPLACE options, your Web
server's user ID must have both "read" and "write" access to the existing
file.
The two INSERT options allow you to insert new text somewhere in the middle of an
existing file. The insertion point is determined by searching the specified file for
the given "marker" text string. For example, you can insert a user's message in a
"guestbook" page by putting some unique text string in that page to identify where
new messages are to be inserted. The marker might be in an HTML comment, such as
<!-- insert_messages_here -->. The command <% OUTPUT guestbook.odb INSERT
BEFORE insert_messages_here %> would copy the existing file up to that marker,
then begin inserting any output produced by the template. When a new OUTPUT command
is encountered in the template (which causes the current output to be closed), then
the full line containing the marker text string is reinserted into the file and the
remaining part of the existing file is copied into the new file. Warning:
Do not use quotes around the marker text string unless the quotes actually exist
in the target string in the file. Also, do not attempt to use "-->" as part of
the marker text string; it will be taken as the end of the OUTPUT command.
INSERT AFTER is similar except that the insertion will be immediately after the
given text marker. For example, the above description of INSERT BEFORE would cause
the guestbook entries to appear in the order that they were entered, but INSERT AFTER
would cause the entries to appear in "latest first" order.
REPLACE BETWEEN allows you to replace everything between two markers with new
text. The existing file is copied down to and including the first specified marker,
and all text from that marker down to the second specified marker is discarded.
The template's output is inserted at this point, then all of the text from
the second marker to the end of the existing file is copied to the new file.
REPLACE BETWEEN will allow you to update a section of a file without disturbing
anything else in the file.
OUTPUT commands are not "nested". That is, each occurrence of an OUTPUT
command causes any previous output file to be closed, and you may not resume
writing to that closed file. (You could, however, use the APPEND option to add to
the end of that file.)
The OUTPUT file will be created whether ODBiC is run
as a CGI program or as a command line program. If
ODBiC is being run as a CGI program and an output
file has been specified (either with the OUTPUT command or by passing in a variable
named "output"), but no output has been sent to the Web browser, then the page
returned to the user will simple say "File created: filename". (If multiple
output files have been created, only the last one will be identified to the user.)
Examples:
The OUTPUT command APPEND option is useful for creating log files of activity.
For example, you might log $sql_statement$ after ever database operation initiated
by FORM input, so that you could track all updates. Or you might log the full FORM
input by using the SHOWINPUT command. Another use might
be to allow users to add their e-mail addresses to a mailing list to be used with
the SENDMAIL command. Here's an example of logging all the
information from an input FORM:
The QBE command automatically generates and executes a "Query By Example" SQL
SELECT statement by examining the specified input fields. This command allows a
wide variety of different queries from a single input form. The QBE-generated
query statement has two major advantages over direct SELECT statements that
you might use in a template file. The first is that the form user,
rather than the template designer, controls which columns are actually included
in the selection criteria for any given query. That is, the input form can have
entry fields for all of the database columns for which the user might
want to specify query criteria, but the user does not have to specify all of
them in a given query. Instead, the user may enter values in any one or in
several fields, and only those fields will actually be used in the query.
The second advantage is that the values entered by the form user do not need
to be tests for an exact match in the database (i.e., "column = value").
Rather, the user can specify a comparison for "not equal", "less than",
"greater than", "less than or equal", or "greater than or equal" the given
value. The user can also use the flexible SQL comparisons of LIKE or NOT LIKE
(i.e., pattern matching), BETWEEN or NOT BETWEEN (given limits), and
IN or NOT IN (i.e., contained in a specified list of values).
The WHERE clause in the generated SQL statement will depend on which of the input
form fields contain any values. That is, input fields with no user-entered values
(and no DEFAULT values!) will not be included in the WHERE clause.
Form input fields that do contain values will be checked to see if they
begin with any of the following characters: "=" (equal), ">" (greater than),
"<" (less than), ">=" (greater than or equal), "<=" (less than or
equal), "<>" or "!=" (not equal). If any of these characters are found
at the beginning of the field, then the generated WHERE clause will use the
specified comparison operator (instead of "="), between the database column
and the input value.
Next, fields are scanned for the presence of a percent character, "%", anywhere
in the field. The "%" character is the SQL "wild card" character used in a LIKE
comparison: Any occurrence of a "%" character matches zero or more occurrences
of any character in the database column. For example, the SQL clause, "WHERE
last_name LIKE 'harr%' " might select HARRIS, HARRINGTON, and any other names
that begins with "harr". A pattern of "%ton" would select any name ending in
with "ton". A pattern of "%ing%" would select any name with "ing" anywhere
within the name (including the beginning and the end). If a percent character
is found in the input form value, ODBiC will
automatically use a LIKE comparison in the WHERE clause. (Note: If you need to
select on a value that contains an actual "%" in the database, then you can use
the "=" at the beginning of the input value to override the automatic LIKE
comparison.)
P>
Next, the entered fields are checked to see if they begin with any of the
special SQL comparison operators: LIKE or NOT LIKE (followed by a pattern
containing any number of "%" characters; note that the LIKE is not really
required if the field contains any "%" character); BETWEEN or NOT BETWEEN
(followed by a lower limit, the keyword AND, and an upper limit); or IN or NOT
IN (followed by a list of comma-separated values enclosed inside a set of
parentheses). (The IN or NOT IN list of values can also be the single-column
result rows of a "subquery" SELECT statement.) If any of these operators is
found, then it is directly inserted into the WHERE clause instead of an "="
sign. NOTE: When any character-data value (as opposed to a numeric value)
is used in any of these special operators, the user must enclose the
data inside of single-quote (apostrophe) characters. (This is because the
comparison is inserted "as is" into the WHERE clause.)
If none of these special operators is found in the input value, then the SQL
WHERE clause will just be "column = value" for that input field.
In the QBE command, the "TABLE=" specification is required. The generated SQL
statement will be "SELECT ... FROM" the specified table.
The "SELECT=" specification is optional. If it is given, then only the specified
columns will be selected from the table. A single column can be specified as
"SELECT=column" and multiple columns can be specified in parentheses,
"SELECT=(col_1, col_2, ...)". If the SELECT option is not given, then the
generated SQL statement will be "SELECT * FROM" the given database table.
("SELECT * " causes all defined columns to be returned).
The "ORDER=" specification is also optional. If it is given, then the specified
columns will be used in an ORDER BY clause. A single column can be specified as
"ORDER=column" and multiple columns can be specified in parentheses,
"ORDER=(col_1, col_2, ...)". If no ORDER is specified in the QBE command, then no
ORDER BY clause will be added to the generated SQL statement, and the query results
will be in the table's default order. (The default order is usually by the
primary key column, or if there is none, the order that the rows were entered.)
The optional "ROWS=" specification limits the number of rows that will be selected.
If no limit is set, then all rows matching the selection criteria will be returned.
NOTE:This limit is set by using the SQL keyword TOP in the query, which may
not be supported by your database (e.g.,it is supported by MS-Access but not
by SQL Server.) If not, you can use the SETOPTION
SQL_MAX_ROWS to set the number of rows before executing the QBE. (If you set that
option, however, it will remain in effect for all subsequent queries until
you set some other number, or reset it to return all rows with the command
SETOPTION SQL_MAX_ROWS=0.)
The "TABLE=", "SELECT=" and "ORDER=" keywords may be used in the QBE command in any
order. Following these specifications, you can specify one or more
column names which may be included in the WHERE clause of the SQL SELECT
statement. These should match the input variables that you have provided in
your query form. As noted above, fields that do not have any current value will
not be included in the WHERE clause.
NOTE: There are several special considerations for the column names
given in the QBE command:
After the QBE command is executed, all the database column values from the
specified table will be selected, and you can use the TABLE, EACHROW, or
UPDATEFORM commands, just as you would after any SELECT statement.
Example:
The REDIRECT command may be used to "transfer" the client browser to a
different URL. To insure proper functioning with all browsers, the URLs used
with the REDIRECT command should be full URLs, including the
"http://www.domain.com/" at the beginning. Like all
ODBiC commands, you can give a variable as the
URL or as any part of the URL string (e.g, http://www.domain.com/$file$).
NOTE: This command can only "redirect" the client browser if no output
has been produced by the current template file. That is, the only things
that can appear before the REDIRECT command in the template file are
ODBiC commands that do not produce output. For
example, you might have an SQL statement to select the URL from a database
table, or you might use an IF statement to conditionally execute the REDIRECT
command.
If any output has already been produced by the template file before the REDIRECT
command is encountered, then ODBiC cannot
automatically redirect the user's browser. In such a case,
ODBiC will insert an HTML text link to the specified
URL instead. This link line will say "Please click here to continue". If you get
this link instead of the intended redirection, check your template for extra
spaces before or after the previous command lines.
When a REDIRECT command is encountered, the remainder of the HTML template file
is not processed.
Example:
The following code could be used at the beginning of a template to check for a
"cookie" named "userID" and transfer to a login page
if there is none.
This command may be used to send an unprocessed file to the user's
browser. Unlike the INCLUDE command,
this command will send the file to the browser without attempting to
execute any commands or replace any variable references that may be
contained in the file. It is most useful for returning an image file,
for example, so ODBiC can be used as the
SRC URL for an <IMG> tag.
NOTE: The referenced filename must have the full directory
path specification, such as would be used to open the file with a file
editor, and no Web-server directory mapping will be applied. (If there is no
directory path, then the file is assumed to be on the "current directory",
which would be the CGI directory.)
When this command is used to return any file that is not an HTML
file, then you should use the HEADER command to
specify the proper Content-type. (If you do not use the HEADER command,
then the standard "Content-type: text/html" header will be sent before
sending the file.)
Example:
The SEARCH command will generate and execute a "keyword search" query on one or
more columns in a database table. The "keywords" (which will generally be a form
field input by a user) may be one or more terms. The user may combine multiple
terms with AND, OR, or NOT (which are not case-sensitive). The default if more
than one term is entered is an AND condition; that is, all the given terms must be
present somewhere in a table row for that row to be selected. An OR condition
between two terms will select a row if either of the given terms is present in the
row.
A NOT condition excludes any row from being selected if it does contain the
term immediately following the NOT. (To be more precise, the row will be excluded
unless the user enters "OR NOT", since the default is an AND NOT condition between
the two terms.)
The user may also enter a word phrase enclosed inside double-quotes, "...", and
only those rows that contain the whole phrase will be selected. Quoted phrases may
be combined, using AND, OR or NOT, with other single terms or quoted phrases.
The generated SQL statement will use the LIKE comparison operator with "wild
card" characters (%) on each side of each term. This means that the search will
actually be for a substring rather than full words. With most database systems,
the search will not be case-sensitive.
In the SEARCH command, you may specify more than one column to be searched; you
may give a list of comma-separated column names. In this case, when two search
terms are combined with AND, both terms must appear somewhere in the row, but they
may appear in different columns. If the NOT specification is given, the term must
not appear in any of the columns for the row to be selected.
The SELECT option allows you to specify the columns to be selected by the query.
For multiple columns, enclose the list in parentheses separated by commas.
(For example, "SELECT=(col_1, col_2, col_3)...") If SELECT is no specified,
the query will be "SELECT * " which returns all columns in the TABLE.
ORDER is also optional, and again, multiple columns may be specified by listing
them inside parentheses, separated by commas. If ORDER is not specified, the
results will be in the table's default order (which is generally by the primary
key, of if there is none, then in the order that the rows were entered).
The optional "ROWS=" specification limits the number of rows that will be selected.
If no limit is set, then all rows matching the selection criteria will be returned.
NOTE:This limit is set by using the SQL keyword TOP in the query, which may
not be supported by your database (e.g.,it is supported by MS-Access but not
by SQL Server.) If not, you can use the SETOPTION
SQL_MAX_ROWS to set the number of rows before executing the QBE. (If you set that
option, however, it will remain in effect for all subsequent queries until
you set some other number, or reset it to return all rows with the command
SETOPTION SQL_MAX_ROWS=0.)
Example (where "keywords" is an INPUT variable passed in from an HTML form):
The SENDMAIL command may be used to send e-mail directly from a template. All
text produced by the template following the SENDMAIL command, up to an
<% ENDMAIL %> command (which is required), is the body of the e-mail
message.
The specifications for SENDMAIL may be given in any order.
The SERVER specification must be the Internet host name of an SMTP mail server
(which might be your own local host). If your local host does not have an SMTP
server running, you will need to know the name of a host that will accept outgoing
mail from your host. (ISP's often have a special node for mail such as
"mail.ispdomain.com".)
The FROM specification can be anything, typically your own e-mail address. It may
be in the form of "Real Name <my_email@myhost.com>" (that is, any name
followed by an e-mail address inside angle brackets). By default, the FROM e-mail
address will be used for any "reply" messages from a recipient, so you should be
careful to use a valid e-mail address.
The TO specification may be a single e-mail address or a list of names enclosed in
parenthesis, such as "TO=(one@some.com, two@other.com, three@another.com)". If
the TO specification is given as a variable (e.g., "TO=$addrlist$"), then
the variable may contain a single address or a comma-separated list of recipients.
The TO specification may also be an address file: Put an "@" in front of the file
path and name, such as "TO=@\mydir\mailinglist.txt". (You must use the full
file system path to the file, since Web-server root directory mapping will not
be applied.) This file should be a standard text file with one e-mail address per
line, or a comma-separated list of addresses on one or more lines. (NOTE: You
can use the OUTPUT APPEND option to allow users to add an
address to a mailing list from a Web form.)
The TO address or address list can also be selected from a database table.
To use this option, put an SQL query (with "SELECT" implied, not given) with
an optional WHERE clause inside parentheses: TO=(column FROM table [WHERE ...]).
The message will be sent to each selected "column" e-mail address.
The SUBJECT specification, if given, will be sent as the subject line of the
e-mail message. If this is omitted, the subject line will be "(No subject)".
The subject may be enclosed in double-quote marks, "...", but it does not need
to be enclosed unless it contains a comma (which separates option keywords).
The CC and BCC keywords may be used to specify "courtesy copy" and "blind
courtesy copy" addresses. ("Blind" copy recipient addresses are not shown in
the message received by "TO" and "CC" recipients.) Like TO, CC and BCC may
be single addresses, multiple addresses separated by commas, or address files
with the file name prefixed with "@". (Note: The SQL SELECT from a database
table is not supported for CC and BCC.)
The REPLY keyword allows specifying a "Reply-to" e-mail address. Most e-mail
reader programs will use this address if the recipient replies to the message.
(Note, however, that some e-mail readers do not recognize the "Reply-to"
address and will send replies to the FROM address.) The default for this
parameter is the FROM address, so you do not need to use REPLY unless you
want replies to go to a different address.
E-mail attachment files may be specified with the ATTACH keyword. A single
attachment file may be given as "ATTACH=file", or a list of file may be given
inside parentheses, separated by commas: "ATTACH=(file1,file2,...)".
The TYPE keyword may be added before a file name to define a MIME content type,
which some e-mail programs can use to know how to display the file
(e.g., TYPE=image/gif for a GIF image file). Attachments are
typically "transfer encoded" in Base64, which allows binary files, but
for ordinary text files you can use the specification TYPE=text/plain,
and the attachment will not be Base64 encoded (which some mail readers
do not recognize). The specification would be given something like:
ATTACH=(TYPE=text/plain,afile.txt). The DISPOSITION keyword may
be added to cause some e-mailer programs to do something with the file other
than save it to disk (e.g, DISPOSITION=inline to show this attached file
when the main message is viewed). (Note: If you use DISPOSITION=inline for an
image file, you should also use a TYPE=image/(exact type) to specify the
image type, such as "image/gif" or "image/jpeg".)
TYPE and DISPOSITION are not required if the
attached file is to be saved to the recipient's local disk, even if the file is
any kind of binary file. (That is, you can attach images, programs, word
processor documents, spreadsheets, etc., a
<% 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, [TEMPLATE=template,] [SUBMIT=text,]
This command generates an HTML <FORM> to insert rows into a database table.
The generated <FORM> will have an ACTION="odbic.exe".
Like the FORM command, INSERTFORM generates HTML <INPUT>
boxes for each of the specified input_fields, but it also generates an SQL INSERT
statement to be executed when the form is submitted. This INSERT statement will
specify database columns corresponding to the input_fields and a VALUES clause
to insert the data entered by the user.
["label"] numeric_field[:size] [=value] |
["label"] 'text_field[:size]' [=value] |
["label"] #date_field[:size]# [=value]
[,OPTIONLIST=()] [,CHECKBOX=()] [,HIDDEN=()]
[,PASSWORD='pwd_field[:size]' [,TRACE] [,...] %>
<% DATABASE $database$ ;
SQL $sql$ %>
(As noted above, you must have a DATABASE command in
your template prior to using INSERTFORM, and this connection string will be
passed as the "hidden" variable named "database".)
<% INSERTFORM TABLE=Employees, TEMPLATE="/httpfile/insert.odb",
"First Name" 'firstname:24', "Last Name" 'lastname:24',
"Home Address" 'address:50x2', "City, State, and Zip" 'csz',
OPTIONLIST=("Department" dept from Departments),
CHECKBOX=("Hourly?" 'hour', Y, N),
"Salary or hourly rate" rate:8,
HIDDEN=(uid, pwd) %>
<% NOTE comment %>
The NOTE command may be used to add a comment to a template that will not
be output to a browser when the template is processed. Ordinary HTML comments
inside <!-- and --> markers (that is, those that are not recognized as
ODBiC 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 template.
(The ONERR does not affect errors that occur prior to the ONERR definition.)
All of the HTML and ODBiC commands between
ONERR and ENDERR will be processed.
<% ONERR %>
<% OUTPUT odbicerr.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 template to determine if an
error occurred) and then clears $odbic_error$ so no message is output.
<% ONERR ;
got_error = $odbic_error$, odbic_error = "";
ENDERR %>
<% 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/odbic.exe/your_dir/getproducts.odb" >
Select category: <% OPTIONLIST 10 Category from Products %> <BR>
Select warehouse: <% OPTIONLIST warehouse = Newark, St. Louis, Oakland %> <BR>
<INPUT TYPE="submit" VALUE="Get Products">
</FORM>
In the first command above, the optional "size" specification is given, which
produces a scrolling window 10 lines high. The available Categories are selected
from the Products database table. In the second OPTIONLIST, three "warehouse"
input selections are given explicitly. In "template2.odb" (which is the
designated target for the ODBiC ACTION function
above), the user's selections will be variables named "Category" and "warehouse"
(which are not case-sensitive). In that template, you might use the following
form to give the user another choice list for the products in the selected
category:
<FORM METHOD="post" ACTION="/scripts/odbic.exe/your_dir/template3.odb" >
Select product: <% OPTIONLIST 25 Product from Products
WHERE Category = '$category$' AND Warehouse = '$warehouse$' %> <BR>
<INPUT TYPE="submit" VALUE="Get Product Description">
</FORM>
<% 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# [, ...]
%>
<% QBE TABLE=Employees, EmployeeID, 'LastName', 'FirstName', 'Title',
'BirthDate', 'HireDate', 'Address', 'City', 'Region', ReportsTo %>
The TABLE to be SELECTed FROM is "Employees". "EmployeeID" and "ReportsTo" are
numeric data columns, and all of the rest are character data (as indicated by
enclosing the column names in apostrophes). Since no SELECT=( ) option is given in
the command, all database columns will be returned, and since no ORDER=( ) option
is given, the rows will be in the table's default order.
<% REDIRECT url %>
<% IF NOT $userID$ ;
REDIRECT http://www.ourdomain.com/login.htm ;
ENDIF %>
...
<% RETURNFILE filename %>
<% HEADER Content-type: image/gif %>
RETURNFILE /images/banner.gif %>
<% SEARCH TABLE=database_table, KEYWORDS=keywords, [SELECT=column(s),]
[ORDER=columns,] [ROWS=number,] column_1 [,column_2, ...] %>
<% SEARCH TABLE=Products, KEYWORDS=$keywords$, Heading, Description %>
Input examples:
Keywords Rows selected
plastic widget Both "plastic" and "widget" in either Heading or Description
plastic and widget (Same as above, where "and" is implicit)
plastic or widget Either "plastic" or "widget" in either Heading or Description
widget not plastic "Widget" in either Heading or Description but "plastic" in neither
"plastic widget" Exact phase "plastic widget" in either Heading or Description
<% SENDMAIL SERVER=smtp_host, FROM=sender,
TO=recipient | TO=(column FROM table [WHERE ...] [,SUBJECT=subject]
[,CC=courtesy_copy_to] [,BCC=blind_copy_to] [,REPLY=reply_to]
[,ATTACH=([TYPE=mime_type] [,DISPOSITION=disp] file [,file,...])]
[,SUP] [,MIME] [,HTML] %> ... <% ENDMAIL %>