Integrating SQL Instruction Blocks

Blocks of SQL instructions can be placed in either interactive or batch models.

Each block of instructions is delimited by Adelia beginning and end instructions (BEGIN_SQL and END_SQL). Between these two instructions, the lines corresponding to only one SQL instruction are specified, and always begin with the character +.

The Adelia instructions are:

BEGIN_SQL	: Beginning of an SQL instruction block
+SQL query
+SQL query	: SQL query complying with the SQL syntax
+SQL query
END_SQL	: End of an SQL instruction block

For example

BEGIN_SQL

+DECLARE EMPLOYEE CURSOR FOR

+SELECT PCOST, PCOMAT, PMTSAL

+FROM PERSON

+WHERE PSERCO = :WSERCO

END_SQL

Each line of the SQL query begins with the character +.

SQL language syntax is not verified by Adelia and must therefore comply with SQL standards.

Use of the query results is then controlled by the model being designed.

For example

A batch program which calculates salary increases, using the personnel file (EMPLOYEE).

RECEIVE WSERCO PIWPCE	: initializes program
WINCPE = PINCPE	:
*	:
BEGIN_SQL	: SQL read
+DECLARE EMPLOYEE CURSOR FOR	: declares job file called
+SELECT PCOMCO, PEMPCO, PSALAM	: EMPLOYEE, selection of
+FROM PERSON	: PCOMCO, PEMPCO, PSALAM
+WHERE PSERCO = :WSERCO	: fields in PERSON file
END_SQL	: when PSERCO is same as
*	: WSERCO parameter (:WSERCO
*	: is called the host variable
*	: in SQL)
BEGIN_SQL	:
+OPEN EMPLOYEE	: opens EMPLOYEE file
END_SQL	:
*	:
BEGIN_SQL	:
+FETCH EMPLOYEE	: starts reading loop and loads
+INTO :ZCOMCO, :ZEMPCO, :ZSALAM	: host variables (model
END_SQL	: variables)
*	: ADELIA verifies that host
*	: variables exist
*	:
DO_WHILE *SQLCODE <> 100	: *SQLCODE is a work code
*	: generated by SQL. The value
*	: 100 means end of reading.
ZSALAM = ZSALAM + ZSALAM*(WINCPE/100)
BEGIN_SQL	: SQL salary update using host
+UPDATE PERSON	: variable ZSALAM when
+SET PSALAM = :ZSALAM	: PERSON key corresponds to
+WHERE CURRENT OF EMPLOYEE	: ZCOMCO and ZEMPCO fields of
END_SQL	: EMPLOYEE cursor file.
*	:
*	:
BEGIN_SQL	:
+FETCH EMPLOYEE	: re-reads
+INTO :ZCOMCO, :ZEMPCO, :ZSALAM	:
END_SQL	:
*	:
REDO	:
*	:
BEGIN_SQL	:
+CLOSE EMPLOYEE	: closes EMPLOYEE file.
END_SQL	:

Control of this model is entirely done by SQL.

Note that the algorithmic structure is given by the job analysis to be performed.

Each SQL instruction block is delimited by BEGIN_SQL and END_SQL.

↑ Top of page