Open DataBase
Scripting Language

Version 3

User's Guide

Two Quick Examples
ODBscript Script Files
ODBscript Variables
ODBscript Commands
Quick Command Reference
File uploading
String Functions
Numeric functions
Running ODBscript as a CGI Program
Running ODBscript from the Command Prompt
Predefined Variables
Using ODBscript Without a Script File

To download some useful example files, to contact us, and to get the latest information about ODBscript releases, please visit our home page:



ODBscript can be used to create a wide variety of interactive Web applications. It includes particular features that provide a quick and easy interface between your Web pages and your ODBC databases. (ODBC stands for Open Database Connectivity, a standard for interfacing applications programs to a variety of databases. ODBscript was formerly known as ODBiC, Open Database Internet Connector, and was renamed beginning with Version 3.)

The ODBscript processor, odb.exe, may be used as a CGI program. CGI stands for Common Gateway Interface, a standard for interfacing applications programs to the Web. CGI programs are executed by Web servers in response to URL (Universal Resource Locator) requests from browsers, much like "static" Web pages, but a CGI program can dynamically generate the output to the browser. With ODBscript, this is done by embedding commands and variables in HTML "script" files, which completely control the output as explained below.

The interface to databases is provided by connecting to any ODBC driver that you have on your system. ODBC uses SQL (Structured Query Language) statements to SELECT, INSERT, UPDATE or DELETE the data. Some ODBC drivers support additional SQL statements such as CREATE and ALTER (table definitions) and GRANT (access privileges).

ODBscript provides commands for connecting to databases and executing SQL, but it also provides many other useful commands to facilitate dynamic, interactive Web page generation. For example, ODBscript can be used to create sophisticated "form mailer" and "guestbook" applications.

The odb.exe script processor can also be run offline as a DOS "command line" program. For example, you can run odb.exe on your PC to generate Web pages and save them as disk files, which may then be uploaded to your Web site.

Note that ODBscript is also available as an ISAPI "server extension" for MS-IIS and compatible Web servers. ISAPIs are more efficient than CGIs, because they are DLLs that only need to be loaded once, and because they can use ODBC "connection pooling".

This User's Guide will assume that you are already fairly familiar with the HTML (Hypertext Markup Language, which is used to format Web pages) and with SQL. If you are not, there is much information available on the Web, and you will need this information to use ODBscript effectively. For the HTML part, you might consider using one of the specialized Web page editors.

ODBscript is a Script Processor

ODBscript reads script (or "template") files as input. The script files generally contain standard HTML formatting commands ("tags") and text, both of which are simply copied directly to the output. But these scripts also contain ODBscript commands and "variable" references. For example, commands may be used to query or update an ODBC database using variable data passed in with an HTML form, and variable references in the HTML can be used to define how query results and calculated values are to be displayed. When ODBscript finds a command statement, that statement is executed immediately. Some commands cause text to be inserted into the output at that point. When ODBscript finds a variable reference, the "current value" of the variable (i.e., a string of text) is substituted at that point.

Variables are referenced in an ODBscript script file by enclosing a variable name within a pair of dollar signs ($); for example, $name$. They may be used anywhere in the text to be output, in HTML tags, or in ODBscript commands. Variables are automatically assigned values by HTML input form fields and URL string variables, by SQL query statements, and by "browser cookies". You can also assign values to variables with command statements, and there are certain variables that the program sets for you.

One source of possible confusion for new users is that variable references are replaced by their "current value" wherever they are encounted -- in text and HTML tags that are simply being output and also in ODBscript commands. However, there are certain contexts (and only certain contexts) that use a special "expression mode." This mode allows for doing arithmetic on numeric values and doing "logical" comparisons (e.g., "equal to", "greater than", "less than"). Expression mode uses certain special characters as "operators" (such as "+" to cause addition) and some special keywords (such and "AND" and "OR"), so it is very important to observe the expression rules to insure that the expression is properly evaluated. But this mode only applies to very specific contexts, and in all other contexts variables are simply replaced with their values, without any attempt to evaluate an arithmetic or logical expression. Specifically, the contexts where expression mode is required are: on the right-hand side of the "=" sign in a SET variable=value command; the entire statement (i.e., the "test condition") in an IF command or a WHILE command; and in most cases, for the "arguments" given for string functions or numeric functions, whether those functions are used inside another expression, in text that is simply being output, or in commands. Typically, for all other commands, variables may be used as required instead of "literal" text, but like ordinary output text, those variables are simply replaced with their current variables, without any attempt to evaluate an expression.

