Skip to Content

PHP rocks on SAP HANA too!

A couple of days ago, a friend told me that some people were trying to make PHP and SAP HANA work without having any success…of course…I took that as my new goal…I haven’t done any PHP is a very long time…but that never stops me 😉

First thing…was to create an ODBC connection and try to make it work…it didn’t…but only because I create it using a “User DSN” instead of a “System DSN”…when do we need to use one or the other…I have no clue…but while it works…I don’t care…

Now that it was working…it was time to make a simple query…this failed too…with a nasty and weird message…

PHP_SAPHANA_001.pngScrollable Result is not yet implemented…what’s that suppose to mean? At first…I thought that maybe NVARCHAR is not supported, so I created a temp table using only two VARCHAR fields…same result…so it was something else…in the end…after looking in Google…I found out that some Databases allows cursors to go back and forth…and somehow…with SAP HANA it was giving me an error…easiest solution was to specify the cursor 😉

$conn = odbc_connect(“HANA_KT_SYS”,”SYSTEM”,”manager”, SQL_CUR_USE_ODBC);

The use of SQL_CUR_USE_ODBC was all I needed to keep going…

For this example, I decided to create an Attribute View joining the tables SPFLI and SCARR. For the first screen I will show all the available CARRIDs and on the second screen, I will show a table with some of the fields on a HTML table. Something simple and direct.

PHP_SAPHANA_002.png

Now, let’s take a look at the PHP code…

PHP_SAPHANA.php

<?php

$conn = odbc_connect(“HANA_KT_SYS”,”SYSTEM”,”manager”, SQL_CUR_USE_ODBC);

if (!($conn)) {

          echo “<p>Connection to DB via ODBC failed: “;

          echo odbc_errormsg ($conn );

          echo “</p>\n”;

}

else{

          if(isset($_POST[“CARRID”]) == false)

          {

                    $sql = “SELECT CARRID, CARRNAME FROM SFLIGHT.SCARR WHERE MANDT = 300”;

                    $rs = odbc_exec($conn,$sql);

                    print(“<DIV ALIGN=’CENTER’>”);

                    print(“<H1>SAP HANA from PHP</H1>”);

                    print(“<FORM NAME=’Get_Data’ ACTION=’$_SERVER[PHP_SELF]’ METHOD=’POST’>”);

                    print(“<SELECT NAME=’CARRID’>”);

                    while($row = odbc_fetch_array($rs)){

       $carrid = $row[“CARRID”];

       $carrname = $row[“CARRNAME”];

       print(“<OPTION VALUE=’$carrid’>$carrname”);

                    }

              print(“</SELECT>”);

    print(“<INPUT TYPE=’SUBMIT’ VALUE=’Get Data’>”);

    print(“</FORM>”);

    print(“</DIV>”);

          }

          else{

                    $carrid_param = $_POST[“CARRID”];

                    $sql = “SELECT * FROM \”_SYS_BIC\”.\”blag/AV_FLIGHTS\”

             WHERE CARRID = ‘$carrid_param'”;

                    $rs = odbc_exec($conn,$sql);

                    print(“<DIV ALIGN=’CENTER’><TABLE BORDER=1>”);

                    print(“<TR><TH>MANDT</TH><TH>CARRID</TH><TH>CONNID</TH>

                   <TH>COUNTRYFR</TH><TH>CITYFROM</TH>

                   <TH>AIRPFROM</TH><TH>COUNTRYTO</TH>

                   <TH>CARRNAME</TH><TH>DISTANCE</TH></TR>”);

                    while($row = odbc_fetch_array($rs)){

       $mandt = $row[“MANDT”];

       $carrid = $row[“CARRID”];

       $connid = $row[“CONNID”];

       $countryfr = $row[“COUNTRYFR”];

       $cityfrom = $row[“CITYFROM”];

       $airpfrom = $row[“AIRPFROM”];

       $countryto = $row[“COUNTRYTO”];

       $carrname = $row[“CARRNAME”];

       $distance = $row[“DISTANCE”];

       print(“<TR><TD>$mandt</TD><TD>$carrid</TD>

               <TD>$connid</TD><TD>$countryfr</TD>

               <TD>$cityfrom</TD><TD>$airpfrom</TD>

               <TD>$countryto</TD><TD>$carrname</TD>

               <TD>$distance</TD></TR>”);

                    }

                    print(“</TABLE>”);

                    print(“<A HREF=’PHP_SAPHANA.php’>Go Back</A></DIV>”);

          }

}

?>

Now, we can call it from any Web Browser…

PHP_SAPHANA_003.png

PHP_SAPHANA_004.png

PHP_SAPHANA_005.png

As you can see…there’s no limitation to what you can do with SAP HANA…sometimes…it’s just take a little bit more of research 😉

