PreviousDynamic SQL OpenESQL AssistantNext

Chapter 7: OpenESQL

The OpenESQL preprocessor enables you to access a relational database via an ODBC driver by embedding SQL statements within your COBOL program.

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

7.1 ODBC Drivers and Data Source Names

To obtain ODBC support, you must:

  1. Install ODBC drivers.

  2. Set up an ODBC Data Source Name (DSN).

7.1.1 Installing ODBC Drivers

At the beginning of the Net Express installation, you should indicate that you wish to install a number of ODBC drivers by selecting ODBC drivers from the given list.

Note: Most of these drivers will only work if the Client Software for the specific database is present.

7.1.2 Setting up a Data Source Name

The Net Express ODBC support cannot work until you have set up a data source name (DSN) in the ODBC Manager. You can access the ODBC Manager via the Control Panel on Windows 95, Windows 98, or Windows NT desktop. Click on the appropriate 16bit ODBC or 32bit ODBC icon. Click on the System DSN tab.

The ODBC Data Source Administrator dialog box opens. All those drivers that are installed as a part of Net Express appear with names that begin Net Express .... In addition, Net Express installs the Microsoft Access driver. This driver appears as Net Express Microsoft Access.

For details of how to assign DSNs, click on the Help button in the ODBC Data Source Administrator dialog box.


7.2 SQL Compiler Directive

When you compile your program, you must specify the SQL Compiler directive and its appropriate options such that the preprocessor converts the embedded SQL statements into function calls to the data source. The ODBC driver that your program calls depends on the particular data source that you are accessing.

There are two ways of specifying options for the SQL Compiler directive:

Note: You cannot use a mixture of these methods - you must use one or the other.

The table below lists the SQL Compiler directive options.

DBMAN=preprocessor Specifies the preprocessor to use. This should always be set to odbc, that is dbman=odbc.
[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 case insensitive. 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.
[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 Set 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 OBDC control panel from which you can specify the file that the trace goes into. ALWAYS lets you control OBDC 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]PRE Default is PRE, which causes the preprocessor to generate code to load the OpenESQL runtime module (odbcrw32.dll) dynamically at runtime. This conflicts with the LITLINK compiler directive. So if you use LITLINK, specify NOPRE to stop the dynamic loading code being generated. In this case, you must add odbcrw32.lib to the list of LIBs to be linked in your build settings. Then the linker generates code into the executable which causes the operating system to load odbcrw32.dll implicitly when the executable is loaded.
[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 If you are using either Microsoft SQL Server or Oracle 7.0 as your target database you can set this option in order to optimize performance.
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 Net Express two Data Source Names (DSNs) are created automatically. These are NetExpress Sample1 and NetExpress Sample2 and they point to the sample Access databases (demo.mdb and sample.mdb) that are installed as part of Net Express in the demo\smpldata directory.

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, it 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 online help file. Look under "OpenESQL" in the help file index.

7.6 Building an Application

To build an OpenESQL application, you need to:

  1. Write your application, surrounding your SQL statements with the keywords EXEC SQL and END-EXEC.

  2. Compile your application using the SQL Compiler directive.

  3. Configure a data source via ODBC Data Sources on the Control Panel (see the section Setting up a Data Source Name).

The copyfiles sqlca.cpy and sqlda.cpy are located in the source directory under your Net Express base installation directory and can be included in your program in the normal way.

When you build your .exe, using Net Express, all the necessary object files are linked in for you automatically.

Note: If you move your application to another system, you should ensure that the file odbcrw32.dll is available on that system.

7.7 Demonstration Applications

A number of demonstration applications are supplied in the odbcesql directory which is located in the demo directory under your Net 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 Net Express including a Microsoft Access driver. In addition, two sample Access databases are supplied in the demo\smpldata directory and two data source names which point to them are created automatically when you install Net Express. You can run the demonstration applications against the sample database pointed to by the data source name NetExpress Sample2.

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.

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 and those that do not may make updates to the database permanent immediately.

7.9 Data Types

The online help includes a table which shows the mappings used by OpenESQL when converting between SQL and COBOL data types. Look up Reference, Database Access, OpenESQL, Data Types in the online help contents.

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).
   INSERT INTO TABLE1 VALUES (1,'1997-01-24 12:24')
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.

   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 NetExpress
      CONNECT TO 'NetExpress Sample1' USER 'admin'
 * If the Table is there drop it.

 * 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 tyoes.
 * If you are creating DATE/TIME columns on another data source,
 * refer to your database documentation to see how to define the
 * columns.

      CREATE TABLE DT ( id  INT,
                      myDate DATE NULL,
                      myTime TIME NULL,
                      myTimestamp TIMESTAMP NULL)

 * INSERT into the table using the ODBC Escape sequences

      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

 * Retrieve the values we just inserted
      SELECT myDate
      FROM DT
            where id = 1

 * 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.

 * Disconnect from the data source


   Stop Run.

7.10 Using the SQLCA

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


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 Dynamic SQL

The demonstration application, is located in the odbcesql directory which is in the demo directory under your base Net Express installation directory. Open this project and select Step from the Animate menu (you may need to rebuild the project first) in order to step through the application for a demonstration of how to use dynamic SQL in your COBOL programs.

7.12 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 maxiumun portability, literal parameters should be avoided and only host variable parameters should be used, and a given call should use either only normal, positional parameters or keyword parameters, but not both. Some servers support a mixture, but keyword parameters should occur after all positional parameters. Keyword parameters are useful as an aid to readability and where the server supports default parameter values and optional parameters.

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 fom 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 numer of rows to be passed.

Note: The Net Express online help provides the structure and examples for both the CALL and EXECSP statements. (Click Help Topics on the Help menu. Then, on the Index tab, click CALL or click EXECSP.)

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 OpenESQL AssistantNext