Another warning about context that might cause confusion: There are certain contexts where a variable name is required (and only a variable name will work correctly because the variable itself is being operated on), so those variable names should not be enclosed in "$" characters like a variable reference that is to be replaced with it's current value. One example is the left-hand side of the "=" sign in a SET variable=value command. Another example is the list of variables given for the EACHMULTI command. Certain functions, such as the $split( ) function, operate on a single variable, so that variable name should be given without the "$" characters. These cases will be explicitely denoted in this users guide as they are presented, but hopefully this warning will alert you to be aware that there a certain contexts where we are refering to a specific variable, not to the current value of that variable, whereas enclosing a variable name inside of "$" characters always means to replace that reference with the current value of the variable.

For all releases of the program prior to ODBiC Release 2.1, ODBscript commands were embedded in the script files by using the standard HTML "comment" notation: The start of an HTML comment is marked by the characters "<!--" and the end of the comment is marked by the characters "-->". Browsers ignore these comments, but ODBscript will look for command names beginning immediately after the characters "<!--" (with no spaces between the comment marker and the command name, such as "<!--SELECT ...").

Beginning with ODBiC Release 2.1, you can also use "script tag" characters, <% and %>, to mark the beginning and ending of commands, and you can use a semi-colon to separate multiple commands inside a set of script-tag markers. This is the method used by the ASP and JSP scripting languages. The HTML-comment style of commands shown above is still supported, but script tags are generally easier to read and require less typing.

The complete explanation of both methods is described in more detail in the Commands chapter. The remainder of this User's Guide will primarily use the script-tag style.

Two Quick Examples

Here is an example of how ODBscript commands will appear in a script file, using the older HTML-comment style:
  <--DATABASE DSN=MyData -->
  <--SELECT * FROM Products -->
  <--TABLE -->

The DATABASE specifies an ODBC database "connection string" for a "Data Source Name" of "MyData". The SQL command will select all the data (i.e., all columns and all rows) from a database table named "Products". The TABLE command will automatically format all of the results into an HTML output table.

(Note that this guide will always show ODBscript commands and SQL keywords in uppercase (capital) letters for clarity, but you may use lowercase if you prefer. Commands and variable names are never sensitive to character case.)

For comparison, the following is the same code but it uses the script tags:

    SELECT * FROM Products;
The script-tag markers were placed on separate lines in the example above, and each command is on a separate line, which some people prefer for multiple lines of script, but the script can also be place immediately before and after commands (spaces are not required), and multiple commands can be on the same line, such as:

The next example will randomly select a "famous quotation" from a database and display it on a Web page. (This example could be modified to display a random banner graphic file.) Assume that you have an MS-Access database with a table named "Quotes". Each row in the table has a quotation in a column named "quote" and the author of that quotation in a column named "author". Each row also has a column named "ID" which is a sequential number starting with 1. The method used is this: determine the number of entries in the table; generate a random number between 1 and that number; select the entry having that random number as its ID; and display this quote and author.

  <% DATABASE "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:/db/quotes.mdb" ;
     SELECT count(*) AS quote_count FROM Quotes ;
     SET quote_number = rand($quote_count$) + 1 ;
     SELECT quote, author FROM Quotes WHERE ID = $quote_number$ %>
  <CENTER> $quote$ <BR> <I> - $author$ </I></CENTER>
Again, the DATABASE statement specifies an ODBC connection to a database, but this example shows a direct ("DSN-less") specification, without using any DSN, of the ODBC driver to use and the file path to the database. (Note: DSN-less connections work on Windows 98 and NT, but will not work for Windows 2000 or XP because of registry access restrictions on CGI programs.) The first SELECT command executes a query that selects the total row count in the "Quotes" table into a result column named "quote_count". (The count(*) function and the "AS" result specification are standard SQL.) Whenever ODBscript executes any SQL SELECT, each result column value will be contained in an ODBscript variable having the same name as the column, so the "quote_count" value can be inserted anywhere in the script by using the variable reference $quote_count$.

The SET statement creates a variable and sets it to the value following the "=" sign. In this case, a variable named "quote_number" is set to the value returned by the "rand" function, plus one. The "rand" function generates a random number that is greater than or equal to zero but less than the given parameter number (i.e., the value inside parentheses, which in this case is the $qoute_count$ value). Adding 1 makes "quote_number" a value greater than or equal to 1 but less than or equal to the total row count.

