Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS
Connecting to a HANA Database using PHP from Ubuntu 14.04 LTS
I need to setup a PHP Website to connect to a HANA. The site is already installed with PHP and MySQL. Time permitting, I will also document the process of converting the existing MySQL Database to HANA. The environment:
- Hana OS: SUSE Linux Enterprise Server 11 SP3 x86_64 (64-bit)
- Web Server: Ubuntu 14.04 LTS
- PHP Version:5.5.9-1ubuntu4.5
First install the php odbc package
- sudo apt-get install php5-odbc
PHP ODBC Information
#php -i|grep odbc /etc/php5/cli/conf.d/20-odbc.ini, /etc/php5/cli/conf.d/20-pdo_odbc.ini, odbc ODBC_LIBS => -lodbc odbc.allow_persistent => On => On odbc.check_persistent => On => On odbc.default_cursortype => Static cursor => Static cursor odbc.default_db => no value => no value odbc.default_pw => no value => no value odbc.default_user => no value => no value odbc.defaultbinmode => return as is => return as is odbc.defaultlrl => return up to 4096 bytes => return up to 4096 bytes odbc.max_links => Unlimited => Unlimited odbc.max_persistent => Unlimited => Unlimited PDO drivers => mysql, odbc
Install HANA Database Client
Before starting, Since the Web server is on Amazon, I just took a quick snapshot so I could rollback to a ‘Pre HANA Client State’ on the server if I needed to.
- Download the HANA using this link SAP Hana Client download the client, you must be a member. Sign up as a public user with all of your information, you will be required to give them some information about your profession industry and company. There are several screen and it takes quite a few before you get to the download. BTW:If this link is broken, please email me and let me know. -[ if this link does not work, try it here }
- Extract the file into a temporary location and cd into the working directory
- cd /tmp
- tar -xvzf /home/michael/sap_hana_client.tgz
- cd sap_hana_linux64_client_rev80/
- Nexe ensure that the scripts that need to execute have execute privileges
- chmod +x hdbinst
- chmod +x hdbsetup
- chmod +x hdbuninst
- chmod +x instruntime/sdbrun
- Finally, run the installation program
- sudo ./hdbinst -a client
- press <enter> to accept the default installation path: /usr/sap/hdbclient
- You will see informaiotn like the following then you can take a look at the log file to see any other details of the installation.
Checking installation...
Preparing package 'Python Runtime'...
Preparing package 'Product Manifest'...
Preparing package 'SQLDBC'...
Preparing package 'REPOTOOLS'...
Preparing package 'Python DB API'...
Preparing package 'ODBC'...
Preparing package 'JDBC'...
Preparing package 'HALM Client'...
Preparing package 'Client Installer'...
Installing SAP HANA Database Client to /usr/sap/hdbclient...
Installing package 'Python Runtime'...
Installing package 'Product Manifest'...
Installing package 'SQLDBC'...
Installing package 'REPOTOOLS'...
Installing package 'Python DB API'...
Installing package 'ODBC'...
Installing package 'JDBC'...
Installing package 'HALM Client'...
Installing package 'Client Installer'...
Installation done Log file written to '/var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log'
- more /var/tmp/hdb_client_2014-12-18_02.06.04/hdbinst_client.log
I saw some reports online that you would have to isntall ‘libaio-dev’, I did not have to, but in case you do, here is the command
- sudo apt-get install libaio-dev
Now you can connect to the hana client
- /usr/sap/hdbclient/hdbsql
Ignoring unknown extended header keyword
This happened to me, this is most likely because the tgz file was created on BSD. to address this install bsd tar and replace the ‘tar’ command above with the bsdtar command below.
- apt-get install bsdtar
- bsdtar -xvzf /home/michael/sap_hana_client.tgz
Testing that the client can connect to a HANA server
To confirm that the client works, run the client and connect to the HANA server
- # /usr/sap/hdbclient/hdbsql
Welcome to the SAP HANA Database interactive terminal
Type: h for help with commands
q to quit
- hdbsql=> c -n xxx.xxx.xxx.xxxx:port -u System -p mypass
I had to type a port, most likly because the server was not installed on a default port.
Creating an ODBC Connection to HANA Server
First Install the unixodbc packages
- sudo apt-get install unixodbc unixodbc-dev odbcinst
- ls /etc/odbc*
/etc/odbc.ini /etc/odbcinst.ini
Open the /etc/odbc.ini file and type the following
[hanadb]
Driver = /usr/sap/hdbclient/libodbcHDB.so
ServerNode =xxx.xxx.xxx.xxx:30015
Note that the blue matches the location you installed to, you must make sure these match or you will receive a cryptic [ISQL]ERROR: Could not SQLConnect message.
Connect using your username and password
- isql hanadb username passwor
+---------------------------------------+
| Connected! |
| sql-statement |
| help [tablename] |
| quit |
+---------------------------------------+
Implementing the HANA ODBC in PHP
As long as you have been able to connect via the ODBC methods above, PHP should be a breeze.
Just place the following in your code.
- $link=odbc_connect(“hanadb”,”myusername”,”mypassword”, SQL_CUR_USE_ODBC);
- $result = odbc_exec($link,”select * from sys.m_tables “);
- while($arr=odbc_fetch_array($result)) print_r($arr);
A couple of gotchas:
- Hana does not have the concept of ‘databases’, they use schemas so the user you connect with must be setup to use the Schema you want by default.
- Or you can prefix every table with your Schema name
- Or you can set the default context to the one you want before each connection
- I accomplished this by adding an extra line under the odbc_connect(); odbc_exec($dblink, “set schema MYSSCHEMA”);
That’s it! More posts to come on HANA.
Thanks to this post for help http://scn.sap.com/community/developer-center/hana/blog/2012/09/14/install-hana-client-on-ubuntu