External Database Access (EDA) Works on Rocket UniVerse 12.1.1 Against MYSQL Server 5.6 or Later

September 18, 2019

Overview

External Database Access (EDA) enables you to convert data stored in a Rocket UniVerse or UniData database to a first normal form (1NF) database, such as SQL Server, Oracle and DB2 Server. You can then access that data using existing UniVerse BASIC programs, RetrieVe, or UniData/UniVerse SQL. UniVerse 12.1.1 supports a new EDA solution against MySQL Server.

In this blog, you will learn how to work with the UniVerse EDA solution on Windows or Linux against MySQL server 5.6. I will provide sample steps to install & configure the setting to connect to MySQL Server 5.6.

Note: You must order an additional UniVerse EDA package license to work on this solution. You can verify the EDA license information using the “uvregen -z” command.

Requirements

MySQL Server Environment with UniVerse EDA

For this new UniVerse EDA solution, use the EDA common driver with the MySQL ODBC driver to connect to MySQL Server running on Windows or Linux. You must set up a MySQL user that can connect from a UniVerse machine to a MySQL Server machine with proper privileges to create and maintain tables. The user can be created via the MySQL Command client or the PhpMyAdmin tool.  MySQL Connector/ODBC is a standard database driver for Windows, Linux, Mac OS and UNIX platforms. It will work with UniVerse on Windows or Linux environments.

Please see more driver information on the MySQL web site– https://dev.mysql.com/downloads/connector/odbc/

Installing the MySQL Connector / ODBC 8.0 client on Windows

You can download the “Windows (x86, 64-bit), MSI Installer” driver from the MySQL download web site. After you’ve installed the driver, you’ll see two drivers listed in the ODBC Data Source Administrator (64-bit) tool.

Create a new MySQL System DSN on the UniVerse Windows machine.

The sample screenshot below shows how to create a MySQL System DSN on a Windows environment.

Set up an EDA driver configuration on Windows

In the Windows UniVerse UV folder, you will manually create a new edaconfig text file. It contains the driver name and loglevel parameters.

Here is the edaconfig file sample to work with MySQL server.

DRIVER=MYSQL

LOGLEVEL=0

You can set the loglevel from 0 to 8.

Set up a new EDA data source on Windows using the EDA Schema Tool

The new EDA Schema Manager Tool (V 4.34.0) supports the new “COM-1NF” EDA driver to work with MySQL server.

The next screenshot shows how to create a new EDA Data Source on a Windows machine with UniVerse 12.1.1. using the EDA Schema Manager.

Installing the unixODBC driver manager on Linux

If you’re running a Linux environment, you’ll need to perform a few more steps to work with MySQL ODBC driver than on a Windows environment. You must install the unixODBC driver manager on your Linux environment first. Some ODBC drivers provider installation of the unixODBC driver manager component. If the ODBC driver is not installed with the unixODBC driver manager, you can find the driver on the unixODBC website – http://www.unixodbc.com/.

If the 64-bit unixODBC is not installed on Linux, you can use the yum command to install it.

The next step is to install the unixODBC driver manager using the yum command. The command must be executed as root mode.

yum install unixODBC.x86_64

After the unixODBC driver is installed, you can use the odbcinst command to verify the installation.

# odbcinst -j

unixODBC 2.3.7

DRIVERS…………: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /root/.odbc.ini

SQLULEN Size…….: 8

SQLLEN Size……..: 8

SQLSETPOSIROW Size.: 8 

Installing the MySQL Connector / ODBC 8.0 client on Linux

The MySQL Connector / ODBC Driver 8.0 can work with RedHat Enterprise Server 6 & 7 and other Linux platforms.

There are several different ways to install the MySQL ODBC 8.0 driver on Linux. You can use the rpm command to install it. It might require some additional system libraries to work with new MySQL Connector ODBC 8.0 driver. For more information, you can find the MySQL ODBC driver and install script on the mysql web site – https://dev.mysql.com/downloads/connector/odbc/

On the MySQL download web site, you should be able to get two rpm packages.

