Coding Requirements for Embedded SQL Programs
The following sections describe coding requirements for writing Embedded SQL programs.
Comments Embedded in COBOL Output
Each embedded SQL statement generates one comment and a few lines of COBOL code. You may find that the preprocessor translates 50 lines of embedded SQL into 200 lines of COBOL. This may result in confusion about the line numbers when you try to debug the original source code. To facilitate debugging, each group of COBOL statements associated with a particular statement is delimited by a comment corresponding to the original embedded SQL source. Each comment is one line long and describes the file name, line number and type of statement in the original source file.
Embedded SQL Statements in IF and PERFORM Blocks
The preprocessor may produce several COBOL statements for a single embedded SQL statement. In most circumstances, the statements can be simply nested in the scope of a COBOL IF or PERFORM statement.
There are some embedded SQL statements for which the preprocessor generates COBOL paragraphs and paragraph names. These statements are:
select-loop
display
formdata
unloadtable
submenu
These statements cannot be nested in the scope of a COBOL IF or PERFORM statement because of the paragraph names the preprocessor generates for them.
These statements must not contain labels.
Another consequence of these generated paragraphs is that they may terminate the scope of a local COBOL paragraph, thus modifying the intended flow of control. For example, a paragraph generated by the preprocessor in a source paragraph may cause the program to return prematurely to the statement following the PERFORM statement that called the source paragraph. To ensure that control does not return prematurely, you must use the THROUGH clause in the PERFORM statement.
The following example demonstrates the use of PERFORM‑THROUGH and an EXIT paragraph to force correct control flow.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ENAME PIC X(20).
EXEC SQL END DECLARE SECTION END-EXEC.
* Include SQLCA, declare program variables, etc.
PROCEDURE DIVISION.
BEGIN.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ENAME PIC X(20).
EXEC SQL END DECLARE SECTION END-EXEC.
* Include SQLCA, declare program variables, etc.
PROCEDURE DIVISION.
BEGIN.
* Initialization of program
* Note the THROUGH clause to ensure correct
* control flow.
PERFORM UNLOAD-TAB THROUGH END-UNLOAD.
* User code
UNLOAD-TAB.
* Note the THROUGH clause to ensure correct
* control flow.
PERFORM UNLOAD-TAB THROUGH END-UNLOAD.
* User code
UNLOAD-TAB.
* This paragraph includes a paragraph generated
* by the preprocessor
EXEC FRS UNLOADTABLE Empform Employee
(:ENAME = Lastname) END-EXEC.
EXEC FRS BEGIN END-EXEC.
EXEC SQL INSERT into person (name)
VALUES (:ENAME)
END-EXEC.
EXEC FRS END END-EXEC.
* This paragraph-name and EXIT statement cause
* control to pass back to the caller's scope
END-UNLOAD.
EXIT.
USER-PARAGRAPH.
* Program continues
* by the preprocessor
EXEC FRS UNLOADTABLE Empform Employee
(:ENAME = Lastname) END-EXEC.
EXEC FRS BEGIN END-EXEC.
EXEC SQL INSERT into person (name)
VALUES (:ENAME)
END-EXEC.
EXEC FRS END END-EXEC.
* This paragraph-name and EXIT statement cause
* control to pass back to the caller's scope
END-UNLOAD.
EXIT.
USER-PARAGRAPH.
* Program continues
COBOL Periods and Embedded SQL Statements
You can place a period following the END-EXEC statement terminator (for more information, see Embedded SQL Statement Syntax for COBOL in this chapter), although the preprocessor does not require this. If you do include a period at the end of an embedded SQL statement, the preprocessor places a period at the end of the last COBOL statement generated by that embedded SQL statement. Therefore, when you include periods in embedded SQL statements, be careful to follow the same guidelines that you use for placing periods in COBOL statements. For example, do not add a period at the end of an embedded SQL statement occurring in the middle of the scope of a COBOL IF or PERFORM statement. If you include the separator period in such a case, you will prematurely end the scope of the COBOL statement.
Similarly, when an embedded SQL statement is the last statement in the scope of a COBOL IF, you must follow it with a period (or, alternatively, an END-IF) to terminate the scope of the IF. For example:
IF ERR-NO > 0 THEN
* Do not use a separating period in the middle
* of an IF statement.
EXEC FRS MESSAGE 'You cannot update the database'
END-EXEC
* Be sure to use a separating period at the
* end of an IF statement.
EXEC FRS SLEEP 2 END-EXEC.
* Do not use a separating period in the middle
* of an IF statement.
EXEC FRS MESSAGE 'You cannot update the database'
END-EXEC
* Be sure to use a separating period at the
* end of an IF statement.
EXEC FRS SLEEP 2 END-EXEC.
In the example above, the absence of the period after the first end‑exec causes the preprocessor to generate code without the separator period, thus preserving the scope of the IF statement. The period following the second end‑exec causes the preprocessor to generate code with a final separator period, terminating the scope of the IF.
The embedded SQL preprocessor always generates necessary separator periods when translating embedded SQL block structured statements, such as a select or unloadtable loop, into COBOL paragraphs. The end‑exec statement terminator associated with these statements and with their begin clauses cannot be followed by a period. A period will cause a preprocessor syntax error on the subsequent components of the block structured statement.
In a display loop, periods are allowed in the statement blocks of initialize and activate statements and following the finalize statement.
The following example shows the use of the period in block‑structured statements and display loops.
EXEC FRS FORMS END-EXEC. -- Period allowed
EXEC FRS DISPLAY empform END-EXEC -- No period
EXEC FRS INITIALIZE END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements -- Periods allowed
EXEC SQL SELECT * INTO :emp_rec
FROM employee END-EXEC -- No period
EXEC SQL BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
EXEC SQL END END-EXEC. -- Period allowed
EXEC FRS DISPLAY empform END-EXEC -- No period
EXEC FRS INITIALIZE END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements -- Periods allowed
EXEC SQL SELECT * INTO :emp_rec
FROM employee END-EXEC -- No period
EXEC SQL BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
EXEC SQL END END-EXEC. -- Period allowed
EXEC FRS END END-EXEC -- No period
EXEC FRS ACTIVATE FIELD emp_name END-EXE -- No period
EXEC FRS BEGIN END-EXEC -- No period
EXEC FRS SUBMENU END-EXEC -- No period
EXEC FRS ACTIVATE frskey3 END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
IF condition THEN
ESQL, COBOL statements -- No periods
ELSE
ESQL, COBOL statements -- No periods
END-IF. -- Period optional
(COBOL rules)
EXEC FRS ACTIVATE FIELD emp_name END-EXE -- No period
EXEC FRS BEGIN END-EXEC -- No period
EXEC FRS SUBMENU END-EXEC -- No period
EXEC FRS ACTIVATE frskey3 END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
IF condition THEN
ESQL, COBOL statements -- No periods
ELSE
ESQL, COBOL statements -- No periods
END-IF. -- Period optional
(COBOL rules)
PERFORM UNTIL condition
ESQL, COBOL statements -- No periods
END-PERFORM. -- Period optional
(COBOL rules)
ESQL, COBOL statements -- No periods
END-PERFORM. -- Period optional
(COBOL rules)
EXEC FRS END END-EXEC -- No period
EXEC FRS END END-EXEC -- No period
EXEC FRS ACTIVATE MENUITEM 'Save' END-EXE-- No period
EXEC FRS BEGIN END-EXEC -- No period
EXEC FRS UNLOADTABLE empform employee END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
EXEC FRS END END-EXEC. -- Period allowed
EXEC FRS END END-EXEC -- No period
EXEC FRS FINALIZE END-EXEC. -- Period allowed
EXEC FRS END END-EXEC -- No period
EXEC FRS ACTIVATE MENUITEM 'Save' END-EXE-- No period
EXEC FRS BEGIN END-EXEC -- No period
EXEC FRS UNLOADTABLE empform employee END-EXEC -- No period
EXEC FRS BEGIN END-EXEC -- No period
ESQL, COBOL statements. -- Periods allowed
EXEC FRS END END-EXEC. -- Period allowed
EXEC FRS END END-EXEC -- No period
EXEC FRS FINALIZE END-EXEC. -- Period allowed
A period after the END-EXEC terminator of any of the following statements will cause a preprocessor error:
display
initialize
activate
submenu
formdata
unloadtable
end, (except when used as the final statement of display, unloadtable, formdata, and select loops)
select, when opening a select loop
For more information on COBOL paragraphs and embedded SQL structured statements, see the preceding section.
Embedded SQL Statements That Do Not Generate Code
The following embedded SQL declarative statements do not generate any COBOL code:
declare cursor
declare statement
declare table
whenever
Do not code these statements as the only statements in COBOL constructs that do not allow null statements. Also, these statements must not contain labels. For example, coding a declare cursor statement as the only statement in a COBOL IF statement causes compiler errors:
IF USING-DATABASE=1 THEN
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT ename FROM employee END-EXEC
ELSE
DISPLAY "You have not accessed the database".
The code generated by the preprocessor is:
IF USING-DATABASE=1 THEN
ELSE
DISPLAY "You have not accessed the database".
EXEC SQL DECLARE empcsr CURSOR FOR
SELECT ename FROM employee END-EXEC
ELSE
DISPLAY "You have not accessed the database".
The code generated by the preprocessor is:
IF USING-DATABASE=1 THEN
ELSE
DISPLAY "You have not accessed the database".
This is an illegal use of the COBOL ELSE clause.
Also, do not precede these statements (declare cursor, declare statement, declare table, and whenever) with a COBOL paragraph label (on the same line) if that label is referenced elsewhere in your program.
Efficient Code Generation
This section describes the COBOL code generated by the embedded SQL preprocessor.
COBOL Strings and Embedded SQL Strings
COBOL stores string and character data in a machine‑dependent data item (UNIX and Windows) or descriptor (VMS). The embedded SQL runtime routines are written in another language (C) that verifies lengths of strings by the location of a null (LOW‑VALUE) byte. Consequently, COBOL strings must be converted to embedded SQL runtime strings before the call to the runtime routine is made.
In some languages, embedded SQL generates a nested function call that accepts as its argument the character data item (UNIX and Windows) or VAX string descriptor (VMS) and returns the address of the embedded SQL null‑terminated string. COBOL does not have nested function calls, and simulating this would require two expensive COBOL statements. Embedded SQL/COBOL knows the context of the statement, and in most cases will MOVE the COBOL string constant or data item in a known area that has already been null‑terminated. This extra statement is cheaper than the nested function call of other languages, as it generates a single machine instruction. Even though your COBOL‑generated code may look wordier and longer than other embedded SQL‑generated code, it is actually as efficient.
COBOL IF-THEN-ELSE Blocks
There are some statements that normally generate an IF‑THEN-ELSE construct in other languages that instead generate IF‑GOTO constructs in COBOL. The reason for this is that there is no way to ensure that no embedded SQL‑generated (or programmer‑generated) period will appear in an IF block. Consequently, in order to allow any statement in this scope, embedded SQL generates an IF‑GOTO construct.
The code generated by embedded SQL for this construct is actually very similar to the code generated by any compiler for an IF‑THEN‑ELSE construct and is no less efficient.
COBOL Function Calls
COBOL supports function calls with the USING clause (UNIX) or the GIVING clause (VMS). This allows a function to return a value into a declared data item. Embedded SQL generates many of these statements by assigning the return values into internally declared data items, and then checking the result of the function by checking the value of the data item. This is obviously less efficient than other languages that check the return value of a function by means of its implicit value (stored in a register).
COBOL has the overhead of assigning the value to a variable. An embedded SQL/COBOL generated function call that tests the result may look like:
Windows and UNIX:
CALL "IIFUNC" USING IIRESULT
IF (IIRESULT = 0) THEN ... 
VMS:
CALL "IIFUNC" GIVING IIRESULT
IF (IIRESULT = 0) THEN ... 
Last modified date: 08/28/2024