Dynamic SQL

DB2

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:

• The ODBC drivers provided with Server Express are licensed for development use only. You may use these drivers to develop Server Express applications but you may not distribute the drivers to your users. To redistribute ODBC drivers with your application, you will need run time licenses for the drivers. Contact your MERANT sales representative for information on obtaining licenses for run time distribution of the ODBC drivers.

• An Informix 7 driver is not available for AIX. Therefore, Informix 7 clients are not supported on AIX.

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:

• 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:

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.

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

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:
   • Manually edit the file odbc.ini. For more information, refer to the appendix DataDirect ODBC Drivers.

   • Use the utiliy esqlconf to edit the file odbc.ini. For more information, refer to the section Demonstration Applications in this chapter.

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:

• 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

• esqlconf.cbl

  This program is a configuration program to assist you in setting up the file odbc.ini. The file odbc.ini contains information on the data sources that will be used by your program if you use OpenESQL.

  The program is provided in source form so that you can add information about additional ODBC drivers you may have or add detection for additional database clients. If your application uses OpenESQL, you may also wish to use some of the code from esqlconf in the installation routines for your application to ensure that the environment is correctly set up for your users.

  Esqlconf looks for an odbc.ini file, first on the ODBCINI environment variable, then in your HOME directory and finally in the local directory. If a file is found, you are given the option of editing it or creating a new file. If no file is found, you are asked if you wish to create a new file. A new file is always created in the current directory.

  Esqlconf provides the following options:

  • Edit an existing entry for a data source in odbc.ini.

    This allows you to change details about a data source.

  • Add a new data source.

  • Remove a data source from odbc.ini.

  • Test a data source.

    This will validate the environment to ensure that the ODBC driver can be loaded. Then if you are using Oracle, Sybase or Informix databases, it will validate that database client software is installed. Finally, it will attempt to connect to the data source.

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.

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:

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

Dynamic SQL

DB2