April 12, 2006

Delayed Evaluation of SQL in SQR

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.

Until next time...