|
VADELIA |
SADELIA |
WADELIA |
MADELIA |
|
(I/B) (S) |
(B) (S) |
(I/B) (S) |
(I) (S) |
Section for use
All
Syntax
EXEC_SQL (SQLRequest)
EXEC_SQL (:QueryVar)
Description
This instruction executes an SQL request SQLRequest (written using SQL syntax).
Host variables can be used by placing a " : " in front of the variable's guide word.
The QueryVar parameter is an alphanumeric variable that contains a dynamically executable, non-selection SQL statement; see the corresponding SQL manual.
Where the request 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 requests, 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 request 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)