|
VADELIA |
SADELIA |
WADELIA |
EADELIA |
(I/B) (S) |
(B) (S) |
(I/B) (S) |
(B) (S) |
Section for use
All
Syntax
EXEC_SQL (SQLQuery)
EXEC_SQL (DynQuery)
DynQuery |
→ |
:QueryVar | :SQLStatement |
Description
This instruction executes an SQL query SQLQuery (written using SQL syntax).
Host variables can be used by placing a " : " in front of the variable's guide word.
The DynQuery parameter is either an alphanumeric variable or an SQL statement previously declared via the SQL_STATEMENT instruction, that contains a dynamically executable, non-selection SQL query; see the corresponding SQL manual.
Where the query concerns a PC-based SQL database, the array name has to be qualified by its framework. If you enter $$. in front of the array name instead of the framework, Adelia will not qualify accesses to the AS/400 SQL tables. Instead it will replace the double dollar symbol $$. with the framework defined by the environment attributes if the array corresponds to a public logical entity, or else by the framework defined by the logical level of the application area.
Warning: When writing SQL queries, you must use the real names of the columns and arrays, not their Adelia names (except for host variables, where the Adelia guide word is used).
With VADELIA and SADELIA programs, you can also check that the record is not locked, by testing it with the *LOCKED reserved word.
After this instruction has been run, it is possible to test the SQL return code with the *SQLCODE reserved word.
Example
EXEC_SQL (select EADRESS,ETOWN into :ZEMPLOYEE_ADR,:ZEMPLOYEE_TOWN from $$.EMPLOYEE where EEMPLOYEE_ID = :ZEMPLOYEE_ID)
EXEC_SQL (select MAX(SAMOUNT) into :MaxAmount from $$.INVOICES where ICUST_ID = :ZCUST_ID)
* This SQL query searches for a customer identified by his ID,
* the maximum amount among all his invoices.
Other examples:
WSQLVAR = 'DROP TABLE schema.mytable'
EXEC_SQL (:WSQLVAR)
** Define and read a cursor to get the turnover sum by city from customer file
EXEC_SQL (declare curs1 cursor for select cu_city, sum(cu_turnover) from $$.customer group by cu_city)
EXEC_SQL (open curs1)
*
EXEC_SQL (fetch curs1 into :CU_CITY, :WTOT_CITY)
do_while *sqlcode = *normal
...
EXEC_SQL (fetch curs1 into :CU_CITY, :WTOT_CITY)
redo
*
EXEC_SQL (close curs1)