Skip to Content

An awesome blog (http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sap-hana-extended-application-services) by Thomas Jung provides us with core concepts and extremely powerful capabilites of SAP HANA Extended Application Services (XS Engine) in SP05.

Our goal was to utilize this platform and build something which will assist users who develop applications on SAP HANA.

While building applications on SAP HANA, most common requirement would be to consume or reuse the existing application core tables and eliminate all possible projections over the core tables such as Info-Cubes, Info-Structures(LIS) etc.

Let us consider a scenario where application requirement is to calculate sales commissions for sales personal, which would depend on data present in core SD tables such as VBAK & VBAP(sales order), VBRK & VBRP(billing doc). For such application, we will have to create application specific tables such as user_table, user_sales_mapping etc. These tables hold information of user and how they are mapped to the SD parameters such as sales org, sales office, sales group, division, distribution channel etc.

Main aspect of developing such an application would be ability to perform necessary testing. For testing such application, we often need to add, change or delete some of the data from HANA database tables. In our case this would be to create or update existing user mapping according to existing data available in IDES data.

Currently we use SQL statements to do this operation is time consuming. Here we discuss on how we can build a generic database editor which will provide options to perform CRUD operations on any HANA table from any Schema.

Setting up the XS Project:

1. Make sure XS engine is configured and working correctly using the test url http://<host_name:<port_no>. (port number 8000 or 8002 usually works)

2. Open the HANA studio with HANA Development perspective.

     > Under SAP HANA systems tab, under content folder of the system, add a new package called “db”.

     > Under SAP HANA Repository, navigate and right click on the folder “DB” and click checkout.

     > Under Project Explorer tab, create a new XS project named “db” and uncheck the use default location checkbox and point the location to directory where  

        checkout was performed within your local repository location.

     > Under db XS project, create the following files

          a. An empty .xsapp file which defines that this is a XS project.

          b. .xsaccess file with following content

               { “exposed” : true }

     > Create a new file database.xsjs under the project “db”. This file contains all the necessary code of our database editor.

Introduction to the frontend:

For the demo purpose, we would be using Grid component of kendo HTML5 framework. In a simple declarative, we can build a table grid.

Refer to http://demos.kendoui.com/web/grid/remote-data.html for more details about kendo UI grid and binding to remote data.

For Grid, we need datasource which is bound to a model. We build this model via getModel method of database.xsjs(server side HANA XS code).

we need to define list of columns, again we obtain this using getMetaData method of database.xsjs.

Coding the database Editor:

Firstly we define commands which our application supports and according write XSJS code to handle these code and call corresponding method to perform the necessary operations:

We will build the following methods within database.xsjs:

1. “read” method to read contents of a table

2. “insert” method to insert a new record to the table

3. “update” method to update a table record. Update will happen using primary keys in the where clause

4. “delete” method to delete a record from the table

5. “getModel” method to get model for the frontend grid binding. (This is tailored as per kendo UI API)

6. “getMetaData” method to get list of column and other details which defines the column behaviour in the frontend. (This is tailored as per kendo UI API)

As mentioned, database.xsjs file contains all the necessary code of our database editor and as follows:

we define default values for table and schema as

var C_DFLT_TABLENAME = ‘COMM_USER_DETAILS3’; //default table to load

var C_DFLT_SCHEMANAME = ‘COMMISSIONS’;

1. We create a read method which will return contents of a given schema_name.table_name in the form of JSON data.

