|
|
|
|
|
|
Embedded SQL in COBOL Programs
Mimer SQL supports embedded SQL for COBOL following the COBOL-85 ANSI standard.
SQL Statement Format
The following sections discuss the SQL statement format.
Statement Delimiters
SQL statements are identified by the leading delimiter EXEC SQL and terminated by END-EXEC.
SQL statements are treated exactly as ordinary COBOL statements with regard to the use of an ending period to mark the end of a COBOL sentence. Any valid COBOL punctuation may be placed after the END-EXEC terminator.
Examples:
EXEC SQL DELETE FROM HOTEL END-EXEC. IF SQLSTATE NOT = "02000" THEN EXEC SQL COMMIT END-EXEC ELSE EXEC SQL ROLLBACK END-EXEC.Margins
Statements (including delimiters) may be written anywhere between positions 8 and 72 inclusive.
Line Continuation
Line continuation rules for SQL statements are the same as those for ordinary COBOL statements.
If a string constant within an SQL statement is divided over several lines, the first non-blank character on the continuation line must be a string delimiter. There is no terminating string delimiter at the end of the line preceding the continuation line.
Example:
EXEC SQL SELECT HOTELCODE, ROOMNO FROM BOOK_GUEST WHERE RESERVATION = :CUST-NUMBER END-EXEC. EXEC SQL COMMENT ON TABLE ROOM_PRICES IS 'Prices apply from date given - ' in column FROM_DATE' END-EXEC.An alternative way to break a character string constant over several lines, is to use a white-space character (ASCII HEX-values 09 - 0D, or 20, i.e. <TAB>, <LF>, <VT>, <FF>, <CR> or <SP>), to join two or more substrings. Each substring must be separately enclosed in delimiters.
EXEC SQL COMMENT ON TABLE ROOM_PRICES IS 'Prices apply from date given'<LF> ' in column FROM_DATE' END-EXEC.Comments
Comment lines, marked by an asterisk (*) in position 7, may be written within SQL statements. The whole line following a comment mark is treated as a comment.
Debugging lines and page eject lines (marked by D and / respectively in position 7) are treated as comments by the preprocessor.
Special Characters
The delimiters in SQL are apostrophes (') for string constants and quotation marks (") for delimited identifiers. This is contrary to the default COBOL string delimiter usage.
Observe that the minus sign (-) is valid in variable names in COBOL. All arithmetic expressions using this operator should have at least one space separating the operands from the operator. For example:
:A - B means "variable called A minus column B"
:A-B means "variable called A-B"Restrictions
The following restrictions apply specifically to COBOL:
- END-EXEC is a keyword reserved to SQL.
- COBOL figurative constants (such as ZERO and SPACE) may not be used as constants in SQL statements.
Host Variables
The following sections discuss declarations, SQL data type correspondence, preprocessor output format and value assignments.
Declarations
Host variables used in SQL statements must be declared within the SQL DECLARE SECTION, delimited by the statements BEGIN DECLARE SECTION and END DECLARE SECTION.
Variables declared within the SQL DECLARE SECTION must conform to the following rules in order to be recognized by the SQL preprocessor:
- variable names must begin with a letter. Within this restriction, any valid COBOL variable name may be used
- host variable structures may not be used
- the specifications JUSTIFIED, BLANK WITH ZERO, and OCCURS may not be used
- the data type must be consistent with SQL data types as specified below
- level number 01 or 77 should be used for all variable names which are used in SQL statements. Other levels may be used for program host variables, but they are not recognized by the preprocessor
- FILLER entries are ignored for variables used in SQL statements
- Indicator variables should be declared as PIC S9(4) COMP or PIC S9(9) COMP
A syntax diagram for COBOL variable declarations recognized by the ESQL/COBOL preprocessor is given below. Other declarations are ignored by the preprocessor:
Commas and semicolons may be used in accordance with standard COBOL practice.
The following abbreviations are accepted:
Abbreviation
for
Full term
Note: The PIC S9(n)9(m) formulation is not accepted.
SQL Data Type Correspondence
Valid host data types are listed below for each of the data types used in SQL statements.
Varying-length character string structures may be used in embedded SQL statements in COBOL programs. In assigning the value of such variables to columns, the current length of the string is used.
The variable name used in SQL statements is the name of the structure (level 01 declaration), not of the character string element (level 49).
Value Assignments
The general rules for conversion of values between compatible but different data types, see the Mimer SQL Reference Manual, apply to the transfer of data between the database and host variables, with the data type correspondence as given in the table above.
The first element in a varying-length character string structure is used to store the current length of the character string. When writing to the variable, the first element is updated with the current length of the variable. If the column value is longer than the variable, the value is truncated.
Preprocessor Output Format
Output from the ESQL/COBOL preprocessor retains SQL statements from the original source code as comments. Comments within SQL statements are retained exactly as written. The output follows the ANSI standard for record format, and should be compiled with a COBOL compiler set to accept ANSI standard.
Debugging lines and page eject lines (using D and / respectively in position 7) remain unchanged after preprocessing.
The preprocessed code is structured to reflect the structuring of the original source code.
Scope Rules
Host variables follows the same scope rules as ordinary variables in COBOL. SQL descriptor names, cursor names and statement names must be unique within the compilation unit. A compilation unit for COBOL is the same as a routine.
 Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se |
|
|
|
|
|
|
