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.

Oracle OCI Support

OpenESQL provides an alternative for developers using ORACLE data sources, in the form of the ORACLE OCI interface. ORACLE OCI provides faster processing of SQL statements than the more generic ODBC interface that OpenESQL normally uses. To use this interface, you must compile your applications with the following directive:

sql(targetdb=ORACLEOCI)

When connecting to the Oracle server, use an Oracle Net8 service name in place of an ODBC data source name in the CONNECT statement. See your Oracle documentation on how to set up ORACLE Net8 services.

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

• BEGIN TRANSACTION statement
• CALL statement
• EXECSP statement
• QUERY ODBC statement
• SET AUTOCOMMIT statement
• SET TRANSACTION ISOLATION statement
• Scrolling cursors (including SET SCROLLOPTION and SET CONCURRENCY statements)
• WITH PROMPT parameter on CONNECT statement (not available on UNIX)
• SQL (CONNECTIONPOOL) directive.

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:

• in a $SET statement. For example:

  $set sql(dbman=odbc, autocommit) 

• on the cob command line, eg:

  cob -V testconn.cbl -C"anim SQL(DBMAN=ODBC)"

• in a COBOL.DIR file.

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

The SQL Compiler directive options are given below:

Creating Debug Files

If an error occurs when compiling a program that requires technical support, your support representative might ask you to provide additional debug files to help in determining the cause of the problem. You obtain these debug files by specifying extra SQL Compiler directives. You might want to specifiy some of these directives to help in your own debugging efforts. The directives are:

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.

• Explicit connection (recommended method):

  The CONNECT statement is used typically if either of the following is true:

  • The program is designed to access different data sources whose names are not known at compilation time.

  • The program is going to access multiple databases.

• Implicit connection:

  This is generally used if your program is only going to connect to one database which is known at compilation time. If you specify the INIT option of the SQL Compiler directive, the Compiler inserts a call at the start of the program. This call connects the program automatically to the data source specified in the DB option of the SQL Compiler directive and uses the login information specified in the PASS option.

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.

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.

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. 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 using the SQL Compiler directive.

3. Configure the ODBC data source that your application will be using. Refer to your ODBC driver vendors' documentation for details on how to achieve this.

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.

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.

Some of the demonstration applications expect that a table called EMP exists on the database to which you are connecting.

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:

• testconn.cbl

  The file testconn.cbl is an OpenESQL demonstration program that shows how to use different formats of the CONNECT and DISCONNECT statements.

• catalog.cbl

  The file catalog.cbl is an OpenESQL demonstration program that shows how to use the ODBC catalog functions from COBOL.

• static.cbl

  The file static.cbl is an OpenESQL demonstration program that shows how to use basic SQL functions from COBOL. It demonstrates the use of INSERT, UPDATE, DELETE, SELECT INTO and SELECT using a cursor. It also shows the use of COMMIT and ROLLBACK.

• dynquery.cbl

  The file dynquery.cbl is an OpenESQL demonstration program that shows how to use dynamic SQL functions from COBOL. It provides the capability for the user to enter any SQL statement and the program will execute them. It shows how to prepare a SQL statement and how to use the SQLDA structure to retrieve data from columns when the type of the column is not known when the program is compiled.

  If you are accessing the EMP table described above, a sample query would be:

  select FIRST_NAME, LAST_NAME, HIRE_DATE, SALARY from EMP

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. For more information see your database driver documentation.

Data Types

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

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(29), for example:

01  mydate      PIC X(29).
...
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(29).
 01  date-field2      pic x(29).
 01  date-field3      pic x(29).

 procedure division.

     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.

Alternatively you can use host variables defined with SQL TYPEs for date/time variables. Define the following host variables:

01  my-id          pic s9(09) comp-5
01  my-date        sql type is date
01  my-time        sql type is time
01  my-timestamp   sql-type is timestamp

and replace the INSERT statement with the following code:

* Insert into the table using SQL TYPE host variables
     MOVE 1                           TO  my-id
     MOVE "1961-10-08"                TO  my-date
     MOVE "12:21:54"                  TO  my-time
     MOVE "1966-01-24 08:21:56"       TO  my-timestamp
 
     EXEC SQL
        INSERT into DT vlaue (
           :my-id
          ,:my-date
          ,:my-time
          ,:my-timestamp  )
     END-EXEC

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.

Stored Procedures

OpenESQL supports two statements that are used with stored procedures:

