HP 3000 Manuals HP 3000 Manuals
General Rules for Embedding SQL
Embedded SQL commands must appear in certain locations within the COBOL
program. Each embedded SQL command must be accompanied by a prefix and a
suffix and followed by punctuation appropriate to the location of the
command in the program. Comments may be placed within an embedded
command, and non-numeric literals in embedded commands may be continued
from one line to another.
Location of SQL Commands
Put SQL commands, including their prefix and suffix, within columns 8
through 72 of either the DATA DIVISION or the PROCEDURE DIVISION:
* BEGIN DECLARE SECTION and END DECLARE SECTION can appear in the
FILE SECTION, the WORKING-STORAGE SECTION, or the LINKAGE SECTION
of the DATA DIVISION.
* INCLUDE SQLCA must appear in the WORKING-STORAGE SECTION of the
DATA DIVISION.
* All other SQL commands must appear in the PROCEDURE DIVISION.
Prefix and Suffix
Precede each SQL command with the prefix EXEC SQL and terminate each SQL
command with the suffix END-EXEC. The complete prefix or suffix must be
specified on one line. For example, the following are legal:
EXEC SQL SELECT PARTNAME INTO :PARTNAME
FROM PURCHDB.PARTS WHERE PARTNUMBER = :PARTNUMBER END-EXEC.
EXEC SQL SELECT PARTNAME
INTO :PARTNAME
FROM PURCHDB.PARTS
WHERE PARTNUMBER = :PARTNUMBER
END-EXEC.
However, the following is not legal:
EXEC
SQL SELECT PARTNAME INTO :PARTNAME
FROM PURCHDB.PARTS WHERE PARTNUMBER = :PARTNUMBER END-EXEC.
Punctuation
The punctuation you use to terminate an embedded SQL command depends on
its location in the program. In the DATA DIVISION, always terminate the
SQL command with a period:
EXEC SQL INCLUDE SQLCA END-EXEC.
In the PROCEDURE DIVISION, terminate the SQL command with a period if it
constitutes an entire COBOL sentence:
EXEC SQL CONNECT TO 'PARTSDBE.SOMEGRP.SOMEACCT' END-EXEC.
Also use a period to terminate the SQL command if it is the final
statement in a COBOL sentence; if, however, other statements appear after
the SQL command in a COBOL sentence, use COBOL rules to determine
appropriate punctuation:
IF RESPONSE-PREFIX = "1"
EXEC SQL SELECT * FROM PURCHDB.PARTS
INTO :MYARRAY
FROM PURCHDB.PARTS END-EXEC
PERFORM DISPLAY-SELECT-ALL
ELSE
IF RESPONSE-PREFIX = "2"
EXEC SQL DELETE FROM PURCHDB.PARTS END-EXEC
PERFORM DISPLAY-DELETE-ALL
ELSE
EXEC SQL RELEASE END-EXEC.
COBOL Comments
You may insert comment lines within or between embedded SQL commands.
Denote comment lines by placing an asterisk (*) in column 7 and entering
the comment in columns 8 through 72:
EXEC SQL SELECT PARTNUMBER, PARTNAME
*put the data into the following host variables
INTO :PARTNUMBER, :PARTNAME
*find the data in the following table
FROM PURCHDB.PARTS
*retrieve only data that satisfies this search condition
WHERE PARTNUMBER = :PARTNUMBER
END-EXEC.
ALLBASE/SQL Comments
ALLBASE/SQL comments can be inserted in any line of an SQL statement,
except the last line, by prefixing the comment character with at least
one space followed by two hyphens followed by one space:
EXEC SQL SELECT * FROM PurchDB.Parts -- This code selects Parts Table values.
WHERE SalesPrice > 500.
END-EXEC.
The comment terminates at the end of the current line. (The decimal
point in the 500 improves performance when being compared to SalesPrice,
which also has a decimal; no data type conversion is necessary.)
Continuation Lines
The COBOL preprocessor allows you to continue a non-numeric literal from
one line to the next. Continue the literal through column 72, enter a
hyphen (-) in column 7 of the continuation line, and enter a quotation
mark (!) in column 12 immediately before the continuation of the
literal:
IF PREDEFINED-COMMENT = '5'
EXEC SQL INSERT INTO PURCHDB.VENDORS
(VENDORREMARKS)
VALUES ("This vendor is bad news. Definitely place no
- "no orders.")
END-EXEC
ELSE
EXEC SQL INSERT INTO PURCHDB.VENDORS
(VENDORREMARKS)
VALUES (:VENDORREMARKS)
END-EXEC.