Expose HDB-Procedure as XSJS service for UI5/Front End Use in SAP HANA

Introduction

This blog provides step by step guide to exposing HDB procedure as XSJS service for UI5 & Front End-use in HANA XS. We’ll also see how to test XSJS service in the browser.

Scenario

We have an HDB procedure with parameters created in SAP HANA and the same we have to expose as XSJS service for UI5 and Front End-use.

Steps:

1. Once the HDB procedure is created, we have to create a new file with the “.xsjs” extension under the respective package as per the below screenshot.

2.In the .xsjs file, we have captured the parameter values first, since we are exposing an HDB procedure with parameter.

Let’s say we have two-parameter in our procedure i.e. Product & Version, so we can get the parameter values from URL using the below code.

var p_product = $.request.parameters.get('im_product');
var p_version = $.request.parameters.get('im_version');

3. Declare results set to capture the output and get the HDB connection details.

var results = {};   ----> Result Set
results.data = [];
var conn = $.hdb.getConnection();  --> HDB connection details 

4. Prepare query statement to call the HDB procedure inside XSJS file
and capture the output of the procedure in the respective result set, as per the below sample.

                --//-->Input Parameter of HDB procedure
var p_product = $.request.parameters.get('im_product');  
                --//-->Input Parameter of HDB procedure
var p_version = $.request.parameters.get('im_version');  
                --//--> Resutl set to capture output 
var results = {};                                        
results.data = [];		

try {
                 --//--> HDB connection details
var conn = $.hdb.getConnection(); 
                --//--> HDB proceude and scheman name                       
var query = conn.loadProcedure("IGO", "IGO.Proc::Order_Calc_SRT_p"); 
               --//--> Pass the parameter values
var params = query(p_product, p_version);    
              --//--> Capture the output using out paramater of HDB procedure                        
var out_data = params['p_out_table'];                                 
             --//--> Pass the output to result set                                                                        
results.data = out_data; 
            --//--> pass the status code to  response                                           
$.response.status = $.net.http.OK;                                   
$.response.contentType = "application/json";
           --//--> set response body with result output
$.response.setBody(JSON.stringify(results));                         
          --//--> Error handeling                                                                        
} catch (err) {                                                     

$.response.setBody(err.message);
}
conn.commit();
conn.close();

5. Set the response body using HDB procedure output.

6. Save the file and activate it.

Testing

Click on “Run” To test the XSJS service in the browser and pass all the parameters in the URL separated by “&”. Refer below the highlighted screenshot of the URL.

xsjs Service URL with Parameter

Now we can use the XSJS service URL in SAP UI5/Frontend to consume the HDB procedure.