| DB2 | Reserved Keywords | |
COBSQL is an integrated preprocessor designed to work with COBOL precompilers supplied by relational database vendors. It is intended for use with:
You should use COBSQL if you are already using any of these precompilers with an earlier version of a Micro Focus COBOL product and want to migrate your application(s) to Server Express.
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 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 access a DBMS from COBOL, the COBOL run-time system needs to be aware of the API routines used by the DBMS. A database vendor may provide a script that creates either:
The script contains a cob command, and you can control the effect of the script by amending the options to the cob command.
The next two sections provide examples of how to use the makefile script provided by Oracle, which is located at $ORACLE_HOME/precomp/lib/ins_precomp.mk You should be able to use these examples as a starting point when you want to use the script provided by another database vendor.
Note: These examples assume that your COBOL programs use multi-threading. If they do not, you can still use them but you must remove the -t cob option, and remove _t from any of the filenames.
Follow these steps to to create an executable run-time system:
cd $ORACLE_HOME/precomp/lib make LOC_RTSORA=newrts -f ins_precomp.mk newrts
where:
newrts is the name you wish to give the output executable run-time
system
cp $COBDIR/bin/rts32_t $COBDIR/bin/rts32_t.sav
ln -s newrts $COBDIR/bin/rts32_t
Follow these steps to to create a shared library of Oracle DBMS routines:
cd $ORACLE_HOME/precomp/lib make LOC_RTSORA=dbmslib -f ins_precomp.mk dbmslib
where:
dbmslib is the name you wish to give the output shared library
| INITCALL"dbmslib" | Enables the DBMS support shared library to be loaded before the first line of the program is executed. |
| SERIAL | Enables the application to be used in a threaded environment (if the Oracle support uses threading) |
If the program fails to execute and run-time system error 173 occurs relating to dbmslib, the run-time system has been unable to find the shared library. This might be because the environment variable LIBPATH, LD_LIBRARY_PATH or SHLIB_PATH (as appropriate to your system) does not include the path in which the Oracle support libraries are stored. Check the setting of the environment variable and rerun.
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.
Note: You must specify cobsql in lower case.
To terminate the directives to be passed to COBSQL, you must use the ENDP COBOL directive, for example:
cob -C "preprocess(cobsql) csqltype==oracle end-c comp5==yes endp"
END-C and ENDP have the following effect:
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:
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:
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.
For example, in the following command line:
cob -V -k testprog.pco -C"p(cobsql) csqlt==ora makesyn end-c
xref==yes mode==ansi endp list()"
end-c, are csqlt=ora and
makesyn.
endp, are xref=yes and mode=ansi.
list(). The following is a list of the COBSQL directives:
| Directive |
Description |
|---|---|
| COBSQLTYPE CSQLT |
Specifies which precompiler to use (ORACLE, ORACLE8, 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 variables or host variables from COMP to COMP-5 is carried out. |
| SQLDEBUG | Creates a number of files that we can use 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 following is a list of the COBOL directives:
| Directive |
Description |
|---|---|
| BELL* | Controls whether COBSQL sounds the bell when an error occurs. |
| BRIEF* | Controls whether COBSQL shows SQL error text as well as the error number. |
| CONFIRM* | Displays accepted/rejected COBSQL directives. |
| LIST* | Saves the precompiler listing file (.lis). |
| WARNING* | Determines the lowest severity of SQL errors to report. |
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 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:
EXEC SQL INCLUDE ... END-EXEC
Oracle uses .pco and .cob extensions, Sybase uses .pco and .cbl extensions and Informix uses .eco, .cob and .mf2 extensions.
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:
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 example 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 Environment Variables in your 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.
The following examples show, for the Oracle Sybase and Informix precompilers, command lines that you can enter at the Server Express Command Prompt to compile a program using COBSQL.
Oracle
cob -a -v -k sample.pco -C "p(cobsql) cstop
cobsqltype==ORACLE endp"
Sybase
cob -a -v -P -k example1.pco -C"p(cobsql) csp CSQLT==syb endp"
Informix
cob -a -k demo1.eco -C "p(cobsql) cobsqltype==informix-new endp"
If you experience problems using COBSQL, first of all check the following:
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.
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.
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.
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.
Check that a preprocessed application runs correctly. Pass the expanded program through the COBOL Compiler and then try to run it.
Try COBSQL with minimal directives. 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 Product Support. To help us locate the cause of the problem:
If you cannot locate the source of the problem, then check each of the following:
Ensure that you are using the latest version of all the products involved.
Check the vendor's documentation and example applications.
Check that environment variables, PATH and configuration file settings are set up correctly.
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.
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.
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.
* CSQL-E-024: Encountered an I/O on file filename * CSQL-E-023: File Status 3 / 5
where filename is the name of the file produced by the database precompiler.
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.
You can use Oracle Pro*COBOL 1.8 or Oracle Pro*COBOL 8.x.
If you are troubleshooting Oracle problems, consider the following:
DBMS HOLD_CURSOR MAXOPENCURSORS MODE RELEASE_CURSOR
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.
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/precomp/admin/pcbcfg.cfg where:
| $ORACLE_HOME | is the root directory for the Oracle installation on your machine |
To use COBSQL with Oracle 8, you should use the following directives:
| Directive |
Description |
|---|---|
| CBL2ORA8 C28 |
This puts calls into the Oracle 8 specific support modules ora8prot and ora8lib. Both of these modules are built into csqlsupp.dll. |
| COBSQLTYPE CSQLT |
EXEC SQL preprocessor. Use the options ORACLE8 and ORA8 to use Pro*COBOL 8.x with COBSQL. |
If you are migrating programs from Pro*COBOL 1.x to 8.x, or developing new programs,, you should be aware of the following:
Define all inserted variables as GLOBAL including the data items inserted by COBSQL that support the EBCDIC to ASCII conversions.
If the Oracle directive DECLARE_SECTION=NO is set (the default), Oracle converts all COMP, BINARY or COMP-4 data items to COMP-5.
To limit the conversion of items to the declare section, set one of the following:
PACKED-DECIMAL
These data items are treated in the same way as COMP-3 data
items.
COMP-4
PIC 9(4) COMP / COMP-4 / BINARY / COMP-5
PIC 9(4) USAGE DISPLAY
PIC s9(4) USAGE DISPLAY SIGN TRAILING
PIC s9(4) USAGE DISPLAY SIGN TRAILING SEPARATE
PIC s9(4) USAGE DISPLAY SIGN LEADING
PIC s9(4) USAGE DISPLAY SIGN LEADING SEPARATE
This type was previously supported as the Oracle DISPLAY data
type.
Pro*COBOL 8.x rejects some Micro Focus COBOL language extensions, data definitions and section headings:
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:
cob PROG -C "p(htmlpp) preprocess(cobsql) csqlt==oracle8
endp"
whereas this line does not:
cob PROG -c "preprocess(cobsql) csqlt==oracle8 p(htmlpp)
endp"
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.
Before using Sybase, you must make the following changes to your system:
If you are troubleshooting Sybase problems, consider the following:
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:
[aix] locale = C, us_english, iso_1 locale = En_US, us_english, iso_1 locale = en_US, us_english, iso_1 locale = default, us_english, iso_1
then a possible LANG setting for US English would be:
LANG=en_US
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:
[aix] locale = C, us_english, iso_1 locale = En_US, us_english, iso_1 locale = en_US, us_english, iso_1 locale = default, us_english, iso_1
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.
copyext(eco,mf2,cob,cpy,cbl) osext(eco)
Copyright © 2002 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names
used herein are protected by international law.
| DB2 | Reserved Keywords | |