SAP SQL Anywhere Tips – ODBC Setting on Linux

NaotoSakai · ‎06-01-2020

First

*This blogpost created upon request. If you have any question or request .Please feel free to request.

There is a growing number of cases of migrating SAP SQL Anywhere from Windows servers to Linux servers. The application that was running on the Windows server will also be migrated to Linux, but if the application was connected via ODBC connection, how should I set it in Linux? This is only mentioned briefly in the manual, so I would like to explain the method.

I used Ver.17 in this post, but it seems that some versions will work slightly differently. If you have problems with other versions, please contact us from the comment section.

Environment used in the explanation

The Linux distribution used in this description is Ubuntu 16.04 LTS. The SAP SQL Anywhere uses Ver.17 64-bit version. Probably it can be used with any Linux distribution with a slight change, but if you find that it does not work with your distribution, please contact us from the comments section.

In addition, nowadays, even on Linux, a tool for setting the ODBC data source with GUI has been provided, but in the case of Linux server machine, there is a case that the GUI environment is not installed, so this time with CUI I would like to explain how to set up. You will be connecting via SSH, Telnet, or the console.

ODBC Driver manager

Unlike Windows, the ODBC driver manager required to use ODBC in Linux does not come standard with the OS, so it is usually necessary to install it additionally. (Although it may be installed as standard by the distribution or the option at the time of OS installation.)

There is one problem here, or there is a troublesome point, and unlike Windows provided as an OS standard function, there are several types of ODBC driver managers themselves in Linux. In addition, SAP SQL Anywhere for Linux itself comes with its own ODBC driver manager from SAP.

So which one should you use? As for the case, there are situations where this depends on the application. For example, in the case of an application developed in C-language by scratch, this needs to be decided by the developer. This means linking the libraries provided by the ODBC driver manager to build the application. This means that you can use any ODBC driver manager in this case. You can use the ODBC Driver Manager that comes with SAP SQL Anywhere to make it a SQL Anywhere-only application, or you can link against open source ODBC Driver Manager libraries such as unixODBC and iODBC for use with a wide variety of databases. There is also a way to develop an application.

If you use an application developed by a third party or a runtime development environment such as Python, you need to follow those guidelines. If you want to use an application that uses a module called pyodbc in Python, this module requires unixodbc, so you need to configure the SQL Anywhere ODBC driver to use with unixodbc.

* For Python, SAP SQL Anywhere also provides its own connection module, so there is also a way to use it.

In this blogpost, I will use unixODBC as the ODBC driver manager. If the unixodbc package is not installed, you need to install it additionally. I think it will be installed as following on ubuntu

# sudo apt install unixodbc

On the SQL Anywhere side, "SQL Anywhere Client" must be installed. (This is included if you installed by default.)

USER Datasource and SYSTEM Datasource

If you are reading this blogpost and you are using Microsoft Windows, you are familiar with the tool "ODBC Data Source Administrator".
These are ....

- User DSN

An ODBC data source that can only be viewed by the user who set it up.

- System DSN

An ODBC data source that is visible to all (privileged) users on this OS. However, it cannot be set without administrative authority

There is a difference. This is a similar concept on Linux, which causes different locations for the files that configure the ODBC settings. If you are migrating from Windows, you need to decide which one you set on Windows, and how to use it if you set a new one.

On Linux, define the data source as a text file. The file that defines the ODBC data source is a file called ".odbc.ini". The SAP SQL Anywhere ODBC driver searches for definition files and uses them in the following order: (If it is found, the search ends there, so if the same DSN exists, the one found earlier will be used.)

Environment variable : ODBCINI
- Please define the full path including the file name. Therefore, the file name does not have to be ".odbc.ini".
  example : export ODBCINI=/etc/odbc.ini

Environment variable : ODBC_INI
- Please define the full path including the file name. Therefore, the file name does not have to be ".odbc.ini".
  example : export ODBCINI=/etc/odbc.ini

Environment variable : ODBCHOME
- Define the path. Use the ".odbc.ini" file in that path.
  例： export ODBCHOME=/opt/odbcsettings

Environment variable : HOME
- Define the path. Use the ".odbc.ini" file in that path

Home directory of current user (~)
- Use ".odbc.ini" file on HOME directory of current user.

PATH environment variable
- We do not recommend using it. Make sure you can find the DSN with the previous settings.