Here is the list for MySQL ODBC 8.0 driver list.

mysql-connector-odbc-8.0.15-1.el7.x86_64.rpm

mysql-connector-odbc-setup-8.0.15-1.el7.x86_64.rpm

You can use the next rpm commands to install the MySQL ODBC 8.0 driver.

rpm -ivh mysql-connector-odbc-8.0.15-1.el7.x86_64.rpm

rpm -ivh mysql-connector-odbc-setup-8.0.15-1.el7.x86_64.rpm

If you cannot use the rpm command to install MySQL ODBC driver on your Linux system, you can install it manually. The rpm2cpio command can extract the MySQL ODBC 8.0 driver from the rpm packages.

The next rpm2cpio command can extract the libmyodbc8S.so driver from the rpm package and put it into the ./usr/lib64” folder.

rpm2cpio mysql-connector-odbc-setup-8.0.15-1.el7.x86_64.rpm | cpio -idmv

The another rpm2cpio command will extract the libmyodbc8a.so and libmyodbc8w.so drivers from the rpm package and put them into the ./usr/lib64” folder.

rpm2cpio mysql-connector-odbc-8.0.15-1.el7.x86_64.rpm | cpio -idmv

After three MySQL ODBC 8.0 drivers are extracted from rpm packages, you can copy them to the  /usr/lib64 system folder on Linux.

ls -al ./usr/lib64 

-rwxr-xr-x 1 root root 5621032 Jan 27 21:43 libmyodbc8S.so

-rwxr-xr-x 1 root root 6064328 Jan 27 21:43 libmyodbc8a.so

-rwxr-xr-x 1 root root 6068384 Jan 27 21:43 libmyodbc8w.so

cp ./usr/lib64/* /usr/lib64 

You’ll also need to update the odbcinst.ini and odbc.ini files manually.

Here is the updated odbcinst.ini file.

[MySQL]

Description=ODBC for MySQL

Driver64=/usr/lib64/libmyodbc8a.so

Setup64=/usr/lib64/libodbcmy8S.so

FileUsage=1

Here is a sample myodbc8a DSN setting on Linux 6 or 7 in the odbc.ini file against MySQL server.

[myodbc8a]

Driver       = /usr/lib64/libmyodbc8a.so

Description  = Connector/ODBC 8.0 ANSI Driver DSN

SERVER       = x.x.x.x

PORT         =

USER         = user

Password     = password

Database     = eda_uvdatabase

OPTION       = 3

SOCKET       =

After the MySQL DSN has been created, you can use the isql command to verify the setting.

[root@dentrpy lib64]# isql -v myodbc8a user password

+—————————————+
| Connected!                                        |
|                                                             |
| sql-statement                                   |
| help [tablename]                             |
| quit                                                     |
|                                                             |
+—————————————+

Set up EDA driver configuration on Linux 

In the UniVerse UV folder, you will run the edasetup.sh script to create a new edaconfig text file. It contains the driver name and loglevel parameters.

# bin/edasetup.sh

Please input DRIVER (DB2, ORACLE, ODBC):

ODBC

Please input DRIVERNAME (EASYSOFT or MSODBC or MYSQL):

MYSQL

Please input ODBCDRVPATH (/usr/lib64):

/disk1/uv/edaconfig has been updated.

Here is the edaconfig file sample to work with MySQL server.

DRIVER=ODBC

DRIVERNAME=MYSQL

ODBCDRVPATH=/usr/lib64

LOGLEVEL=0

The loglevel can be set from 0 to 8.

Set up a new EDA data source using the EDA Schema Tool

The new EDA Schema Manager Tool (V 4.34.0) supports a new “COM-1NF” EDA driver to work with MySQL server.

Here is the sample screenshot of the EDA Schema Manager.

If the odbc.ini file is not installed in the default /etc folder, you might see the following error.

EDA COM Driver: [unixODBC][MySQL][ODBC 8.0(a) Driver]Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

You can copy the odbc.ini file to the /etc/odbc.ini file to solve the mysql.sock issue.