COBSQL is an integrated preprocessor designed to work with COBOL
precompilers supplied by relational database vendors. It is intended for
use with:

Oracle Pro*COBOL Version 1.8

Oracle Pro*COBOL Version 8

Sybase Open Client Embedded SQL/COBOL Version 11.1

Informix Embedded SQL/COBOL V7.3

You should use COBSQL if you are already using either of these
precompilers with an earlier version of a MERANT Micro Focus COBOL product
and want to migrate your application(s) to Server Express, or if you are
creating applications that will be deployed on UNIX platforms and need to
access either Oracle or Sybase relational databases.

For any other type of embedded SQL application development, we recommend
that you use OpenESQL.

Note: The Oracle precompiler version 1.8
does not support nested programs. COBSQL does not support Object Oriented
COBOL syntax (OO COBOL). If you want to use OO COBOL, therefore, you must
use OpenESQL.

You can access the SQL functions offered by the Oracle, Sybase or
Informix Database Management System (DBMS) by embedding SQL statements
within your COBOL program in the form:

EXEC SQL
SQL statement
END-EXEC

and then using the Oracle, Sybase or Informix precompiler to process the
embedded SQL before passing the program to the COBOL Compiler. The
database precompiler replaces embedded SQL statements with the appropriate
calls to database services. Other additions are made to the source code to
bind COBOL host variables to the SQL variable names known to the database
system.

The advantage of embedding SQL in this way is that you do not need to
know the format of individual database routine calls. The disadvantage is
that the source code that you see when you animate your program is that
output by the precompiler and not the original embedded SQL. You can
overcome this disadvantage by using COBSQL.

COBSQL provides an integrated interface between MERANT Micro Focus COBOL
and the third-party standalone precompiler, enabling you to animate a
program containing EXEC SQL statements and display your original source
code rather than the code produced by the precompiler.

This chapter shows you how you can use COBSQL in conjunction with either
the Oracle, Sybase or Informix precompiler to compile and animate your
programs.

To use COBSQL, specify the PREPROCESS"COBSQL" Compiler
directive when you compile your program. All directives following it are
passed from the Compiler to COBSQL. You can specify Compiler directives by
using $SET statements in your program or via the cob command line.

To terminate the directives to be passed to COBSQL, you must use the
ENDP COBOL directive. You can do this by making the following changes to
the directives:

C"preprocess(Cobsql) csqltype=oracle end-c comp5=yes endp"

When using Server Express, END-C and ENDP have the following effect:

Directives placed before END-C pass to COBSQL.

Directives placed between END-C and ENDP pass via COBSQL to the
precompiler.

Directives placed after ENDP pass to the COBOL compiler. Therefore,
without the ENDP directive, compiler directives continue to pass to
COBSQL rather than to the COBOL compiler.

You specify directives to COBSQL as if they were Compiler directives,
but you must put them after the directive PREPROCESS"COBSQL".

It is also possible to add the Cobsql directives to the standard Server
Express directives file cobol.dir.

Notes:

Each line in the cobol.dir file may contain one or more
compiler directives.

Avoid splitting a compiler directive across multiple lines in the
cobol.dir file.

When the compiler encounters either P(COBSQL) or PREPROCESS(COBSQL)
in the cobol.dir file, the compiler passes the rest of the line
to the pre-processor until it reaches an ENDP.

The compiler treats the pre-process statement and all the options
that follow, up to the ENDP, as a single compiler directive. Therefore,
the pre-process statement and all the options must all appear on a
single line in the cobol.dir file.

Alternatively, you can put COBSQL and precompiler directives in a file,
cobsql.dir. This file should reside either in the current
directory or in a directory specified in $COBDIR. COBSQL searches the
current directory and then along the COBDIR path for a cobsql.dir
file. Once COBSQL finds a cobsql.dir file, it stops searching. So,
if you have a cobsql.dir file in the current directory, the COBDIR
path is not searched.

Notes:

Each line in the file cobsql.dir can contain one or more
COBSQL directives.

Since COBOL does not read the file cobsql.dir, avoid putting
COBOL compiler directives into the file.

