Connecting To SAP HANA via PHP and ODBC
We’ve recently been so busy in the office with all things BI on HANA that it’s been a while since I had an opportunity to blog. Wow, how the world is changing, and how quickly too!
I was messing about last week with some custom views to support a direct Lumira connection and thought it would be interesting to see how difficult it would be to connect to SAP HANA via PHP and ODBC.
Those who have seenFormer Member will know that I love building visualisations using the D3.js library, and acquiring a dataset is the foundation step of all good visualisations.
It struck me that connecting directly to SAP HANA from my own web server might open up a few interesting opportunities. Also, since the advent of B4HANA and SAP’s stated objective to remove all barriers to the Enterprise BI datasets held in SAP BW systems it seems we are all now becoming DBAs (its views all the way down). And what better way to prove your HANA modelling concepts than messing about with your own custom UX.
(Note: this is just a PoC and not intended to usurp the Fiori UX development approach – I just wanted to see if I could connect and what the effort would be).
Well, probably not surprisingly, it was pretty easy (once I had configured the correct ODBC drivers!)
Here is an image of a subset of one of our Product Hierarchies to illustrate what I was able to achieve:
Here’s what you need to do:
1. Install the SAP HANA ODBC Drivers:
- The best way (and only way I could find) to do this is to install the SAP HANA Client. I found this here: https://launchpad.support.sap.com/#/softwarecenter (search for hana client)
- Be sure to choose the appropriate configuration for your application. I’m running a 64bit Windows machine so I chose the following file:
- IMDB_CLIENT100_112_7-10009664.SAR
- For 32bit Windows choose: IMDB_CLIENT100_112_7-10009665.SAR
- Note: You might also need to download and install the SAPCAR zip tool to unpack the archive.
- Then install the SAP HANA Client, this will install the relevant HANA ODBC driver, see next step.
2. Create an ODBC Datasource
- Search for the ODBC Data Source Administrator on your machine (I searched my machine for “ODBC” and then selected the 64bit version of the ODBC admin tool)
- In the ODBC Data Source Administrator go to the System DSN page and add a new Data Source.
- Use the HDBODBC (64bit) or HDBODBC32 (32 bit) driver that was been installed when you installed the SAP HANA Client, see above.
- Configure your connection with the IP address and the Port (Note: the port number is 3+<instance number – usually 00>+15: 30015.
- You might also find the following SAP Help page useful.
3. Create a PHP file to execute a query on the SAP HANA database
- I used the PHP PDO protocol to make the connection. The main PHP syntax is as follows:
$username = “<HANA User ID”;
$password = “<HANA password>”;
$dsn =”odbc:<name of Data Source>”;$queryString = ‘SELECT …’;
try {
// Connect to the data source
$dbh = new PDO($dsn, $username, $password);
$stmt = $dbh->prepare($queryString);
$stmt -> execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);.
- Then I wrote some manual logic to parse a JSON structure from the result set, and then closed out the PHP file:
catch (Exception $e) {
echo $e->getMessage();
}
?>
4. Write a Web Application to visualise the data
- Finally I adapted the D3 Radial Tree example from Mike Bostock into an HTML, JS, CSS solution and hosted it on my local webserver.
- Instead of using a static .csv or .json file I pointed my javascript at the .php file I created in the above step to query the HANA database in realtime.
- All-in-all quite a satisfying PoC.
Not sure if this post is useful to anyone else out there, but I found the exercise informative.
Later,
Andrew
Hello Andrew,
Thanks for your feedback.
I also tried this and succeeded but without using PDO. Can you tell me if you don't have trouble handling UTF8 charset between client and database ? Currently having errors in the management of accents (when I read from database and when i insert).
Regards,
Christian
Hi Christian,
I have not yet reached those challenges, but I'm sure by now you would have found the recommendation to use the following extension to the connection string:“CHAR_AS_UTF8=true”.
Please let me know if it works!
Maybe also check our Blag's excellent blog and the comments at the end where this link appears.
Hope it helps,
Andrew
At this moment, we ar using odbc_connect method to connect with sap-hana, but we want to move to PDO connection method.
Is it possible to add schema on PDO connection? Do we really need to create an ODBC Datasource? It is not necessary for odbc_connect method. Only a proper connection string.