The next SELECT statement queries the Quotes table for the row having that random number as its ID column, returning the "quote" and "author" columns. Following that query, as before, each column value in the result row can be inserted anywhere into the script by referring to a variable having that same name, so the last line simply outputs the HTML to display the $quote$ and $author$ variables.

ODBscript Script Files

Script files are the input to ODBscript and they control the output as stated above. They will look very similar to normal HTML Web page files. In fact, you may wish to start with an existing Web page and add the ODBscript commands. Although it is possible to execute an SQL query and generate simple output without a script file (see the section Using ODBscript Without a Script File), you can get much more sophisticated results with a script.

When ODBscript is used as a CGI program, there are two ways to specify the script file. The easiest way is to include it as part of the URL that is used to invoke (execute) ODBscript. For example, if ODBscript is used as the ACTION function for a FORM declaration, then you need a URL that "points to" ODBscript on your system. The minimum specification might be something like this (depending on where you installed the program):

  <FORM METHOD="post" ACTION="http://www.yourdomain.com/scripts/odb.exe">
But the CGI interface allows you to include some "extra path" information after such a URL, and this information is available to the CGI program when it executes. You can use this "extra path" information to tell ODBscript the path and file name for your script file. For example, this URL:
will tell ODBscript that you want to process a file named "script1.odb", and it is on subdirectory "your_dir". That is, "your_dir" is a subdirectory of the Web server's main HTML file directory. This "root" directory is defined in your server's configuration file; refer to your server's documentation. If your script is directly on the server's root directory -- not on a subdirectory -- then you would just give the file name. (Note that your server's root directory for HTML files might actually be a subdirectory itself, such as "\www\htmlfile\".)

In this guide, script files will be identified with ".odb" file name extensions. This is not required; you can name your scripts anything you like. However, consistently using file name extensions will make it easier to identify how a file is supposed to be used. You might also want to create a separate directory for your scripts so that they aren't confused with regular HTML files.

Like regular Web pages, your script files must be on a directory that is accessible to the server. Like regular URLs, the server's root directory is omitted from the "extra path" specification. Therefore, the "extra path" information for your script file, immediately following the "odb.exe", will be the same as the path you would use following "www.yourdomain.com" to access that file directly through the Web, such as: "http://www.yourdomain.com/your_dir/script1.odb". (In fact, you should use your browser to try to access your script file that way, to make sure that you've got it in the right place.)

NOTE: This method only works if your Web server properly sets up a CGI input variable named PATH_TRANSLATED. If ODBscript is unable to open the script file, then it will give you an error message showing the file path and name that it tried to open. If you get this error message, examine the path carefully: This should be the full directory path, including the Web server's root directory. If it is not, then your server does not properly set up the PATH_TRANSLATED CGI variable.

However, if ODBscript does not get the PATH_TRANSLATED variable from the Web server, then it will try to use a variable named PATH_INFO, which is simply everything following the "odb.exe" in the URL. If the path shown in the error message is only this "extra path" string (e.g., in the example above, just "/your_dir/script1.odb"), then you should try using the full file system path (including the root directory) following the "odb.exe":

If you are forced to use this method, however, remember that your URLs may not work if you install a different server or move your files to a system with a different server.

If neither of these methods work, then you will have to tell ODBscript the name of the script file by passing in a variable named "input". There are two ways to do this, depending on whether you are using a FORM (with ODBscript as the ACTION function), or you are using a direct "anchor" link in the text (i.e., <A HREF="..."> link text </A>) For an "anchor" link, you can include the "input" variable after a question mark (?) in the URL:

  <A HREF="http://www.yourdomain.com/scripts/odb.exe?input=/htmlroot/your_dir/script1.odb">
Note that if you specify the script file with the "input" variable, then you must provide the full file system directory path, starting at the root directory. (That is, no Web server directory "mapping" can be used.)

If you are using a FORM to execute ODBscript, then the "input" variable should be given as a FORM variable. (It can be anywhere after the <FORM ...> declaration but before the </FORM>.) This variable should be specified as a "hidden" form field, such as:

<INPUT TYPE="hidden" NAME="input" VALUE="/htmlroot/your_dir/script1.odb">.)

