Dynamic SQL

DB2

Chapter 7: OpenESQL

7.2 SQL Compiler Directive

• 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 or the other.

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

[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 the program is designed to access different data sources whose names are not known at compilation time.
  • OR if 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 Reserved Keywords chapter in this book.

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.

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