Avoid splitting a COBSQL directive across multiple lines in the file
cobsql.dir.

When COBSQL encounters either END-C, END or END-COBSQL in the cobsql.dir
file, the rest of the line passes to the database precompiler.

The options to be passed to the database precompiler must all appear
on a single line in the file cobsql.dir.

COBSQL processes cobsql.dir first and then any directives
specified via the cob command line.

A number of the directives can be reversed by placing NO in front of
them, for example, DISPLAY can be reversed using NODISPLAY. All the
directives in the lists below that can have NO placed in front of them are
marked with an asterisk. By default, the NO version of a directive is set.

You can specify shortened versions of some of the directives. If
applicable, the shortened version of a directive is shown in the lists
below, immediately after the full length version.

Some directives can be passed to COBSQL by the COBOL Compiler (see the
section COBOL Directives below), removing the
need to specify common directives more than once. Directives that can be
retrieved from the COBOL Compiler are processed before COBSQL directives.

Specifies which precompiler to use (ORACLE, SYBASE or
INFORMIX-NEW); for example, COBSQLTYPE=ORACLE .

CSTOP*
CSP

Forces COBSQL to load the stop run module that performs a rollback
if the application terminates abnormally.

DEBUGFILE*
DEB

Creates a debug (.deb) file.

DISPLAY*
DIS

Displays precompiler statistics. Should only be used when initially
verifying that COBSQL is correctly calling the standalone precompiler.

END-COBSQL
END-C
END

Signals the end of COBSQL directives; remaining
directives, if any, are passed to the precompiler.

KEEPCBL

Saves precompiled source file (.cbl).

MAKESYN

Converts all COMP host variables to COMP-5 host variables. The
default situation, if MAKESYN is not set, is that all variables (not
just host variables) are converted from COMP to COMP-5.

NOMAKESYN

No conversion of COMP-5 variables or host variables is carried out.

SQLDEBUG

Creates a number of files that can be used by MERANT to debug
COBSQL. These files include the output file from the precompiler
(normally this has a .cbl extension), the listing file produced
by the precompiler (this has a .lis extension), plus a COBSQL
debug file which has a .sdb extension. SQLDEBUG will also turn
on KEEPCBL and TRACE.

TRACE*

Creates a trace file (.trc).

VERBOSE

Displays all precompiler messages and gives status updates as the
program is processed. You should only use this when initially verifying
that COBSQL is calling the standalone precompiler correctly.

The complete set of methods used within COBOL to manipulate copyfiles is
not available with database precompilers and COBSQL itself cannot handle
included copyfiles. These problems can be overcome, however, by using the
MERANT Micro Focus Copyfile Preprocessor (CP).

CP is a preprocessor that has been written to provide other
preprocessors, such as COBSQL, with a mechanism for handling copyfiles. CP
follows the same rules as the COBOL Compiler for handling copyfiles so any
copyfile-related Compiler directives are automatically picked up and
copyfiles are searched for using the COBCPY environment variable. CP will
also expand the following statements:

Oracle and Sybase
For CP to resolve copyfiles and include statements correctly, use the
following COBOL Compiler directives for Sybase and Oracle:

copyext (pco,cbl,cpy,cob) osext(pco)

Informix For Informix, use:

copyext (eco,mf2,cbl,cpy,cob) osext(eco)

COBSQL can call CP to expand copyfiles before the database precompiler
is invoked. This means that all the copy-related commands are already
resolved so that it appears to the database precompiler that a single
source file is being used.

The other advantage of using CP is that it makes copyfiles visible when
animating.

When CP sees an INCLUDE SQLCA statement, it does the
following:

Searches for a file called sqlca.ext in the current
directory where ext is any copyfile extension as set up
by the OSEXT and COPYEXT Compiler directives. By default these are .cbl
and .cpy.

Searches for sqlca.ext along the COBCPY path.

An example sqlca.cpy file is provided in the source
directory under your Server Express base installation directory. If the
SQLCA file supplied by the database vendor is not located on the COBCPY
path, this MERANT demonstration version of the SQLCA will be used.

