Omnis Technical Note TNSQ0001

Using Bind Variables With SQL Classes

For Studio version 1.2 and later
By Bob Mitchell.    Revised by Chris Ross, March 2013    (

The Problem

How to use bind variables to parameterize the SQL generated by the query, schema and table classes.

The Solution

When you execute one of the default table instance methods, such as $select() or $insert(), OMNIS dynamically generates a SQL statement , and then prior to executing it, OMNIS “tokenizes” the statement. In particular, this means that OMNIS searches the statement for bind variable references in the form @[variable]. When OMNIS tokenizes the statement, it uses a scope in which to look up the variables. In the case of a table instance, the scope is that of the table instance itself.

In the case where the list associated with the table instance is defined directly from a schema or query class, the scope allows you to use:

@[#...] - a hash variable, for example @[#S1], or @[#10]. Some exceptions apply to this general rule. Do not use #D, #T, #CT which are technically "hash macros" that cannot be evaluated at bind-time. To bind the current date use a variable in scope: Calcualte lvCurrentDate as #D, then use @[lCurrentDate].

@[taskvar] - a task variable, provided both the calling task and the task which defined the list are the same. Unfortunately, there is a bug which prevents task variables working in 1.2, but they do work in 2.0.

@[$cinst.column] - for a row defined from a SQL class, one of the columns in the row.

@[$cinst.N.column] - for a list defined from a SQL class, one of the columns in the list, in row N, where N is a literal number. For example, @[$cinst.5.column] will bind the column value from line 5. Runtime evaluation of bind variables cannot process indirection (square-bracket notation). So @[$cinst.[lvRowNo].column] can not be used. Calculate $cinst.$line as lvRowNo followed by @[$cinst.column] will work. The following is an example of using a bind variable in a process to obtain data related to Col3 in a list:

Begin Statement
Sta: Select * from Table
Sta: Where Col = @[iList.Col3]
End Statement
Do lStatementObj.$prepare() Returns lStatus
For iList.$line from 1 to iList.$linecount
    Do lStatementObj.$execute() return lStatus
    Do lStatementObj.$fetch(iRow)
End for

In the case where the list associated with the table instance is defined via a table class, you can also use:

@[$cinst.iVar] where iVar is an instance variable in the table class, which you could set with your own custom method e.g. MyList.$setbind('var value') before calling MyList.$select(), MyList.$insert() etc. To do this create a custom method in the Table class named $setbind with a parameter pVar and a single line of Calculate iVar as pVar. Then from outside the table class you could have code like this:

Calculate lFruit as 'Oranges'
Do lList.$definefromsqlclass('MyTable')
Do lList.$setbind(lFruit)
Do lList.$sessionobject.$assign(iSessionObj)
Do lList.$select('WHERE Fruit=@[$cinst.iVar]')
Do lList.$fetch(kFetchAll)

This allows you to use variables that would not otherwise be in scope within the table class.