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
Welcome to the SAP HANA Database interactive terminal.
Type: \h for help with commands
\q to quit
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
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:
Driver = /opt/sap/hdbclient/libodbcHDB.so
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 |
5, Connect hana via Python with odbc.
Write a simple test script and see how it's work
conn = pyodbc.connect('DSN=hana;UID=username;PWD=password')
If there's no exception throw out and you can see the connection print out, congrats.
<pyodbc.Connection object at 0x155fd98>
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.