function read(){
var body = ”;

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname : C_DFLT_SCHEMANAME;

  var query = ‘SELECT * from \”‘+schemaname+’\”.\”‘+tablename+’\”‘;
  $.trace.debug(query);
  var conn = $.db.getConnection();
  var pstmt = conn.prepareStatement(query);
  var rs = pstmt.executeQuery();

//build JSON from resultset

  var l_body = ‘{‘;
 
  var rs_meta = rs.getMetaData();
  var colCnt = rs_meta.getColumnCount();
  var cnt = 1; 

  var i = 1;
  while (rs.next()) {
  
   for (i=1;i<=colCnt;i++)
   {
    l_body +=   ‘\”‘ + rs_meta.getColumnLabel(i) + ‘\” : \”‘ + rs.getString(i) + ‘\”,’;
    cnt = cnt+1;
   }
   l_body = l_body.substring(0, l_body.length-1);
   l_body +=   ‘},{‘;
   cnt = 1;
  }

l_body = l_body.substring(0, l_body.length-2); 
body = ‘{ \”d\”: { \”results\”: [‘ + l_body + ‘  ] } }’;

rs.close();
pstmt.close();
conn.close();

//set JSON response

$.response.setBody(body);
$.response.contentType = ‘application/json’;
$.response.status = $.net.http.OK;

}

2. “insert” method to insert a new record to the table. Upon inserting a new record, kendo will send a http request to database.xsjs with new record under parameter named “models”.

 

function insertRecord() {

// request parameter models hold the new record to be inserted in JSON format

var jsonData = JSON.parse($.request.parameters.get(‘models’));

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname
   : C_DFLT_SCHEMANAME;

var obj = 0;
var prop = null;

//build insert statement
var stmt = ‘ ( ‘;
for (prop in jsonData[obj]) {
  if (jsonData[obj].hasOwnProperty(prop)) {
   /* alert(prop + ‘:’ + jsonData[obj][prop]); */
   stmt += ‘\” + jsonData[obj][prop] + ‘\’,’;
  }
}
stmt = stmt.substring(0, stmt.length – 1) + ‘ ) ‘;

var query = ‘INSERT INTO \”‘ + schemaname + ‘\”.\”‘ + tablename + ‘\” ‘
   + ‘ VALUES ‘ + stmt;
var conn = $.db.getConnection();
conn.setAutoCommit(1);
var pc = conn.prepareStatement(query);

//execute insert query
var no_of_rows = pc.executeUpdate();

pc.close();
conn.close();

$.response.status = $.net.http.OK;

//send updated status in response
$.response.setBody(‘query : ‘ + query + ‘<br/> ‘ + ‘ Affected rows : ‘
   + no_of_rows);

}

3. “update” method to update a table record. Update will happen using primary keys in the where clause

function updateRecord() {

// request parameter models hold the updated record in JSON format

var jsonData = JSON.parse($.request.parameters.get(‘models’));

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;

schemaname = typeof schemaname !== ‘undefined’ ? schemaname : C_DFLT_SCHEMANAME;

//get db connection
var conn = $.db.getConnection();

//set autocommit to reflect changes immediately
conn.setAutoCommit(1);

var pstmt;
var rs;
var lenght = 0;
var columns = [];
var stmt = ”, stmt1 = ”;
var i = 0;
var obj;
var prop;

// get primary columns from CONSTRAINTS table from SYS schema

var q = ‘SELECT count(*) as count FROM \”SYS\”.\”CONSTRAINTS\” where table_name = \”
   + tablename
   + ‘\’ and schema_name = \”
   + schemaname
   + ‘\’ and is_primary_key = \’TRUE\”;

pstmt = conn.prepareStatement(q);
rs = pstmt.executeQuery();

while (rs.next()) {
  lenght += rs.getString(1);
}

//build where condition for update query based on primary columns

for (obj in jsonData) {
  if (jsonData.hasOwnProperty(obj)) {
   for (prop in jsonData[obj]) {
    if (jsonData[obj].hasOwnProperty(prop)) {
     if (i < lenght) {
      stmt += ‘\”‘ + prop + ‘\” = \” + jsonData[obj][prop]
        + ‘\’ AND ‘;
      i = i + 1;
     } else {
      break;
     }
    }
   }
  }
}

stmt = stmt.substring(0, stmt.length – 4);

obj = 0;

// build updated column::value pairs used in SET clause of update query
for (prop in jsonData[obj]) {
  if (jsonData[obj].hasOwnProperty(prop)) {
   /* alert(prop + ‘:’ + jsonData[obj][prop]); */
   stmt1 += ‘\”‘ + prop + ‘\” = \” + jsonData[obj][prop] + ‘\’ , ‘;
  }
}

//build update query
stmt1 = stmt1.substring(0, stmt1.length – 2);
var query = ‘ UPDATE \”‘ + schemaname + ‘\”.\”‘ + tablename + ‘\” SET  ‘
   + stmt1 + ‘  WHERE ‘ + stmt;

pstmt = conn.prepareStatement(query);

//execute update query
var no_of_rows = pstmt.executeUpdate();
pstmt.close();
conn.close();

$.response.status = $.net.http.OK;

//send updated status in response
$.response.setBody(‘query : ‘ + query + ‘<br/> ‘ + ‘ Updated rows : ‘   + no_of_rows);

}

4. “delete” method to delete a record from the table

function deleteRecord() {

// request parameter models hold the deleted record in JSON format
var jsonData = JSON.parse($.request.parameters.get(‘models’));

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname  : C_DFLT_SCHEMANAME;

var obj = 0;
var prop = null;
var stmt = ”;

// build where clause for deletion

for (prop in jsonData[obj]) {
  if (jsonData[obj].hasOwnProperty(prop)) {
   /* alert(prop + ‘:’ + jsonData[obj][prop]); */
   stmt += ‘\”‘ + prop + ‘\” = \” + jsonData[obj][prop] + ‘\’ AND ‘;
  }
}

stmt = stmt.substring(0, stmt.length – 4);

//build delete query

var query = ‘DELETE FROM \”‘ + schemaname + ‘\”.\”‘ + tablename + ‘\” ‘
   + ‘ where ‘ + stmt;
var conn = $.db.getConnection();

//set autocommit to reflect changes immediately

conn.setAutoCommit(1);
var pc = conn.prepareStatement(query);

//ececute the delete query
var no_of_rows = pc.executeUpdate();

pc.close();
conn.close();

$.response.status = $.net.http.OK;

//send delete status in response
$.response.setBody(‘query : ‘ + query + ‘<br/> ‘ + ‘ Affected rows : ‘   + no_of_rows);

}

5. “getModel” method to get model for the frontend grid binding. (This is tailored as per kendo UI API)

    

function getModel() {

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname
   : C_DFLT_SCHEMANAME;

// get all columns of the input table from TABLE_COLUMNS table of HANA from SYS schema

var q = ‘SELECT column_name , data_type_name , is_nullable FROM \”SYS\”.\”TABLE_COLUMNS\” where table_name = \”
   + tablename
   + ‘\’ and schema_name = \”
   + schemaname
   + ‘\’ ORDER BY POSITION ASC’;
var conn = $.db.getConnection();
var pstmt = conn.prepareStatement(q);
var rs = pstmt.executeQuery();
var sstr = ”;

// build the kendo model specific to table being edited upon
while (rs.next()) {
  if (sstr === ”) {
   sstr += ‘id: \”‘ + rs.getString(1) + ‘\” , fields: {‘;
  }
  sstr += rs.getString(1) + ‘:{},’;
}
sstr = sstr.substring(0, sstr.length – 1) + ‘}},’;
rs.close();
pstmt.close();
conn.close();

return sstr;
}