Refer to the section Running ODBscript as a CGI Program for more information on invoking and passing variables to ODBscript.

If you are using ODBscript as a "command prompt" program, rather than as a CGI online program, then the input file (and the output file) can be specified in the "run" command line. See the section Running ODBscript from the Command Prompt.

ODBscript Variables

As noted above, variable references in a script file are replaced by their "current value" within ODBscript. Variable references may be used anywhere in the script file: in ordinary text that is to be output; in HTML commands that are to be output; or within ODBscript command statements.

A variable is referenced in an ODBscript script file by enclosing a variable name within a pair of dollar signs ($). Variable names must always begin with an alphabetic character, "a" to "z" or "A" to "Z". (This helps to avoid confusion with an actual dollar amount like $100.) After the initial character, variable names may be any uppercase or lowercase alphabetic character, any numeric digit, the underscore character (_), the hyphen character (-), or a space (blank) character.

There are five ways that variables can be defined and set to values in ODBscript:

See the sections Running ODBscript as a CGI Program and Running ODBscript from the Command Prompt for more details on input-type variables. In the ODBscript script file, any variable name that exactly matches an input variable name is assumed to be a reference to that input variable, and that reference is replaced by the input value. Actually, for all variables, variable names match "exactly" if they are spelled the same, but character case does not matter. For example, "name" would match "Name" or "NAME".

The second source for ODBscript variables is from a database. Whenever any SQL SELECT statement is executed, variables are automatically created for each of the result column names. These variables are initialized with the values from the first result row after the SELECT is executed. (There is no "fetch" command; a "fetch" of the first row is automatically performed when the SQL statement is a SELECT.) There is a command syntax for "looping through" multiple result rows, which changes the "current value" of the column variables on each row.

The third way that a variable can receive a value is by using the SET command in the script. With this command, you can set a variable to be a specific number or character string (which might be called a "constant" or "literal" value), or to another variable's value, or to the value of an arithmetic or character string "expression" using variables and constants.

In addition to the SET command, there are two ODBscript commands that can set variable values. One allows you to set default values for any variables that you wish to use in the script file, but which might not have an actual value. For example, a user might leave a form input field blank, or a column retrieved from the database might be blank or null. If no other value for a variable is found, then its default value will be used. Another command that controls variable values allows you to define translation tables for values. This is useful when database values employ codes that are not immediately obvious or readable. The translation table for a variable consists of a set of translation pairs, each pair being an actual value and the desired display text for that value. For example, you might have a database "flag" for some option where a value of "1" means "Yes" and "0" means "No". The translation table for that variable would have two entries: 1=Yes, 0=No.

"Cookies" are special variables that can be sent to browsers with the SETCOOKIE command. These variables will be returned to CGI programs on the same system, much like a <FORM> input variable, except that they are automatically available when the browser executes any CGI. (They are actually passed using a different method than <INPUT> variables, but ODBscript hides that complication by setting up cookies just like input variables.)

ODBscript also has certain predefined variables that may be used for output, such as the current date and the current SQL result row number. There are also some predefined input variables that you may use to control processing. These are documented in the section Predefined Variables.

NOTE: If an undefined variable name is encountered in the script file, then the variable name itself will be output "as is", including the enclosing "$" signs. (The one exception to this rule is that undefined variables used in any logic expressions or string functions are treated as null strings.)

Any variable's text value may contain other variable references, and the embedded variables may also reference other variables. All variables are fully resolved before output or before execution of any command containing variables.

There are some special character "string functions" that you may use anywhere that you can use a variable. For example, there are functions for taking left, right, or middle "substrings" from a variable. The names of these functions begin with a "$" sign, and they will always have one or more "function arguments" enclosed in parentheses. For example, this expression, "$mid($string$,3,4)" is replaced by the value of variable $string$, starting from the third character for a length of four characters. Any of the arguments to a string function may be arithmetic or logic expressions using variables or literals. You can also define your own string functions using the FUNCTION command, which allows you reuse common processing commands without copying them ever place you need them, and it also can make your scripts easier to read.

Some of the "numeric" functions return numeric values but they are intended to be used with character-string variables: len( ), pos( ), char( ), match( ), isNumber( ), isAlpha( ), and isAlphaNum( )). These may be used anywhere in SET expressions or string-function arguments, or they may be used as true/false logic tests in IF statements to validate input data.

Next Chapter: ODBscript Commands

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