Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
Labels must be assigned according to COBOL rules. These rules apply to labels:
(a) SQL reserved words
- Both uppercase and lowercase letters can be used
- Uppercase and lowercase letters can be mixed
(b) Host names
- Labels that begin with SQL cannot be used
- Spaces can be entered following a colon within a host name
- Host names are not case-sensitive
- Uppercase and lowercase letters can be mixed
- The corresponding double-byte and single-byte versions of letters, numeric characters, symbols, katakana characters, and the space character are treated as different characters.
Embedded variables, indicator variables, and branching destination labels must be named in accordance with the COBOL labeling rules. The following types of labels, which have the external attribute, cannot be used:
- Labels that begin with the uppercase SQL
- Labels that begin with the lowercase p_
- Labels that begin with the lowercase pd
- Each SQL statement must be preceded by the SQL prefix (EXEC SQL) and followed by the SQL terminator (END-EXEC).
Example:
EXEC SQL SQL-statement; END-EXEC.
- COBOL statements and SQL statements can both be specified on the same line.
- All SQL statements (from the SQL prefix through the SQL suffix) must be entered in the B area (columns 12 - 72)
![[Figure]](FIGURE/ZU070301.GIF)
- The SQL statement continuation rules are generally the same as the COBOL line continuation rules.
A line break can occur in an SQL description wherever a space must be specified or can be specified; a description can span multiple lines.
To break a line where a space cannot be specified in the SQL statement, a hyphen (-) must be specified in the indicator area; the description can resume on the next line in any column in the B area.
To break a line in the middle of a character string literal, the description must be specified through column 72, and a quotation mark (") must be specified anywhere in the B area on the next line. To continue the character string, first specify a quotation mark or an apostrophe (whichever was specified at the beginning of the character string), and resume the character string specification from the next column after that quotation mark or apostrophe.
- A paragraph header can be entered before the SQL prefix (but not on the same line as the SQL prefix).
- Valid specification:
FINISH.
EXEC SQL SQL-statement END-EXEC.
- Invalid specification:
FINISH.
EXEC SQL SQL-statement END-EXEC.
Bold letters indicate the invalid portion.
- One SQL statement is treated as one COBOL language instruction. Therefore, if an SQL statement is the last instruction of a concluding statement, a period and space must be specified following the SQL terminator.
- Example of when an SQL statement constitutes a concluding statement:
EXEC SQL
SQL-statement
END-EXEC.
- Example of when an SQL statement is the last instruction of a concluding statement:
IF U-FLUX = '2'
THEN
EXEC SQL SQL-statement END-EXEC.
- Example of when an SQL is an instruction in the middle of a concluding statement:
IF U-FLUX = '1'
THEN
EXEC SQL SQL-statement
END-EXEC
ELSE IF U-FLUX = '2'
THEN NEXT SENTENCE.
- Although a comment cannot be specified within an SQL statement, any number comment lines can be specified between the SQL prefix and the SQL terminator.
- Example:
EXEC SQL
*Declaration of cursor for SELECT statement (1)
*that retrieves STOCK table (1)
SQL-statement
END-EXEC.
(1): Comment lines
- The following rules apply to declaring embedded variables.
- Specify the embedded SQL declare section in one of the following sections:
FILE SECTION of DATA DIVISION
WORKING-STORAGE SECTION
LOCAL-STORAGE SECTION
LINKAGE SECTION
- For details about embedded variables for SQL data types, see F. SQL Data Types and Data Descriptions.
- JUSTIFIED, BLANK, and the WHEN ZERO clause cannot be specified in the data description item of an embedded variable.
- Although a level 66 re-instruction item or level 88 conditional name item cannot be used as an embedded variable, such items can be defined in an embedded SQL declaration section.
- The COBOL line continuation rules apply to continuation of data description item lines of an embedded SQL declaration section.
- FILLER cannot be used as an embedded variable.
- A data item that uses the TYPE, TYPEDEF, or SAME AS clause can be used as an embedded variable.
- If you use the REDEFINES clause, the system does not check whether the item that performs the redefining and item to be redefined use the same column justification. The size of the larger area is used.
- A data item in which the PICTURE clause is omitted and only the VALUE clause is specified cannot be used as an embedded variable.
- When you use the -E option, you can use the declared data item as an embedded variable even if you do not use an embedded SQL declare section. However, the only data items that can be used as embedded variables in SQL statements are those that are declared with a format described in F. SQL Data Types and Data Descriptions. Data items that are declared with other formats cannot be used as embedded variables.
The effective scope of each data item name is determined by the COBOL syntax rules. The data items that can be used as embedded variables must be specified in the source program. Data items in library text that is included with the COPY or INCLUDE statement cannot be used as embedded variables.
- Data items that are inherited from a parent class by the class inheritance facility of COBOL2002 cannot be used as embedded variables.
- If you specify the -XU16 option when you execute the SQL preprocessor, UAPs that use the Unicode functionality of COBOL2002 can use Japanese data items for storing UTF-16 character data as embedded variables. You can specify an embedded variable using Japanese data items at any location in a SQL statement that allows an embedded variable for a mixed character string data type (MCHAR or MVARCHAR). The supported characters are limited to the range of characters supported by the Unicode functionality of COBOL2002.
For details about UAP execution using the Unicode functionality of COBOL2002, see 8.4.3 UAP execution using the Unicode functionality of COBOL2002.
- The following rules apply to declaring indicator variables.
- An indicator variable must be either a basic item between level 01 and level 49 or an independent item of level 77.
- For details about the data description terms for embedded variables, see F. SQL Data Types and Data Descriptions.
- The SIGN, JUSTIFIED, BLANK, and WHEN ZERO clauses cannot be specified in the data description item of an indicator variable.
- FILLER cannot be used as an indicator variable name.
- When you use the -E option, you can use the declared data item as an indicator variable even if you do not use an embedded SQL declare section. However, the only data items that can be used as indicator variables in SQL statements are those that are declared with a format described in F. SQL Data Types and Data Descriptions. Data items that are declared with other formats cannot be used as indicator variables.
The effective scope of each data item name is determined by the COBOL syntax rules. The data items that can be used as indicator variables must be specified in the source program. Data items in library text that is included with the COPY or INCLUDE statement cannot be used as indicator variables.
- Data items that are inherited from a parent class by the class inheritance facility of COBOL2002 cannot be used as indicator variables.
- The following table shows the divisions (DIVISION) in COBOL in which SQL statements can be described.
Table 7-3 Divisions in COBOL for describing SQL statements
SQL statement |
Data division# |
Procedure division |
Definition SQL |
-- |
D |
Data manipulation SQL |
-- |
D |
Control SQL |
-- |
D |
Embedded language |
BEGIN DECLARE SECTION |
D |
-- |
END DECLARE SECTION |
D |
-- |
COPY |
D |
D |
WHENEVER |
-- |
D |
DECLARE CONNECTION HANDLE UNSET |
-- |
-- |
COMMAND EXECUTE |
-- |
-- |
Other statement |
-- |
D |
- D: Can be described.
- --: Cannot be described.
- #: Indicates the working section, file section, or linkage section.
- Because the WHENEVER statement and cursor declaration are declaration statements, they cannot be specified within an IF or EVALUATE instruction.
- Do not specify a control word for compile list output (EJECT, SKIP1, SKIP2, SKIP3, or TITLE) in the SQL statements that are enclosed between the SQL prefix and SQL terminator. To use EJECT, SKIP1, SKIP2, SKIP3, or TITLE as a table name or column name, close the word in double quotation marks. However, if EJECT, SKIP1, SKIP2, SKIP3, or TITLE is contained in a word phrase, such as a table name or column name, enclosing the word in quotation marks is unnecessary.
- Comments (/*...*/) specified between the SQL prefix and the SQL terminator are deleted. However, SQL optimization specifications (/*>>...<<*/) are not deleted but instead treated as SQL statements. If a specified comment or SQL optimization specification extends over several lines, each line is assumed to start from the beginning of the B area until */ is specified. Do not use line continuation characters. For details about comments and SQL optimization specifications in SQL statements, see the manual HiRDB Version 9 SQL Reference.
- A note (*>) can be specified in a line. However, notes within lines cannot be used between the SQL prefix and the SQL terminator. If a note within a line is specified, the note is treated as a character string instead of a note.
- In lines specified in the embedded SQL declare section and SQL statements, the tab code is treated as being one character in length. If you use the -E2 or -E3 option, the tab code is treated as being one character in length in all data sections.
- When the object-oriented facility of COBOL2002 is used, the rules described in 7.5.1(2) SQL coding rules also apply.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.