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 (www.caliach.com)
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.