Open DataBase Internet Connector

---

Version 2.2

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

http://www.odbic.com

Variables are referenced in an ODBiC template 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 ODBiC 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.

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

Beginning with 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 complete explanation of both methods is described in more detail in the Commands chapter. The remainder of this User's Guide will 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 ODBiC commands and SQL keywords in uppercase (capital) letters for clarity, but you may use lowercase if you prefer.)

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.) 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.

Template files are the input to ODBiC 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 ODBiC commands. Although it is possible to execute an SQL query and generate simple output without a template file (see the section Using ODBiC Without a Template File), you can get much more sophisticated results with a template.

When ODBiC is used as a CGI program, there are two ways to specify the template file. The easiest way is to include it as part of the URL that is used to invoke (execute) ODBiC. For example, if ODBiC is used as the ACTION function for a FORM declaration, then you need a URL that "points to" ODBiC 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/odbic.exe">

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

In this guide, template files will be identified with ".odb" file name extensions. This is not required; you can name your templates 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 templates so that they aren't confused with regular HTML files.

Like regular Web pages, your template 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 template file, immediately following the "odbic.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/template1.odb". (In fact, you should use your browser to try to access your template 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 ODBiC is unable to open the template 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 ODBiC 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 "odbic.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/template1.odb"), then you should try using the full file system path (including the root directory) following the "odbic.exe":

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

If neither of these methods work, then you will have to tell ODBiC the name of the template file by passing in a variable named "input". There are two ways to do this, depending on whether you are using a FORM (with ODBiC 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/odbic.exe?input=/htmlroot/your_dir/template1.odb">

If you are using a FORM to execute ODBiC, 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/template1.odb">.)

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

If you are using ODBiC 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 ODBiC from the Command Prompt.

• Variables are input from an HTML form or URL when ODBiC is run;
• they may be selected as data columns from the database;
• they can be explicitly declared and set in the template;
• 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 ODBiC as a CGI Program and Running ODBiC from the Command Prompt for more details on input-type variables. In the ODBiC template 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 ODBiC 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 template. 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 ODBiC commands that can set variable values. One allows you to set default values for any variables that you wish to use in the template 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 ODBiC hides that complication by setting up cookies just like input variables.)

ODBiC 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 template 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 templates 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.