Overview
You will learn
- How to create and test an SAP HANA ODBC data source
- How to use an ODBC data source in an application
Prerequisites
- You have completed the first 3 tutorials in this mission.
Steps
Intro
Open Database Connectivity (ODBC) provides an API for accessing databases. Database vendors provide ODBC drivers for their database products. An application written to the ODBC standard can be ported to other databases that also provide an ODBC interface.
The ODBC Data Source Administrator lists the installed ODBC drivers and the configured data sources.
Open the administrator by entering ODBC after clicking on the Microsoft Windows start icon.
Ensure that you choose the 64-bit version if you have the 64-bit version of the SAP HANA client installed.
Start the ODBC Administrator Click the Drivers tab and view the installed drivers.
odbc admin drivers The SAP HANA ODBC driver (HDBODBC) is visible.
Click the User DSN tab to view the data sources.
Click Add to create a new data source to connect to a SAP HANA database.
Add ODBC user data source Select
HDBODBCand click Finish.
New data source Example data source configuration for SAP HANA Cloud.
For the host, paste the SQL Endpoint from your instance. For more details, review Choose an SAP HANA Database.
ODBC HANA Cloud Example data source configuration for SAP HANA on-premise instance.
ODBC HXE To connect to SAP HANA on-premise, Multitenant should be checked and the Validate TLS/SSL certificate option can be unchecked. For more information on this topic, see How to Configure TLS/SSL in SAP HANA 2.0.
Click Test connection.
Test Succeeded The user name
USER1and passwordPassword1can be entered when prompted for credentials.Press OK to save the data source.
Saved values can also be viewed using the Microsoft Windows registry editor under the key
Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI.
For additional details see Connect to SAP HANA via ODBC.
The following instructions demonstrate how unixODBC can be used to configure and test a data source on Linux or Mac.
On SUSE Linux, the YaST installer can be used to install unixODBC.
YaST installer For more details on how to accomplish this, please follow the second step of this tutorial.
On a Mac, unixODBC can be installed using Homebrew.
Shellbrew install unixodbcThe following commands can be used to confirm that unixODBC is installed, determine the location of the .odbc.ini file (if it exists), and to confirm the location of the SAP HANA client install that contains the ODBC driver.
Shellodbcinst -j which hdbsql
odbcinst Navigate to a directory, similar to the first one that is highlighted in the screenshot above.
Shellcd $HOMEEdit the
.odbc.inifile (or create it if it does not exist) to add one or more data sources. Be sure to configure the values ofservernodeanddriverso that they conform with your setup.The driver’s file extension is
dylibinstead ofsoon a Mac..odbc.ini[HANA_Cloud] servernode = 6b7ae0da-ee5a-4782-bc7e-297099099b59.hana.prod-ca10.hanacloud.ondemand.com:443 driver = /home/dan/sap/hdbclient/libodbcHDB.so encrypt = true sslValidateCertificate = true sessionVariable:APPLICATION = ODBC Application [HANA_OnPremise] servernode = hxehost:39015 driver = /home/dan/sap/hdbclient/libodbcHDB.so databasename = HXE sessionVariable:APPLICATION = ODBC ApplicationThe
sessionVariable:APPLICATIONproperty sets a custom application name for the connection. This value can be viewed in theCLIENT_APPLICATIONcolumn of theM_CONNECTIONSsystem view and may also appear in SAP HANA telemetry data.unixODBC provides a basic SQL query tool called
isqlthat can be used to validate a data source.Shellisql -v HANA_Cloud User1 Password1Once you see a message that indicates a connection has been made, enter the following statement to verify this.
ShellSELECT * FROM HOTELS.CUSTOMER
isql
The following instructions demonstrate how unixODBC can be used to configure and test a data source on Linux or Mac.
On SUSE Linux, the YaST installer can be used to install unixODBC.
YaST installer For more details on how to accomplish this, please follow the second step of this tutorial.
On a Mac, unixODBC can be installed using Homebrew.
Shellbrew install unixodbcThe following commands can be used to confirm that unixODBC is installed, determine the location of the .odbc.ini file (if it exists), and to confirm the location of the SAP HANA client install that contains the ODBC driver.
Shellodbcinst -j which hdbsqlodbcinst Navigate to a directory, similar to the first one that is highlighted in the screenshot above.
Shellcd $HOMEEdit the
.odbc.inifile (or create it if it does not exist) to add one or more data sources. Be sure to configure the values ofservernodeanddriverso that they conform with your setup.The driver’s file extension is
dylibinstead ofsoon a Mac..odbc.ini[HANA_Cloud] servernode = 6b7ae0da-ee5a-4782-bc7e-297099099b59.hana.prod-ca10.hanacloud.ondemand.com:443 driver = /home/dan/sap/hdbclient/libodbcHDB.so encrypt = true sslValidateCertificate = true sessionVariable:APPLICATION = ODBC Application [HANA_OnPremise] servernode = hxehost:39015 driver = /home/dan/sap/hdbclient/libodbcHDB.so databasename = HXE sessionVariable:APPLICATION = ODBC ApplicationThe
sessionVariable:APPLICATIONproperty sets a custom application name for the connection. This value can be viewed in theCLIENT_APPLICATIONcolumn of theM_CONNECTIONSsystem view and may also appear in SAP HANA telemetry data.unixODBC provides a basic SQL query tool called
isqlthat can be used to validate a data source.Shellisql -v HANA_Cloud User1 Password1Once you see a message that indicates a connection has been made, enter the following statement to verify this.
ShellSELECT * FROM HOTELS.CUSTOMERisql
Linux and macOS users can set a custom application name by adding the
sessionVariable:APPLICATIONproperty to the data source definition in the.odbc.inifile as shown in the previous step. The following instructions only apply to Microsoft Windows.
The SAP HANA ODBC driver supports setting a custom application name using the sessionVariable:APPLICATION connection property. This value can be viewed in the CLIENT_APPLICATION column of the M_CONNECTIONS system view and may also appear in SAP HANA telemetry data.
Open ODBC Data Sources (64-bit) from the Microsoft Windows Start menu.
Select your SAP HANA data source and click Configure.
Configure ODBC application name Click Advanced in the lower-left corner of the configuration dialog.
Open ODBC advanced settings Click Add and enter the following values:
Property:
sessionVariable:APPLICATIONValue:
ODBC Application- You can replace ODBC Application with your preferred application name.
Add ODBC connection property Click OK to close the Advanced dialog.
Click OK on each remaining configuration window to save the changes.
The custom application name will not be saved unless you confirm and close all open configuration dialogs by clicking OK.
An application that supports ODBC can now make use of the created data source. One example on Windows is Microsoft Excel.
Microsoft Excel and the SAP HANA client must use the same architecture (both 32-bit or both 64-bit).
The Excel version can be checked under File | Account | About Excel.
If needed, the 32-bit installer for SAP HANA client is available from the SAP Software Centre site and can be installed into a separate directory such as C:\SAP\hdbclient32.
The following steps demonstrate how to use Microsoft Excel to query data in SAP HANA using the ODBC connector.
On Microsoft Windows, open Microsoft Excel.
In the Data tab, go to Get Data | From Other Sources | From ODBC.
Excel ODBC Select the previously created data source that contains the connection information to an SAP HANA database.
Excel DSN Enter the credentials.
Credential Screen Select a schema and table, then press Load.
Browse Schema The selected data is now imported into a Microsoft Excel spreadsheet.
Excel Show Data One further option for some tools is to provide the connection details directly to an ODBC enabled application as shown below.
Excel ODBC Connection String For further information on programming an application to use the ODBC client driver, see ODBC Application Programming.
Congratulations! You have configured an ODBC data source to contain connection information for a SAP HANA database and used that data source from Microsoft Excel.
Resources
Discussion
Share feedback on this tutorial or join the conversation in SAP Community.