PreviousDynamic SQL DB2Next

Chapter 7: OpenESQL

OpenESQL is an integrated preprocessor that enables you to use Embedded SQL in your COBOL applications to access ODBC-enabled data sources.

Unlike separate preprocessors, OpenESQL is controlled by specifying the SQL directive when you compile your application.

7.1 ODBC Drivers and Database Client Software

A program that uses OpenESQL uses an ODBC driver at run time to access a relational database. A set of high performance ODBC drivers is provided with Server Express that provide access to the leading relational database systems. When you install Server Express, the ODBC drivers are an optional component. To confirm that they have been installed, look for the sub-directory odbc under the main directory COBDIR .

Full documentation on configuring the ODBC drivers is included in the appendix DataDirect ODBC Drivers. This appendix supplies information on what is required for each ODBC driver. For some ODBC drivers, it may also be necessary to install database client software.

The utility esqlconf is provided to help set up the environment information and test the connection to the database. For more information on esqlconf, refer to the section Demonstration Applications in this chapter.


Notes:


7.1.1 Oracle OCI Support

If you are using an Oracle database and compile with the directive SQL(TARGETDB=ORACLEOCI) then, at run time, the application will make OCI calls rather than ODBC calls. This means that the deployed application will not require an ODBC driver, which has potential cost and performance benefits.

When you use Oracle OCI, you should be aware that the following OpenESQL functions are not supported:

When you use Oracle OCI, the CONNECT statement references the actual ORACLE database, since the ODBC data source name is not applicable.

7.2 SQL Compiler Directive

When you compile a program that includes Embedded SQL statements, you must specify the compiler directive SQL and appropriate options. The ODBC driver that your program calls depends on the particular data source that you are accessing.

You can specify the SQL directive anywhere where you can specify any other compiler directive, that is:


Note: You cannot use a mixture of these methods - you must use one only.


The SQL compiler directive options are given below:

Option Description
[NO]ANSI92ENTRY If this is set, OpenESQL conforms to the SQL ANSI 92 entry level standard.
[NO]AUTOCOMMIT If this is set, each SQL statement is treated as a separate transaction and is committed immediately upon execution. If this is not set, and the ODBC driver you are using supports transactions, statements must be explicitly committed (or rolled back) as part of a transaction.
[NO]CHECK If this is set, each SQL statement is sent to the database at compilation time. If you specify statement checking at compilation time, you must also set DB and PASS.
CONNECTIONPOOL=[DRIVER | ENVIRONMENT | NONE] Default is NONE. Enables use of ODBC 3.0 connection pooling. When a connection is closed, the Driver Manager actually keeps it alive for a timeout period, and saves the overhead of re-establishing a connection from scratch if the application re-opens an identical connection. ODBC allows you to choose between having a pooling for an ODBC environment or for each driver. See your ODBC documentation for details. This option is only useful for applications that frequently open and close connections. Note that some environments, such as Microsoft Transaction Server (MTS), control connection pooling themselves. This option will probably improve the performance of ISAPI applications that are not running under MTS.
[NO]CURSORCASE If ESQLVERSION is 2.0, CURSORCASE is implied. Default is NOCURSORCASE which means that cursor names are not case sensitive. CURSORCASE means that they are case sensitive. Note that in previous versions of OpenESQL, cursor names have been case sensitive.
[NO]DB The name of the data source to connect to. This option works in conjunction with the INIT and/or CHECK options.
DBMAN=preprocessor Specifies the preprocessor to use. This should always be set to odbc, that is dbman=odbc.
The .int file generated is the same except for the TARGETDB number embedded in the file.
[NO]DETECTDATE Default is NODETECTDATE. If DETECTDATE is set, OpenESQL inspects character host variables for ODBC escape sequences:
{d<data>} - date
{t<data>} - time
{ts<data>} - timestamp
and binds the parameter appropriately, rather than as a character column. This is necessary if your server does not have a suitable native character string date representation (for example, Microsoft Access). It is also useful for generic applications. It can, however, cause problems if you have other character columns that can legitimately contain data that starts with "{d", "{t" or "{ts".
[NO]ESQLVERSION Sets OpenESQL syntax level.
[NO]INIT If this is set, the preprocessor automatically generates code to make the connection to the database. If you specify INIT, you must also specify DB and PASS.
[NO]NIST If this is set, OpenESQL will conform to the NIST interpretation of the SQL ANSI 92 entry level standard.
ODBCTRACE= [ALWAYS | NEVER | USER] Default is USER. ODBCTRACE=USER enables you to control ODBC tracing via the ODBC control panel from which you can specify the file that the trace goes into. ALWAYS lets you control ODBC tracing via a directive, which is more convenient from within the IDE. ALWAYS generates the trace into MFSQLTRACE.LOG in the current directory, regardless of the settings on the ODBC Control Panel. Under normal development conditions, and depending on the project's build setting, this is the Debug or Release directory of the current project. NEVER means that the application will never be traced and overrides the control panel. As ODBC trace files can contain sensitive information, use NEVER in production applications in secure environments.
[NO]PARAMARRAY Default is PARAMARRAY. If PARAMARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all input parameters.
[NO]PASS The login to use to connect to the data source. This option works in conjunction with the INIT and/or CHECK options.
[NO]RESULTARRAY Default is RESULTARRAY. If RESULTARRAY is set, ODBC array binding is used, if it is supported by the ODBC driver, for all output parameters.
[NO]TARGETDB Specifies that only one database engine will be used. OpenESQL will then optimize for that particular database. Two options are currently supported: specify TARGETDB=ORACLE7 if you are using Oracle V7.x; specify TARGETDB=ORACLEOCI if you want to use the Oracle OCI instead of the ODBC driver.
THREAD=[SHARE | ISOLATE] Default is SHARE. If THREAD is set to ISOLATE, all connections, cursors and so on are local to the thread that creates them. This is required for multi-threaded application server environments such as IIS/ISAPI. With THREAD=SHARE, if you have a hard-coded CONNECT statement and thread 1 executes it and then thread 2 executes it, thread 2 gets an error because the connection is already open. With THREAD=ISOLATE, each thread gets its own connection.

