SAP HANA Native Development - Generic Table Editor...

srinivasabyaiahramachan_r · ‎06-10-2013

An awesome blog (http://scn.sap.com/community/developer-center/hana/blog/2012/11/29/sap-hana-extended-application-ser...) 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;

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

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

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 :smile:

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:

Adding New Record:

Updating a record:

Deleting a record:

For any clarifications, feel free to contact us:

Srinivasa Reddy BR (I058650)

A M Shreehari (I058796)

SAP HANA Native Development - Generic Table Editor to perform CRUD operations

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win