Problem:

MySQL is the worlds most popular open source database because of its consistent fast performance, high reliability and ease of use.

Can MySQL database be used with Net Express and Server Express?

Resolution:

Although the product documentation talks about specific databases such as DB2, Informix, Oracle and Sybase, it is possible to use any RDMS with OpenESQL and the relevant ODBC driver.

Pre-requisites:

1.

An ODBC driver manager such as unixODBC

2.

An ODBC driver such as the one available from MySQL (version 3.51 was used to test the demo programs mentioned in this article)

The unixodbc.org and mysql.com websites provide comprehensive documentation on installing and configuring the manager and driver.

Please refer to $COBDIR/docs/readme.txt for details on the platforms supported by OpenESQL.

Minimum ODBC configuration when connecting to database on local system:

If using unixODBC the following configuration information goes into odbcinst.ini.  This can be done either by editing the file directly or via ODBCConfig.  N.B. User needs to be root to ensure permissions are correct otherwise an error will occur.

[MySQL ODBC 3.51 Driver]

Description     =

Driver          = /usr/local/lib/libmyodbc3.so

Driver64        = /usr/local/lib64/libmyodbc3.so

Setup           = /usr/local/lib/libmyodbc3S.so

Setup64         = /usr/local/lib64/libmyodbc3S.so

UsageCount      = 1

CPTimeout       =

CPReuse         =

There must also be an entry in odbc.ini to define the data source.

If using unixODBC this goes in odbc.ini into the same directory as odbcinst.ini for a System DSN or .odbc.ini in the users home directory for a User DSN.  If using ODBCConfig then this is done via the System DNS or User DSN tabs.

[myodbc351]

Driver          = MySQL ODBC 3.51 Driver

DATABASE                = test

DESCRIPTION             = 32 bit ODBC

SOCKET          = /var/lib/mysql/mysql.sock

Using the OpenESQL demonstration programs with MySQL:

Example programs are provided with both Net Express and Server Express that use standard SQL that can be used to interact with any database for which an ODBC driver is availabe.  This includes MySQL.

The locations of these programs is the $COBDIR/demo/openesql directory for Server Express and <install_directory>\\Examples\\Net Express IDE\\ODBCESQL for Net Express.

1.  testconn.cbl (Server Express) / connect.cbl (Net Express)

When running testconn the response to the "Enter data source name" prompt needs to be the name in the odnc.ini configuration file.  For the example above this would be myodbc351.

2.  dynquery.cbl (Server Express) / dynamic.cbl (Net Express)

If you wish to use non ANSI standard SQL then you can do so using the PREPARE and EXECUTE syntax.  The dynquery.cbl demonstration program shows how to do this.  As with testconn, the data source name must match an odbc.ini entry.

3.  static.cbl (both)

For this demo program to work you must change the CREATE TABLE to specify a database engine that creates transaction tables.  With MySQL, if no engine is specified, the MyISAM default engine is used.  This engine is not transactional and so COMMIT and ROLLBACK statements have no effect as any database changes are committed immediately after execution of any SQL.

Example of CREATE TABLE using the InnoDB storage engine:

           EXEC SQL

               create table mfesqltest (

                   char_col char(10),

                   int_col  integer)

               engine = InnoDB

           END-EXEC