lunes, 21 de enero de 2013

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



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


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




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 -;)

Greetings,

Blag.