SAP HANA SPS 09: New Developer Features; New XSJS Database Interface

This blog is part of the larger series on all new developer features in SAP HANA SPS 09:http://scn.sap.com/community/developer-center/hana/blog/2014/12/02/sap-hana-sps-09-new-developer-features

In this blog we will have a first look at the new XSJS database interface in SAP HANA SPS09.  This is a completely redesigned and rebuilt database interface which replaces the current implementation in the $.db package.  This new interface, which is available as a separate API in $.hdb, focuses on several key areas of improvements.

Performance gains

• Achieves higher throughput on both read and write operations of a single session
• Better scale out support and usage of multiple nodes in a HANA scale out scenario
• Reduce the amount of remote process communication even in distributed query scenarios

Usability improvements

• Simple and easy to use JavaScript interface that accepts and returns JavaScript variables and JSON objects. No more type specific getter/setters
• Code reduction
• No boilerplate code

Light-weight architecture

• Based upon a new, thin C++ client library
• No SQL processing in the XS Layer itself. Push all SQL processing into the Index Server
• Uses internal HANA communication protocol optimizations

The usage of this new API is best explained with a few samples.

The old database interface

First lets look at the pre-SPS 09 database interface.

var productId = $.request.parameters.get("ProductId");
  productId = typeof productId !== 'undefined' ? productId : 'HT-1000'
var conn = $.db.getConnection();
var query = 'SELECT * FROM "SAP_HANA_EPM_NEXT"."sap.hana.democontent.epmNext.data::EPM.Purchase.Item" ' +
            ' WHERE "PRODUCT.PRODUCTID" = ? ';
var pstmt = conn.prepareStatement(query);
    pstmt.setString(1, productId);
var rs = pstmt.executeQuery();
var body = '';
while (rs.next()) {
  var gross = rs.getDecimal(6);
  if(gross >= 500){
  body += rs.getNString(1) + "\t" + rs.getNString(2) + "\t" +
         rs.getNString(3) + "\t" + rs.getDecimal(6) + "\n";
  }
}rs.close();
pstmt.close();
$.response.setBody(body);
$.response.contentType = 'application/vnd.ms-excel; charset=utf-16le';
$.response.headers.set('Content-Disposition',
  'attachment; filename=Excel.xls');
$.response.status = $.net.http.OK;

Notice in this example how you build the query string but then must set the input parameters via a separate setString function. Not only is this extra code, but also error prone because you must use the correct function call for the data type being set.

More troublesome, however, is the result set object returned from the query.  This rs object is a special object than can only be iterated over once in order. No direct index support.  Its contents aren’t visible in the debugger and you have to use similar type specific getters to retrieve individual column values.

The new database interface

Now for the same example rewritten with the new database interface in SPS 09.

var productId = $.request.parameters.get("ProductId");
  productId = typeof productId !== 'undefined' ? productId : 'HT-1000'
var conn = $.hdb.getConnection();
var query = 'SELECT * FROM "SAP_HANA_EPM_NEXT"."sap.hana.democontent.epmNext.data::EPM.Purchase.Item"' +
            ' WHERE "PRODUCT.PRODUCTID" = ?';
var rs = conn.executeQuery(query,productId);
var body = '';
for(var i = 0; i < rs.length; i++){
   if(rs[i]["GROSSAMOUNT"] >= 500){
  body += rs[i]["HEADER.PURCHASEORDERID"] + "\t" + rs[i]["PURCHASEORDERITEM"] + "\t" +
  rs[i]["PRODUCT.PRODUCTID"] + "\t" + rs[i]["GROSSAMOUNT"] + "\n";
   }
}
$.response.setBody(body);
$.response.contentType = 'application/vnd.ms-excel; charset=utf-16le';
$.response.headers.set('Content-Disposition',
  'attachment; filename=Excel.xls');
$.response.status = $.net.http.OK;

The most striking difference is the removal of the need for the type specific getters or setters.  Now you simply pass in your JavaScript variable and the interface determines the type.  The result set is no longer some special object type, but instead a JSON object.  You process it in your JavaScript as you would any other JSON (direct index access, easy looping, or combination of both); accessing the columns by name.  The other advantage is that the result set object might look and act like a JSON object, but in fact it is rather special. It doesn’t materialize the data into the JavaScript VM. Instead only pointers to the data are maintained in the JavaScript VM as long as only read operations are performed on the data.  The helps to keep the memory requirements of the JavaScript VM lower.

This also means that you can view the contents of this result set object easily within the debugger.

Another excellent advantage of this new interface is that because the result set object of a query is JSON its ready for output. So often most of the processing in an XSJS service was just to convert the Result Set to JSON so it can be passed to the client side. Now we can these results and directly insert them into a response object.

var connection = $.hdb.getConnection();
var results = connection.executeQuery(
  'SELECT * FROM "sap.hana.democontent.epmNext.data::EPM.MasterData.Employees" ' +
  'WHERE LOGINNAME <> ?', 'EPM_USER');
$.response.setBody(JSON.stringify(results));

But this new interface doesn’t just help with SQL statements.  It also provides similar benefits to calling SQLScript stored procedures from XSJS. This interface creates what appears to be a JavaScript function to serve as a proxy for calling the stored procedure.  We can then easily pass in/out JavaScript variables and JSON objects for the procedure interface. No more having to insert data into temporary tables just to pass it into a procedure call.

var connection = $.hdb.getConnection();
var partnerRole = $.request.parameters.get("PartnerRole");
partnerRole = typeof partnerRole !== 'undefined' ? partnerRole : '01';
var getBpAddressesByRole = connection.loadProcedure("SAP_HANA_EPM_NEXT",
"sap.hana.democontent.epmNext.procedures::get_bp_addresses_by_role");
var results = getBpAddressesByRole(partnerRole);
//Pass output to response
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(results));