• CALL

  Provides generic support for ODBC stored procedure calls.

• EXECSP

  Provides backwards compatibility with the Micro Focus Embedded SQL Toolkit for Microsoft SQL Server.

A stored procedure can:

• Accept input parameters.
• Return output parameters.
• Accept and return input/output parameters.
• Use positional or keyword parameters.
• Return a result.
• Return result sets.
• Be called with parameter arrays.

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:

• Avoid using literal parameters.

• Use host variable parameters only.

• Ensure that given calls use one of normal, positional or keyword parameters, but not a mixture of these. Whilst some servers do support a mixture of these parameters, any 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 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.

Positioned Update

ODBC supports positioned update, which updates the row most recently fetched by using a cursor. However, not all drivers provide support for positioned update.

Note: You cannot use host arrays with positioned update.

With some ODBC drivers, the select statement used by the cursor must contain a FOR UPDATE clause to enable positioned update. Most data sources require specific combinations of SCROLLOPTION and CONCURRENCY to be specified either by SET statements or in the DECLARE CURSOR statement. If this fails to work, the ODBC Cursor Library provides a restricted implementation of positioned update which can be enabled by compiling with the directive SQL(USECURLIB=YES) and using SCROLLOPTION STATIC and CONCURRENCY OPTCCVAL (or OPTIMISTIC). To avoid multiple rows being updated when using the ODBC Cursor Library, the cursor query should include the primary key column(s) for the table to be updated.

Example

$SET SQL(usecurlib=yes)
  WORKING-STORAGE SECTION.
 
  EXEC SQL INCLUDE SQLCA  END-EXEC
 
  *> after an sql error this has the full message text
  01 MFSQLMESSAGETEXT  PIC X(250).
  01 IDX               PIC X(04)  COMP-5.

  EXEC SQL BEGIN DECLARE SECTION  END-EXEC
  *> Put your host variables here if you need to port
  *> to other COBOL compilers
  EXEC SQL INCLUDE Products END-EXEC
 
  EXEC SQL END DECLARE SECTION END-EXEC
 
  PROCEDURE DIVISION.
 
  EXEC SQL
      WHENEVER SQLERROR perform OpenESQL-Error
  END-EXEC
          
  *> Demo for positioned updates using ACCESS datasource  
  EXEC SQL
    CONNECT TO 'Inventory' USER 'admin'
  END-EXEC
 
  *> Put your program logic/SQL statements here
 
      EXEC SQL
       DECLARE CSR679 CURSOR
         FOR SELECT
              A.ProductID
             ,A.ProductName
             ,A.UnitPrice
         FROM Products A
        WHERE ( A.ProductID <  3 )
      END-EXEC
 
     EXEC SQL SET SCROLLOPTION static  END-EXEC
     EXEC SQL SET CONCURRENCY optccval END-EXEC
 
      EXEC SQL OPEN CSR679 END-EXEC
      PERFORM UNTIL SQLSTATE >= "02000"
        EXEC SQL
        FETCH CSR679 INTO
            :ProductID
           ,:ProductName:ProductName-NULL
           ,:UnitPrice:UnitPrice-NULL
        END-EXEC
        *> Process data from FETCH
        IF SQLSTATE = "00000"
           *> increase price by 10%
            compute unitprice = unitprice * 1.10
            EXEC SQL
               UPDATE Products
                 SET UnitPrice = :UnitPrice:UnitPrice-NULL
               WHERE CURRENT OF CSR679
           END-EXEC
        END-IF
      END-PERFORM
      EXEC SQL CLOSE CSR679 END-EXEC
 
     EXEC SQL COMMIT END-EXEC
 
  EXEC SQL DISCONNECT CURRENT END-EXEC
  EXIT PROGRAM.
  STOP RUN.
  *> Default sql error routine / modify to stop program if needed
  OpenESQL-Error Section.
 
      display "SQL Error = " sqlstate " " sqlcode
      display MFSQLMESSAGETEXT
  *> stop run
      exit.

Unicode Support in OpenESQL

Some applications want to retrieve or store Unicode data in Microsoft SQL Server data sources without it being converted to ANSI. Previous versions of OpenESQL did not have a way of accessing data without it being automatically converted. Now, however, OpenESQL and Server Express can work directly with Unicode data without having it converted to ANSI, by using a new type of host variable. Microsoft SQL Server supports three Unicode column types:

• NCHAR
• NVARCHAR
• NTEXT

