 |
Scripting Language |
Version 3
|
Scripting Language |
The following string functions can be used anywhere that you can use a variable: in the output text; in HTML tags; and in ODBscript commands and expressions.
All string function names begin with a $ sign. Like variables, the names are not case-sensitive. Each function has one or more "arguments" enclosed within a set of parentheses. You can put spaces between the function name and the left "(" of the arguments if you like.
Each of the arguments to these functions are evaluated like SET expressions, so you can use any arithmetic or logic expressions as arguments. For each of these string functions, the result of evaluating the first argument will be treated as a character string, even if it is an arithmetic expression. Like SET and IF expressions, character strings do not need to be enclosed in quotes unless you need to prevent inadvertent interpretation (e.g, if the string contains math operation symbols or commas), but you may use quotes if you like. (Note, however, that variable references within quotes will not have their values substituted.) Character strings may consist of several "concatenated" quoted or unquoted strings or variables in a row, such as ("Variable one is" $var1$ "and variable two is" $var2$), which will be treated as a single string argument.
(Note that the section after this defines numeric functions that always return numeric results, and which are ordinarily used only in the context of arithmetic or logical expressions. However, each of those functions can also be used as string functions by placing a "$" prefix in front of the numeric function name, so any those functions can also be used anywhere you can use a string function.)
Important Note: When string functions are used directly in the output text (as opposed to being used in ODBscript commands), the entire list of arguments must appear on a single line. Otherwise, you will get an error message, "function missing right paren ')'...". (ODBscript commands, however, do not have this limitation: function arguments in commands, such as SET, can span multiple lines.)
response = $httpGet(www.domain.com/script,var); note: "?var=$url($var$) added to URL;
WHILE $response$;
line = $split(response, $asc(10)); note: split response at first CR character;
note: process a single line of the response in the variable $line$;
ENDWHILE; note: loop will end when no more lines left in $response$
Note that the response headers are set in a single variable named
$http_headers$, also separated by CR (carriage return) characters, so
a similar $split( ) loop can be used to process the individual headers.
Example:
<% SET avg = $if($number$ > 0, $sum$/$number$, 0) %>
In the above statement "avg" is set to "$sum$/$number$" only if "$number$ > 0". Otherwise, "avg" is set to "0". Like all string functions, $if can be used directly in the output. For example, the following will produce lines such as "No records selected", "1 record selected", or "10 records selected" depending on the value of $row$:
$if($row$, $row$, "No") record$if($row$<>1, "s") selected.
Please see the match( ) "numeric" function for a complete list of regular expression "meta-characters" and some examples.
<% SET tempfile = $newFileName("/temp", "out") %>
<% OUTPUT $tempfile$ %>
Given a file name argument (which may include a subdirectory), the $pathInfo( ) function returns a URL-type file path specification relative to the path of the current script. (By "URL-type file path", we mean relative to your Web server's root and virtual directory mappings, as used in URLs.) This function uses the server variable named "path_info," which is normally the "extra path" directory information after the "odb.exe" in the URL that invoked ODBscript. For example, if the URL was "http://www.yourdomain.com/scripts/odb.exe/your_site/your_dir/script1.odb" then the server variable "path_info" contains "/your_site/your_dir/script1.odb". ODBscript always sets up a predefined variable named $path_info_dir$ with just the directory part of the path_info, which would be "/your_site/your_dir" in the given example, but the $pathInfo( ) function provides a more convenient way to use that value: It combines the $path_info_dir$ value with the given "file" argument, according to conventional "relative path" notation. If the "file" argument is just a file name, then $pathInfo( ) simply returns the $path_info_dir$ followed by a "/" followed by the given file name. That is, it returns a URL file path for a file on the same directory as the current script file. For the given example, $pathInfo("script2.odb") would return "/your_site/your_dir/script2.odb". But the "file" argument can also be preceded by "../" to move up one level in the directory structure (i.e., ".." represents the "parent" directory of the current path_info, without knowing its name). For the given example, $pathInfo("../another_dir/script3.odb") would return "/your_site/another_dir/script3.odb". You can use any number of levels, such as "../../" to move up two levels.
This function uses the value of the server variable named "path_translated", which is the "path_info" of the current script file but with the Web server directory mappings translated to actual file system directories. (Note that the file system directory of the current script is also in the predefined variable named $path_translated_dir$, but the $pathTranslated( ) function makes it easier to use.) Similar to the $pathInfo( ) function described above, this function takes the given "file" argument and returns an actual file system path relative to the current script file, using conventional "relative path" notation. The "file" argument can include a subdirectory before the file name (e.g. $pathTranslated("include/file.txt"), and note that in this case, no preceding "/" should be used). The "file" argument can also use "../" to represent the "parent" directory of the current script file, to any number of levels. This is the major advantage of using this function: you can reference files "up" and "over" in the current script directory structure, without knowing or "hard coding" the names of those directories.
For example, something like this can be used to insert a user and password into a file:
<% INSERT INTO Users (user, password) VALUES ('$user$','$pwd($password$)') %>
Now, when a user accesses a form and enters a user ID and password (lets call
the form input variables "userid" and "passwd" so they don't conflict with the
database columns), you can do this:
<% SQL SELECT password FROM Users WHERE user='$userid$' ;
IF $row$ = 0 %>
No such user. Please go back and reenter your user ID.
<% EXIT %>
<% ENDIF;
IF $pwd($passwd$) <> $password$ %>
Incorrect password. Please go back and reenter your password.
<% EXIT %>
<% ENDIF;
NOTE: encrypted password matches, allow operation %>
This function always produces exactly 16 output characters, regardless of the
length of the password. Note that since passwords cannot be decrypted, if
someone forgets his password, you cannot extract the current password from
the data. You will have to assign a new password. Also note that $pwd( )
is not compatible with any other password encryption schemes; you must
use ODBscript to create the encrypted passwords and
to check them.
Your host system is: $reverseDNS($remote_addr$, "(Unknown)")
For example, assume that a variable named "input" contains the character string value "x=10". After this SET command,
<% SET var = $split(input, "=") %>
then variable $var$ would have the value "x" and the original variable
$input$ would have the value "10".
The $split( ) function is also useful in a WHILE loop to handle an indefinite list of delimited values in a variable. For example, suppose that variable "input" is a list of names separated by commas. The following loop will allow processing each of these names individually until the "input" variable is empty:
<% WHILE $input$ %>
<% SET name = $split(input, ",") %>
$name$
...
<% ENDWHILE %>
<A HREF="/scripts/odb.exe/script.odb?category=$url($category$)&uid=$url($uid$)">The CGI program will get these named variables just like <INPUT> variables in a <FORM>.
The $var( ) function can also be used on the lefthand side of the SET command, as the target variable of the SET assignment. This is the only case where a variable reference or string function can be used as the target of the SET command. (The SET command will allow you to use the syntax "SET $variable_name$ = ..." instead of "SET variable_name = ...", but the "$" characters are actually ignored in the first case, and both statements have the same result: setting a variable named "variable_name".) When used in this manner, the $var( ) function is again a type of "indirection", because the value of the function argument is evaluated, and that becomes the name of the variable that is actually SET. In this case, it is very important to be careful whether or not you use $var(variable_name) or $var($variable_name$), or some combination of variable references and literal values, depending on exactly what you want to do. The rule is that the argument will be completely resolved, just as with any other string function argument, and the resulting value becomes the name of variable that is actually set. Therefore, "SET $var(variable_name) = ..." is not really useful, since that will be the same as using "SET variable_name = ...". However, "SET $var($variable_name$) = ..." can be used when the value of variable_name is actually the name of the variable that you want to set. This is also a useful function when you want to construct a variable name dynamically, using some combination of variables and literal values. For example, if a variable named "number" contains the value "3", then using "SET $var(row$number$) = ..." will actually set a variable named "row3".
Examples:
1. <% IF createFile($filename$) %>
File $filename$ created.
<% ELSE %>
ERROR creating $filename$. The error was: $file_error$
<% ENDIF %>
2. <% SET stat=createFile($filename$) %>
3. File $filename$ $if(createFile($filename$), "created", "not created")
Example: The following loop is executed 10 times, automatically decrementing "n" each time it is referenced in the WHILE test condition. (Note that "n" is initialized to 10, so the first use of decr( ) will return 9. Within the loop, $n$ will be 9 on the first loop and 0 on the last loop.)
<% SET n = 10 %>
<% WHILE decr(n) >= 0 %>
N is now $n$ ...
<% ENDWHILE %>
Examples:
1. <% IF deleteFile($filename$) %>
File $filename$ deleted.
<% ELSE %>
ERROR deleting $filename$. The error was: $file_error$
<% ENDIF %>
2. <% SET stat=deleteFile($filename$) %>
3. File $filename$ $if(deleteFile($filename$), "deleted", "not deleted")
Example:
<% IF fileExists($filename$) %>
File $filename$ already exists.
<% ELSE %>
File $filename$ does not exist.
<% ENDIF %>
Example: The following loop is executed 10 times, automatically incrementing "n" each time it is referenced in the WHILE test condition:
<% SET n = 0 %>
<% WHILE incr(n) <= 10 %>
Line $n$ ...
<% ENDWHILE %>
The regular expression "meta-characters" that are supported are:
| ^ | Beginning of string (i.e. the following pattern must match the string starting at character number 1). |
| $ | End of string (i.e. the preceding match must be at the end of string's value). |
| . | Match any single character. |
| * | Match zero or more occurrences of the preceding character or [...] character class. |
| ? | Match zero or one occurrence of the preceding character or [...] character class. |
| + | Match one or more occurrence of the preceding character or [...] character class. |
| [...] | Match a single character to any one of the characters in the specified list (a "character class"), such as [ABC]. |
| [a-z] | Match a single character to any one of the characters between the two characters separated by "-", e.g. [0-9] would be any digit, or [A-Z] would be any uppercase alphabetic. You can include several pairs in the same list, such as [a-zA-Z0-9] to match any alpha-numeric character. You can mix character ranges with lists, such as [ABC0-9] to match "A", "B", "C", or any digit. |
| [^...] | Match any character that is not specified in the list for the character class, such as [^a-zA-Z0-9] to match any non-alpha-numeric character. |
| {n} | Match the preceding character or character class exactly "n" times, e.g. [0-9]{8} would match exactly eight digits. |
| {n,} | Match the preceding character or character class "n" times or more, such as [0-9]{8,} to match eight or more digits. |
| {n,m} | Match the preceding character or character class at least "n" times but no more than "m" times, such as [0-9]{8,10} to match eight, nine, or ten digits. |
| | | Pattern "or" separator. Match either the preceding pattern completely or the following pattern completely. May be used multiple times. |
| \t | The ASCII tab character. |
| \r | The ASCII carriage-return character. |
| \n | The ASCII linefeed ("new line") character. |
| \ | "Escape", take the next character "literally" as part of the pattern, not as a special character. For example, \* means the asterisk is in the pattern to be matched, instead of the meaning shown above. Use \\ to specify the backslash itself. |
Pattern Examples:
A standard US phone number has a three-digit area code, a three-digit exchange code, then four digits. But several different conventions are used for the format of a phone number, such as optional parentheses around the area code and hyphens or spaces between the groups. The following pattern will match any of the typical formats, while failing to match an invalid phone number:
match($phone$, "^(?[0-9]{3})?[- ]?[0-9]{3}[- ]?[0-9]{4}$")
The pattern is given as a quoted string. The "^" as the first character means
that the remainder of the pattern must match starting with the first character
of the subject string. (Otherwise, a pattern can be matched anywhere within the
string.) The next character, "(" is immediately followed by a "?" which
specifies zero or one occurrence of the character (i.e., an optional character).
Next, "[0-9]{3}" means that exactly three digits should be present. Then there
is another optional ")". Following that, the specification "[- ]?" means
that a single hyphen or space may or may not be present. Similarly, the rest
of the pattern specifies a three-digit group followed by a four-digit group,
perhaps with a hyphen or space between them. The last character of the
pattern, "$", means that the end of the subject string is expected after the
four-digit group, so this pattern will not match if extra characters or digits
are entered. (Typically, your patterns used with the VALIDATE command will
begin with "^" and end with "$", to exclude extraneous characters. If neither
of these characters is given, then a matching substring may be found anywhere
within the subject string, but you might use only one or the other to match
only the beginning or the end of the string.)
The pattern above will match any of the following phone number formats:
(719) 555-1212, (719)555-1212, (719) 555 1212, 719-555-1212, 719 555-1212, 719 555 1212, 719 5551212, or 7195551212.
The following pattern could be used to validate that an e-mail address is of the general form "something@something.something". It checks that the initial character in each section is alpha-numeric, and that there is at least one more character (any character) in each section.
match($email$, "[a-zA-Z0-9].+@[a-zA-Z0-9].+\.[a-zA-Z0-9].+")
Examples:
1. <% IF renameFile($upload_filename$, $filename$) %>
Upload file $upload_filename$ was renamed to $filename$.
<% ELSE %>
ERROR renaming $upload_filename$ to $filename$. $file_error$
<% ENDIF %>
2. <% SET stat=renameFile($upload_filename$, $filename$) %>
3. File $upload_filename$
$if(renameFile($upload_filename$, $filename$), "renamed", "NOT renamed")
to $filename$.
| date_short | Current date in the format MM-DD-YYYY, where "MM" is the number of the current month, "DD" is the number of the day, and "YYYY" is the four-digit year |
| date_ymd | Current date in the format YYYYMMDD, where "YYYY" is the four-digit year, "MM" is the number of the current month, and "DD" is the number of the day |
| date_y_m_d | Current date in the format YYYY-MM-DD, where "YYYY" is the four-digit year, "MM" is the number of the current month, and "DD" is the number of the day |
| day | Day number in current month, one or two digits (Use $format("0#", $day$) to force two digits) |
| file_is_dir | Inside an EACHFILE loop, a value of 1 indicates that the current file is a subdirectory, and 0 indicates that it is an ordinary file. |
| file_date | Inside an EACHFILE loop, the date that the current file was last written to. |
| file_name | Inside an EACHFILE loop, the current file name (without directory information). |
| file_size | Inside an EACHFILE loop, the size of the current file in bytes. |
| http_cookie | HTTP cookie string input by browser. All cookies sent are in this string in the form of "name=value" with multiple cookies separated by semi-colons. (Each cookie is also set up as a named variable, so this string is not required to use cookies.) |
| http_referer | The URL of the document that was used to invoke ODBscript. This variable is typically not set by the browser for a bookmark link or a locally loaded page. |
| http_response | The status response (the first response header) of an HTTPGET or HTTPPOST request. |
| http_headers | All response headers, after the first one (which is in http_response) from an HTTPGET or HTTPPOST request. The multiple headers in this variable are separated by their original "newline" ($asc(10)) characters. |
| importrow | Current input line number in an IMPORT loop |
| input_variable | Current input CGI variable name in an EACHINPUT loop |
| input_value | Value of CGI variable ($input_variable$ above) in an EACHINPUT loop |
| month | Current month, one or two digits. (Use $format("0#", $month$) to force two digits) |
| monthname | Full name of current month (e.g., "September") |
| multirow | Current variable number in an EACHMULTI loop |
| odbic_error | The text of the last ODBscript error message that was generated. May be tested (e.g., <% IF $odbic_error$ %>) to check for errors following commands such as SENDMAIL. |
| path_info | Any "extra path" directory information after the "odb.exe" in the URL that invoked ODBscript. When ODBscript is run as a CGI, this should be the script file. (Example: if the URL was "http://www.yourdomain.com/scripts/odb.exe/your_dir/script1.odb" then "path_info" contains "/your_dir/script1.odb" and the input script file is expected to be "script1.odb" on subdirectory "your_dir".) If the invoking URL does not specify this "path_info", then a variable named "script" must be passed in to ODBscript to name the script file |
| path_info_dir | The directory specified in path_info (i.e., path_info with the script file name removed). (See also the $pathInfo( ) function, which produces file paths relative to this directory.) |
| path_translated | If any directory information is provided after the odb.exe URL (i.e., any "path_info"), then this is the full file system directory path after it has been "mapped" by the Web server |
| path_translated_dir | The directory specified in path_translated (i.e., path_translated with the script file name removed). (See also the $pathTranslated( ) function, which produces file paths relative to this directory.) |
| query_string | Any data following a "?" in the URL that was used to invoke ODBscript. (Any "variable=value" pairs in this string will already be set up, but you can use $query_string$ directly if you do not use that format; for example, you might use "/cgi-bin/odb.exe/script.odb?command" to pass a single command word in the query string. Everything following the "?" will be in $query_string$) |
| remote_addr | Internet numeric address (nnn.nnn.nnn.nnn) that the user is running on |
| remote_host | Host that the user is running on (may be node name, but commonly is numeric Internet address) |
| row | Current result row number after a SELECT statement |
| rows_affected | Number of rows affected by an INSERT, UPDATE or DELETE SQL statement |
| script_name | The path to the odb.exe CGI program (e.g., "/scripts/odb.exe") from the invoking URL. (Useful for creating forms in the script file that will invoke ODBscript, rather than "hard-coding" the URL.) |
| server_name | Internet node name of the host system (i.e., the system that ODBscript is running on) |
| sql_error | Error message returned by ODBC driver after SQL execution (or may say "No rows selected" after a valid SELECT, or "### rows affected" after a valid INSERT, UPDATE or DELETE) |
| sql_statement | The last executed SQL statement, with all variables expanded |
| sql_status | The status of the SQL operation (interpretation depends on operation): -2 = no rows selected/affected; -1 = SQL execution error; 0 = SELECT or DDL succeeded; 1 = one or more rows affected by INSERT, UPDATE, or DELETE. |
| time | Current 12-hour clock time in the format HH:MMam or HH:MMpm, where "HH" is the hour and "MM" is the minute |
| time_hms | Current 12-hour clock time in the format HH:MM:SSam or HH:MM:SSpm, where "HH" is the hour, "MM" is the minute, and "SS" is the seconds |
| time24 | Current 24-hour clock time in the format HH:MM, where "HH" is the hour and "MM" is the minute |
| time24_hms | Current 24-hour clock time in the format HH:MM:SS, where "HH" is the hour, "MM" is the minute, and "SS" is the seconds |
| today | Current date in the format "Month DD, YYYY", where "Month" is the full name of the current month, "DD" is the one- or two-digit day of the month, and "YYYY" is the four-digit year |
| upload_name | (where name is the name of the variable, not the name of a file, in an <INPUT TYPE="file" NAME="..."> tag) Unique name assigned to an uploaded file (usually a temporary name) |
| upload_name_size | (where name is the name of the variable, not the name of a file, in an <INPUT TYPE="file" NAME="..."> tag) Size in bytes of an uploaded file |
| weekday | Full name of current day of week (e.g., "Monday") |
| year | Current year, four digits |
| in_database | ODBC connection string (may be used instead of DATABASE command; required for "no script mode"). NOTE: This input string must be encrypted with the $sysEncrypt( ) function on the same system to which it is sent. |
| default | Specifies variable default values (like the DEFAULT command) in the format "variable=value [, variable=value, ...]" |
| eachrow | Formatting to use for each result row (optional when no script file) |
| format | Specifies variable formatting masks (like the FORMAT command) in the format "variable=mask [, variable=mask, ...]" |
| script | script file to process |
| max_upload | Specifies the maximum file size, in bytes, for an uploaded file. |
| output | Output file to write (i.e., output is sent to this file instead of back to the user's browser) |
| path_info | Any "extra path" data following "odb.exe" in the URL used to invoke ODBscript (normally set by CGI interface but provided as an input in case your server does not set properly). This should be the script file |
| path_translated | Full file system directory path after "path_info" has been "mapped" by your Web server (normally set by CGI interface but provided as an input in case your server does not set properly) |
| script_name | CGI path program name, (e.g., "/scripts/odb.exe") (normally set by CGI interface but provided as an input in case your server does not set properly) |
| in_sql | SQL statement to execute (required for "no script mode"). NOTE: This input string must be encrypted with the $sysEncrypt( ) function on the same system to which it is sent. |
| sql_footer | Text to output following the "in_sql" statement execution and "eachrow" result formatting (optional for "no script mode") |
| sql_header | Text to be output before executing the "sql" statement (optional for "no script mode") |
| sql_title | Text to use in the HTML TITLE command (optional for "no script mode") |
| trace | Turns on TRACE mode when the passed value is set to "on". |
| translate | Specifies a translation table for a variable (like the TRANSLATE command) in the format "variable_name value=newvalue [, value=newvalue, ...]" |
| upload_dir | Specifies the directory on the host system to receive an uploaded file. |
| upload_prefix | Specifies the file name prefix to use for an uploaded file. (The actual file name will have a unique number added.) |
| table_font | Any HTML parameters for a <FONT> tag (e.g., FACE, COLOR, SIZE) to be added to data cells output by the TABLE command. (No default.) |
| table_th_font | Any HTML parameters for a <FONT> tag (e.g., FACE, COLOR, SIZE) to be added to the table headers output by the TABLE command. (Defaults to table_font, if that is defined.) |
| table_td | Any HTML parameters for the <TD> tags (e.g., BGCOLOR, ALIGN) for the table column data output by the TABLE command. (No default.) |
| table_th | Any HTML parameters for the <TH> tags (e.g., BGCOLOR, ALIGN) for the table column headers output by the TABLE command. (No default.) |
| table_tr | Any HTML parameters for the <TR> tags (e.g., BGCOLOR, ALIGN) for the table rows output by the TABLE command. (No default.) |
| form_font | Any HTML parameters for a <FONT> tag (e.g., FACE, COLOR, SIZE) to be added to all prompt labels output by the FORM, UPDATEFORM, and INSERTFORM commands. (No default.) |
| form_prompt_td | Any HTML parameters for the <TD> tags (e.g., BGCOLOR, ALIGN) for the prompt labels output by the FORM, UPDATEFORM, and INSERTFORM commands. (Default: "align=right".) |
| form_value_td | Any HTML parameters for the <TD> tags (e.g., BGCOLOR, ALIGN) for the input fields output by the FORM, UPDATEFORM, and INSERTFORM commands. (No default.) |
| form_table | Any HTML parameters for the <TABLE> tag (e.g., WIDTH, BGCOLOR) output by the FORM, UPDATEFORM, and INSERTFORM commands. (No default.) |
| form_tr | Any HTML parameters for the <TR> tags (e.g., BGCOLOR, ALIGN) for the table rows output by the FORM, UPDATEFORM, and INSERTFORM commands. (No default.) |
Some file upload parameters can be passed directly in this HTML form (e.g. , as hidden input fields in the form), but to address certain security issues, you can define uploading options, limits, and restrictions in a special file named "odb_upload.ini", which is normally placed on the CGI directory (see below).
Browser uploading is implemented through the use of the HTML form type < FORM ENCTYPE="multipart/form-data">. This form should have at least one input field with a type of <INPUT TYPE="file" ...>. Multiple files may be uploaded in the same form by having multiple TYPE="file" input fields. There is an example of a file-uploading form, named uploadf.htm, in the EXAMPLE.ZIP file.
The file data is actually saved to disk before executing the script. Files can be uploaded to any directory for which your CGI programs have "read" and "write" access privileges, but the specific directory must be specified. The upload directory (i.e., the receiving directory on your host system) can be set in a variable named "upload_dir" that is passed in the form itself, or it can be set in the "odb_upload.ini" file. For example, you might code this variable in the form as an <INPUT TYPE="hidden" NAME="upload_dir" VALUE="..."> field, or perhaps you might give the user a "pull-down" selection list, as in the example form. You could also allow the user to enter the directory in a normal INPUT field named "upload_dir", but that could create some obvious security problems. In the "odb_upload.ini" file, you can simply have a line that says something like "upload_dir=..." (where the value after the "=" is your upload directory). If an "upload_dir" is specified in the "odb_upload.ini" file, then that variable will be ignored in the form input.
As noted, files can be uploaded to any directory for which your CGI programs have "read" and "write" access privileges, so you don't necessarily need to upload to a Web server document directory (unless, of course, you want to make the files available for later downloading through the Web). Typically, you should use a special directory, used only for uploading, to make the uploaded files more manageable.
If you need to upload to several directories, but don't want the process to be allowed to upload to all accessible directories, you can use the "odb_upload.ini" file to specify which directories are allowed for uploading. This is done by specifying one or more "allowed_dir=..." lines in the file, where each line specifies a directory where uploads are allowed. You can then have different upload directories specified in the upload forms, without worrying that a form might be modified to upload to specify a directory that you don't want to allow: If an "upload_dir" is specified in the form that isn't also specified as an "allowed_dir" in the INI file, then the upload will not be processed.
An uploaded file does not initially have the same name as the original file on the user's system. (Doing so would potentially create a conflict with an existing file of the same name on the host.) Instead, ODBscript always creates a file with an arbitrary, unique name to receive the uploaded file data. Optionally, the input form data can specify a variable named "upload_prefix" and the uploaded file names will have this prefix plus a unique number. The "upload_prefix" defaults to "upload", so file names would be "upload1", "upload2", etc. In the script, there will be a variable set to be the unique name that was actually given to the uploaded file and also a variable set to the original file name on the user's system, and the script can use these names as appropriate for your application.
For example, if you use this functionality to allow users to upload HTML files to their own Web directories, you will probably want to rename the upload files to be the same as the original files on the user's system. ODBscript sets the user's original file name in the variable named by the file upload tag itself. For example, if the tag is <INPUT TYPE="file" NAME="filename"> then the variable $filename$ will contain the original file name. The unique file name created on the host (with the upload directory path) is then set in a variable with the prefix "upload_" plus this same tag name (e.g., in this case, $upload_filename$). ODBscript has a function that will allow you do the needed renaming: renameFile($upload_filename$, $filename$). There is also a function called fileExists($filename$) that will allow you to test for the existence of a given file, which you should use before attempting the renaming. (If such a file already exists, the renaming attempt will fail.) Optionally, you might want to delete any previous file with the same name, so there is also a deleteFile($filename$) function.
On the other hand, if you allowed users to upload graphic files that you want to be associated with database entries that are made in the same form, and all these files are stored on the same directory, you might run into name conflicts if you try to use the original names. In this case, you may not want to rename the uploaded file, but instead keep the unique name assigned by the upload. That is, you could just insert the created file name into the database with the other data, and then use that name in any links to the image file when you later display the data in an HTML page.
Another possibility is that the script could use the uploaded data immediately in some manner and then delete the upload file. For example, if the uploaded file is attached to an e-mail, then you will probably want to delete it after sending the e-mail. Or, the file could contain some data that the script is to operate on (e.g., with an IMPORT command), and then the file could be deleted.
The odb_upload.ini File
You can install a file named odb_upload.ini on your system to control certain file uploading parameters such as the maximum file size, the allowable upload directories, and the allowable upload file types. This is a security feature, so unless you really need unrestricted uploading access, using odb_upload.ini is strongly recommended.
When uploading a file, ODBscript will look for this control file on the "current working directory" (i.e., it will attempt to open the file without specifying any directory). This working directory is set up by the Web server when executing CGI programs, and it will usually be either the URL "root" directory of your Web site, or possibly the CGI directory itself. However, your Web server may set some other directory (such as the server's own base directory), so this may require some experimentation to get correct. One quick way to determine the CGI working directory it to execute a test script containing the command <% EXEC DIR %>, which will produce a listing of the working directory (because no other directory is specified), with the name of that directory at the top of the listing. To test that you have correctly identified the working directory, you can put a test file on that directory to output a message (e.g., "<HTML> Hello" is enough), and then try a URL that executes odb.exe passing that file name as an input named "script" (e.g., your domain and CGI path followed by "odb.exe?script=testfile"). If the "script" input does not provide a file path, ODBscript will try to open it on the current working, so if your test file works, you can put odb_upload.ini on that same directory.
The lines in this odb_upload.ine file are in the form of "variable = value" (with spaces and tabs ignored). Do not put any quotes around the values, and put each variable=value pair on a separate line. Any line that begins with an exclamation (!) is ignored as a comment line. The variables that you may use in this file are:
The Example Files
As mentioned, there is an HTML file included in EXAMPLE.ZIP, uploadf.htm, that shows an example of a file uploading form. The ACTION URL for the form links to odb.exe and specifies the script file uploadf.odb, which is also included in EXAMPLE.ZIP. This form and script implement a general purpose file uploading utility which you may find useful "as is", or you can customize them for your particular application.
Note in particular that the <FORM> declaration has a specification of ENCTYPE="multipart/form-data", which is required for any file uploading form. Also required is at least one <INPUT TYPE="file" ...> in the form body. This will create a standard INPUT text box in which the user can directly type a file path, but the browser will also add a "Browse" button so the user can find the file using a Windows directory browsing function (similar to that used by Notepad, for example) to locate the directory and highlight the desired file.
The file-type INPUT must specify a NAME="...", which can be anything. When the ACTION script is executed, a variable with this name will contain the original file name (without any directory information). For example, uploadf.htm has <INPUT TYPE="file" NAME="filename">, so in the script uploadf.odb, variable $filename$ will contain the original file name. You can have multiple TYPE="file" input fields in your form; in this case, you can use a different NAME for each, or you could use the same NAME for all of them and then process these with an EACHMULTI loop.
As noted above, when a file is first uploaded, it does not have the original file name. Instead, uploaded files are given a unique, arbitrary file name. The default for this name is "upload" plus a unique number (i.e., a number that is currently unused by any file on the upload directory). In your input form, you can provide a hidden input NAME="upload_prefix" with a VALUE that specifies a different name prefix. In the ACTION script, the actual uploaded file name (with directory path) will be preset in a variable named "upload_" plus the variable NAME used in the TYPE="file" input. For example, in the case of the uploadf.htm form which has an <INPUT TYPE="file" NAME="filename">, when the script uploadf.odb is executed, the variable $upload_filename$ will contain the actual name of the uploaded file and $filename$ will contain the original file name. (As another example, if you were to use <INPUT TYPE="file" NAME="imagefile">, then $imagefile$ and $upload_imagefile$ would contain the original file name and the uploaded file name, respectively.) There is another variable set using this input variable name, upload_name_size (e.g., for the two previous examples, upload_filename_size or upload_imagefile_size), which will be set to the uploaded file size, in bytes.
The uploading process needs to know where to put the file on the server system, so you must provide an input variable named "upload_dir" to specify this directory. In uploadf.htm, "upload_dir" is implemented as a SELECT choice list, which you can edit to be a list of the upload directories you want to use on your system. If you want to always upload to a fixed directory, you can omit the SELECT list and define "upload_dir" as a TYPE="hidden" input. (You could also use a standard TYPE="text" input if you want to allow the user to specify any directory, but that's obviously risky.) Or, you can specify "upload_dir = ..." in the "odb_upload.ini" file, and all uploads will go to that directory. If "upload_dir" is not defined by one of these methods, the upload will fail.
Note that in the script, you can move a file to a different directory with the renameFile( ) function. Therefore, the "upload_dir" directory is not necessarily the final destination for the file. So, you may prefer to always upload to a fixed directory, then deal with the final destination by the renaming that you do in the script.
The maximum size of an uploaded file can be controlled with an optional input variable named "max_upload", which can be set in the upload form or in the "odb_upload.ini" file. (If "max_upload" is set in the INI file, then it will be ignored if passed in the form.) In the script uploadf.htm, a hidden input is used to specify a value of 1,000,000. You can edit (or omit) this value to suit your application. As noted above, there is a variable set to be the actual size of the uploaded file: upload_name_size (where name is the NAME parameter of the <INPUT> tag). Note that this file size might be 0, which might be because the original file is actually empty, but it also might be because the specified file did not exist on the user's system (which can happen if the user types in a file name rather than browsing to find the file). Browsers do not indicate this error condition in the upload process -- they simply send an empty file -- so you may want to warn the user of this possible error, as is done in the sample script.
The input form in uploadf.htm has some other variables that are used in script uploadf.odb to implement a general-purpose upload utility. Note that these variables are used ONLY by the script, and not by ODBscript itself, so you may want to modify these for your implementation. One of these is <INPUT NAME="host_filename"> which the script uses (if the form user enters a value) to rename the uploaded file. This allows giving the uploaded file a name that is different from the original file on the user's system. Otherwise, if this variable is not entered in the form, the script will rename the uploaded file to the original file name.
The form in uploadf.htm also has "radio" input variable named "exists_opt" which the script uploadf.odb uses to handle any previously existing file with the same name. The default is "new file", which means that the upload will NOT replace any existing file, and the upload will fail if the file already exists. But if the "overwrite" option is checked, the script will replace any existing file with the uploaded file. If the "save existing file.BAK" option is checked, the script will first rename any existing file, adding a ".bak" extension to its name, before renaming the uploaded file. This allows you to "recover" the original file using the form at the bottom of uploadf.htm. The "restore" function consists of deleting the current version of the file name, then renaming the ".bak" file to the original name.
To run ODBscript as a CGI program, the executable file (odb.exe) must be installed on a "CGI directory". CGI directories are defined in your Web server's configuration file. CGI directories are generally created as subdirectories of the primary HTML directory (that is, the same root directory as your Web pages), and they are commonly named something like "scripts", "cgi", or perhaps "cgi-bin". If you already have such a directory defined, then simply copy odb.exe there. If you do not already have such a directory, you will need to check your Web server's documentation for configuration details.
With a properly defined CGI directory, your Web server will automatically recognize CGI programs when this directory is specified in a URL. Like the URL for a Web page, a CGI URL should omit the primary HTML directory. For example, if you have a Web page named "pagex.html" and your primary HTML directory is defined in your server's configuration file to be "httpfile", then the directory path to the file would be "\httpfile\pages.html". However, the URL would be something like "http://www.yourdomain.com/pagex.html" because your server expects that all HTML files will be on the "httpfile" directory. Similarly, if your CGI directory is "\httpfile\scripts" and ODBscript has been installed there, then the URL would be "http://www.yourdomain.com/scripts/odb.exe".
There are two ways that CGI programs may be referenced from an HTML document. Perhaps the most common is as the ACTION function of a FORM declaration, such as:
<FORM METHOD="post" ACTION="http://www.yourdomain.com/scripts/odb.exe">
(Note that ODBscript will also work with METHOD="get",
but the "get" method has limitations on the number of bytes of data that may
be passed to the program.)
All of the INPUT fields in the FORM declaration will be passed in to ODBscript as named variables; see the ODBscript Variables section.
The second method of invoking a CGI program from an HTML document is as an ordinary URL in an "anchor" link (that is, the HTML "A" command). In this case, HTML allows variables to be included in the link by placing a question mark (?) immediately at the end of the URL and adding a "variable=value" specification. Additional "variable=value" pairs may be added to this by separating them with an ampersand (&) character. (Note, however, that certain "special" characters are not allowed in URLs and must be represented by codes. Spaces are not allowed, and the convention is to use the plus sign (+) as the code for a space. Other special characters are encoding by using a percent sign (%) followed by the two-digit ASCII hex code for the character.) In ODBscript, variables that are passed in with the URL are decoded for special characters, but after that they are treated the same as FORM INPUT variables.
If you are creating an anchor link in ODBscript script file, then you can use the $url( ) string function to encode a variable so that it may be passed in a URL.
You can run ODBscript from the DOS command line prompt and write the output to an HTML file on disk. If you do not need "real time" access to your database (for example, if the data does not change often), then using this method will avoid CGI and database overhead for every page access. Or, if your Web pages are on your Internet Service Provider's host machine and you cannot install CGI programs and databases on that host, then you can generate your pages from a database on your PC and upload the generated HTML. Also, you can use the Windows Schedule server (which you will find in the Services window) to run ODBscript on at regular timed intervals.
It's important to note that ODBscript only runs as a "console" type program -- not as a pure Windows program. The main significance of this is that if you simply double-click the program file name in a folder listing, the program will be executed in a Command Prompt window, but since no input file can be specified this way, odb.exe will immediately display an error and quit, closing the window. (This usually happens too fast to even read the error message.) So, you need to either open a Command Prompt window first (which is usually found in the Start > Programs > Accessories menu), so you can specify the input script in the run command, or you can use the Windows "Run..." function from the Start menu (which also allows typing in the necessary input script specification).
Also note that on Windows XP systems, offline usage requires using the Program Compatibility Wizard to set the program to run in Windows 95 mode with "visual themes" disabled.
To use this mode, the odb.exe program file can be on any directory. Run ODBscript as you would any DOS program -- from the MS-DOS prompt or from the Windows "Run..." box. Following the program name (ODBscript) in the run command, you must specify an input file (the script) with a "-i" prefix and an output file (the generated HTML) with a "-o" prefix. (The file name may be placed immediately after the "-i" or "-o", with no spaces, or you may place a space between "-i" or "-o" and the filename.) Unless these files are on the current working directory, you will also need to specify directory paths for ODBscript and the files, such as:
\httpfile\scripts\ODBscript -i \httpfile\script.odb -o \httpfile\pagex.odb
You can also pass variables into ODBscript on the
command line. Following the input and output file specifications, you can
specify any necessary "variable=value" pairs. For example, if the SQL
statement in the script file is something like, "SELECT * FROM items
WHERE category = $cat$", then you can say "cat=widgets" in the
ODBscript run command to give the $cat$ variable a
value. Important Note: If there are any spaces in either the variable
name or the value, then you must enclose the entire "variable=value"
specification inside of double-quote marks, such as "name=Joe Smith".
(Otherwise, DOS will not interpret the variable declaration as a single
argument.) If neither the variable nor its value contain any spaces, you
can omit the double-quotes. If you need to specify several "variable=value"
pairs, separate them from each other in the run command by spaces.
If an output file is specified, then the file will be created if it does not already exist; otherwise, the existing file will be over-written. If your input script contains an OUTPUT command, then that command will override any output file specified with the -o option. If no output file is specified when ODBscript is run from the command prompt (either with the "-o" option or an OUTPUT command in your script), then the output will be written to your screen.
You can get ODBscript to execute an SQL statement and do some simple formatting without a script file. To do this, you will need to specify some required input variables, and you may optionally use some others. (Refer to the section Predefined Variables, in the "Optional Variables Input" table.)
If ODBscript in not given a script file to process, it will first look for a file named "default.odb" on the "current working directory" (see below). An example of such a file in included in the distribution ZIP file, and you may customize this script for your application. If ODBscript finds this default.odb file, it will be used as the script; otherwise ODBscript will use an internally defined default script which is similar to the example.
In the <FORM> that you use to execute the "no script mode", you can define certain hidden input variables (e.g., <INPUT TYPE="hidden" NAME="var" VALUE="value"<) to control the processing. For the supplied default.odb script and the internally defined script, the minimum required input variables are "in_database", which defines the ODBC connection, and "in_sql", which defines an SQL statement to execute.
Important Note: For security, the input variables "in_database" and "in_sql" must be encrypted using the $sysEncrypt( ) function on the same server that they will be executed. For example, when you output a form to the user's browser containing these hidden inputs, you should use VALUE="$sysEncrypt('...')" instead of directly passing the database connection string and the SQL statement. Since encryption is required for these two special input variables, they will always be decrypted, automatically, before processing the script, so you should not use $sysDecrypt$( ) when you reference $in_database$ and $in_sql$ in your default.odb script.
You could use, for example, something like this in a form:
<INPUT NAME="in_sql" TYPE="hidden" VALUE="$sysEncrypt("INSERT INTO ...")">
However, since the SQL statements can be rather long, there is a problem with
putting the $sysEncrypt("...") directly in the output form like that:
Remember that for all function references used directly in the
output text, the entire list of function arguments must be on a single
line, or you will get a "missing right parenthesis" error.
The SET command, however, does not have that limitation -- the function argument
list and quoted strings used as function arguments can be continued on one or more
lines -- so the easiest way to avoid that problem is to first set a variable to be
the encrypted SQL statement and then use VALUE="$variable$" as the value of the
form's <INPUT> tag.
Here, then, in an example of a "no template mode" form that insert a user's name and e-mail address into a table named Email. Note that the $sysEncrypt( ) arguments are given as quoted strings, so the variable references $UserName$ and $UserEmail$ will not be "de-reference" in those strings. (They will de-reference to be the user's input when the form is submitted.) Important: Also note that the VALUE="..." parts of the <INPUT> tags use double-quoted values, not single-quoted (apostrophe) values. This is because the apostrophe is a possible character in the encrypted string, which will cause browsers to cut the value short if VALUE='...' is used, but the double-quote character is not a possible encryption character.
<%
SET in_database=$sysEncrypt("DSN=MyData;UID=Admin");
SET in_sql=$sysEncrypt("INSERT INTO Email (UserName, UserEmail)
VALUES('$UserName$','$UserEmail$')");
%>
<FORM ACTION="/cgi-bin/odb.exe" METHOD="post">
Your Name: <INPUT NAME="UserName" TYPE="text" SIZE="40"><BR>
Your E-mail: <INPUT NAME="UserEmail" TYPE="text" SIZE="40"><BR>
<INPUT TYPE="submit" VALUE="Add your e-mail"><BR>
<INPUT NAME="in_database" TYPE="hidden" VALUE="$in_database$">
<INPUT NAME="in_sql" TYPE="hidden" VALUE="$in_sql$">
</FORM>
If you use only these variables, then ODBscript will execute the SQL and format the results using some defaults. The default page formatting will include a page title of "SQL Result". If the SQL statement is a SELECT, then all the results will be displayed using a TABLE command. For INSERT, UPDATE, and DELETE statements, the number of rows affected will be displayed. If an SQL execution error is detected, then the ODBC error message will be displayed.
You may optionally define the variable "sql_title" and it will be used as the Web page title (that is, in an HTML TITLE command, to be shown at the top of the browser window).
You may optionally define the variable "sql_header" and it will be inserted into the output before executing the SQL command. (Like all variables, $sql_header$ may include other variable references, such as $sql_statement$, $sql_title$, or other input variables from the form.)
You may optionally define the variable "eachrow" which will be used like the text in an EACHROW command to format each of the result rows from a SELECT statement.
You may optionally define the variable "sql_footer" which will be inserted into the output following the SQL execution and the result formatting.
You may also define any of the other variables shown in the "Optional Variables Input" table.
If you wish to use the default.odb (or your own customized version), you must install it on the "current working directory" that ODBscript will see when it runs. Unfortunately, this varies between different Web server products. Some will use the URL "root" directory of your Web site, whereas others will set the working directory for CGIs to be the CGI directory itself, and still other servers may use their own working directory. This may require some experimentation to get correct. One quick way to determine the CGI working directory it to execute a test script containing the command <% EXEC DIR %>, which will produce a listing of the working directory (because no other directory is specified), with the name of that directory at the top of the listing.
You can customize default.odb anyway you like. For example, you can use your standard site HTML page layout and formatting. You might also add SQL error logging to a disk file (using OUTPUT ... APPEND, or OPEN ... APPEND and WRITE) or you might e-mail the errors to a support person (using SENDMAIL). You could add your own standard input variables, such as a URL to REDIRECT to after the SQL execution. (If you use REDIRECT, make sure that your script doesn't produce any output, since REDIRECT won't work if you've already sent any HTML to the browser.)
Important Note: The example default.odb script has one feature that you may or may not wish to use: It will check to see if the form submitting the SQL statement was loaded from the same host system. This is done by checking the standard CGI request header HTTP_REFERER, which is the URL of the page submitting the form. If it does not match the current host name when ODBscript runs, the user will see an error message, "Input from foreign host is not allowed." This is a security feature, in addition to the encryption, to prevent "foreign" forms (e.g., edited forms on the user's PC) from executing SQL statements in your database. Unfortunately, some PC "firewall" products can suppress any outgoing HTTP_REFERER headers, so users running firewalls with that suppression enabled will not be able to execute "no script mode" unless you define a default.odb script that does not contain that checking, or you inform your users that they must disable that suppression in their firewalls. (Note that the internally defined default script does not have this checking.)