Using program variables in static SQL

Static SQL syntax supports the usage of program variables as SQL parameters.

Using program variables directly in static SQL statements gives a better understanding of the source code and requires less lines as when using SQL parameters in dynamic SQL statements.

MAIN
  DEFINE c_num INTEGER
  DEFINE c_name CHAR(10)
  DATABASE stock 
  SELECT cust_name INTO c_name FROM customer WHERE cust_num = c_num
END MAIN

If a database table name or column name conflicts with a program variable, you can use the @ sign as the column prefix. The compiler will treat the identifier following the @ as an SQL object name:

MAIN
  DEFINE cust_name CHAR(10)
  DEFINE cnt INTEGER
  DATABASE stock 
  SELECT COUNT(*) INTO cnt FROM customer WHERE @cust_name = cust_name 
END MAIN

The @ sign will not figure in the resulting SQL statement stored in the .42m compiled module.

A database object name may also conflict with another symbol of the program code, such as a module name. In this case, you can also use the @ sign before the conflicting database object name to solve the issue.

In the next example, the imported module name conflicts with a database table name "account":

IMPORT FGL account
MAIN
    DEFINE rec RECORD
               pkey INTEGER,
               name VARCHAR(50)
           END RECORD
    SELECT * INTO rec.* FROM account
      WHERE @account.pkey = rec.pkey
END MAIN

Without the @ sign, the compiler would produce the following error, expecting that account.pkey is a public variable of the imported account module:

IMPORT FGL account
MAIN
    DEFINE rec RECORD
               pkey INTEGER,
               name VARCHAR(50)
           END RECORD
    SELECT * INTO rec.* FROM account
      WHERE account.pkey = rec.pkey
| The symbol 'pkey' does not represent a defined variable.
| See error number -4369.
END MAIN