Use an ODBC based querying tool with SAP HANA, express edition

After registering to download SAP HANA, express edition, use the Download Manager to retrieve the client package for the system you will connect from.

The package names are:

• Linux (x86/64) : clients_linux_x86_64.tgz
• Windows: clients_windows.zip

The downloaded archive for the client package contains both the SAP HANA HDB Client and the SAP HANA XS CLI.

The SAP HANA HDB Client software package includes the following connectivity/drivers:

• SQLDBC
• ODBC
• JDBC
• Python (PyDBAPI)
• Node.js
• Ruby

Here you will install the SAP HANA HDB Client only.

Connecting from Linux environments

First, you need to extract the contents of the downloaded package:

tar -xvzf /opt/hxe/clients_linux_x86_64.tgz -C /opt/hxe

The following files will be extracted:

• hdb_client_linux_x86_64.tgz : the SAP HANA HDB Client software package
• xs.onpremise.runtime.client_linuxx86_64.zip : the SAP HANA XS CLI software package

You need now to decompress the SAP HANA HDB Client package executing the following command:

tar -xvzf /opt/hxe/hdb_client_linux_x86_64.tgz -C /opt/hxe/installer

And now you can run the installer program executing the following commands:

sudo su -l hxeadm

cd /opt/hxe/installer/HDB_CLIENT_LINUX_X86_64
./hdbinst

exit

Accept the prompts default values to configure your installation:

• Installation Path : /usr/sap/hdbclient

Once the installation is completed, you should get the following elements in your console:

Installation done

Connecting from Windows environments

First, you need to extract the contents of the downloaded package using your favorite archive manager (for example 7zip, WinZip or WinRar).

The following files will be extracted:

• hdb_client_windows_x86_32.zip : the SAP HANA HDB Client software package for Windows 32 bits platforms
• hdb_client_windows_x86_64.zip : the SAP HANA HDB Client software package for Windows 64 bits platforms
• xs.onpremise.runtime.client_ntamd64.zip : the SAP HANA XS CLI software package

You need now to decompress the SAP HANA HDB Client package for your target platform.

And now you can run the installer program executing the hdbsetup.exe application located in the HDB_CLIENT_WINDOWS_X86_64 directory.

Accept the prompts default values to configure your installation:

• Installation Path : C:\Program Files\sap\hdbclient

Once the installation is completed, you should get the following elements in your console:

Installation finished successfully

The driver (lbodbcHDB.dll/so), which is installed as part of the SAP HANA client, is located at (unless specified otherwise during the installation):

• on Linux and UNIX platforms /usr/sap/hdbclient/
• on Microsoft Windows platforms: C:\Program Files\SAP\hdbclient\

Connecting from Linux environments

For the Linux environment, you will first need the unixODBC package to be installed.

Please refer to your system documentation for installation instructions.

In addition, it is important to create the following addition symbolic links:

ln -s /etc/unixODBC/odbc.ini /etc/odbc.ini
ln -s /etc/unixODBC/odbcinst.ini /etc/odbcinst.ini

To validate that unixODBC is properly installed, you can run the following command to print the current version:

odbcinst --version

Now, you can append HDBODBC driver definition to the unixODBC drivers file (in /etc/odbcinst.ini):

[ODBC]
TraceFile   = /tmp/sql.log
Trace       = No

[HDBODBC]
Driver      = /usr/sap/hdbclient/libodbcHDB.so
Description = SAP HANA ODBC Driver
FileUsage   = 1

To validate that the SAP HANA ODBC Driver is properly registered, you can run the following command:

odbcinst -q -d

The following output should be returned:

[HDBODBC]

Connecting from Windows environments

To validate that the SAP HANA ODBC Driver is properly registered, run the ODBC Data Source Administrator (64 bits) using the following command and switch to the Drivers tab:

odbcad32

Before connecting with ODBC, you need to create an ODBC DSN (Data Source Name).

ODBC DSN can either be added as User or System DSN. A System DSN will be visible to any user compared to User DSN which are defined per users.

We will be configuring System DSN in this tutorial, but if you don’t have the permission you can replicate with User DSN .

Connecting from Linux environments

To add your ODBC DSN on Linux environments, you need to add the a new section to your odbc.ini file.

This section will be based on the following template:

[<ODBC DSN Name>]
ServerNode  = <host>:<port>
Driver      = HDBODBC
Description = <description>

Edit the System DSN odbc.ini file is located in /etc/odbc.ini and append the following:

[DSN_HXE]
ServerNode  = <server ip>:39015
Driver      = HDBODBC
Description = HXE Tenant

[DSN_SYSTEMDB]
ServerNode  = <server ip>:39013
Driver      = HDBODBC
Description = SYSTEM Database

Make sure to update the <server ip> with the proper information.

Note : for Docker users you will need to update the HXE port to 39041 and the SYSTEMDB one to 39017.

 

To validate that your ODBC DSN are properly registered, you can run the following command:

odbcinst -q -s

The following output should be returned:

[HXE]
[SYSTEMDB]

Connecting from Windows environments

To add a new ODBC DSN, run the ODBC Data Source Administrator (64 bits) using the following command and switch to the System DSN tab:

odbcad32

Click on Add….

Select HDBODBC and click on Finish.

Enter the following details (replace by your instance host name or IP address):

Click on Connect.

Enter the database user credentials and click on OK

The connection should be successful. Click on OK.

Repeat the steps for the SYSTEMDB:

At the end, you should have both the HXE and SYSTEMDB ODBC DSN listed.

Note : for Docker users you will need to update the HXE port to 39041 and the SYSTEMDB one to 39017.

 

Connecting from Linux environments

The unixODBC package comes with a command-line interactive SQL tool called isql.

You can run the isql command to get more details about the program options and switches:

In order to connect to the DSN_HXE DSN, you can run the following command (after adjusting the password):

echo "select * from M_DATABASES;" | isql DSN_HXE SYSTEM password -c -m10 -b

The result should look like this:

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| DATABASE_N| DESCRIPTIO| ACTIVE_STA| ACTIVE_STA| OS_USER   | OS_GROUP  | RESTART_MO|
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| HXE       | HXE-90    | YES       |           |           |           | DEFAULT   |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
SQLRowCount returns 1
1 rows fetched

In order to connect to the DSN_SYSTEMDB DSN, you can run the following command (after adjusting the password):

echo "select * from M_DATABASES;" | isql DSN_SYSTEMDB SYSTEM password -c -m10 -b

The result should look like this:

+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| DATABASE_N| DESCRIPTIO| ACTIVE_STA| ACTIVE_STA| OS_USER   | OS_GROUP  | RESTART_MO|
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| SYSTEMDB  | SystemDB-H| YES       |           |           |           | DEFAULT   |
| HXE       | HXE-90    | YES       |           |           |           | DEFAULT   |
+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
SQLRowCount returns 2
2 rows fetched

Connecting from Windows environments

As stated before, there is no pre-installed querying tool installed by default on Windows environment.

There are several options available like:

• ODBC Test by Microsoft:

  As part of the Microsoft Data Access Components (MDAC) Software Development Kit (which can be downloaded from the Microsoft Download Center), this tool shows all the ODBC API function call and allow you to run SQL

• FlySpeed SQL Query free edition by ActiveDBSoft

• ODBC QueryTool on SourceForge

Make sure you always use a 64 bit version else your DSN won’t be listed.

Based on the available ODBC Connection Properties listed in the online documentation, provide an answer to the question below then click on Validate.