| Fsview | Embedded SQL Using DB2 | |
This chapter describes how to access a relational database management system (RDBMS) from a COBOL program.
Two methods are used to access a relational database management system (RDBMS) from a COBOL program.
EXEC SQL .... END-EXEC
This is known as Embedded SQL, or ESQL.
The embedded SQL statements must be translated by a SQL precompiler (or preprocessor) rather than the COBOL Compiler.
Open Database Connectivity (ODBC) is an API which is independent of any particular database system.
A database system can have several interfaces including:
In addition, third parties may provide ODBC support or offer their own API.
This section describes the preprocessor interfaces for Embedded SQL supported by Micro Focus COBOL.
Precompiler Services is used by the COBOL Compiler to translate Embedded SQL statements. The Compiler reads the source code and calls Precompiler Services when it detects Embedded SQL statements. Compilation is controlled by the SQL Compiler directive.
Precompiler Services is used by:
The Integrated Preprocessor Interface is an extension to the COBOL Compiler which enables close integration between a preprocessor and the Compiler. (See your Programmer's Guide to Writing Programs for details of the interface.) The preprocessor is specified by the PREPROCESS Compiler directive, and the COBOL Compiler uses the preprocessor to read and preprocess lines of source code. The preprocessor tells the Compiler which lines are original source code, and which have been inserted by the preprocessor. This enables Animator to present the original source during debugging, rather than the code generated by the preprocessor.
The Integrated Preprocessor Interface is used by XDB.
Informix, Oracle and Sybase provide standalone preprocessors for Embedded SQL. They operate independently of the COBOL Compiler, and so Animator works with the output of the preprocessor rather than the programmer's original source code.
COBSQL is an integrated preprocessor supplied by Micro Focus for use with preprocessors from Informix, Oracle and Sybase. The Compiler uses COBSQL as it would any other preprocessor but COBSQL runs the standalone preprocessor and returns its output to the Compiler, noting which lines are original and which have been added by the preprocessor.
COBSQL provides platform independent animation modules for Oracle and Sybase. The Informix preprocessor generates code to support animation itself.
Application Programming Interfaces to relational databases are generally designed for use by C programs. See your Programmer's Guide to Writing Programs for information about calling C from COBOL.
ODBC is a database-independent API based on work by the SQL Access Group (SAG) and developed by Microsoft. Others include Oracle's OCI, dblib for Microsoft SQL Server and Sybase SQL Server Version 4, and ctlib for Sybase System 10.
When you animate a program containing calls to external routines, Animator needs to know how to resolve references to the external routines.
UNIX:
On UNIX, this is done by creating a customised COBOL run-time system,
which is linked with the SQL run-time libraries.
16-bit:
On the 16 bit system, entry points may be made visible to Animator via an
Installf module. See your Object COBOL User Guide for
details of creating Installf modules. Your COBOL system includes example
source code for an Installf module for use with Oracle under DOS. This can
be adapted for other database systems.
Windows and OS/2:
In Windows and OS/2 environments, library routines often correspond
directly to DLL entry points. Where this is the case, you can run your
COBOL programs without linking if you make the DLL entry points visible to
the COBOL run-time system. This is especially useful for animation.
You make the entry points to a DLL visible by setting a procedure- pointer to the DLL; for example:
01 SQL-RTS-Ptr Procedure-Pointer.
01 Bad-Pointer Procedure-Pointer.
. . .
set Bad-Pointer to entry "xyzzy" *> Non-existent DLL
set SQL_RTS-Ptr to entry "ODBC"
if SQL_RTS-Ptr=Bad-Pointer
display "ODBC.DLL not found"
end-if
The Microsoft Open Database Connectivity (ODBC) interface enables applications to access data from database management systems. A single application can access diverse back-end database environments. You can develop and ship an application without binding the application to a specific database environment. ODBC was initially defined for use on Windows systems, but is now becoming available on other platforms.
Your COBOL applications can be animated on any system which has an ODBC driver installed. To link ODBC applications, an import library for odbc.dll is required. This can built from the DLL with an implib utility (if your system has one), or purchased in an ODBC Software Development Kit (SDK). You need ODBC API documentation to develop your own ODBC applications, which you can find in an SDK or 3rd party reference publication. SDKs are available from Microsoft for Windows Platforms, and Visigenic for other platforms.
Your COBOL system includes the COBOL copyfile mfodbc78.cpy; this file contains constants used in calls to ODBC API functions. You might find it convenient to use this file in your applications.
If you encounter problems when using an ODBC driver from a COBOL application, you should try to isolate them to either COBOL or your SQL product. Do this by verifying correct operation with either SQL vendor-only software, or third party tools. For example, most SQL systems have an interactive SQL tool which can be used to verify that the database is online and operational:
With ODBC there are several test tools which can be used in a similar way, Microsoft Query being the most widely available as it is included in several Microsoft products.
In addition, use the following checks to help establish the cause:
This is a very frequent cause of problems. Use the COBSW environment variable with the /S option to increase stack space. Try values in the range 12000 to 26000.
If several drivers are installed, name clashes and version conflicts between driver components can mean that the driver does not function correctly. Use a third party tool such as MSQuery to ensure that the driver functions correctly in a non-COBOL environment.
Some drivers that fall into this category allow a data source to disable stack checking by placing the line:
NoStackCheck=1
in the file odbc.ini in the Windows directory, in the section that describes the data source. (It does not apply to the section describing the driver itself. It is necessary to check with the driver provider if this applies to a specific driver, and it may also be necessary to increase stack space as described above.)
If attempts to execute SQL queries result in messages of the form:
query/expression too complex maximum xxxxx limit exceeded
this may be your problem.
Object COBOL and Dialog System V2.5 have been tested with the following servers and drivers:
The demonstration programs have been tested with the drivers in the Microsoft ODBC SDK V2.0 on Windows NT, and with Intersolv drivers on Windows, Windows NT, OS/2 and AIX. The ODBC demonstration programs contain conditional compilation which shows the changes needed to operate on these platforms.
Copyright © 1999 MERANT International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| Fsview | Embedded SQL Using DB2 | |