| Delayed Evaluation of SQL in SQR | KEVIN RESCHENBERG
04-12-2006 |
A customer writes to say that he's working on
a new SQR program that uses
a temporary table. The program creates the table,
uses it, and then drops it. But SQR reports an
error immediately when the program starts, because the
temporary table referenced in the program does not
exist yet. How can the customer get around
this issue? It's a common problem. Fortunately, the
solutions are easy and will save the customer from the
pain of, for example, converting the entire program to App Engine!
Some people get around this problem by making
their "temporary" tables more permanent. This can
be done by creating the table in advance using
Query Analyzer, Toad, or some other SQL tool.
Others prefer to define the table in Application
Designer. But for tables that really are for
temporary use, neither of these approaches is
necessary.
SQR scans a program and checks SQL before the
program is run. There are two exceptions to this.
First, any SQL located within a BEGIN-SETUP section
is executed before the program really starts—that
is, before the BEGIN-PROGRAM or BEGIN-REPORT
statement is executed. SETUP sections are run
at "compile" time. This means that we can do
a CREATE TABLE in the SETUP section and then
refer to that table throughout the program.
The other exception to the initial SQL scan occurs
when SQR can't determine the text of the SQL in
advance. This happens when we use dynamic SQL.
If SQL contains a string variable ($variable)
within square brackets, the value of the variable
becomes part of the SQL at run time:
let $TableName = 'MY_TEMP_TABLE'
begin-select
MY_FIELD
...
FROM [$TableName]
WHERE ...
end-select
In this example, since SQR doesn't know the value
of $TableName until run time, it can't send the
SQL off for a syntax check.
Even an empty variable can be used to delay the
syntax check:
let $Nothing = ' '
begin-select
MY_FIELD
...
FROM MY_TEMP_TABLE
WHERE ...
[$Nothing]
end-select
Don't force SQR to delay SQL processing unless you
need to do this. But where it's needed, this can be
a useful technique.
|
