- Download Hana Odbc Driver
- Install Odbc For Microsoft Access
- Hana Odbc Connection Parameters
- Add Hana Driver As Odbc Source
- Microsoft Access Odbc Driver Download
- Hana Odbc Driver Version
Once the executable client file is downloaded from SAP Service marketplace, then double click on it to install ODBC HANA client on our PC. Before you can connect to an SAP HANA database using Power Query, you need the SAP HANA ODBC Driver on your computer.
Article 3 in a 5 part series
Introduction
In this article I’ll show you how to (1) download and install the HANA client drivers (2) configure a HANA ODBC profile (3) configure a PowerBuilder ODBC DBProfile (4) Write code to initialize a PowerBuilder Transaction Object prior to connecting at runtime. You must have access to a running HANA instance to do these procedures. These four procedures apply to both the PowerBuilder Classic and PowerBuilder .NET IDEs. I’ll also show you how to configure a PowerBuilder JDBC DBProfile in the Classic IDE.
Downloading and Installing HANA Client drivers
If you have the HANA One Platform version 52, login to your HANA One Management Console, navigate to the Downloads page and download the 32 bit client drivers.
If you installed the developer edition (version 48) download the HANA Studio from here and client drivers from here
Important note: Even on a 64 bit platform, PowerBuilder 12.5 requires 32 bit client drivers!
The client installation process will install the ODBC driver and register it with the ODBC administrator. It will also install the JDBC driver as well as some utility support programs
After installing you will have these folders and files
If you are running HANA One v52 and the database is not running, navigate to the Administration page and start it. (HANA Developer edition v48 starts the database when you start the instance)
Once the database is up and running you can configure a PB IDE to connect to it via ODBC using this two-step process:
Step 1: Configuring an ODBC Profile
Open the ODBC Administrator tool either via the control panel
Or the PowerBuilder DBProfile painter
On the System DSN tab (profiles accessible to all workstation users) click Add
Choose the HANA ODBC32 driver HDBODBC32
Name your Data Source with a descriptive identifier. Then enter your connection parameters via the SAP Hana supplied dialog.
For Server:Port enter <your ec2 url:30015>
For example my test server is (sometimes) at 54.227.252.172 30015
Click the Connect button to test your Server:Port
You will be prompted for your credentials
Enter SYSTEM and the password you chose when configuring your database.
Click OK to save the profile and close the Administrator tool
Note: Logon credentials are NOT stored in the ODBC profile. You will be prompted for them during every login.
Alternate way to specify ODBC server settings: Securing Server/Port information
HANA provides a User Store that enables you to store connection information for connecting to an SAP HANA system with ODBC. Instead of entering connection information each time you make a connection, you store the information; assign it a key, and use this key when making connections.
The user store makes it easier to move between systems (for example, when executing SQL from the command line), and also keeps connection information, including user names and passwords, in a secure place. This facility, described in chapter 15 of the HANA Developer Guide, only has a commandline interface. Here’s the commandline options and output from a sample run:
Now, as shown below when you connect you can specify a user store entry key in the place actual server/port. Note that the user id and password are NOT supplied from the user store!
Entries on the Settings…. Dialog lets you configure SSL and custom key/values to place in the communication header
After you successfully defined your ODBC profile, you are ready to move on to the next (one time) step.
Step 2: Configuring a PowerBuilder DBProfile
Go to the PB DB Profile Painter and create a PB database profile based on the ODBC profile you just created.
Note these 3 special entries
- On the System tab enter the user or schema name PB should designate as the owner of its Catalog tables. On a multi-schema system, you might want to have one schema for those catalog tables and then reference that for the location of them when logging into any of the other schemas. Also, if you are set up so that you are using developer accounts rather than a schema owner account, you would need to point the catalog tables to the actual catalog schema location, or else each developer would end up with their own set of catalog tables. The PowerBuilder IDE will always attempt to find its catalog tables. If it doesn’t find them it will attempt to create them
On the Options tab, for Connect Type, choose SQLDriver_NoPrompt from the drop down list. If you don’t set this you will always be prompted with this dialog containing your stored UID & Password or if none is specified to supply a login name on a driver generated secondary login page
On the Syntax tab, check “Enclose Table and Column names in Quotes”. If you don’t, you will get SQL syntax errors when HANA database object names are not all uppercase.
You can test connect your profile on the Preview tab. When satisfied, Save your profile. You should now be able to connect to HANA from within the IDE!
Issue: The first time you connect to HANA PowerBuilder will attempt create the five extended attribute tables. As shown in figure xx, the attempt will fail and you will get an error message. As shown in figure xx PB will only create one of the five tables (PBCatTbl). Later on when we create HANA tables and replicate data to them using the Pipeline Painter, PowerBuilder will properly create its catalog tables. I reported this issue to engineering.
It only created one of the tables (table catalog)! As you can see from this screen shot of me attempting to edit an extended column attribute, the Column Catalog was not created!
Note: You can configure the Database Painter to prevent it from using extended attributes. On the Design menu à Options and uncheck the option. This setting however, does not stop PowerBuilder from attempting to create and connect to the tables
Congratulations! You can now connect your IDE to your HANA database. Notice the overlay and state checkmarks on the icon next to the HANA profile name.
Here’s a video in which I’ll walk you through the process
Here’s a video in which I’ll show you a couple of nuances of connecting using the SAP HANA ODBC Driver and PowerBuilder
Exploring sample data
Now you can use the Database painter to explore the contents in your Database and the DataWindow painter to build a report and data entry objects.
If you are on HANA One then you can open the Database Painter, navigate to your HANA database and expand the table list. Whew, when logged in as SYSTEM there’s a lot of stuff in there!
For fun and to see what HANA can do, I chose to create a Bar Chart Graph DataWindow that required aggregated all rows in the demo.GDP_Data table. This column oriented table has a mere 656,100 rows.
I entered this SQL Select statement for my data source definition:
Amazing! 656000 rows and the results were returned almost before I finished clicking the retrieve button!
Connecting in Application Code
For a running application, its standard fare to configure your Transaction Object properties. This listing illustrates two snippets
You can configure a PB IDE to connect to HANA via JDBC using this two-step process
Download Hana Odbc Driver
Step 1: Setting the Java Classpath
Go to Tools à System Options -à Java
Add ngdbc.jar to the IDE’s Java Classpath (it is located in the Program Filessaphdbclient folder)
Restart PowerBuilder so the classpath change takes effect.
Step 2: Creating a JDBC DBProfile
Use the DBProfile Painter to create a new profile in the JDBC section
Give the profile a descriptive name. Enter the fully qualified driver name com.sap.db.jdbc.Driver and provide your login credentials
Remember to set the “Embed Identifiers in Quotations” and Catalog Owner as mentioned above for ODBC. You can test connect your profile on the Preview tab. Save your profile and Connect!
ToDo Checklist
(1) Create an ODBC profile to connect to your HANA Cloud Server
(2) Use the profile to test connect to your HANA Cloud Server as SYSTEM
(3) Create a PB DBprofile based on the ODBC profile you just created and connect as SYSTEM
(4) Use DBPainter to explore the Demo schema and its contents
(5) Use the DataWindow painter to create a DataWindow Object displaying data in the Demo schema
(6) Watch this video on HANA Academy showing how to create a User
(7) Use HANA Studio to create a HANA user named SSSS
(8) Create a PB DBprofile based on the ODBC profile you just created and connect as SSSS
In the next article I’ll show you how to use PowerBuilder data pipelines to replicate data to HANA and how use database vendor tools to bulk load data
Long Live PowerBuilder!
Hi All, The topic of my Blogging activities in SDN will be initially on Installation of SAP HANA CLIENT, ODBC & DESIGN STUDIO.
TOOLS INFORMATION:
HANA CLIENT TOOL
ODBC
DESIGN STUDIO
PREREQUISITES:
The following components need to be installed before starting the installation:
- Microsoft Office 2007
- Adobe Flash 10.1 or higher
- SQL Server Native Drivers.
- Hana client tools.
HANA CLIENT TOOL
The SAP HANA client software can be installed on a variety of platforms.
The following platform types are supported:
● AIX
● HP-UX
● Linux
● Microsoft Windows
● Solaris
HANA Client is the piece of software which enables you to connect any other entity, including Non-Native applications to a HANA server. This “other” entity can be, say, an NW Application Server, an IIS server..etc. The HANA Client installation also provides JDBC, ODBC drivers. This enables applications written in .Net, Java..etc to connect to a HANA server, and use the server as a remote database. So, consider client as the primary connection enabler to HANA server.
- SAP HANA Client are the “client tools” like ODBC Drivers for windows to connect to the HANA Database when you want to use for example excel to connect to SAP HANA.
- The SAP HANA Studio is the Eclipse based development Environment for SAP HANA.
Download the media from service market place, Need SMP credentials
When you install the SAP HANA client software package, supported clients are installed and available. The clients available on Microsoft Windows platforms are as follows:
● SQLDBC
● ODBO
● ODBC
● JDBC
● Microsoft ADO.NET
Run the setup file with run as administrator rights, follow the wizard
Confirm the standard options and go through the short installation procedure:
Click on Install
After installing SAP HANA Client and SAP HANA Studio, we can start HANA Studio by navigating to the Windows Start menu, go to “All Programs type SAP HANA and hit “SAP HANA Studio”:
Installation completed successfully.
Default Installation Paths for Windows
Microsoft Windows x86, 64-bit 64-bit C:Program Filessap hdbclient
For more info, please check SAP guides.
1577128 – Supported clients for SAP HANA
ODBC Connection to SAP HANA
After the HANA Client is installed, if not already created, you will need to create an ODBC Connection to HANA.
What is ODBC?
ODBC (Open Database Connectivity) is a standard programming language middle ware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.
The figure above shows how ODBC fits in between an application and the database it is accessing.
From the Start menu → select Control Panel →Select Administrative Tools
Select Data Sources (ODBC)
Select the System DSN tab → Select Add → Select HDBODBC for 64 bit machines → Select HDBODC32 for 32 bit machines → Select Finish.
Click on finish tab.
Complete the entries Data Source Name → HANA_ODBC (suggested) Description • HANA ODBC (suggested) Server Port → Select Connect
Server name should be filled in for you to test your connection, leave the User and Password field blank! Your AD/MC credentials will transmit automatically
When you receive the Connect successful! Message • Click OK.
DESIGN STUDIO MEDIA DOWNLOAD AND INSTALLATION PROCESS:
System Requirements:
Before installing the design tool of SAP Business Objects Design Studio, make sure that the following components are installed on the local machines of your application designers:
● Internet Explorer 9.0, 10 or 11
● For 64-bit version of design tool installer: 64-bit version of Windows
● For 64-bit version of design tool installer: 64-bit Secure Login Client of SAP NetWeaver Single-Sign On .
SAP BusinessObjects Design Studio enables application designers to create analysis applications and dashboards – based on SAP NetWeaver BW, SAP HANA and universe data sources – for browsers and mobile devices (iPads, for example).
SAP BusinessObjects Design Studio can be used locally and integrated in the following platforms:
- SAP BusinessObjects Business Intelligence (BI platform)
- SAP NetWeaver
- SAP HANA
Download the media from service market place by providing the credentials.
Click on DESIGN STUDIO CLIENT 1.5 and download the required media.
INSTALLATION PROCESS:
Run the setup file with run as administrator rights,
Click on next tab
Click on next tab, Installation process started…
Installation completed successfully.
The default path for 32-bit is C:Program Files (x86)SAP BusinessObjectsDesign Studio and for 64-bit the path is C:Program FilesSAP BusinessObjectsDesign Studio.
Connecting Design Studio
Install Odbc For Microsoft Access
Go to Application–> New
Hana Odbc Connection Parameters
Provide the necessary information and choose finish.
Click on Add Data Source Tab
Click on Browse tab, automatically our Hana db system will appears as we have configured ODBC server, choose DBH.
Click on OK tab, provide the Hana database credentials,
Now connection is active,
Click on ok.
Able to view tables successfully.
That’s it, in my next blog will explain about SAP LUMIRA and Analysis office.
Add Hana Driver As Odbc Source
Thank you for reading the blog.
Microsoft Access Odbc Driver Download
Rajesh K.