6. “getMetaData” method to get list of column and other details which defines the column behaviour in the frontend. (This is tailored as per kendo UI API)

function getMetaData() {

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname
   : C_DFLT_SCHEMANAME;

// get all columns of the input table from TABLE_COLUMNS table of HANA from SYS schema

var q = ‘SELECT column_name , data_type_name , is_nullable FROM \”SYS\”.\”TABLE_COLUMNS\” where table_name = \”
   + tablename
   + ‘\’ and schema_name = \”
   + schemaname
   + ‘\’ ORDER BY POSITION ASC’;
var conn = $.db.getConnection();
var pstmt = conn.prepareStatement(q);
var rs = pstmt.executeQuery();
var sstr = ”;

// build the columns metaData information for Grid component of kendo UI
while (rs.next()) {
  sstr += ‘{ field: \”‘ + rs.getString(1) + ‘\” },’;
}

rs.close();
pstmt.close();
conn.close();

return sstr;
}

Putting it all together:

We will create a main method called loadEditor, which will provide an HTML view to be rendered in the browser. This HTML view contains the kendoUI script to load the GRID component with column list obtained from getMetadata method then connect this to a datasource which refers to the model obtained using getModel method.

loadEditor() method:

function loadEditor() {

//set content type as html.
$.response.contentType = ‘text/html’;

var record = $.request.parameters.get(‘record’);

// tablename and schemaname can be input by user via request parameters, else default constants will be considered

var tablename = $.request.parameters.get(‘tablename’);
var schemaname = $.request.parameters.get(‘schemaname’);

tablename = typeof tablename !== ‘undefined’ ? tablename : C_DFLT_TABLENAME;
schemaname = typeof schemaname !== ‘undefined’ ? schemaname
   : C_DFLT_SCHEMANAME;

var body = ”;
var answer;

var cssStr = “”;

// load necessary files for UI and build the scripts which will be rendered on front-end (web browser)
cssStr += “<link rel=\”shortcut icon\” href=\”\/favicon.ico\”\/>”;
cssStr += ”    <link href=\”http:\/\/cdn.kendostatic.com\/2013.1.319\/styles\/kendo.common.min.css\” rel=\”stylesheet\” \/>”;
cssStr += ”    <link href=\”http:\/\/cdn.kendostatic.com\/2013.1.319\/styles\/kendo.rtl.min.css\” rel=\”stylesheet\” \/>”;
cssStr += ”    <link href=\”http:\/\/cdn.kendostatic.com\/2013.1.319\/styles\/kendo.default.min.css\” rel=\”stylesheet\” \/>”;
cssStr += ”    <link href=\”http:\/\/cdn.kendostatic.com\/2013.1.319\/styles\/kendo.dataviz.min.css\” rel=\”stylesheet\” \/>”;
cssStr += ”    <link href=\”http:\/\/cdn.kendostatic.com\/2013.1.319\/styles\/kendo.dataviz.default.min.css\” rel=\”stylesheet\” \/>”;

body = “<!DOCTYPE html>  <html> <head> <title></title> <script src=\”http://code.jquery.com/jquery-1.8.2.min.js\”></script> <script src=\”http://cdn.kendostatic.com/2013.1.319/js/kendo.all.min.js\”></script> “
   + cssStr;
body += ‘</head> <body><div id=”example” class=”k-content”> <div id=”grid”></div>’;

var strVar = “”;
strVar += “<script>”;
strVar += ”                $(document).ready(function () {“;
strVar += ”                    var crudServiceBaseUrl = \”http:\/\/<host_name>:<port_no>\/db\/database.xsjs?tablename=”
   + tablename + “&schemaname=” + schemaname + “&cmd=\”,”;
strVar += ”                        dataSource = new kendo.data.DataSource({“;
strVar += ”                            transport: {“;
strVar += ”                                read:  {“;
strVar += ”                                    url: crudServiceBaseUrl + \”read\”,”;
strVar += ”                                    dataType: \”json\””;
strVar += ”                                },”;
strVar += ”                                update: {“;
strVar += ”                                    url: crudServiceBaseUrl +  \”update\”,”;
strVar += ”                                    dataType: \”jsonp\””;
strVar += ”                                },”;
strVar += ”                                destroy: {“;
strVar += ”                                    url: crudServiceBaseUrl + \”delete\”,”;
strVar += ”                                    dataType: \”jsonp\””;
strVar += ”                                },”;
strVar += ”                                create: {“;
strVar += ”                                    url: crudServiceBaseUrl +  \”create\”,”;
strVar += ”                                    dataType: \”jsonp\””;
strVar += ”                                },”;
strVar += ”                                parameterMap: function(options, operation) {“;
strVar += ”                                    if (operation !== \”read\” && options.models) {“;
strVar += ”                                        return {models: kendo.stringify(options.models)};”;
strVar += ”                                    }”;
strVar += ”                                }”;
strVar += ”                            },”;
strVar += ”                            batch: true,”;
strVar += ”                            pageSize: 20,”;
strVar += ”                            schema: {“;
strVar += ”                                model: {“;
body += strVar + getModel();
strVar = ”                                data: \”d.results\” “;
strVar += ”                            }”;
strVar += ”                        });”;
strVar += “”;
strVar += ”                    $(\”#grid\”).kendoGrid({“;
strVar += ”                        dataSource: dataSource,”;
strVar += ”                        pageable: true,”;
strVar += ”                        height: 430,”;
strVar += ”                        toolbar: [\”create\”],”;
strVar += ”                        columns: [“;
body += strVar + getMetaData();
strVar = ”                            { command: [\”edit\”, \”destroy\”], title: \” \”, width: \”172px\” }],”;
strVar += ”                        editable: \”inline\””;
strVar += ”                    });”;
strVar += ”                });”;
strVar += ”            <\/script>”;

body += strVar;
body += ” </body></html>”;
$.response.setBody(body);
$.response.status = $.net.http.OK;
}

//The handler block of database.xsjs would determine and call necessary method within database.xsjs to perform the CRUD operations.

var aCmd = $.request.parameters.get(‘cmd’);

switch (aCmd) {

case “load”:

loadEditor();

break;

case “read”:

read();

break;

case “delete”:

deleteRecord();

break;

case “create”:

insertRecord();

break;

case “update”:

updateRecord();

break;

default:

$.response.status = $.net.http.INTERNAL_SERVER_ERROR;

$.response.setBody(‘Invalid Command: ‘ + aCmd);

}

Finally save, commit and activate the database.xsjs file and HANA database editor is ready for use via URL

http://<host_name>:<port_no>/db/database.xsjs?cmd=load&tablename=COMM_USER_DETAILS3&schemaname=COMMISSIONS

If you would like to use this, all you need is to import the attached database.xsjs file into your XS project and make sure you change

<host_name> and <port_no> in this file.

You can change the tablename and schemaname in the URL and then perform CRUD operations on it and accordingly test your applications 🙂

Note: For this version data validations, roles/authorizations are not included. For the demo purpose, we used kendo UI as frontend, but can be easily

replaced with any UI technology.

Screenshots:

Loading Editor:

loadEditor.jpg

Adding New Record:

/wp-content/uploads/2013/06/insert_record_228950.jpg

