jueves, 30 de mayo de 2013

AngularJS, PHP and SAP HANA

Yesterday, I realized that it's been a while since a post a blog about SAP HANA...so I decided to think about something cool to write about...checking my feeds, I came to notice AngularJS The Super Heroic JavaScript MVW Framework...

So...what is good about AngularJS? Well...according to them...

Other frameworks deal with HTML’s shortcomings by either abstracting away HTML, CSS, and/or JavaScript or by providing an imperative way for manipulating the DOM. Neither of these address the root problem that HTML was not designed for dynamic views.

So...in this blog, we're going to use PHP to get information from SAP HANA, return them as a JSON object and be presented by AngularJS.

Let's get our hands dirty -;)

First...I create a System DSN for my SAP HANA connection...so I could call it from PHP...

menu.php
<?php
$conn = odbc_connect("HANA_SYS","SYSTEM","********", SQL_CUR_USE_ODBC);
$query = "SELECT table_name from SYS.CS_TABLES_ where schema_name = 'SFLIGHT'";
$rs = odbc_exec($conn,$query);
$result = array();
while($row = odbc_fetch_array($rs)){
          $menu["table_name"] = $row["TABLE_NAME"];
          array_push($result,$menu);
}
echo json_encode($result);
?>

This code will allow us to get all the tables names included in the SFLIGHT schema...so we can present a dropdown list to choose from...

tables.php
<?php
$data = file_get_contents("php://input");
$objData = json_decode($data);
$data = $objData->data;
$conn = odbc_connect("HANA_SYS","SYSTEM","*********", SQL_CUR_USE_ODBC);
$query = "select column_name from SYS.CS_COLUMNS_ A inner join SYS.CS_TABLES_ B";
$query .= " on A.table_oid = B.table_oid where schema_name = 'SFLIGHT'";
$query .= " and table_name = '$data' and internal_column_id > 200 order by internal_column_id";
$rs = odbc_exec($conn,$query);
$result = array();
$fields = array();
$fields_array = array();
while($row = odbc_fetch_array($rs)){
          array_push($result,$row["COLUMN_NAME"]);
          $fields["FIELDS"] = $row["COLUMN_NAME"];
          array_push($fields_array,$fields);
}
sort($fields_array);
 
 
$content = array();
$table = array();
$query = "select * from SFLIGHT.$data";
$rs = odbc_exec($conn,$query);
array_push($content,$fields_array);
while($row = odbc_fetch_array($rs)){
          for($i=0;$i<count($result);$i++){
                    $table["$result[$i]"] = $row["$result[$i]"];
          }
          array_push($content,$table);
}
echo json_encode($content);
?>

This code will get a table name as parameter and will get the Fields name and the contents of the table...both PHP scripts will return a JSON object...

get_menu.js
function MenuCtrl($scope, $http) {
    $scope.url = 'menu.php';
        
        $http.post($scope.url).
        success(function(data, status) {
            $scope.status = status;
            $scope.data = data;
            $scope.tables = data;
        })
        .
        error(function(data, status) {
            $scope.data = data || "Request failed";
            $scope.status = status;        
        });
 
            $scope.gettable = function() {
                      $scope.url = 'tables.php';
        $http.post($scope.url, { "data" : $scope.table}).
        success(function(data, status) {
            $scope.status = status;
            $scope.data = data;
                              $scope.contents = data;
        })
        .
        error(function(data, status) {
            $scope.data = data || "Request failed";
            $scope.status = status;        
        });
    };
}

This code will simply read back the JSON objects generated by the PHP scripts...the first part for dropdown list and the second for the tables fields and contents...

Finally...we have our HTML which call the AngularJS script to perform the magic...

main.html
<!DOCTYPE html>
<html ng-app>
<head>
<title>Angular.JS, PHP and SAP HANA</title>
    <link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" />
    <script src="http://code.angularjs.org/angular-1.0.0.min.js"></script>
    <script src="get_menu.js"></script>
</head>
<body ng-controller='MenuCtrl'>
<div align="center">
          <H1>AngularJS, PHP and SAP HANA</H1>
          <form>
                    <label>Choose table:</label>
                    <select ng-model="table">
                              <option ng-repeat="table in tables" value='{{table.table_name}}'>{{table.table_name}}
                    </select>
                    <button type="submit" class="btn" ng-click="gettable()">Get Table</button>
          </form>
          <br/>
          <table border="1" ng-show="contents.length">
                    <tr ng-repeat="(key, value) in contents" ng-show="$first">
                              <th ng-repeat="values in value")>{{values.FIELDS}}</th>
                    </tr>
                    <tr ng-repeat="(key, value) in contents" ng-show="!$first">
                              <td ng-repeat="values in value")>{{values}}</td>
                    </tr>
          </table>
</div>
</body>
</html>

This code is simple...it will grab the JSON objects and simply use repeaters to read the information and place it on both the menu and the table.

Here's are some screenshots of the application running...




Now...you might notice that the fields are actually sorted! And not in the defined order...well...this is a JavaScript problem more than an AngularJS problem...at least that's what I found out...but...who cares in the end...the data is presented and that's all that matters -;)

One thing to notice as well...is that AngularJS is not particularly fast...for some big tables...it will throw up a time limit exception...even when SAP HANA will send back the information really fast...and for sure PHP is going to create the JSON objects as fast as well...I guess the root problem is that AngularJS will sort the columns, read each line and then construct the table...or...maybe it's just because I'm an AngularJS newbie -:P

Either way...this my was my first experience ever with AngularJS...and I gotta say...I had a lot of fun! -:D The learning curve is really fast and AngularJS provide many cool features that makes it and really good alternative for web development -;)

See you in my next blog -}:)

Greetings,

Blag.