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

1. 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.

1. 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 }
2. Extract the file into a temporary location and cd into the working directory
   1. cd /tmp
   2. tar -xvzf /home/michael/sap_hana_client.tgz
   3. cd sap_hana_linux64_client_rev80/
3. Nexe ensure that the scripts that need to execute have execute privileges
   1. chmod +x hdbinst
   2. chmod +x hdbsetup
   3. chmod +x hdbuninst
   4. chmod +x instruntime/sdbrun
4. Finally,  run the installation program
   1. sudo ./hdbinst -a client
   2. press <enter> to accept the default installation path: /usr/sap/hdbclient
   3. 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'

   4. 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

1.  sudo apt-get install libaio-dev

Now you can connect to the hana client

1. /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.

1. apt-get install bsdtar
2. 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

1. # /usr/sap/hdbclient/hdbsql

   Welcome to the SAP HANA Database interactive terminal

   Type: h for help with commands

         q to quit

2. 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

1. sudo apt-get install unixodbc unixodbc-dev odbcinst
2. 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

1. 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.

1. $link=odbc_connect(“hanadb”,”myusername”,”mypassword”, SQL_CUR_USE_ODBC);
2. $result = odbc_exec($link,”select * from sys.m_tables “);
3. 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