UniVerse External Database Access (EDA) with Microsoft ODBC Driver 17 for Linux
External Database Access (EDA) enables you to convert data stored in a Rocket UniVerse and UniData database to a first normal form (1NF) database, such as Microsoft SQL Server, then access that data using existing UniVerse BASIC programs, RetrieVe, or UniData/UniVerse SQL. For UniVerse running on a Linux platform, previously you had to purchase an ODBC driver to work with the EDA solution. Now, Microsoft provides you with ODBC Driver 17 for SQL Server on all Linux platforms. It is free and easy to install and use. UniVerse supports it starting with UV 184.108.40.20618 or later.
In this blog, you will learn how to work with the UniVerse EDA solution with Microsoft ODBC Driver 17 for SQL Server on Linux. I’ll cover the sample steps that will convert the UniVerse CUSTOMER file data into SQL Server tables.
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.
|Rocket MultiValue products||Version|
|UniVerse||220.127.116.1118 (4/26/2018) or later|
|UniVerse EDA license||Yes|
|EDA Schema Manager||4.33.1
(U2 DB Tools – 18.104.22.16820)
|SQL Server or SQL Server Express||2012 or higher|
SQL Server Configuration Setting
When using Microsoft ODBC Driver 17 for Linux, you must set the SQL Server authentication option to “SQL Server and Windows Authentication mode”. You can change the option by using the Microsoft SQL Server Management Studio Tool.
You must set up the SQL Server to a fixed port like 1433; you cannot use TCP Dynamic Ports via the SQL Server Configuration Manager Tool. You can run this tool by using the “C:\Windows\SysWOW64\SQLServerManager12.msc” command for SQL Server 2014.
For different SQL Server versions, the path is different. Please see Microsoft documentation via this web link – https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-configuration-manager?view=sql-server-2017
Start the SQL Server Configuration Manager Tool to set up the TCP/IP port for either the SQLSERVER or SQLEXPRESS version.
Right-click on the “TCP/IP” protocol option to change the TCP Port number to 1433 or a different port number.
Installing the Microsoft ODBC Driver 17 for SQL Server on Linux
Microsoft ODBC Driver 17 for SQL Server can work with RedHat Enterprise Server 6 & 7 and other Linux platforms. Here is the sample script file to install Microsoft ODBC Driver 17.
sudo su #RedHat Enterprise Server 6 curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo #RedHat Enterprise Server 7 curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo exit sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts sudo ACCEPT_EULA=Y yum install msodbcsql17 # optional: for unixODBC development headers sudo yum install unixODBC-devel
Please see more installation information on the Microsoft web site via the following link – https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
The Linux yum commands will install the Microsoft libmsodbcsql-17.1.so.0.1 driver in the “/opt/microsoft/msodbcsql17/lib64” folder. It will also install the UNIX ODBC driver manager tool (libodbc.so.2) in the “/usr/lib64” folder. For the UniVerse EDA driver, it will only look up the libodbc.so file. You must copy the “/usr/lib64/libodbc.so.2” to “/usr/lib64/libodbc.so”, if the libodbc.so file does not exist in the “/usr/lib64” folder.
The odbcinst.ini file will be created in the /etc folder.
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
However, the odbc.ini file must be created manually to find your SQL Server DSN setting.
Here are two sample DSN names in the /etc/odbc.ini file that work with SQL Server and SQL Server EXPRESS. If you work with SQL Server and Express versions on the same machine, you must change one port number to a different port, e.g. 1435.
[sqlserver] Driver=ODBC Driver 17 for SQL Server Description=SQL Server DSN created during installation Server=x.x.x.x UID=xxx PWD=xxx Database=sample_database [sqlexpress] Driver=ODBC Driver 17 for SQL Server Description=SQL Server DSN created during installation Server=y.y.y.y\\SQLEXPRESS,1433 UID=xxx PWD=xxx Database=sample_database
After an ODBC data source name (DSN) has been created in the /etc/odbc.ini file, you can use the “isql” command to verify the DSN setting.
Syntax isql –v DSN [UID [PWD]] [options] [root@ etc]# isql -v sqlserve xxx xxx +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
If the “isql” command does not provide any meaningful error message, you can turn on the UNIX ODBC driver manager log. The logging setting can be added to the /etc/odbcinst.ini file.
[ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1 UsageCount=1 [ODBC] Trace = Yes TraceFile = /tmp/unixodbc.log ForceTrace = Yes Pooling = No
On UniVerse, the EDA driver supports different ODBC drivers for Linux platform. It requires you to run the edasetup.sh script to configure the EDA driver for Microsoft ODBC that will save the Microsoft ODBC driver setting to the edaconfig file in the UVHOME folder.
[root@den-vm-t14 uv1131]# bin/edasetup.sh Please input DRIVER (DB2, ORACLE, ODBC): ODBC Please input DRIVERNAME (EASYSOFT or MSODBC): MSODBC Please input ODBCDRVPATH (/usr/lib64): /disk1/uv1131/edaconfig has been updated. [root@den-vm-t14 uv1131]# more edaconfig DRIVER=ODBC DRIVERNAME=MSODBC ODBCDRVPATH=/usr/lib64 LOGLEVEL=0
Note: The ODBCDRVPATH setting is not used on the current UniVerse release.
Start the EDA Schema Manager Tool to create a new EDA data source with a DSN defined in the /etc/odbc.ini file as shown in the previous step. For the UniVerse EDA Data Source, the driver option is “COM-1NF” for Microsoft ODBC for Linux.
Right-click on the UniVerse CUSTOMER file to create a new CUSTOMER schema with default mapping.
When you convert the U2 CUSTOMER file to an EDA file, it will create CUSTOMER.CUSTOMER and CUSTOMER.ORDERS_MV tables in SQL Server. You can use the Microsoft SQL Server Management Studio Tool to verify that two tables were created.