Dynamic SQL

OpenESQL Assistant

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.

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.

Notes:

• The ODBC drivers provided with Server Express are licensed for development use only. You may use these drivers to develop Net Express applications but you may not distribute the drivers to your users. To redistribute ODBC drivers with your application, you need run-time licenses for the drivers. Contact your Micro Focus sales representative for information on obtaining licenses for run-time distribution of the ODBC drivers. See List of Key Features in your Getting Started for more detailed licensing information.

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

• You can use the $SET statement in your program.
  For example:

  $set sql(dbman=odbc, datecheck, autocommit) 

• You can use the Net Express Advanced Directives screen. Select Build Settings from the Project menu, select your program, click on the Compile tab and then press the Advanced button.

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.

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.

• Explicit connection (recommended method)

  The CONNECT statement is typically used if 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 to automatically connect the program to the data source specified in the DB option of the SQL Compiler directive using the login information specified in the PASS option.

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:

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.

• connect.app

  Prompts for a data source, user name and password. Enter a data source name of "NetExpress Sample2", a user name of "admin" and leave the password blank (just press return). Four tests which perform connects and disconnects using a variety of syntax options are run. The fifth test displays an SQL Data Sources dialog. Select "NetExpress Sample2" (from the Machine Data Source list) and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. The fifth test is run and the program terminates.

• select.app

  Connects to the sample database and prompts for a customer code. Enter BLUEL (as the prompt suggests). Two fields from the customer record are displayed and the program prompts for another customer code. Just press the return key this time. The program prompts for a region. Enter CA (as the prompt suggests). The program displays a list of customers in that region and prompts for another region. This time, press the return key to terminate the program.

• static.app and dynamic.app

  Both applications run the same sequence of tests, but using different SQL syntax options. They both start by prompting for a data source and user name. Enter "NetExpress Sample2" and "admin" respectively. The test sequence is:

  connect
  drop test table
  create test table
  insert a row
  commit
  update the row
  read and verify
  rollback
  read and verify
  drop test table
  disconnect
  create test table

  Step two may output an error message - this is expected and the programs will continue. The final stage should produce an error message, and again this is not treated as a genuine error (though the absence of an ODBC error is treated as a test failure).

• whenever.app

  Attempts to connect and displays an error message. Displays an SQL Data Sources dialog. Select "NetExpress Sample2" and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. Tests error handling and outputs two error messages.

• catalog.app

  Displays an SQL Data Sources dialog. Select "NetExpress Sample2" and click on OK. A Login dialog is displayed. Enter a login name of "admin", leave the password blank and click on OK. Performs three data dictionary queries and outputs the results.

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).
...
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 NetExpress
   EXEC SQL
      CONNECT TO 'NetExpress 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 tyoes.
 * 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 source directory under your Net Express base installation directory. To include it 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 Dynamic SQL

The demonstration application, dynamic.app 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

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

Dynamic SQL

OpenESQL Assistant