HP 3000 Manuals HP 3000 Manuals
SQL in COBOL Programs
This section contains details only of COBOL specific features. For
details on the SQL syntax and system specifications of your SQL system,
see the documentation supplied by the database vendor.
The EXEC SQL Statement
SQL statements are embedded in COBOL using the EXEC SQL and END-EXEC
delimiters, in the form:
exec sql SQL-statement end-exec
The EXEC SQL statement can be broken over as many lines as necessary,
following the normal COBOL rules for continuation. Between the EXEC SQL
and END-EXEC statements, you can only code SQL statements. You must not
include any COBOL statements (EXEC SQL and END-EXEC are COBOL
statements).
Example.
The following is an example of a complete COBOL program with embedded SQL
commands:
working-storage section.
exec sql begin declare section end-exec
* Examples of host variables declared in a
* DECLARE section
01 filler.
03 cust-no pic 9(4) packed-decimal.
03 cust pic X(20).
exec sql end declare section end-exec
* Example of host variable not declared in a
* DECLARE section
01 house-no pic 9(4) comp.
exec sql include sqlca end-exec
procedure division.
exec sql declare cust cursor for
select custno, custname, hseno
from region where postdept = 'rg1'
end-exec
exec sql open cust end-exec
perform until sqlcode not = 0
exec sql
fetch cust into :cust-no, :cust, :house-no
end-exec
display "customer-no: " cust-no
" customer-name: " cust
" house-no: " house-no
end-perform
exec sql close cust end-exec
stop run.
Host Variables
Host variables are data items defined in the COBOL program and used for
communicating values to and from the SQL system. They are defined using
ordinary COBOL syntax and can be in the File Section, Working-Storage
Section, Local-Storage Section or Linkage Section. They can have any
level number from 1 to 48 (level 49 is reserved for VARCHAR elements).
The Embedded SQL support specifies that integers have the format of USAGE
COMP-5. A Micro Focus extension allows USAGE COMP, COMP-4 and BINARY as
described in the later section Extensions to Embedded SQL Support.
Microsoft SQL Server limits the length of the name of a host variable to
29 characters.
Within an EXEC SQL statement, host variables must be prefaced with a
colon (:) to identify them to the Compiler.
The ANSI definition of SQL requires that the declaration of host
variables must be preceded by the statement:
exec sql begin declare section end-exec
and followed by:
exec sql end declare section end-exec
Permissible database data-types and their corresponding COBOL definitions
are described in the following sections.
Small Integer.
A small integer (SMALLINT or int2) is a two-byte binary item, which can
be represented in COBOL with USAGE BINARY, COMP, COMP-4, or COMP-5. If a
value larger than 32,767 is defined as unsigned, your database system
treats it as negative.
Examples.
01 shortint1 pic s9(4) comp.
. . .
01 . . .
03 shortint2 pic s9(4) comp-5.
. . .
03 shortint3 pic x(2) comp-5.
. . .
03 shortint4 pic s9(4) comp-4.
Large Integer.
A large integer (INTEGER or int4) is like a small integer except that it
is a four-byte item. If a value larger than 2,147,483,647 is unsigned,
your database system treats it as negative.
Examples.
01 longint1 pic s9(9) comp.
. . .
01 . . .
03 longint2 pic s9(9) comp-5.
. . .
03 longint3 pic x(4) comp-5.
Varying Length Character Strings.
Varying length character strings (VARCHAR and LONG VARCHAR) are group
items containing just two elementary items, which must both have a level
number of 49. The first of these, which is a small integer, is used to
hold the effective length of the item. The second is PIC X(n), where n
is any integer up to 32,700, and holds the actual data. The item is
classified as VARCHAR if n is less than or equal to 4,000: otherwise, it
is classified as LONG VARCHAR.
Example.
01 varchar1.
49 varchar1-len pic 9(4) comp-5.
49 varchar1-data pic x(100).
. . .
01 . . .
03 longvarchar1.
49 longvarchar1-len pic 9(4) comp.
49 longvarchar1-data pic x(20000).
Fixed Length Character Strings.
A fixed length character string (CHAR) is an alphanumeric item having a
maximum length of 254 characters.
Example.
01 char-field1 pic x(3).
. . .
01 . . .
03 char-field2 pic x(254).
Decimal.
The DECIMAL data type describes a packed-decimal item, with or without a
decimal point. In COBOL such items can be declared either as COMP-3 or
as PACKED-DECIMAL.
Example.
01 packed1 pic s9(8)v99 usage comp-3.
. . .
01 . . .
48 packed2 pic s9(3) usage packed-decimal.
Float.
The FLOAT data type describes a double-precision floating-point item. In
COBOL such items are declared as COMP-2.
Example.
01 float2 usage comp-2.