Note: Using the file sqlca.cpy can result in errors when
the program is run.

You can specify the CP preprocessor's SY directive to prevent CP
expanding the SQLCA include file, for example:

preprocess"cobsql" preprocess"cp" sy endp

You should always use CP's SY directive when processing Sybase code
because Sybase expects to expand the SQLCA itself.

As Oracle can produce code with either COMP or COMP-5 variables, it has
two sets of copyfiles. The standard sqlca.cob, oraca.cob
and sqlda.cob all have COMP data items. The sqlca5.cob,
oraca5.cob and sqlda5.cob files have COMP-5 data items. If
you are using the comp5=yes Oracle directive, you must set the COBSQL
directive MAKESYN to convert the COMP items in the SQLCA to COMP-5.

If CP produces errors when attempting to locate copyfiles, check to make
sure that the OSEXT and COPYEXT Compiler directives are set correctly.
COPYEXT should be set first and should include as its first entry the
extension used for source files (.pco or .eco, for
example).

If these are set correctly, ensure that the copyfile is either in the
current directory or in a directory on the COBCPY path.

When using CP in conjunction with COBSQL, SQL errors inside included
copyfiles will be reported correctly. Without CP, the line counts will be
wrong, and the error will either go unreported or will appear on the wrong
line.

COBSQL error messages can be displayed in different languages depending
on the setting of the LANG environment variable. For full details on NLS
and how to set the LANG environment variable, see the chapter
Internationalization
Support in your Programmer's Guide to Writing Programs.
For details on the LANG environment variable see the appendix
Micro
Focus Environment Variables in your Server Express
User's Guide.

The COBSQL error message cobsql.lng has been translated into a
number of different languages and can be found with the COBOL NLS message
files. If there is not an error message cobsql.lng for the current
setting of LANG, then the default error message file is used.

Note: COBSQL does not translate any error messages produced by
the database precompilers.

If you experience problems using COBSQL, first of all check the
following:

Basic network connectivity

Forget SQL, and determine whether the client and server are
communicating. For TCP/IP, check whether you can ping the server from
the workstation and vice versa. If host names don't work, try raw IP
addresses.

SQL networking software

Check that the SQL networking software is "talking"
correctly to the network software. Many SQL vendors supply a ping
utility which will show whether the SQL network is set up correctly.

Interactive SQL

If the SQL network is working correctly, try some interactive SQL.
Most vendors supply a simple utility that allows you to enter SQL from
the keyboard and view the results. Most vendors also supply a sample
database that is useful for this purpose.

Standalone precompiler

Verify that the standalone precompiler works. There may be an icon
or a command line for the precompiler. Verify that it can produce
COBOL code correctly. It is normal for some sample applications to be
supplied with the precompiler.

Preprocessed application

Check that a preprocessed application runs correctly. Pass the
expanded program through the COBOL Compiler and then try to run it.

COBSQL with minimal directives

Try COBSQL with minimal directives. Set up a project in Server
Express, place the SQLCA copyfile into the directory with the sample
program (prior to running the precompiler), and see if this works.

Then, if you still experience problems, please contact MERANT Technical
Support. To help Technical Support locate the cause of the problem:

If you cannot locate the source of the problem, then check each of the
following:

Latest versions

Ensure that you are using the latest version of all the products
involved.

COMP/COMP-5 conflicts

Check the vendor's documentation and example applications.

Configuration

Check that environment variables, PATH and configuration file
settings are set up correctly.

Directives

By default, COBSQL does not display the command line it passes to
the database precompiler. Setting the SQLDEBUG directive enables the
command line to be displayed (you will need to do this if the
precompiler gives command line errors). Possible causes of command
line errors are that the directives to be passed to the precompiler
are incorrect or that the length of the precompiler command line has
been exceeded.

Memory

COBSQL may display the following error because the database
precompiler has terminated unexpectedly:

* CSQL-F-021: Precompiler did not complete -- Terminating

This may be because the operating system has run out of memory
attempting to execute the database precompiler.

Missing output files

