Host Variables

Chapter 1: Introduction

Note: This book does not provide details about SQL syntax, error messages returned or any use of SQL outside of the COBOL environment. For details of these, refer to the documentation supplied by your database vendor.

1.1 Overview

Server Express includes a number of SQL preprocessors (OpenESQL, the DB2 ECM and COBSQL) which enable you to access relational databases by embedding SQL statements within your COBOL program:

OpenESQL:
OpenESQL is provided on UNIX platforms for which Open Database Connectivity (ODBC) drivers are available.

OpenESQL is an integrated preprocessor that enables you to use Embedded SQL in your COBOL applications to access ODBC-enabled data sources.

Unlike separate preprocessors, OpenESQL is controlled by specifying the SQL directive when you compile your application. A set of high-performance ODBC drivers is provided for the major relational databases, including Oracle, Sybase, Informix.

OpenESQL in Server Express is compatible with applications developed using Micro Focus Net Express. This means that applications developed using the OpenESQL Assistant in Net Express can be easily ported to UNIX platforms.

Use OpenESQL if your application is designed to use different relational database systems or you are unsure which relational database system you may be using in the future.

If you are using an Oracle database and compile with the directive TARGETDB set to ORACLEOCI then, at run time, the application will make OCI calls rather than ODBC calls. This means that the deployed application will not require an ODBC driver, which has potential cost and performance benefits. For more details, see Oracle OCI Support in the chapter OpenESQL.

DB2 ECM:
The DB2 External Checker Module (ECM) is a new type of integrated preprocessor provided with Server Express and designed to work closely with the Micro Focus COBOL Compiler. The DB2 ECM converts embedded SQL statements into the appropriate calls to DB2 database services. It is intended for use with:
- IBM DB2 Universal Database Version 5.0 for AIX
- IBM DB2 Universal Database Version 5.0 for HP-UX
- IBM DB2 Universal Database Version 5.0 for Solaris Operating Environment
- IBM DB2 Universal Database Version 5.0 for OS/2 Warp
- IBM DB2 Universal Database Version 5.0 for Windows NT Operating Environment
- IBM DB2 Connect Version 5
COBSQL
COBSQL is an integrated preprocessor designed to work with COBOL precompilers supplied by relational database vendors. It is intended for use with:
- Sybase Open Client Embedded SQL/COBOL Version 11.1
- Oracle Pro*COBOL Version 1.8
- Oracle Pro*COBOL Version 8
- Informix Embedded SQL/COBOL Version 7.3
You should use COBSQL if you are already using either of these precompilers with an earlier version of a Micro Focus COBOL product and want to migrate your application to Server Express. For any other type of embedded SQL development, we recommend that you use OpenESQL.
Notes:
- The OpenESQL, DB2 ECM and COBSQL preprocessors are supported in 32-bit mode. OpenESQL is also supported in 64-bit mode with Oracle OCI on Sun Solaris only.
- Use of COBSQL is supported only for standard procedural COBOL programs. You cannot use COBSQL with Object Oriented COBOL syntax (OO programs) or with nested programs.

1.2 Embedded SQL

Each of the preprocessors works by taking the SQL statements that you have embedded in your COBOL program and converting them to the appropriate function calls to the database.

Within your COBOL program, each embedded SQL statement must be preceded by the introductory keywords:

EXEC SQL

and followed by the keyword:

END-EXEC

For example:

EXEC SQL
   SELECT au_lname INTO :lastname FROM authors
      WHERE au_id = '124-59-3864'
END-EXEC

The embedded SQL statement can be broken over as many lines as necessary following the normal COBOL rules for continuation, but between the EXEC SQL and END-EXEC keywords you can only code an embedded SQL statement, you cannot include any ordinary COBOL code.

You can use any SQL statement between the EXEC SQL and END-EXEC keywords. A description of standard SQL is beyond the scope of this book and you should refer to the reference manuals supplied with your relational database or one of the many reference books on SQL that are available. However, Embedded SQL provides some extensions to standard SQL that either change the behavior of standard SQL statements or add new functionality. These extensions are summarized in the table below.

A full description of the syntax for each of these statements, together with examples of its use, is given in the appendix Embedded SQL Statements.