Updating a record:

/wp-content/uploads/2013/06/edit_record_228951.jpg

Deleting a record:

/wp-content/uploads/2013/06/delete_record_228952.jpg

For any clarifications, feel free to contact us:

Srinivasa Reddy BR (I058650)

A M Shreehari (I058796)

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. Justin Molenaur

    This is a great start to functionality that most BI centric instances will need. I was searching for a simple solution/example to show a project team and luckily found this page! However, I imported this code and was able to get it to recognize the metadata for a given schema/table, but there is no table contents being returned.

    For example, with the following URL, the table columns are being returned, but there is no data being shown when there is actually data in the underlying table.

    database.xsjs?cmd=load&tablename=TEST_TABLE&schemaname=TEST_SCHEMA.

    Additionally, the add record does not actually perform an insert. The system I am working on is rev 62 if that makes any difference.

    Thanks for the effort!

    Regards,

    Justin

    (0) 
    1. Justin Molenaur

      Marlo – from what I can see there is only one place where host/port needs to be changed, which I did. After changing this, did this work for you?

      Regards,

      Justin

      (0) 
      1. Marlo Souza

        Justin,

        Also changed the datatype of the update, destroy and create methods, between lines 161 and 171. Just removed the “P” JSONP, JSON leaving.

        The mine is operating normally. If it works, please give a like.

        Regards,

        Marlo

        (0) 
        1. Justin Molenaur

          It seems I had the wrong host/port, after correcting that and yours above I was able to get data to load up. However, I cannot get the data manipulation to work (insert/update/delete) for some reason.

          Regards,

          Justin

          (0) 
          1. Marlo Souza

            Hello,

            I replaced the JSONP per JSON as code below:

            Original Code

            read:  {“;

              strVar += ”                                    url: crudServiceBaseUrl + \”read\”,”;

              strVar += ”                                    dataType: \”json\””;

              strVar += ”                                },”;

              strVar += ”                                update: {“;

              strVar += ”                                    url: crudServiceBaseUrl +  \”update\”,”;

              strVar += ”                                    dataType: \”jsonp\””;

              strVar += ”                                },”;

              strVar += ”                                destroy: {“;

              strVar += ”                                    url: crudServiceBaseUrl + \”delete\”,”;

              strVar += ”                                    dataType: \”jsonp\””;

              strVar += ”                                },”;

              strVar += ”                                create: {“;

              strVar += ”                                    url: crudServiceBaseUrl +  \”create\”,”;

              strVar += ”                                    dataType: \”jsonp\“”;

              strVar += ”                                },”;

            Changed Code

            read:  {“;

              strVar += ”                                    url: crudServiceBaseUrl + \”read\”,”;

              strVar += ”                                    dataType: \”json\””;

              strVar += ”                                },”;

              strVar += ”                                update: {“;

              strVar += ”                                    url: crudServiceBaseUrl +  \”update\”,”;

              strVar += ”                                    dataType: \”json\””;

              strVar += ”                                },”;

              strVar += ”                                destroy: {“;

              strVar += ”                                    url: crudServiceBaseUrl + \”delete\”,”;

              strVar += ”                                    dataType: \”json\””;

              strVar += ”                                },”;

              strVar += ”                                create: {“;

              strVar += ”                                    url: crudServiceBaseUrl +  \”create\”,”;

              strVar += ”                                    dataType: \”json\“”;

              strVar += ”                                },”;

            Regards,

            Marlo

            (0) 
            1. Dheeram Kallem

              Hello Marlo,

              I already changed the code as above, with that loading of data is working fine, but the insert/update/delete are not working. Any suggestions?

              Thanks,

              Dheeram

              (0) 
                  1. Justin Molenaur

                    Marlo, I am testing with a table that sits in the user schema of the user I am testing with, so I definitely have full object privileges for the schema. I thought this might the problem, but it looks good on that side. Any other permissions you may be speaking of?

                    Regards,

                    Justin

                    (0) 
                    1. Dheeram Kallem

                      While debugging I found that these operation update/delete/insert statements are hanging and the control is not returning back.

                      I’m also testing with a user who has full permission on the schema. I used other xsodata

                      service on the same table with same user, which is working. Am I missing something here?? When I do the any of these operations a long URL is generated with all the fields of the row(which contains URL escape characters in them).

                      Thanks,

                      Dheeram

                      (0) 
        2. Dheeram Kallem

          I’m also having same issue as mentioned by Justin. I’m able to load the data, but somehow my insert/update/delete operations are failing. When I check the console I found that it failed to load the resource. The URL that is generated is long which included with data, but I think if I’m not wrong that data has to be sent as in JSON format.

          (0) 
  2. Henk Binnendijk

    function looks handy but it indeed doesnot work when using the attached code. also when validating it points to variables that are not used (resulting in warnings) it seems that the code is not complete… with me it returns the DDL in screen without data, adding records doesn’t work either. So the function to get the tablestructure is good, but when it has to load data it seems to miss some code…

    (0) 

Leave a Reply