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

NetExpress 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
The OpenESQL preprocessor enables you to access a relational database via an ODBC driver by embedding SQL statements within your COBOL program.
DB2 ECM
The DB2 External Checker Module (ECM) is a new type of integrated preprocessor provided with NetExpress 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 Software Development Kit (SDK) for Windows 95/NT Version 2.1
- IBM DB2 for Windows 95/NT Single User Version 2.1
- IBM Distributed Database Connection Services (DDCS) for Windows NT Version 2.3
- IBM DB2 Universal Database Version 5
- 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
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 NetExpress, or if you are creating applications that will be deployed on UNIX platforms. For any other type of embedded SQL development, we recommend that you use OpenESQL.

Notes:

OpenESQL applications cannot be deployed on UNIX platforms.
Use of COBSQL is only supported 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.

Most vendors provide SQL Reference documentation with their database software which will include full information about embedded SQL statements but you should, for example, be able to perform the following typical operations using the statements shown:

Operation	SQL Statement(s)
Add data to a table	INSERT
Change data in a table	UPDATE
Retrieve a row of data from a table	SELECT
Create a named cursor	DECLARE CURSOR
Retrieve multiple rows of data using a cursor	OPEN, FETCH, CLOSE

With the exception of INSERT, DELETE(SEARCHED) and UPDATE(SEARCHED) which are included for your convenience, the embedded SQL statements described here work somewhat differently, or are in addition to, standard SQL statements.

Statement	Description
BEGIN DECLARE SECTION	Marks the beginning of a host variable declaration section.
CALL	Executes a stored procedure.
CLOSE	Ends row-at-a-time data retrieval initiated by the OPEN statement.
COMMIT	Commits a transaction.
CONNECT	Connects to a database.
DECLARE CURSOR	Defines a cursor for row-at-a-time data retrieval.
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.
SELECT INTO¹	Retrieves one row of results (also known as a singleton select).
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.
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.

A full syntax description is given in the online help for each of the embedded SQL statements above, together with an example of its use.

Notes:

These statements have the same name as a standard SQL statement but the syntax given in the online help augments the standard SQL syntax.
The DISCONNECT statement is not supported when accessing an Oracle database via COBSQL.
These statements are not supported by COBSQL.

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. See the chapter OpenESQL for details.
DB2 ECM
Specify the DB2 Compiler directive. See the chapter DB2 for details.
COBSQL
Specify the PREPROCESS"COBSQL" Compiler directive. See the chapter COBSQL for details.

1.3.1 Internet Application Wizard

NetExpress includes an Internet Application Wizard. Use this wizard to generate complete Web applications that access a relational database. You can create a working SQL application within minutes.

For further information, refer to the on-line book Internet Applications.

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 dynamic-link libraries (.dll files). 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.

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

OpenESQL and 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.

OpenESQL and DB2
Cursor names should be unique within an application.

Host Variables