Currently Being Moderated

Background

Recently I'm kinda of fancy with Nodejs, but as you know that nodejs doesn't support many RDBMS. I know node can connect to MySQL and mongoDB but definitely not HANA, since no one write a drive base on hana database. I'm not an expert of HANA and even Nodejs, so write a drive of HANA is beyond my knowledge. So I decide to connect HANA on my Ubuntu through odbc

 

But I encountered lots of problems when I was trying to accomplish the job that I think it's simple enough, but actually it take me four hours to figure it out how to. I decided to document it just in case someone else need to configure HANA with odbc and maybe it can save you times.

 

1.Install HANA Client on Linux

My environment is Ubuntu 12.04, connect HANA via Windows is easy and well documented. I highly recommend you to install HANA client on your Ubuntu, because you test and debug your connection with HANA. I wrote another post and share you how to install it. My installation is under /opt/sap/hdbclient, if the HANA client was successfully installed. execute command ./hdbsql under /opt/sap/hdbclient you will get

 

ethanz@ubuntu:/opt/sap/hdbclient$ ./hdbsql

 

Welcome to the SAP HANA Database interactive terminal.

 

Type:  \h for help with commands         

       \q to quit                        

 

 

hdbsql=>

 

You can test your connection first by typing following command, now I'm connecting to our modeling server which is a testing environment.


hdbsql=> \c -n your-hana-server -u username -p secret

Connected to VS5@xml1009:30015

hdbsql VS5=>

 

2, Install unixodbc and odbcinst on Ubuntu

 

Before you connect to HANA via odbc, first make sure the odbc and odbc client was installed. odbcinst is the command tool to manage your DSN entry. unixodbc is used to  connect actual database.

 

Install odbc and odbcinst by following command

 

sudo apt-get install unixodbc unixodbc-dev

sudo apt-get install odbcinst

 

3,Edit your ODBC Entry

 

After successfully install unixodbc, system will create two blank ini files /etc/odbc.ini and /etc/odbcinst.ini. Go ahead edit /etc/odbc.ini with your favorite text editor and the input like followings:

 

[hana]

Driver              = /opt/sap/hdbclient/libodbcHDB.so

ServerNode      =hana-server-name:30015

 

I don't know why the configuration is totally different with others (like MySQL). Be careful with the key of ServerNode. It's not Host or Server, the key is ServerNode, otherwise odbc will try to connect to your localhost and always get connection errors. And I found the username and password in the odbc.ini file does NOT work, so you don't need them at all.

 

4, Test your connection

After step 3, you are almost done, now let's test your connection and how does it work. In your terminal, type

 

isql hana username password

 

If you see following responses, congratulations

 

ethanz@ubuntu:~$ isql hana username password

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL>

 

5, Connect hana via Python with odbc.

 

Write a simple test script and see how it's work

 

import pyodbc

conn = pyodbc.connect('DSN=hana;UID=username;PWD=password')

print conn

 

If there's no exception throw out and you can see the connection print out, congrats.

<pyodbc.Connection object at 0x155fd98>

 

6, Conclusion

Several things need wrap up, one thing is the configuration key, it's ServerNode instead of Host/Server. another thing is you have to implicitly to provide the username and password in your connection string or terminal isql command.

 

Last thing to remember, if you install pyodbc via pip like sudo pip install pyodbc, make sure you have installed python-dev, otherwise the compilation will fail.

 

sudo apt-get install python-dev && sudo pip install pyodbc

 

Ok, fellows, that's it. Good luck with your HANA journey.

Comments

Actions

Filter Blog

By author:
By date:
By tag: