Hana Driver Odbc

Posted on  by 

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

Download hana odbc driver

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

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

Parameters


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.

Hana Odbc Driver Version


Coments are closed