32 Comments
You must be Logged on to comment or reply to a post.
  • love it blag,

    you’re a pioneer

    I’m doing something funny with Hana at the moment too, the curiosity challenge of will it work won’t it is the motivator

    Once it is working I’m thinking to blog it, but I gotta say, this is in no way officially encouraged nor supported by SAP, we could call it, fun  research.

    All the best,

    Andy.

    • Andy:

      Thanks for the kind words 🙂 I’m just doing my job and having fun…or having fun and doing my job 😉

      Would love to read your blog! Totally looking forward for it! 🙂

      Greetings,

      Blag.

  • And yet another (language) door slammed right in…  🙂

    You are aiming for the world record title for having connected HANA with the largest number of development environments out there, aren’t you?

    Keep on!

      • I was just wondering if SAP HANA could contribute into world’s next fastest SUPER COMPUTER.

        I know HANA is more of a database, and super computers are about faster processing (calculations) power. But HANA do has capacity of parallel processing at very fast speed.

        Can HANA make any promises around getting faster to end results as the super computers does?

  • Hi  Alvaro

    Thanks for PHP blog with HANA, (another assignment to do 😉 )  BTW I agree with Lars Breddemann about making the world record 😉 for connecting HANA with every available language on this planet.

    Also can you please elaborate ODBC connection using USER DSN. 

    I am also looking forward for “Series of blog – on Hadoop with HANA” and whenever you start let us know, so that we all can follow you step by step. 🙂

    Regards

    Kumar

    • Kumar:

      Thanks 🙂 Always happy to share my little knowledge 😉

      Well…you can create USER or SYSTEM DSN connection for ODBC…for most languages “USER” works fine…for PHP and R for example it must be “SYSTEM”…not sure why…haven’t done any research either…

      For Hadoop with HANA…well…it might take a while 🙁 I’m very busy these days…and will start two online courses…one on MongoDB and other on R…so…give me time and I will provide 😀

      Greetings,

      Blag.

  • Great blog. Few learnings are below. I’m using a 64bit Windows 7 machine.

    1. I had Zend server installed and odbc was disabled (found thru phpinfo() that gets from php.ini and echos)

    2. Enabled the odbc by changing the php.ini (enabling the odbc extensions)

    extension=php_pdo_odbc.dll

    extension=php_odbc.dll

    You will be able to edit the php.ini only if you open notepad with “run as administrator”

    3. My machine was a 64 bit and I had installed Hana client of 64 bit (so no odbc drivers of 32 bit were installed).

    If you use the odbcad32 (from windows run) for defining the DSNs, you will be mislead as this opens the 64 bit DSNs. If you want to check the 32 bit DSNs, you will need to use the odbcad32.exe in “C:\Windows\SysWoW64”

    4. Installed the 32 bit Hana client that installs the 32 bit odbc drivers

    Then it worked like magic !

  • Hi …

    Thanks for this informative blog …

    I have a query related to this … I am trying to call a procedure from PHP

    My procedure returns 2 parameters. However, I can get only 1 as output … how to get the 2nd one as output…

    I am calling the procedure like this:

    $sql = “CALL CALL_KSB1(‘800′,’1000′,’20120101′,’20120331′,”,”,’H1′,”,”,”,’B’,?,?)”;

    $result = odbc_exec($hanadb,$sql);

    In the $result, I get output of only 1 parameter … how to get the output of the other parameter ?

    • Varun:

      I’m not sure I understand correctly…but procedures should return only one value…unless I’m really missing something…

      Greetings,

      Blag.

      Developer Empowerment and Culture.

  • Great post! 🙂

    Reading this actually draws me closer to SAP’s HANA every time I read it! PHP was the first code-level-language I started learning.

    So (naturally!) this is not my first time reading your post. 😉

    Thank you for sharing!

    Tharindu Fernando

    • Well…PHP and ERP was the topic that brought me closer to SCN…so I understand your feelings 🙂 Glad you liked my blog… 😀

      Greetings,

      Blag.

      Development Culture.

  • Hi,

    Thanks for the info. I could successfully connect to HANA using PHP. However, I hit a wall when trying to get utf-8 data using PDO_ODBC connector. Query runs fine, I count the resulting records fine. When fetching the data, when a non-ascii character is encountered, the $result->fetch() returns “false”. As a result the reading loop ends much before hitting the end of the table. I just can’t figure out a work around for this issue. Any suggestions?

  • Hello!  I realize this is a very old post, but it’s what came up while searching for HANA PHP ODBC so…  here’s my question:  Does this work the same using the DSN-less connection strings, or are DSNs a must?

    • Hello Michael:

      Well…I haven’t try it but I think it would be perfectly possible…because in the end the DSN connection is using the driver…so creating a DSN-less connection shouldn’t be a problem….

      Greetings,

      Blag.

      Development Culture.