Statement	Description
BEGIN DECLARE SECTION	Marks the beginning of a host variable declaration section
BEGIN TRANSACTION³	Opens a transaction in AUTOCOMMIT mode
CALL³	Executes a stored procedure
CLOSE	Ends row-at-a-time data retrieval initiated by the OPEN statement
COMMIT	Commits a transaction
COMMIT WORK RELEASE⁴	Commits a transaction and disconnects from the database
CONNECT	Connects to a database
DECLARE CURSOR	Defines a cursor for row-at-a-time data retrieval
DECLARE DATABASE	Identifies a database
DELETE (POSITIONED)¹	Removes the row where the cursor is currently positioned
DELETE (SEARCHED)	Removes table rows that meet the search criteria
DESCRIBE	Populates an SQLDA data structure
DISCONNECT²	Closes connections to one or all databases
END DECLARE SECTION	Marks the end of a host variable declaration section
EXECSP³	Executes a stored procedure
EXECUTE	Runs a prepared SQL statement
EXECUTE IMMEDIATE	Runs the SQL statement contained in the specified host variable
FETCH	For a specified cursor, gets the next row from the results set
INCLUDE	Defines a specific SQL data structure for use by an application
INSERT	Adds data to a table or view
OPEN	Begins row-at-a-time data retrieval for a specified cursor
PREPARE	Associates an SQL statement with a name
QUERY ODBC³	Queries the ODBC data dictionary
ROLLBACK	Rolls back the current transaction
ROLLBACK WORK RELEASE⁴	Rolls back a transaction and disconnects from the database
SELECT DISTINCT	Associates a cursor name with an SQL statement
SELECT INTO¹	Retrieves one row of results (also known as a singleton select)
SET AUTOCOMMIT³	Controls AUTOCOMMIT mode
SET CONCURRENCY³	Sets the concurrency option for standard-mode cursors
SET CONNECTION³	Specifies which database connection to use for subsequent SQL statements
SET OPTION³	Assigns values for query-processing options
SET SCROLLOPTION³	Sets the scrolling technique and row membership for standard-mode cursors
SET TRANSACTION ISOLATION³	Sets the transaction isolation level mode for a connection
UPDATE (POSITIONED)¹	Changes data in the row where the cursor is currently positioned
UPDATE (SEARCHED)	Changes data in existing rows, either by adding new data or by modifying existing data
WHENEVER	Specifies the default action (CONTINUE, GOTO or PERFORM) to be taken after a SQL statement is run

Notes:

In the table above:

These statements have the same name as a standard SQL statement but the syntax given in the appendix augments the standard SQL syntax.
The DISCONNECT statement is not supported when accessing an Oracle database via COBSQL.
These statements might not work with all ODBC drivers and are not supported by COBSQL.
These statements are supported by COBSQL only when accessing an Oracle Database.

1.2.1 Case

The case of embedded SQL keywords in your programs is ignored, for example:

EXEC SQL CONNECT exec sql connect Exec Sql Connect

are all equivalent.

The case of cursor names, statement names and connection names must match that used when the variable is declared. For example, if you declare a cursor as C1, you must always refer to it a C1 (and not as c1).

The settings for the particular database determine whether other words, such as table and column names, are case-sensitive.

Hyphens are not permitted in SQL identifiers (in table and column names, for example).

1.3 Building your Application

Once you have written your COBOL application containing embedded SQL, you must compile it specifying the appropriate Compiler directive such that the preprocessor converts the embedded SQL statements into function calls to the database:

OpenESQL
Specify the SQL Compiler directive. For details, see the OpenESQL chapter.
DB2 ECM
Specify the DB2 Compiler directive. For details, see the DB2 chapter.
COBSQL
Specify the PREPROCESS"COBSQL" Compiler directive. For details, see the COBSQL chapter.

1.4 Multiple Program Modules

Multiple embedded SQL source files, compiled separately and linked to a single executable file, can share the same database connection at run time. This is also true for programs that are compiled into separate callable shared objects. If subsequent program modules (in the same process) do not process a CONNECT statement, they share the same database connection with the module that included the CONNECT statement.

OpenESQL
In a program that includes multiple, separately compiled modules, only one module should be compiled with the INIT option of the SQL Compiler directive. All other modules within the program should share that first automatic connection or make explicit connections using the CONNECT statement.

OpenESQL - DB2

Statement names are local to a particular program module (compilation unit). This means that a statement cannot be prepared in one module and executed in another.
Within an application, cursor names should be unique.

COBSQL
With COBSQL, if the INIT directive is specified more than once, second and subsequent uses are ignored.

Host Variables