Use a JDBC 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 (ngdbc.jar), 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\

Here we will use the Eclipse Data Source Explorer as an example to highlight the key information that are require to configure a new JDBC driver in your tool of preference.

Such configuration will be required if the SAP HANA JDBC driver is not pre-configured.

First, change your current perspective using the Window > Perspective > Other menu.

Select to Database Development and click OK.

The Data Source Explorer view should now be displayed.

Now, using the menu, go to Window > Preferences and navigate to Data Management > Connectivity > Driver Definition.

Click on Add…

The New Driver Definition popup will appear.

On the Name/Type tab:

• select Vendor Filter as Generic JDBC
• select Generic JDBC in the Available Driver Templates
• input SAP HANA, express edition as your Driver Name

On the JAR List tab:

• add the ngdbc.jar to the list using the Add JAR/Zip button (refer to step 3 for the full path).

On the Properties tab:

• set the Connection URL to jdbc:sap://<server host>:<port>[/?<options>]
• set the Driver to com.sap.db.jdbc.Driver

Click on OK

The Drivers Definitions list should include the new entry.

Click on Apply and Close to close the Preferences popup window.

Now, either right-click on Database Connections > New or click on the to create a new Connection Profile.

Connect to the SYSTEM Database

On the Connection Profile panel, select Generic JDBC and enter SYSTEMDB @ HXE (SYSTEM) in the Name field.

Click on Next.

To connect to the System Database, you will need to provide the information for the database name, the URL connection string and a user name & password.

The SQL/MDX service port for the SYSTEMDB is 39013 (except with Docker where it is 39017).

For more details about the URL Connection String, please check the last few steps.

With the SAP HANA, express edition default settings, it may look like this:

Click on Finish.

Connect to the HXE tenant

On the Connection Profile panel, select Generic JDBC and enter HXE @ HXE (SYSTEM) in the Name field.

Click on Next.

To connect to the HXE tenant, you will need to provide the same type of information.

The SQL/MDX service port for the HXE tenant is 39015 (except with Docker where it is 39041).

With the SAP HANA, express edition default settings, it may look like this:

Click on Finish.

Click on the open scrapbook to edit SQL icon , then select:

Execute the following SQL statement, by pasting the following content then press CTRL+ALT=X to run it:

select * from M_DATABASES;

It should return the following result:

Now switch to the SYSTEMDB @ HXE (SYSTEM) connection and run the same SQL statement, and the result should be:

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

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:

jdbc:sap://myServer:39015;failoverserver1:39015;failoverserver2:39015

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.

The JDBC driver a full stack of options that can become handy when developing you application

For example, you can use the options parameter to specify the auto-commit behavior, as in the following example:

jdbc:sap://myServer:39015/?autocommit=false

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