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
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)
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!
- SAP Managed Tags:
1 Comment