7.3 Data Sources

When you install the ODBC drivers, the file odbc.ini has several data sources set up. The dBase data source looks like:

[dBase]
Driver=INSTALLDIR/lib/C5dbf14.sl
Description=dBase
Database=INSTALLDIR/demo

You should replace the two occurrences of INSTALLDIR with the directory path on which you installed ODBC. For example, if the ODBC directory is /home/fritz/odbc, you would use:

[dBase]
Driver=/home/fritz/odbc/lib/C5dbf14.sl
Description=dBase
Database=/home/fritz/odbc/demo

7.4 Database Connections

Before your program can access any data in a database, it must make a connection to the database.

There are two methods your program can use to connect to a database.

When your application has finished working with a database, the application should disconnect from the database. This is done using the DISCONNECT statement.

If implicit connection is being used, OpenESQL automatically disconnects from the data source when the program terminates.

If you want OpenESQL to perform an implicit disconnect and rollback in the event of abnormal program termination, this can be achieved by specifying the INIT=PROT option of the SQL Compiler directive.

7.5 Keywords

A number of keywords are recognized by OpenESQL and should not therefore be used within your program for other purposes. A full list of reserved keywords is given in the appendix Reserved Keywords in this book.

7.6 Building an Application

The steps needed to create an OpenESQL application are:

  1. Code your application. If you also have Micro Focus Net Express, you may find it easier to develop your application on a personal computer using the OpenESQL Assistant and then publish it to your UNIX system. The Net Express OpenESQL Assistant provides a graphical, drag-and-drop means of developing SQL statements.

  2. Compile your application, ensuring that you specify the SQL directive and appropriate options. As a minimum, specify SQL.

  3. Use one of the following methods to configure the ODBC data source that your application will be using:

The copy files sqlca.cpy and sqlda.cpy are provided in the directory $COBDIR/cpylib. You should ensure that this directory is included in the environment variable COBCPY.

The OpenESQL run time module is odbcrunx.so for non-threaded applications or odbcrunx_t.so for multi-threaded applications. You should ensure that this module is included with your application when it is distributed.

7.7 Demonstration Applications

A number of demonstration applications are supplied in the directory openesql, which is located in the directory demo under your Server Express base installation directory.