COBSQL may display the following errors because it cannot find the
precompiler's output file. This may be because the precompiler did not
produce an output file. The normal reason for this is that the
precompiler hit a fatal error which meant it could not create the
output file.

where filename is the name of the file produced by the
database precompiler.

Premature end of expanded source

If COBSQL reports the error "Premature end of expanded source",
and the precompiler runs correctly, this indicates that COBSQL has not
been able to match the original source lines with the lines produced
by the database precompiler.

Another possible reason for COBSQL reporting this error is that the
program does not contain any SQL. Generally, if the database
precompiler does not come across any SQL it will abort the creation of
its output file part way through, causing this error to be displayed.

The following Oracle precompiler options can have a marked effect on
the behavior and the memory requirements of an Oracle application. We
recommend that you take time to review your Oracle documentation before
changing the setting of these directives.

DBMS
HOLD_CURSOR
MAXOPENCURSORS
MODE
RELEASE_CURSOR

Within an Oracle program it is possible to use the ALTER SESSION
command to change the OPTIMIZER_GOAL. This can have a profound effect on
the performance of the application. Before using this syntax, we
recommend that you consider what is the best optimization for an
application in the general case rather than for a particular SQL
statement. If you need to change the performance of one particular
statement, it is probably better to use HINTS. Consult your Oracle
documentation for further information on ALTER SESSION, OPTIMIZER_GOAL
and HINTS.

Oracle provides a method of reducing network access by using arrays
within Embedded SQL statements. This enables the application to perform
'batch' SQL commands, in that more than one row can be acted upon in a
single SQL statement. See the section Host
Arrays in the chapter Host Variables for more details.

The use of arrays enables an application, for example, to fetch ten
rows at a time instead of one at a time. Oracle supply an example
program (normally called sample3.pco) that uses an array to
fetch multiple rows. Arrays are documented in the Pro*Cobol
Supplement to the ORACLE Precompilers Guide.

Pro*Cobol treats BINARY host variables as though they had been
defined with a picture clause of COMP. This means that BINARY items will
also be converted to COMP-5 when the Pro*Cobol comp5=yes option is used.

Pro*Cobol always needs to be supplied with an include directive,
unless the SQLCA copyfile is in the current directory, because it needs
to know the location of the SQLCA copyfile. If this is not supplied,
Pro*Cobol gives an error on filename.pco, where filename
can be any string of eight characters.

When Pro*Cobol runs, it can generate useful information. When the
COBSQL VERBOSE directive is used, COBSQL displays as much of this
information as it can.

To get the maximum information from Pro*Cobol, set the Pro*Cobol
directive xref=yes. You can add this directive to the Pro*Cobol
configuration file $ORACLE_HOME\PROxx\pcbcfg.cfg
where:

xx

is the Pro*COBOL version (for example, for Oracle 8.0 this is
PRO80)

$ORACLE_HOME

is the root directory for the Oracle insallation on your
machine

By using the COBSQL DISPLAY directive, the current Pro*Cobol
directives can be seen on the screen. This is followed by the
statistical information created by Pro*Cobol.

If the COBOL LIST directive is used, COBSQL passes any information it
has collected from the precompiler to the COBOL checker for inclusion at
the the bottom of the COBOL listing.

To overcome this you need to put these items into copyfiles which are
not opened by Pro*COBOL. However, this does not work if you use CP which
expands copyfiles before Pro*COBOL is invoked. This could cause a problem
if you are using htmlpp which calls CP to expand copyfiles. You
must therefore invoke htmlpp before COBSQL.

For example, the following compile line works:

COBOL PROG P(HTMLPP) PREPROCESS(COBSQL) CSQLT=ORACLE8

whereas this line does not:

COBOL PROG PREPROCESS(COBSQL) CSQLT=ORACLE8 P(HTMLPP)

You must define at least one variable within the Working-Storage Section
for Pro*COBOL 8.0.4 to add its variables to the generated .cbl
file.

If CP is not used with COBSQL to locate copyfiles, the copyfile to be
brought in by the Sybase precompiler needs to be fully qualified. The
Sybase precompiler does not search for any extensions.

