 |
Internet Connector |
Version 2.2
|
Internet Connector |
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 "odbic_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 template. 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 "odbic_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 "odbic_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 "odbic_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 "odbic_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 specifiy 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, ODBiC 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 template, 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 template 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. ODBiC 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$). ODBiC 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 template 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 template is to operate on (e.g., with an IMPORT command), and then the file could be deleted.
The Odbic_upload.ini File
Like the "default.odb" file, ODBiC will attempt to open this optional upload control file on its own "current working directory" (without specifying any directory). This will usually be the CGI directory itself. However, your Web server may set some other directory, so this may require some experimentation to get correct. As a suggestion, install your "odbic_upload.ini" on the CGI directory first, and if that doesn't work, try installing it on the Web server's HTML "root" directory. If that doesn't work, try the directory that contains the Web server executable program.
The lines in this file are in the form of "variable = value" (with spaces and tabs ignored). Do not put any quotes around the values. 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 odbic.exe and specifies the template file uploadf.odb, which is also included in EXAMPLE.ZIP. This form and template 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 template 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 template 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 template, 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 template 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 "odbic_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 template, 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 template.
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 "odbic_upload.ini" file. (If "max_upload" is set in the INI file, then it will be ignored if passed in the form.) In the template 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 template.
The input form in uploadf.htm has some other variables that are used in template uploadf.odb to implement a general-purpose upload utility. Note that these variables are used ONLY by the template, and not by ODBiC itself, so you may want to modify these for your implementation. One of these is <INPUT NAME="host_filename"> which the template 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 template 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 template 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 template will replace any existing file with the uploaded file. If the "save existing file.BAK" option is checked, the template 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.
The following string functions can be used anywhere that you can use a variable: in the output text; in HTML tags; and in ODBiC 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.)
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$ %>
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 ODBiC to create the encrypted passwords and
to check them.
Your host system is: $reverseDNS($remote_addr$, "(No DNS entry)")
For example, assume that a variable named "input" contains the 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/odbic.exe/template.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")
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 %>
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 ODBiC. 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_name | Current input CGI variable name in an EACHINPUT loop |
| input_value | Value of CGI variable ($input_name$ 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 ODBiC 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 "odbic.exe" in the URL that invoked ODBiC. This should be the template file. (Example: if the URL was "http://www.yourdomain.com/scripts/odbic.exe/your_dir/template1.odb" then "path_info" contains "/your_dir/template1.odb" and the input template file is expected to be "template1.odb" on subdirectory "your_dir".) If the invoking URL does not specify this "path_info", then a variable named "input" must be passed in to ODBiC to name the template file |
| path_info_dir | The directory specified in path_info (i.e., path_info with the template file name removed). |
| path_translated | If any directory information is provided after the odbic.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 template file name removed). |
| query_string | Any data following a "?" in the URL that was used to invoke ODBiC. (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/odbic.exe/template.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 odbic.exe CGI program (e.g., "/scripts/odbic.exe") from the invoking URL. (Useful for creating forms in the template file that will invoke ODBiC, rather than "hard-coding" the URL.) |
| server_name | Internet node name of the host system (i.e., the system that ODBiC 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 |
| database | ODBC connection string (may be used instead of DATABASE command; required when no template file) |
| 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 template file) |
| format | Specifies variable formatting masks (like the FORMAT command) in the format "variable=mask [, variable=mask, ...]" |
| input | Template 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 "odbic.exe" in the URL used to invoke ODBiC (normally set by CGI interface but provided as an input in case your server does not set properly). This should be the template 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/odbic.exe") (normally set by CGI interface but provided as an input in case your server does not set properly) |
| sql | SQL statement to execute (required when no template file) |
| sql_footer | Text to output following the "sql" statement execution and "eachrow" result formatting (optional when no template file) |
| sql_header | Text to be output before executing the "sql" statement (optional when no template file) |
| sql_title | Text to use in the HTML TITLE command (optional when no template file) |
| 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.) |
To run ODBiC as a CGI program, the executable file (odbic.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 odbic.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 ODBiC has been installed there, then the URL would be "http://www.yourdomain.com/scripts/odbic.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/odbic.exe">
(Note that ODBiC 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 ODBiC as named variables; see the ODBiC 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 ODBiC, 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 ODBiC template 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 ODBiC 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 NT Schedule server (which you will find in the Services window) to run ODBiC on at regular timed intervals.
To use this mode, the odbic.exe program file can be on any directory. Run ODBiC as you would any DOS program -- from the MS-DOS prompt or from the Windows "Run..." box. Following the program name (odbic) in the run command, you must specify an input file (the template) 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 ODBiC and the files, such as:
\httpfile\scripts\odbic -i \httpfile\template.odb -o \httpfile\pagex.odb
You can also pass variables into ODBiC 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 template file is something like, "SELECT * FROM items
WHERE category = $cat$", then you can say "cat=widgets" in the
ODBiC 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 template contains an OUTPUT command, then that command will override any output file specified with the -o option. If no output file is specified when ODBiC is run from the command prompt (either with the "-o" option or an OUTPUT command in your template), then the output will be written to your screen.
You can get ODBiC to execute an SQL statement and do some simple formatting without a template 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 ODBiC in not given a template 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 template for your application. If ODBiC finds this default.odb file, it will be used as the template; otherwise ODBiC will use an internally defined default template.
In the <FORM> that you use to execute the "no template 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 template and the internally defined template, the minimum required input variables are "database", which defines the ODBC connection, and "sql", which defines an SQL statement to execute. (Note that the default template actually executes an " EACHMULTI sql" loop, so you can pass in several "sql" statements.) If you use only these variables, then ODBiC 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 ODBiC will see when it runs. Unfortunately, this varies between different Web server products. Some will set the working directory for CGIs to be the CGI directory itself, whereas others will leave its own working directory set. This may require some experimentation to get correct. As a suggestion, install default.odb on the CGI directory first, and if that doesn't work, try installing it on the Web server's HTML "root" directory. If that doesn't work, try the directory that contains the Web server executable program.
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 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 template doesn't produce any output, since REDIRECT won't work if you've already sent any HTML to the browser.)
Note that the supplied default.odb file has one feature that is not present in the internal default template: It will check to see if the form submitting the SQL statement was loaded from the host system. (This is done by checking the standard CGI variable HTTP_REFERER, which is the URL of the page submitting the form.) This is a security feature to prevent "foreign" forms (e.g. forms on the user's PC or host) from executing SQL statements in your database. (This feature is not present in the internal default template because there would be no way to disable it, whereas you can easily remove that code from default.odb if you wish to allow foreign forms to execute SQL.)