On Linux, user DSNs and system DSNs are associated with file and directory permissions. For example, if you set the odbc.ini file that everyone can see with the "1. Environment variable : ODBCINI ", it is treated as a system DSN. And if you place .odbc.ini on the home directory ("5. Home directory of current user (~)) , this will be the user DSN because it will not be normally viewable by other users. As far as I can see, 1 and 5 are often used.

Setting of odbcinst.ini

odbcinist.ini is the definition of the ODBC driver library file. It corresponds to the driver in the ODBC administrator of Windows.

Instead of writing this file directly, it will be registered with the odbcinst command.
First, create the following text file with the name sqladriver.template.

[SQL Anywhere 17]
Description=SAP SQL Anywhere 17 ODBC Driver
Driver=/opt/sqlanywhere17/lib64/libdbodbc17_r.so
Setup=/opt/sqlanywhere17/lib64/libdbodbc17_r.so

* /opt/sqlanywhere17 is the default installation directory, but if you installed it in a different directory, change it appropriately. In addition, in the example, the 64-bit version is set. When using the 32-bit version, replace 64 to 32, such as lib64.

Then read the SQL Anywhere environment variables and specify the sqladriver.template file created by the odbcinst command to install.

$ source /opt/sqlanywhere17/bin64/sa_config.sh

$ sudo odbcinst -i -d -f sqladriver.template
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc

The odbcinst command usually needs to be installed with root privileges. In the above example, Usage count is displayed as 1, but this is a count, so it may be a different number.

If the command is successful, the following entry will be written in /etc/odbcinst.ini.

[SQL Anywhere 17]
Description=SAP SQL Anywhere 17 ODBC Driver
Driver=/opt/sqlanywhere17/lib64/libdbodbc17_r.so
Setup=/opt/sqlanywhere17/lib64/libdbodbc17_r.so
UsageCount=1

The SAP SQL Anywhere ODBC driver is now ready to use.

Setting of odbc.ini

About example database

The database to connect in the example from here is as follows.

- - Host:localhost, running on port 2638
  - Database server name, database name is demo

I think that you can think of it as a database started with
dbsrv17 -n demo -x "tcpip (port = 2638)" demo.db
on localhost.

Define ODBC data source as SYSTEM data source

Make it available to multiple users as a system data source.
Here, create the ODBC data source definition file as /etc/odbc.ini. This file should not be modifiable by normal users. For example

# sudo nano /etc/odbc.ini

Create it with root privileges as above.

Enter the contents of the file as follows.

[demodb1]
Driver=SQL Anywhere 17
DatabaseName=demo
ServerName=demo
CommLinks=tcpip(host=localhost;port=2638)

[demodb1]：ODBC Data source name
Driver：ODBC driver name to use
DatabaseName：Database name
ServerName：Database server name
CommLinks：Network settings , above is .. tcpip connection ,Host = localhost,port=2638

The settings are the same as when connecting from SQL Central or Interactive SQL, so refer to the SQL Anywhere manual.

As a reminder, make sure that Driver matches the name defined in odbcinst.ini and the contents enclosed in [] in the first line of sqladriver.template. Now the library defined in odbcinst.ini will be used and the ODBC connection will be established.

Define ODBC data source as USER data source

* For convenience of explanation, a connection to the same database as the database set as the system data source is created using a different DSN name. (If you use the same DSN name, the connection setting found first will be used, as described above.)

Create a data source that only one user can use as a user data source. Create an ODBC data source definition file as .odbc.ini in your home directory. Now the configuration file can only be read by users who have that directory as their home directory (except for the admin user), so it will act as a user data source.

% cd $HOME
% nano .odbc.ini

Enter the contents of the file as follows.

[demodb2]
Driver=SQL Anywhere 17
DatabaseName=demo
ServerName=demo
CommLinks=tcpip(host=localhost;port=2638)

The user data source is now complete.

Test connection

Try a test connection. Here, I will use the command line SQL tool called isql that comes with unixodbc.

Don't forget to read and set environment variables before using , like followings

# source /opt/sqlanywhere17/bin64/sa_config.sh
# export ODBCINI=/etc/odbc.ini

*export ODBCINI= .... is only need for use SYSTEM data source.

isql command syntax is following

isql <Data Source Name> <User name> <Password>

I will try to connect according to this. The following connects using DSN:demodb1.

If "Connected!" Is displayed, the connection is successful. Enter an appropriate SQL statement and check if the connection is correct.

Next, try testing with your application.

Summary

As above, you can connect to SAP SQL Anywhere database via ODBC on Linux. It is a format that is often used, so I hope you find it useful.

Stay home , stay safe.