Skip to Content

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

Previous

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

By Abdel DADOUCHE

Provide details on the installation the SAP HANA client and instruction to connect a SAP HANA, express edition instance from almost any ODBC based querying tool.

Details

You will learn

In this tutorial, you will install the SAP HANA client for SAP HANA, express edition.

Then, you will learn how to connect your SAP HANA, express edition instance using the Linux isql tool as a ODBC based querying tool.

The Linux isql tool is included in the unixODBC package. Please refer to your system documentation for installation instructions.

On Windows environments, there is no pre-installed tool available out-of-the-box.

For alternate options, you can also check the following link: Select a SQL query tool for SAP HANA, express edition.

Step 1: Download & Install the SAP HANA HDB client

Before you can proceed with the next steps, you will need to complete the Installing SAP HANA HDB Client for your target platform from the Install the SAP HANA, express edition clients group.

Please log in to access this content.
Step 2: Locate the ODBC Driver

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\
Please log in to access this content.
Step 3: Configure the driver

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
image Step 4
Please log in to access this content.
Step 4: Configure your connections

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….

image Step 5

Select HDBODBC and click on Finish.

image Step 5

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

Data Source Name DSN_HXE
Description HXE Tenant DSN
Server:Port <server host>:39015

Click on Connect.

image Step 5

Enter the database user credentials and click on OK

image Step 5

The connection should be successful. Click on OK.

image Step 5

Repeat the steps for the SYSTEMDB:

Data Source Name DSN_SYSTEMDB
Description SYSTEM Database DSN
Server:Port <server host>:39013
image Step 5

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

image Step 5

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

 

Please log in to access this content.
Step 5: Test the connections with SQL

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:

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

Please log in to access this content.
Step 6: Validation

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

Which connection options are available within the following list?
×
Step 7: The server host

You can use the IP address, the host name or the fully qualified name as long as the server host is reachable using a ping command from the machine that will run your program.

You can also specify one or more failover servers by adding additional hosts, as in the following example:

ServerNode=myServer:39015,failoverserver1:39015,failoverserver2:39015
Please log in to access this content.
Step 8: The port number

The port number to be used in the connection string include the instance number which is assigned when a new instance is created.

And, the pattern used for port assignments is: 3<instance number><service port>.

To execute SQL, you will need to connect to the SQL/MDX service port.

SAP HANA, express edition 1.0 and Single Database Container

In these earlier release, Single Database Container was the default installation mode and the default instance number was 00.

The SQL/MDX service port to access the database of a single tenant system is 15, so the port is 30015.

To access a specific database, you will use the databasename in the option parameter.

SAP HANA, express edition 2.0 and Multi Database Container

With newer releases, Multi Database Container are installed by default and the instance number used by default value is 90, unless specified otherwise during the setup.

With Multi Database Container you must consider the SQL/MDX service port to access the System database (also called SYSTEMDB) and the Tenant databases.

The SQL/MDX service port to access the SYSTEMDB System database of a multitenant system is 13, so the port is 39013

The SQL/MDX service port to access the HXE Tenant databases of a multitenant system is 15, so the port is 39015

Make sure that you can reach the port (using a telnet command) from the computer you will connect using Eclipse.

Please log in to access this content.
Step 9: The options

The ODBC driver supports a full set of options that can become handy when developing your application.

For example, you can use the options parameter in your DSN to specify the connection current schema, as in the following example:

CURRENTSCHEMA=ML_USER

For more information about the ODBC Connection Properties, you can check the online documentation

Please log in to access this content.

Updated 07/11/2018

Time to Complete

10 Min.

Intermediate

Tags

Next
Back to top