Host VariablesNext

Chapter 1: Introduction

1.1 Overview

Net 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:


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:


and followed by the keyword:


For example:

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

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:

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.

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

BEGIN DECLARE SECTION Marks the beginning of a host variable declaration section
BEGIN TRANSACTION3 Opens a transaction in AUTOCOMMIT mode
CALL3 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
DECLARE DATABASE Identifies a database
DELETE (POSITIONED)1 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
DISCONNECT2 Closes connections to one or all databases
END DECLARE SECTION Marks the end of a host variable declaration section
EXECSP3 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 ODBC3 Queries the ODBC data dictionary
ROLLBACK Rolls back the current transaction
SELECT DISTINCT Associates a cursor name with an SQL statement
SELECT INTO1 Retrieves one row of results (also known as a singleton select)
SET CONCURRENCY3 Sets the concurrency option for standard-mode cursors
SET CONNECTION3 Specifies which database connection to use for subsequent SQL statements
SET OPTION3 Assigns values for query-processing options
SET SCROLLOPTION3 Sets the scrolling technique and row membership for standard-mode cursors
SET TRANSACTION ISOLATION3 Sets the transaction isolation level mode for a connection
UPDATE (POSITIONED)1 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


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.2.2 OpenESQL Assistant

Net Express includes an OpenESQL Assistant. You can use this interactive tool to:

For further information, refer to the chapter OpenESQL.

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:

1.3.1 Internet Application Wizard

Net Express 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.

The table below gives guidelines on how to use multiple program modules with the different SQL preprocessors:

SQL preprocessors
OpenESQL In a program that includes multiple, separately compiled modules, you should compile only one module 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.
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.
Cursor names should be unique within an application
COBSQL If you specify the INIT directive more than once, Net Express ignores second and subsequent uses

Copyright © 2000 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.

Host VariablesNext