If the Sybase precompiler has problems locating the correct language
to use to report messages on the client, check that the Sybase file,
locales.dat is configured correctly.

If the default setting for the client operating system has been
configured, but Sybase still reports national language support errors,
use the LANG environment variable to override the setting in the locales.dat
file.

For example if the aix client was causing problems and the
locales.dat file contained the following setting for AIX:

It can be difficult to identify Sybase error messages. To help COBSQL
identify them, you can modify the esql.loc file. Use a text
editor to edit the esql.loc file to change the layout of the
messages to the following:

SYB-severity-number-text

where the parameters are:

SYB

A string which indicates to COBSQL that this is a modified
Sybase error message.

severity

Indicates the severity of the error; some of the Sybase
messages are only warnings rather than normal or fatal errors.

number

A unique, four digit error number assigned to the Sybase error.

text

The original Sybase error message.

For example, a typical entry in the esql.loc file might be:

9 = M_PRECLINE, "Warning(s) during check of query on line %1!."

and this would be changed to read:

9 = M_PRECLINE, "SYB-W-2009 Warning(s) during check of query on line %1!."

We recommend that you make a copy of esql.loc before
altering it. Using the modified version, COBSQL can detect the full
range of Sybase error messages.

The location of esql.loc is dependent on the language and
code page used. This is defined in the locales.dat file. If
the definition of the default language for the AIX platform was as
follows:

The default language would be us_english, using the iso_1 code page,
so the copy of esql.loc that is to be used is:

/sybase home/locales/messages/us_english/iso_1/esql.loc

where sybase home is the directory that the Sybase client is
installed into.

For more information on how Sybase uses and locates the different
error message files, refer to your Sybase Client Reference Manual.

To use Sybping with different Sybase servers, each server should have
a service defined. The Sybase product sqledit shows you how to enter the
required information.

If the name of the server is not specified when connecting to Sybase,
Sybase searches for an environment variable called DSQUERY.

When the Sybase precompiler runs, it can generate useful information.
If the COBSQL directive VERBOSE is set, COBSQL displays as much of this
information as it can.

By using the COBSQL DISPLAY directive, the statistical information
created by the Sybase precompiler can be seen on the screen.

If the COBOL LIST directive is set, COBSQL passes any information it
has collected from the precompiler to the COBOL checker for inclusion at
the end of the COBOL listing.

The Sybase precompiler accepts programs that do not contain any SQL
statements, and produces an output file. This means that all of the
programs in a project can be compiled successfully. However, it also
means that the Sybase precompiler inserts all the Working-Storage items
and COBOL support code that it would insert for a program that contains
Sybase SQL statements into a non-Sybase program. This makes the program
larger, and may affect its performance.

The Sybase COBOL precompiler can be supplied separately to the Sybase
System 11 Server. If this is the case, care must be taken when
installing the Sybase COBOL precompiler as it is supplied with its own
version of the Open Client support files and problems can arise if the
two products have different versions of these files. In this situation,
we strongly recommend that you contact Sybase support before proceeding
with the installation of the Sybase System 11 COBOL precompiler.

You should take particular care if you are installing the Sybase
client onto a machine that has the Server installed onto it (or vice
versa). Installing the Sybase client only does not cause any problems.

Informix uses the eco, cob and mf2 file
extensions. For CP to resolve copyfiles and include statements
correctly, use the following COBOL Compiler directives:

copyext(eco,mf2,cob,cpy,cbl) osext(eco)

Under UNIX, because COBSQL invokes the Informix precompiler, the
INFORMIXCOB, INFORMIXCOBTYPE and INFORMIXCOBDIR environment variables
all need to be set up before using COBSQL. For more information about
these environment variables, refer to the Informix COBOL/ESQL
Programmers Guide.

Informix only produces error messages in its list files. Normally the
error message will contain the line the error occurred on. If COBSQL
cannot locate the line number from the error message, it will not report
the error.

To run COBSQL correctly with Informix, the INFORMIXDIR environment
variable should be set before using COBSQL.