Open DataBase Scripting Language

---

Version 3

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

http://www.ODBscript.com

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

  <--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:

  <%
    DATABASE DSN=MyData;
    SELECT * FROM Products;
    TABLE;
  %>

  <%DATABASE DSN=MyData; SELECT * FROM Products; TABLE%>

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>

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.

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">

  "http://www.yourdomain.com/scripts/odb.exe/your_dir/script1.odb"

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":

  "http://www.yourdomain.com/scripts/odb.exe/htmlroot/your_dir/script1.odb"

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">

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.

• Variables are input from an HTML form or URL when ODBscript is run;
• they may be selected as data columns from the database;
• they can be explicitly declared and set in the script;
• they may be input as "cookies" that you have previously sent to a browser;
• and there are special predefined variables that are set by the program itself.

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.