HP 3000 Manuals HP 3000 Manuals
Extensions to Embedded SQL Support
This section discusses Micro Focus extensions to the Embedded SQL
support.
The INCLUDE Statement
Statements of the form:
exec sql include file-name end-exec
are permitted and are processed in exactly the same way as the statement:
copy filename.
The file named by filename can contain any COBOL statements that a
copyfile can, including further EXEC SQL statements.
AIX On AIX, the filename is converted to lower case for
the special case of sqlca, no matter how it is
specified
The DECLARE TABLE Statement
Statements of the form:
exec sql DECLARE table-name TABLE .... end-exec
are permitted and are treated as comments.
Integer Host Variables
The Embedded SQL support requires the format of integers to be USAGE
COMP-5. For your convenience, the compiler also allows host variables to
use USAGE COMP, COMP-4 and BINARY and will generate additional code to
convert the format. The most efficient code will be generated if COMP-5
is used.
Undeclared Host Variables
You can use as host variables data items that had not previously been
declared using:
exec sql begin declare section end-exec
and:
exec sql end declare section end-exec
in your program. The Compiler automatically declares host variables to
the database system as required.
You must specify the SQLDB2 Compiler directive in order to use this
facility. See the appendix Directives for Compiler in your COBOL System
Reference for details on SQLDB2.
Qualified Host Variables
Host variables can be qualified using DB2 compatible syntax.
Example.
Suppose you have defined some host variables as follows:
01 block-1.
03 hostvar pic s9(4) comp-5.
01 block-2.
03 hostvar pic s9(4) comp-5.
You can qualify which instance of hostvar to use with syntax of the form:
exec sql fetch s2 into :block-1.hostvar end-exec
Host Variable Groups and Indicator Arrays
When host variables are declared in a group item, an SQL statement which
needs to refer to each of these variables in turn can be abbreviated by
referring instead to the group-name. If you need to associate indicator
variables with these host variables, define a table of indicator
variables with as many instances as there are host variables, and
reference this table (the item with the OCCURS clause, not a group item
containing it).
You must set the SQLDB2 Compiler directive to enable the use of group
host variables and group indicator arrays.
Example.
Suppose variables are defined as follows:
01 host-structure.
03 sumh pic s9(9) comp-5.
03 avgh pic s9(9) comp-5.
03 minh pic s9(9) comp-5.
03 maxh pic s9(9) comp-5.
03 varchar.
49 varchar-l pic s9(4) comp.
49 varchar-d pic x(1000).
01 indicator-table.
03 indic pic s9(4) comp-5 occurs 4.
01 redefines indicator-table.
03 indic1 pic s9(4) comp-5.
03 indic2 pic s9(4) comp-5.
03 indic3 pic s9(4) comp-5.
03 indic4 pic s9(4) comp-5.
exec sql end declare section end-exec
Then the procedural statement:
exec sql fetch s3 into
:host-structure:indic
end-exec
is equivalent to:
exec sql fetch s3 into
:sumh:indic1, :avgh:indic2, :minh:indic3,
:maxh:indic4, :varchar
end-exec
The four declared indicator variables are allocated to the first four
host variables. If five or more had been declared, then all five host
variables would have an associated indicator variable.
The table of indicator variables is redefined only to show the equivalent
SQL statement (subscripting is not allowed in SQL statements). The
redefinition can be omitted and the COBOL program can refer to the
indicator variables using subscripting, if desired.
The NOT Operator ()
DB2 allows the operators =, >, < These are mapped to <>, <= and >=. The
character representation of the character can vary from system to system,
so you can define it using the SQLNOT Compiler directive.
SQL Communications Area
After any SQL statement has executed, important information is returned
to the program in an area called the SQL Communication Area (SQLCA).
There is a status indicator in a field of the SQLCA called SQLCODE and,
if your system supports it, SQLSTATE. SQLCODE is a numeric data item (PIC
S9(9) COMP-5 or COMP) which contains the result of the SQL operation. A
value of zero means that the statement has executed successfully; a
positive value means the statement did execute, but that some exceptional
condition occurred; and a negative value means that an error occurred and
the statement did not complete successfully. SQLSTATE is defined as a
five-character data item PIC X(5) and is provided for compatibility with
the ANSI SQL2 database language definition.
The SQL Communication Area is usually included in your program by the
statement:
exec sql include sqlca end-exec
This causes the source file sqlca.cpy (on Windows and OS/2) or sqlca.cbl
(on UNIX) to be included in your source code. This source file, supplied
with this COBOL system, contains a COBOL definition of the SQLCA.
If you do not include this statement, the Compiler automatically
allocates an area, but this area is not addressable in your program.
However, if you declare either or both of SQLCODE and SQLSTATE, the
Compiler generates code to copy the corresponding fields in the SQLCA
area to the user-defined fields after each EXEC SQL statement. We
recommend you define the entire SQLCA (this facility is provided for ANSI
compatibility).
If your selected COBOL dialect does not support COMP-5 data items (for
example mainframe emulation), you can edit the SQLCA source file and
change the COMP-5 items to COMP. However, this can result in larger and
slower object code.
After any non-zero condition in SQLCODE, the Compiler updates the
contents of the MFSQLMESSAGETEXT data item with a description of the
exception condition, provided it has been defined. If it is, it must be
declared as a character data item (PIC X(n), where n can be any legal
value; if the message does not fit into the data item it is truncated).
None of SQLCA, SQLCODE, SQLSTATE and MFSQLMESSAGETEXT are required to be
declared as host variables.
The SQLINIT and SQLINIM Modules
Early versions of IBM Database Manager do not provide any means in SQL
Syntax to connect to a database at execution time; instead you are
expected to code calls to the appropriate SQL API routines. This COBOL
system provides modules containing the required calls (sqlinit for IBM
Database Manager and sqlinim for Microsoft SQL Server) and if the SQLINIT
Compiler directive is specified (which is set by default) a call to it is
automatically embedded in the application by default.
These modules can be used even if syntax is available. They have the
additional purpose of ensuring that the Database Manager is initially
started and the database connection is correctly closed down even if the
application is abnormally terminated, to avoid possible database
corruption. If the application is abnormally terminated, then all
changes since the last COMMIT are rolled back (Database Manager only).
Typically the SQLINIT Compiler directive should be specified for all SQL
programs. However, the sqlinit module must only be called once for an
application. SQL programs called by other SQL programs should have the
NOSQLINIT Compiler directive specified. Alternatively, you can specify
the SQLINIT Compiler directive for the first SQL program to be executed
in a run unit, to ensure that the sqlinit module is called before any SQL
statements are invoked.
If used, the modules connect to the database that was used at compilation
time (specified in the SQLDB Compiler directive). If the application
needs to access a different database or multiple databases, then you
should use API calls or syntax if available instead, and you should
compile the program with the NOSQLINIT Compiler directive. An
application can be connected to only one database at any time. If
concurrent access to multiple databases is required, you can connect
programs in different sessions to different databases, and use some form
of interprocess communication (for example, Named Pipes or NETBIOS) to
work around this restriction.
If the program (or no program at all in a multi-program application) is
compiled with the SQLINIT directive, we strongly recommended that you use
the SQLPROT Compiler directive instead. This ensures that the database
connection is still correctly shut down when the application ends.
When using the add-on products Micro Focus CICS and IMS Option, you must
specify the NOSQLINIT and NOSQLPROT directives for programs running
within CICS or IMS. The CICS and IMS products provide equivalent SQLINIT
and SQLPROT support. Refer to your Micro Focus CICS and IMS Option
product manuals for instructions on enabling support for SQL. You must
use the NOSQLINIT and NOSQLPROT directives within CICS and IMS to ensure
proper sync pointing of SQL data.
Windows and When you specify the SQLINIT or SQLPROT Compiler
OS/2 directive, you must include the sqlinit.obj (IBM
Database Manager) or sqlinim.obj (Microsoft SQL Server)
file when the application is linked. When running or
animating a program, sqlinit.gnt or sqlinim.gnt in the
utils.lbr library are automatically located by the COBOL
system.
AIX When you specify the SQLINIT or SQLPROT Compiler
directive, you must include $COBDIR/src/sql/sqlinit.o on
the cob command line if you are creating an executable
module. When running or animating the program, the
sqlinit.gnt file is used. This file is contained in the
directory $COBDIR and is automatically located by the
COBOL system.