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