Skip to Content

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.

/wp-content/uploads/2013/06/0301_236375.png

If you installed the developer edition (version 48) download the HANA Studio from here and client drivers from here

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/webcontent/uuid/402aa158-6a7a-2f10-0195-f43595f6fe5f

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

/wp-content/uploads/2013/06/0302_236376.png

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)

/wp-content/uploads/2013/06/0303_236377.png

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

/wp-content/uploads/2013/06/0304_236378.png

Or the PowerBuilder DBProfile painter

/wp-content/uploads/2013/06/0305_236379.png

On the System DSN tab (profiles accessible to all workstation users) click Add

/wp-content/uploads/2013/06/0306_236382.png

Choose the HANA ODBC32 driver HDBODBC32

/wp-content/uploads/2013/06/0307_236383.png

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

/wp-content/uploads/2013/06/0308_236384.png

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.

/wp-content/uploads/2013/06/0309_236385.png

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:

/wp-content/uploads/2013/06/0310_236386.png

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!  

/wp-content/uploads/2013/06/0311_236387.png

Entries on the Settings…. Dialog lets you configure SSL and custom key/values to place in the communication header

/wp-content/uploads/2013/06/0312_236388.png

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

/wp-content/uploads/2013/06/0313_236389.png

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

/wp-content/uploads/2013/06/0315_236390.png

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.

/wp-content/uploads/2013/06/0316_236391.png

/wp-content/uploads/2013/06/0317_236392.png

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.

/wp-content/uploads/2013/06/0318_236396.png

/wp-content/uploads/2013/06/0319_236397.png

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! 

/wp-content/uploads/2013/06/0320_236406.png

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

/wp-content/uploads/2013/06/0321_236414.png

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.

/wp-content/uploads/2013/06/0322_236415.png

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!

/wp-content/uploads/2013/06/0323_236416.png

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.

/wp-content/uploads/2013/06/0324_236423.png

I entered this SQL Select statement for my data source definition:

/wp-content/uploads/2013/06/0325_236424.png

Amazing! 656000 rows and the results were returned almost before I finished clicking the retrieve button!

/wp-content/uploads/2013/06/0326_236425.png

Connecting in Application Code

For a running application, its standard fare to configure your Transaction Object properties.  This listing illustrates two snippets

/wp-content/uploads/2013/06/0327_236429.png

Creating a PowerBuilder JDBC DBProfile

You can configure a PB IDE to connect to HANA via JDBC using this two-step process

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 Files\sap\hdbclient folder)

/wp-content/uploads/2013/06/0328_236430.png

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

/wp-content/uploads/2013/06/0329_236431.png

Give the profile a descriptive name. Enter the fully qualified driver name com.sap.db.jdbc.Driver and provide your login credentials

/wp-content/uploads/2013/06/0330_236432.png

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!

To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

Leave a Reply