Open DataBase Internet Connector

---

Version 1.4

Variables are referenced in an ODBiC template file by enclosing a variable name within a pair of dollar signs ($).

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 "<!--". Here is an example of how ODBiC commands will appear in a template file:

    <!--DATABASE DSN=MyData -->
    <!--SQL select * from Products -->
    <!--TABLE-->

The DATABASE specifies on 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.

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

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

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: "http://www.yourdomain.com/your_dir/template1.html". (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.tmpl"), then you should try using the full path (including the root) following the "odbic.exe":

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

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

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

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.

There are four ways that variables can be defined and set to values in ODBiC: they are input when ODBiC is run; they are selected as data from the database; they can be explicitly declared and set in the template; or they are special predefined variables that are set by the program.

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

The second command for variables 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.

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 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. Another function encodes a variable in "URL format" so that it may be passed to a CGI program in an HTML link. 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 are intended to be used with character-string variables: len( ), pos( ), char( ), 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.

You must have a DATABASE statement in your HTML template file before any SQL commands. (Optionally, you may pass in a connection string in the variable named "database"; see the section Predefined Variables.) This command does not actually establish a connection, however. Rather, the connection string specified by this statement will be used to connect when an SQL command is executed.

The connection string set by this statement applies to all subsequent SQL commands until a different DATABASE command is encountered. If you need to access another database, just use another DATABASE statement before those SQL statements. (This does not mean that the database connection is re-established for each SQL statement, however. The connection established by the first SQL statement stays open until another SQL statement is executed with a different connection string, or until ODBiC terminates.)

You can use variables anywhere in the DATABASE statement (including the DSN). One common usage would be variables to insert the user's ID and password, which you might get from an input form:

    <!--DATABASE DSN=Employees; UID=$user$; PWD=$password$ -->

However, you can bypass all DSN user associations by giving a complete ODBC connection specification in the DATABASE statement. This would include a driver and file type specification, file location path, and various options. Refer to the ODBC documentation for complete details, but here is an example of a connection string for an MS Access database contained in the file C:\httpfile\db\products.mdb:

    <!--DATATBASE DRIVER={Microsoft Access Driver (*.mdb)};
        DBQ=c:\httpfile\db\products.mdb; FIL=MS Access-->

Depending on the database that you are using, the DBQ specification may need to be a complete file path and file name, or it may just be a directory. Refer to the examples in the chart below. For example, the MS-Access DBQ gives the file name of the database, "c:\temp\sample.mdb", but dBASE puts each database on a separate directory, so the DBQ just indicates this directory, "c:\temp".

If you are using ODBC 3.0 (which is shipped with Office97), you may also need to use the DRIVERID keyword. In the chart below, if you are using ODBC 2.x do not use the DRIVERID keyword.

Example Connection Strings Without Using DSN

  Database                Keywords

  Microsoft Access        DRIVER={Microsoft Access Driver (*.mdb)};
                          DBQ=c:\temp\sample.mdb;
                          FIL=MS Access

  dBASE                   DRIVER={Microsoft dBASE Driver (*.dbf)};
                          DBQ=c:\temp;
                          DRIVERID=277;
                          FILE=DBASE2   (or DBASE3, DBASE4)

  Microsoft Excel 3/4     DRIVER={Microsoft Excel Driver (*.xls)};
                          DBQ=c:\temp;
                          DRIVERID=278;
                          FIL=EXCEL

  Microsoft Excel 5       DRIVER={Microsoft Excel Driver (*.xls)};
                          DBQ=c:\temp\sample.xls;
                          DRIVERID=22;
                          FIL=EXCEL

  Microsoft FoxPro        DRIVER={Microsoft FoxPro Driver (*.dbf)};
                          DBQ=c:\temp;
                          DRIVERID=536;
                          FIL=FOXPRO 2.0   (or FOXPRO 2.5, FOXPRO 2.6)

  Paradox                 DRIVER={Microsoft Paradox Driver (*.db );
                          DBQ=c:\temp;
                          DRIVERID=26;
                          FIL=PARADOX

  Text                    DRIVER={Microsoft Text Driver (*.txt;*.csv)};
                          DEFAULTDIR=c:\temp;
                          FIL=TEXT

You may define DEFAULT values for up to 100 different variables. You may also redefine the default value for a variable that was previously used in the template file. (The default is in effect from the point of the DEFAULT command until another DEFAULT is specified for the same variable.) And you may define a default value that is another variable (for example, "<!--DEFAULT var1 = $var2$, ... -->"). (Note that this variable substitution will be done when the defaulted variable is actually referenced, not when the DEFAULT value is set by this command. This allows the DEFAULT value to change as "var2" changes.)

<!--EACHROW--> ... <!--ENDROW-->

Inside an EACHROW specification, you may use the ODBiC variable $row$ to reference the current row number. The $row$ variable is initialized after each SQL statement (see the SQL command) and it is incremented for each fetched result. You might use this variable to enumerate the results, or you might want to test for particular row numbers. For example, you could use a conditional statement <!--IF $row$ = 1 --> to do some special output, such as a table header, before any results are output. (But it is generally easier to put such "first row" formatting between the SQL statement and the EACHROW command: Any output after the SQL but before the EACHROW will be done for the first row only.) The $row$ variable is most useful when you want to limit the number of rows displayed.

Since the EACHROW command always loops through all of the result rows, you should not use EACHROW combined with any of the other result looping commands, TABLE, UPDATEFORM, or OPTIONLIST.

The formatting text between the EACHROW and the ENDROW can span multiple lines up to a maximum size of 12K bytes (which is about 150 full 80-character lines).

<!--EXEC dos_command [arguments] -->

For security reasons, the command string cannot contain a "$T" command separator (which, on a DOS command line, can be used to issue multiple commands on a single line).

<!--EXIT-->

The EXIT command causes ODBiC to stop processing the input template file. It is most useful inside an IF statement, such as after output of an error message. In such cases, an "early exit" can avoid some complicated and heavily nested IF/THEN/ELSE clauses that would be necessary to skip the rest of the file.

<!--FORMAT variable=mask [, variable=mask, ...] -->

Note that the FORMAT command does not cause formatting at the point that it is issued; it defines a mask that will be used anytime that the variable is referenced. Therefore, the FORMAT command can appear anywhere in the template before the point that the variable will be referenced for output. (Specifically, the FORMAT command should not, and cannot, be used inside an EACHROW loop. Specify the FORMAT mask before the EACHROW.) Note, however, that there is a string function, $format( ), that does perform this same formatting function at the point that it is encountered.

The formatting mask uses the pound sign character (#) to indicate a position that can be filled by a character or digit from the variable. For numeric values, the zero character (0) also represents a position that can be filled by a digit from the variable, or a "0" if there is no digit at that position. Other characters (except as noted below) are copied to the formatted output.

For variables that have a numeric value (i.e., a variable containing only digits, plus or minus sign, or a decimal point), you may use the minus sign (-) as the first character of the mask to indicate that negative numbers should be formatted with a "-" sign in front, but positive numbers are to have no sign. A plus sign (+) as the first mask character causes both positive and negative numbers to be shown with a plus sign or a minus sign, respectively. If the mask does not use either the plus or minus signs as the first character, then negative numbers will be shown without a sign.

You may also use the dollar sign character ($) or pound sign character (£) as the first mask character (or as the second character if you have a plus or minus sign as the first character.) This will cause a dollar sign or pound sign to be added to the front of a numeric value.

For numeric variables, the explicit or implicit decimal points of the mask and the number are aligned. The result will have a decimal point only if the mask does. Working toward both the left and right of the decimal point, digits from the variable replace any "#" characters in the mask, but only if there is a digit at a given position. If there is no digit at that position, then the formatting process stops. That is, as long as there are digits remaining in the value, then "#" characters are replaced by digits and special characters such as commas are copied to the output, but when there are no digits left, then the formatting is finished and special characters past that point are ignored. A "0" character in the mask will be replaced by the digit from the variable at that corresponding position, if there is one, or the "0" will remain in the output if there is no digit.

Here are some examples:

    <!--FORMAT price="$#,###,##0.00" -->

    If price is:    the output will be:
     10.00                 $10.00
     1250.00               $1,250.00
     6.0000                $6.00
     .501                  $0.50
     -1                    $1.00

    <!--FORMAT price="-$#,###,##0.00" -->

    If price is:    the output will be:
      235000               $235,000.00
      -10.999              -$10.99

    <!--FORMAT value="+#####0.0###" -->

    If value is:    the output will be:
      1                    +1.0
      505.505              +505.505
      -23.123456           -23.1234
      .5                   +0.5

NOTE: If your mask contains any commas, then you must enclose the mask in double-quotes (") in the FORMAT command. This is because commas separate the "variable=mask" pairs in the command. The quotes are optional if there are no commas in the mask.

You may define up to 50 format masks. You might use separate FORMAT commands for each variable or declare several in the same command, separated by commas. The list of "variable=mask" pairs can span multiple lines with the command-terminating "-->" mark after the last variable on the last line.

<!--IF [NOT] value1 condition value2 [AND | OR ...] --> ... <!--ELSE--> ... <!--ENDIF-->

"Value1" and "value2" can be any variable (referenced by the variable name prefixed and suffixed with "$" signs), a "literal" value (a number or a text string), an arithmetic expression using numeric-valued variables or literals, or a "string function" expression that produces a text string. Arithmetic expression may use "+" for addition, "-" for subtraction, "*" (asterisk) for multiplication, or "/" for division. Parentheses, "(" and ")", may be used to indicate the order of evaluation (i.e., operations inside parentheses are performed first). A "unary" minus sign is allowed to indicate that a variable, constant, or expression inside parentheses is to be negated (e.g., $x$ / -$y$ or -($x$ / $y$)). You may also use any of the math functions in an expression.

NOTE: Variables used in IF statements must be enclosed in "$" characters. That is, the program does not assume that any operands in an IF comparison are variables. Like output text, you must enclose the variable names in "$" characters to cause the variable's values to be substituted into the expression.

The "condition" specifies a test between the two values: "=" (equal), "<>" or "!=" (not equal), ">" (greater than), "<" (less than), ">=" (greater than or equal), or "<=" (less than or equal). If the specified relationship between the two values is true, then all text and statements following the IF, up to an ELSE or ENDIF statement, will be processed. The ELSE reverses the sense of the test, and any text and statements up to the ENDIF will be processed only if the test specified in the IF statement is false.

You may combine conditional tests using AND (i.e., conditions on both sides of the AND must be true) or OR (either side may be true), or use NOT in front of a condition expression to reverse its sense. You may use parentheses to indicate the order of the compounded tests. The default is that NOT is performed first, AND is performed next, and OR has the lowest precedence. For example, "NOT $a$=1 AND $b$=2 OR $c$=3 AND $d$=4" is the same as "((NOT $a$=1) AND $b$=2) OR ($c$=3 AND $d$=4)".

Actually, you can test the "condition" of a single variable. When only one value is given in a conditional expression (or a single value is compounded with NOT, AND, or OR), then the test produces a "true" result if the value is any non-empty string or any non-zero number. For example, you can say <!--IF NOT $name$ --> to test if the variable "name" has no value, or <!--IF $opt1$ AND $opt2$ --> to test for having values for both variables. Three of the math functions are intended to be used in this way to validate input data: isNumber ( ), isAlpha ( ), and isAlphaNum ( ). For example, you can say <!--IF NOT isNumber($price$) --> to check for an invalid number in the "price" variable.

An IF statement comparison is assumed to be a numeric comparison whenever both values are numeric values or arithmetic expressions. Otherwise, if either value is non-numeric, then a text string comparison will be used. You can use quote marks around or in a value expression to force the entire value to be treated as a text string, but the quotes are optional if the string expression contains any non-numeric characters. For these "implicit" text string comparisons, leading and trailing space on values are ignored. (For example, <!--IF $var$ = this value --> is the same as <!--IF $var$="this value" -->.) You can, however, include spaces inside quotes if you need to have them as part of the comparison, such as <!--IF $var$ = " " -->.

You may include another IF statement in the ELSE statement, such as:

    <!--IF $type$=A -->
        ...
    <!--ELSE IF $type$=B -->
        ...
    <!--ELSE IF $type$=C -->
        ...
    <!--ELSE-->
        ... (not A, B, or C)
    <!--ENDIF-->

Note that if you test a variable that has a TRANSLATE table defined for it, you must test for the translated value rather than the original value. In general, remember that the "value" expressions in an IF statement are processed like normal output before any arithmetic or the comparison itself is performed.

Example (indentation helps to pair IFs with ELSEs and ENDIFs):

    <!--IF $Discontinued$ = Yes -->
        This is a discontinued item.
    <!--ELSE-->
        <!--IF $UnitsOnHand$ -->
            We have $UnitsOnHand$ units in stock,
            <!--IF $UnitsOnHand$ < $UnitsOrdered$ -->
                which is insufficient to fill this order.
            <!--ELSE IF $UnitsOnHand$ - $UnitsOrdered$ < $ReorderLevel$ -->
                so we can fill this order, but it is time to reorder.
            <!--ELSE-->
                so we can fill this order.
            <!--ENDIF-->
        <!--ELSE-->
            We have no units in stock. Time to reorder.
        <!--ENDIF-->
    <!--ENDIF-->

The condition specified in an IF or ELSE IF statement can span multiple lines, but the "-->" must mark the end of the condition.

<!--IFNEW variable -->

    <!--SQL SELECT category, item_number, description FROM item ORDER BY category -->
    <!--EACHROW-->
    <!--IFNEW category -->
      <H1> $category$ </H1>
    <!--ENDIF-->
    <P> $item_number$ $description$ 
    <!--ENDROW-->

    <!--SQL SELECT * FROM master, detail WHERE master.id = detail.id 
            ORDER BY master.id, detail.order_date -->
    <!--EACHROW-->
    <!--IFNEW id -->
      (... format any data from the master table)
    <!--ENDIF-->
    (... format the data from a single detail row)
    <!--ENDROW-->

Note that the only argument in the IFNEW statement is a single variable. Since this argument must be a variable, you can use just the variable name without the "$" signs around it. (However, any "$" signs will be ignored.)

<!--INCLUDE filename -->

NOTE: The referenced filename must have the full directory path specification, such as would be used to open the file with a text editor, and no Web-server directory mapping will be applied.

<!--OPTIONLIST [size] column FROM table [WHERE ...] -->

    <FORM METHOD="post" ACTION="\scripts\odbic.exe\your_dir\template2.htm" >
    Select category: <!--OPTIONLIST 10 Category from Products --> <BR>
    <INPUT TYPE="submit" VALUE="Get Products">
    </FORM>

    <FORM METHOD="post" ACTION="\scripts\odbic.exe\your_dir\template3.htm" >
    Select product: <!--OPTIONLIST 25 Product from Products WHERE Category = '$category$' --> <BR>
    <INPUT TYPE="submit" VALUE="Get Product Description">
    </FORM>

OUTPUT commands are not "nested". That is, each occurrence of an OUTPUT command causes any previous output file to be closed, and you may not resume writing to that closed file.

<!--QBE TABLE=database_table, numeric_field | 'text_field' [, ...] -->

    <!--QBE TABLE=Employees, EmployeeID, 'LastName', 'FirstName', 'Title',
       'BirthDate', 'HireDate', 'Address', 'City', 'Region', ReportsTo -->

When a REDIRECT command is encountered, the remainder of the HTML template file is not processed.

<!--SET variable = value [, variable = value, ...] -->

The SET command explicitly creates and assigns a value to a variable. Unlike the DEFAULT command, a SET variable will override any other definition for the variable. Also, you may use other variables and arithmetic expressions in SET commands, and they are evaluated at the point that the SET command is used.

Arithmetic expressions may use "+" for addition, "-" for subtraction, "*" (asterisk) for multiplication, or "/" for division. Parentheses, "(" and ")", may be used to indicate the order of evaluation (i.e., operations inside parentheses are performed first). A "unary" minus sign is allowed to indicate that a variable, constant, or expression inside parentheses is to be negated. You may also use any of the math functions and the string functions in the expression. You can use logical expressions in a SET command: A "true" value will be represented by a "1" and a "false" value will be represented by a "0". You may use these "1" and "0" logic values in arithmetic expressions, such as <!--SET $count$ = $count$ + ($type$ = A)-->. (A "1" will be added to $count$ only if the $type$ is "A", which saves using an IF/ENDIF statement around the SET.)

NOTE: If you use any variables in the "value" expression of a SET command, you must enclose the variable names in "$" characters. That is, the program does not assume that any part of the "value" is a variable.

If the string value contains any arithmetic operation characters that you don't want evaluated as arithmetic, then you must put the value inside double-quote (") marks. You must also use quotes if the value contains any commas, since commas are used to separate multiple variable specifications in the SET command. (The exception to this rule is that commas inside parentheses, e.g. commas separating function arguments, are recognized as being part of the expression for one variable.) Otherwise, quotes are not required, and any leading or trailing space characters will be ignored.

You can cause "string concatenation" by giving multiple variables in the "value", such as "var = $a$$b$" or "var = $a$ plus $b$". You may use quotes around any part of such a concatenated expression or around the entire expression. Variable references and arithmetic that are inside quotes, such as "$val$ + 1", will not be evaluated when the SET command sets the value. When the variable is actually used for output, however, any variables embedded in the SET expression will be replaced by their current value.

Since the target of a SET assignment is necessarily a variable, you do not need to enclose that variable name in "$" characters. (However, they will be ignored if you do.)

Examples:

    <!--SET count = $count$ + 1, done = Yes,
       value = ($number1$ + $number2$) * $price$, tax = $price$ * $rate$ -->

    <!--SET hypotenuse = sqrt($x$*$x$ + $y$*$y$), prefix = $left($string$,3) -->

    <!--SET total = "$eval($UnitPrice$ * $Quantity$)" -->

    <!--SQL SELECT item_number, description FROM items -->

ODBiC supports the keyword "AS" (upper or lowercase) which may be used to "rename" a result column. For example:

    <!--SQL SELECT item_nbr AS item, qty AS quantity FROM ... -->

    <!--SQL SELECT (quantity * unit_price) AS total_price ... -->

One non-obvious caution about column names used in SQL statements: Some databases (such as MS Access) allow spaces in column names. (This is why ODBiC allows spaces in variable names, even though this author discourages the practice.) Remember to enclose such column names inside double-quote marks (") when you use them in SQL statements. (ODBiC-generated SQL statements, such as the QBE query and UPDATEFORM, will also have double-quotes around any column names that contain spaces.)

Variables can be used anywhere within SQL statements. The most common usage would be as column values, such as in the WHERE clause of a SELECT statement or the SET clause of an UPDATE statement. (Remember, however, to enclose variable references inside apostrophes for any character-data values, such as "... WHERE name = '$name$' ...".) But you might also use a variable for a table name or column name. In fact, the entire SQL statement can be referenced as a variable, and that variable may also contain variable references. This is useful when you want to pass SQL statements in from forms.

For example, ODBiC can be used without an HTML template file. To do so, it is necessary to pass in a CGI variable named "sql" containing an SQL statement to execute. If there is no template file, ODBiC automatically executes the equivalent of a command: <!--SQL $sql$ -->. However, in a template file, you can specify any variable for the SQL command, such as <!--SQL $user_statement$ -->.

After each SQL statement, ODBiC sets several internally maintained variables that you may access. One of these is $row$, which is set to 0 if there are no rows returned by the SQL statement, or is set to 1 if at least one result row has been "fetched". Therefore, you can test the $row$ variable after a SELECT to see if any results were returned. For example:

    <!--SQL SELECT item, description FROM items -->
    <!--IF $row$ = 0 -->
      $sql_error$
    <!--ELSE-->
      . . . (format results, e.g., with an <!--EACHROW--> command)
    <!--ENDIF-->

Another ODBiC variable set after an SQL statement is $rows_affected$, but it takes on a meaningful value only for UPDATE, INSERT, and DELETE statements. Again, for these statements, $sql_error$ is set if there is an error, but $sql_error$ is also set to "### rows affected" for successful statements, where the ### will be equal to $rows_affected$. (This means that, unless you want to do some special error handling, you might always just print out $sql_error$ after an UPDATE, INSERT, or DELETE statement, without testing $rows_affected$ for being 0. Remember that for UPDATE and DELETE, $rows_affected$ might be 0 even if the statement was valid, because no rows met the WHERE clause criteria.)

There is a status variable that can be used to determine directly the success of the SQL operation, but its interpretation depends on the type of statement. The variable is $sql_status$, and its possible values are:

$sql_status$ = -2 means "No rows selected/affected"
$sql_status$ = -1 means "SQL execution error"
$sql_status$ = 0 means "SQL SELECT or DDL statement successful"
$sql_status$ = 1 means "One or more rows affected by INSERT, UPDATE, or DELETE"

For example, immediately after a SELECT, a "0" status will mean that at least one row has been selected, whereas a "-2" means that there were no rows that met the WHERE clause selection criteria. However, after any of the result-looping commands (TABLE, EACHROW, UPDATEFORM, and OPTIONLIST), the status will always be "-2". A status of "1" is the normal return for a successful INSERT, UPDATE, or DELETE, and a "-2" status would indicate that no rows met the WHERE clause selection criteria. A "-1" status always means that there was some error encountered by the ODBC driver in executing the SQL statement.

The SQL statement, which must be terminated with the "-->" marker, can span multiple lines up to a maximum size of 4K bytes (which is about 50 full 80-character lines).

<!--TABLE [html_table_opts] -->

The TABLE command automatically formats all columns and all rows from the previous query into an HTML table. (This formatting uses the HTML <TABLE>, <TR>, and <TD> commands. The database column names (or the "AS" renamed columns; see the example in the SQL command) are used as the column headers. (The table headers are formatted with HTML <TH> commands.) For improved appearance and readability, ODBiC capitalizes the first letter of the column headers, converts any underscore characters to spaces, and capitalizes any letter following an underscore. Thus, the database column "customer_name" would have a table header of "Customer Name".

The html_table_opts specifications are the same as the HTML <TABLE> optional elements. That is, whatever you put for html_table_opts will simply be included in the generated HTML <TABLE> command. For most HTML 2.0 browsers, these options can include BORDER, CELLSPACING, and CELLPADDING. Some browsers allow the BGCOLOR command.

Since the TABLE command always loops through all of the result rows, you should not use TABLE combined with any of the other result looping commands, EACHROW, UPDATEFORM, and OPTIONLIST.

<!--TRANSLATE variable value=newvalue [, value=newvalue, ...] -->

You may define up to 32 different translation tables (i.e., tables for 32 different variables), and each table may have up to 100 translation value pairs. You may also redefine a translation table for a variable that was previously used in the template file. (The translation is in effect from the point of the TRANSLATE command until another TRANSLATE is given for the same variable.) And you may define a translation "newvalue" that is another variable (for example, "<!--TRANSLATE var1 value1=$var2$, ... -->").

<!--UPDATEFORM [FILE=html_template,] TABLE=database_table, KEY=key_field, [...,] numeric_field[:size] | 'text_field[:size]', ... -->

If given, the optional "FILE=..." specification will be included on the form as a "hidden" field to tell ODBiC what HTML template file to use when the form is submitted. (However, a template file is not necessarily required; see "Using ODBiC Without a Template File .) The form also includes a "hidden" HTML field to specify the database connection to use for the update; this will be the same as the database in effect when the UPDATEFORM command is used. (If necessary, use a DATABASE command immediately before the UPDATEFORM command, but if you have just selected data from a database, then that same database will be used for the update form.) If you do use the "FILE" keyword to define an HTML template file to process the UPDATE statement, then that template will need to reference two of the predefined variables: The database string will be passed in with the name "database" and the generated SQL UPDATE statement will be passed in as "sql". Therefore, the update can be performed with these statements:

    <!--DATABASE $database$ -->
    <!--SQL $sql$ -->

The "TABLE=..." specification for this command is required. It specifies the database table that the generated SQL statement will update. One or more "KEY=..." specifications are also required. They will be included on the generated form, but they will be shown simply as text, not as modifiable fields. They will also be used in the WHERE clause of the generated SQL (i.e., "UPDATE database_table SET ... WHERE key_field=(current value), ..."). NOTE: You should specify KEY fields that will uniquely identify the record; otherwise all rows that satisfy the WHERE clause will be updated!

Following the KEY field or fields, one or more modifiable data fields may be specified. These are included on the form with their current values filled in, and the value may be changed by the user. The field names given in the UPDATEFORM list will be used as the HTML names for the generated <INPUT> fields, and they must be the same as the database column names (see below). Moreover, they should be currently-defined variable names in ODBiC, or they will not have any "current value" on the update form. Each of these fields will be included as SET column specifications in the generated SQL statement. The complete SQL statement would therefore be: "UPDATE database_table SET field=$field$, ... WHERE key_field=(current value), ...". When the user has made changes to any fields and submits the form, ODBiC will insert the new values from the update form into this SQL statement and then execute it. (This is the reason that the database column name, the ODBiC variable name, and form variable name must all be the same for any given field.)

NOTE: There are several considerations for specifying the key fields and update fields that are unique for this command:

• When ODBiC generates the SQL statement to be used for the subsequent update, it needs to distinguish between numeric fields and text fields. This is because, in SQL statements, numeric fields are given as plain digits but text fields must be enclosed in single-quotes (apostrophes). Therefore, you MUST tell ODBiC that a key field or update field is to be treated as a text field by enclosing its UPDATEFORM name in apostrophes. If you fail to do this, the generated SQL statement will not "quote" those values, and the statement will be rejected when it is executed.
• As noted above, the database column names must be exactly the same as ODBiC variable names. This is because ODBiC uses the field names, as they are given in the UPDATEFORM list, in the generated SQL statement, and it also uses the current values for those fields from its variable list to initialize the INPUT fields. This is usually not a problem, since the intended application for this command is to select data with one SQL statement, then write a form to allow the user to update the current data. (This does not mean that the variables necessarily got their values from a previous SQL select; they might be input variables or defaults. The only requirement is that the variable names are the same as the column names.) If you use an HTML template file to process the form when it is submitted, remember that the input field names from the form will be the same as the database column names.
• Although ODBiC will expect to find variables for each of the fields, do not put the $ prefixes and suffixes on the variable names in the UPDATEFORM list.

You can control the size of each update <INPUT> field by using the optional ":size" specification after the field name in the UPDATEFORM list. If no size is specified, ODBiC uses a default size of 50 for text fields and 12 for numeric fields. If you give a field size larger than 99, then ODBiC automatically uses an HTML <TEXTAREA> input, which is a multiple-line scrolling window. This window will be at most 64 characters wide and as many lines as it takes to hold your specified field size. (For example, a specified size of 250 would produce a 50-character, 5-line textarea window.) However, you can directly specify the size of a <TEXTAREA> by giving the "size" specification as two numbers separated by an "x" (for example, 'Description:64x4'), where the two numbers are to be the number of columns and the number of rows. (The numbers can be given in either order; the larger number will always be used as the field width and the smaller number will be the number of lines.)

NOTE: If you use an UPDATEFORM command after a SELECT statement that returns several result rows, you will automatically get a separate update form for each result row. Each of these forms will have its own SQL UPDATE statement and "submit" button, but the user will only be able to update one row at a time. (The user could, however, use the browser "Back" button to go back and update another row.)

Since the UPDATEFORM command always loops through all of the result rows, you should not use UPDATEFORM combined with any of the other result looping commands, EACHROW, TABLE, and OPTIONLIST.

Example:

    <!--UPDATEFORM FILE=/htmlroot/your_dir/upd_empl.html, TABLE=Employees, 
       KEY=EmployeeID, 'LastName:20', 'FirstName:10', 'Title:30', 'BirthDate:9',
       'HireDate:9', 'Address:60', 'City:15', 'Region:15', ReportsTo:8 -->

The math functions len ( ), pos ( ), and char ( ) are designed to be used with the string functions to get the length of a string, find the position of a substring within a string, or to find the first occurrence of any one of a set of characters within a string, respectively. The functions isNumber ( ), isAlpha ( ), and isAlphaNum ( ) are intended to be used in IF statements to test string contents for being all numeric digits, all alphabetic characters, or all alphanumeric characters, respectively.

$asc (number)

$eval (expression)

$format (mask, expression)

$hex (string)

$left (string, count)

$mid (string, start [,count])

$right (string, count)

$url (string)

<A HREF="http://your.com/scripts/odbic.exe/template.htm?category=$url($category$)&uid=$url($uid$)">

abs (number)

char (string, characters)

cos (angle)

exp (number)

isAlpha (string)

isAlphaNum (string)

isNumber (string)

len (string)

log (number)

log10 (number)

max (number1, number2)

min (number1, number2)

mod (number, mod)

pos (string1, string2)

rand (mod)

sin (angle)

tan (angle)

    <FORM METHOD="post" ACTION="http://www.yourdomain.com/scripts/odbic.exe">

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.

    \httpfile\scripts\odbic -i \httpfile\template.html -o \httpfile\pagex.html 

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

The minimum required variables are "database" (which defines the ODBC connection) and "sql" (which defines an SQL statement to execute). If you use only these variables, then ODBiC will execute the SQL and format the results using some defaults. The default formatting will include a page "header" that shows the variable-expanded SQL statement that was executed. If the SQL statement is a SELECT, then all the results will be displayed using a default TABLE command. For INSERT, UPDATE, and DELETE statements, the number of rows affected will be displayed. If there was an SQL execution error, 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. (For security reasons, you may not use the EXEC command in the "eachrow" variable.)

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.

You may register online and pay by credit card using a secure server. With this server, your ordering information and credit card number are encrypted during transit through the Internet. Visa, Master Card, and American Express cards are accepted. You may link to the online ordering form from the ODBiC Home Page, or you may go directly to the ordering form at this link.

If you prefer, you my register by mail with a US bank check or money order. There is a text file in the download Zip file named "register.txt". Please print out that form, fill in the information, and mail it along with your registration fee to the address shown on the form. Thank you!