 |
Internet Connector |
DEMO Version 1.6
|
Internet Connector |
This documentation is provided with the Demo Version 1.6, which you are licensed to use for 30 days. This is a fully functional version, but it will expire and discontinue functioning after 30 days. Please note that this demo version will add a small link to the ODBiC home page at the bottom of any output HTML pages, and it will add a notice at the bottom of any output e-mail messages. These messages are not present in the registered version.To register ODBiC, 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
ODBiC provides an 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. Since this guide will refer frequently to the ODBiC program and the ODBC system, ODBiC will be shown in red to help avoid confusion.)
ODBiC may be used as a CGI program to access the data in "real time". (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 URLs (Universal Resource Locators) from browsers, much like "static" Web pages, but a CGI program can dynamically generate the output to the browser. For efficiency, certain Web servers support CGI-like "server extensions", and ODBiC is available as an ISAPI version for MS-IIS and other servers and an NSAPI version for Netscape servers. ODBiC can also be run offline as a DOS "command line" program to generate the Web pages and save them as disk files.
The interface to databases is provided by connecting to any ODBC driver. 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).
ODBiC 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, ODBiC can be used to create sophisticated "form mailer" and "guestbook" applications.
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 ODBiC effectively. For the HTML part, you might consider using one of the specialized Web page editors.
ODBiC reads "template" (or "script") files as input. The template files generally contain standard HTML formatting commands (or "tags") and text, both of which are simply copied directly to the output. But these templates also contain ODBiC commands and variable references. When ODBiC finds a command statement, that statement is executed immediately. Some commands cause text to be inserted into the output at that point. When ODBiC finds a variable reference, the current value of the variable is substituted at that point.
Variables are referenced in an ODBiC template file by enclosing a variable name within a pair of dollar signs ($). They may be used anywhere in the text to be output, in HTML tags, or in ODBiC commands.
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
"<!--".
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.)
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.
Two Quick Examples
Here is an example of how ODBiC commands will
appear in a template file:
<!--DATABASE DSN=MyData -->
<!--SQL SELECT * FROM Products -->
<!--TABLE-->
<!--DATABASE DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:/db/quotes.mdb -->
<!--SQL SELECT count(*) AS quote_count FROM Quotes -->
<!--SET quote_number = rand($quote_count$) + 1 -->
<!--SQL 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"
specification, without using any DSN, of the ODBC driver to use and the file
path to the database. The first SQL 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 ODBiC
executes any SQL SELECT, each result column value will be contained in an
ODBiC variable having the same name as the column, so this "quote_count"
value can be inserted anywhere in the template 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 SQL 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 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">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 ODBiC the path and file name for your template file. For example, this URL:
"http://www.yourdomain.com/scripts/odbic.exe/your_dir/template1.odb"will tell ODBiC that you want to process a file named "template1.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 template 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, 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 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 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">Note that if you specify the template 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 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.
A variable is referenced in an ODBiC template 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 ODBiC:
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.
Some of the "math" 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.