Before you can use any of the demonstration applications, you need to have installed at least one ODBC driver. A number of ODBC drivers are installed automatically with Server Express.

Some of the demonstration applications expect that a table called EMP exists on the database to which you are connecting. Scripts are provided to create this table on different database engines in the directory odbc/demo under $COBDIR. If you use the file odbc.ini supplied with Server Express, a sample Dbase database is already setup. To use this database when you run the program:

  1. When prompted to enter a data source name, enter SampleDbase

  2. Leave the user name and password blank.

The OpenESQL demonstration applications all produce a console log displaying their progress and, possibly, query results. They all terminate on receipt of an error, after displaying an error message.

The following programs are provided:

7.8 Managing Transactions

With OpenESQL, you can use the COMMIT and ROLLBACK statements to exploit the transaction control facilities of ODBC. Although ODBC specifies transaction AUTOCOMMIT after each statement as the default mode of operation, OpenESQL turns this off for greater compatibility with other SQL systems. If you require this functionality, specify the AUTOCOMMIT option of the SQL Compiler directive.


Note: Not all ODBC drivers implement transaction processing. Those that do not implement transaction processing may make immediate and permanent updates to the database.


7.9 Data Types

The following table shows the mappings used by OpenESQL when converting between SQL and COBOL data types.

SQL Type
COBOL Picture
Notes
SQL_CHAR(n)
PIC X(n)

SQL_VARCHAR(n)
PIC X(n)

SQL_LONGVARCHAR
PIC X(max) max = 32K
SQL_DECIMAL(p,s) PIC 9(p-s)V9(S) COMP-3 p = precision (total number of digits).
s = scale (number of digits after the decimal point).
SQL_NUMERIC(p,s) PIC 9(p-s)V9(S) COMP-3
SQL_SMALLINT PIC S9(4) COMP-5
SQL_INTEGER PIC S9(9) COMP-5
SQL_REAL COMP-2
SQL_FLOAT COMP-2
SQL_DOUBLE COMP-2
SQL_BIT PIC S9(4) COMP-5
SQL_TINYINT PIC S9(4) COMP-5
SQL_BIGINT PIC S9(18) COMP-3
SQL_BINARY(n) PIC X(n)
SQL_VARBINARY(n) PIC X(n)
SQL_LONVARBINAR PIC X(max)
SQL_DATE PIC X(10) yyyy-mm-dd
SQL_TIME PIC X(8) hh:mm:ss
SQL_TIMESTAMP PIC X(26) yyyy-mm-dd hh:mm:ss.ffffff

The format of an ODBC date is yyyy-mm-dd, and an ODBC time is hh:mm:ss. These may not correspond to the native date/time formats for the data source in use. For input character host variables, native data source date/time formats can be used. For most data sources, we recommend a picture clause of PIC X(26), for example:

01  mydate      PIC x(26).
...
EXEC SQL
   INSERT INTO TABLE1 VALUES (1,'1997-01-24 12:24')
END-EXEC
...
EXEC SQL
   SELECT DT INTO :mydate FROM TABLE1 WHERE X = 1
END-EXEC
DISPLAY mydate

Alternatively, you can use ODBC escape sequences. ODBC defines escape sequences for date, time and timestamp literals. These escape sequences are recognized by ODBC drivers which replace them with data source specific syntax.

The escape sequences for date, time and timestamp literals take the form:

{d 'yyyy-mm-dd'} - for date.
{t 'hh:mm:ss'} - for time.
{ts yyyy-mm-dd hh:mm:ss[.f...] - for timestamp.

The example program below shows date, time and timestamp escape sequences being used:

 working-storage section.
 EXEC SQL INCLUDE SQLCA END-EXEC

 01  date-field1      pic x(26).
 01  date-field2      pic x(26).
 01  date-field3      pic x(26).

 procedure division.
* Connect to the data source.  This is one of the Sample
* datasources supplied with Server Express
     EXEC SQL
         CONNECT TO 'Server Express Sample1' USER 'admin'
     END-EXEC
* If the Table is there drop it.
     EXEC SQL
         DROP TABLE DT
     END-EXEC

* Create a table with columns for DATE, TIME and DATE and 
* TIME
* NOTE:  Access uses DATETIME column for all three.
*        Some databases will have dedicated column types.
* If you are creating DATE/TIME columns on another data 
* source, refer to your database documentation to see how to 
* define the columns.

     EXEC SQL
         CREATE TABLE DT ( id  INT,
                           myDate DATE NULL,
                           myTime TIME NULL,
                           myTimestamp TIMESTAMP NULL)
     END-EXEC

* INSERT into the table using the ODBC Escape sequences

     EXEC SQL
         INSERT into DT values (1 ,
               {d '1961-10-08'},  *> Set just the date part
               {t '12:21:54'  },  *> Set just the time part
               {ts '1966-01-24 08:21:56' } *> Set both parts
                   )
     END-EXEC

* Retrieve the values we just inserted
   
     EXEC SQL
         SELECT myDate
               ,myTime
               ,myTimestamp
           INTO
                :date-field1
               ,:date-field2
               ,:date-field3
           FROM DT
           where id = 1
     END-EXEC

* Display the results.

     display 'where the date part has been set :' 
             date-field1
     display 'where the time part has been set :' 
             date-field2
     display 'NOTE, most data sources will set a default'
             ' for the date part '
     display 'where both parts has been set :' 
             date-field3

* Remove the table.
   
     EXEC SQL
         DROP TABLE DT
     END-EXEC

* Disconnect from the data source

     EXEC SQL
         DISCONNECT CURRENT
     END-EXEC

     stop run.

7.10 Using the SQLCA

The SQLCA data structure is included in the file sqlca.cpy in the cpylib directory under your Server Express base installation directory. To include the SQLCA data structure in your program, use the following statement in the data division:

EXEC SQL INCLUDE SQLCA END-EXEC

If you do not include this statement, the COBOL Compiler automatically allocates an area, but it is not addressable from within your program. However, if you declare either of the data items SQLCODE or SQLSTATE separately, the COBOL Compiler generates code to copy the corresponding fields in the SQLCA to the user-defined fields after each EXEC SQL statement.

If you declare the data item MFSQLMESSAGETEXT, it is updated with a description of the exception condition whenever SQLCODE is non-zero. MFSQLMESSAGETEXT must be declared as a character data item, PIC X(n), where n can be any legal value. This is particularly useful, as ODBC error messages often exceed the 70-byte SQLCA message field.


Note: You do not need to declare SQLCA, SQLCODE, SQLSTATE or MFSQLMESSAGETEXT as host variables.


7.11 Stored Procedures

OpenESQL supports two statements that are used with stored procedures:

A stored procedure can:


Note: The features provided by different database vendors vary considerably, and any given vendor will offer only a subset of the features listed above. For this reason, stored procedure calls are much less portable between data sources than other OpenESQL statements.


When a stored procedure is called, any parameters are passed as a comma separated list, optionally enclosed in parentheses. A parameter can be a host variable or a literal, or the keyword CURSOR. The keyword CURSOR causes the parameter to be unbound, and should only be used with Oracle 8 stored procedures which return result sets.

If the parameter is a host variable it can be followed by one of the following words, which indicate the parameter type: IN, INPUT, INOUT, OUT, OUTPUT. If no parameter type is specified, INPUT is assumed.

Host variable parameters can be passed as keyword parameters, by preceding the host variable with the formal parameter name and an equals sign:

EXEC SQL CALL myProc (keyWordParam = :hostVar) END-EXEC

For maximum portability:

If a stored procedure call returns a result set, it must be used in a cursor declaration, thus:

EXEC SQL DECLARE cursorName CURSOR FOR storedProcecureCall

The stored procedure is then called by OPENing the cursor and FETCHing result set rows, like any other type of cursor.

Currently OpenESQL supports only a single result set.

ODBC parameters differ from Oracle array parameters. The effect of using a parameter array is the same as repeating the statement for each element of the array. On a stored procedure call, if one parameter is passed as an array, then all parameters must be arrays with the same number of elements. The stored procedure will "see" one call for each "row" of parameters. The number of rows passed can be limited to less than the full array size by preceding the call with the phrase FOR :hvar where :hvar is an integer host variable containing a count of the number of rows to be passed.


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

PreviousDynamic SQL DB2Next