To access these columns without the data being automatically converted, define a host variable with a definition of:

PIC N(xx) USAGE NATIONAL

where xx is the size of the column. This format is currently supported for both fixed and variable length data. Variable length data can be terminated with nulls to signify end of data for column when inserting or updating data. When data is retrieved from the data source, it will be space filled to the end of the host variable.

For example, the following program will retrieve employee information from the Northwind sample database that comes with Microsoft SQL Server 2000 product:

       $SET UNICODE(NATIVE)
       $SET SQL
       WORKING-STORAGE SECTION.

       EXEC SQL INCLUDE SQLCA  END-EXEC

       *> after an sql error this has the full message text
       01 MFSQLMESSAGETEXT  PIC X(250).
       01 IDX               PIC X(04)  COMP-5.

       EXEC SQL BEGIN DECLARE SECTION  END-EXEC
       *> Put your host variables here if you need to port
       *> to other COBOL compilers
       EXEC SQL INCLUDE Employees END-EXEC

       EXEC SQL END DECLARE SECTION END-EXEC

       PROCEDURE DIVISION.

       EXEC SQL
           WHENEVER SQLERROR perform OpenESQL-Error
       END-EXEC

       EXEC SQL
         CONNECT TO 'LocalServer'
       END-EXEC

       *> Put your program logic/SQL statements here
            EXEC SQL
             DECLARE CSR135 CURSOR FOR SELECT
                    A.FirstName
                   ,A.LastName
                   ,A.EmployeeID
                   ,A.HireDate
               FROM Employees A
            END-EXEC
            EXEC SQL OPEN CSR135 END-EXEC
            PERFORM UNTIL SQLSTATE >= "02000"
              EXEC SQL
              FETCH CSR135 INTO
                  :Employees-FirstName
                 ,:Employees-LastName
                 ,:Employees-EmployeeID
                 ,:Employees-HireDate:Employees-HireDate-NULL
              END-EXEC

              *> Process data from FETCH
              IF SQLSTATE < "02000"

              *> for array fetches, field sqlerrd(3) contains the number
              *> of rows returned
              *>  PERFORM VARYING IDX FROM 1 BY 1 UNTIL IDX > SQLERRD(3)

              *>  you will need to add code here to process the array

              *>  END-PERFORM
               END-IF
            END-PERFORM
            EXEC SQL CLOSE CSR135 END-EXEC

       EXEC SQL DISCONNECT CURRENT END-EXEC
       EXIT PROGRAM.
       STOP RUN.
       *> Default sql error routine / modify to stop program if needed
       OpenESQL-Error Section.

           display "SQL Error = " sqlstate " " sqlcode
           display MFSQLMESSAGETEXT
       *> stop run
           exit.

This is the same code that would retrieve the data in ANSI except for the definitions in the INCLUDE copybook Employees which now looks like:

      *>  --------------------------------------------------------------
      *> COBOL DECLARATION FOR TABLE Employees
      *>  --------------------------------------------------------------
       01  DCLEmployees.
           03 Employees-EmployeeID             PIC S9(09)  COMP-5.
           03 Employees-LastName               PIC N(20)
                                   USAGE NATIONAL.
           03 Employees-FirstName              PIC N(10)
                                   USAGE NATIONAL.
           03 Employees-Title                  PIC N(30)
                                   USAGE NATIONAL.
           03 Employees-TitleOfCourtesy        PIC N(25)
                                   USAGE NATIONAL.
           03 Employees-BirthDate              PIC X(23).
           03 Employees-HireDate               PIC X(23).
           03 Employees-Address                PIC N(60)
                                   USAGE NATIONAL.
           03 Employees-City                   PIC N(15)
                                   USAGE NATIONAL.
           03 Employees-Region                 PIC N(15)
                                   USAGE NATIONAL.
           03 Employees-PostalCode             PIC N(10)
                                   USAGE NATIONAL.
           03 Employees-Country                PIC N(15)
                                   USAGE NATIONAL.
           03 Employees-HomePhone              PIC N(24)
                                   USAGE NATIONAL.
           03 Employees-Extension              PIC N(4)
                                   USAGE NATIONAL.
           03 Employees-Photo                  PIC X(64000).
           03 Employees-Notes                  PIC N(32000)
                                   USAGE NATIONAL.
           03 Employees-ReportsTo              PIC S9(09)  COMP-5.
           03 Employees-PhotoPath              PIC N(255)
                                   USAGE NATIONAL.

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