SAP HANA Tutorial, Material and Certification Guide

In this article we will show you an XSJS example to demonstrate – How to do database activities from XSJS service.
Note: To check all the examples on XSJS, read the article SAP HANA XSJS Examples

Introduction:

In this example we will explain how to do SELECT, INSERT, UPDATE and DELETE operation from XSJS.

Prerequisite:

Open SQL Console and paste below script to create table and insert records.

--REPLACE SAP_HANA_TUTORIAL WITH YOUR SCHEMA NAME
-- CREATE PRODUCT TABLE
CREATE COLUMN TABLE "SAP_HANA_TUTORIAL"."PRODUCT_INFO"(
"PRODUCT_ID" INTEGER NULL PRIMARY KEY,
"PRODUCT_NAME" VARCHAR (100),
"SUPPLIER_COUNTRY" VARCHAR(50)
);
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT_INFO" VALUES(1,'Shirts','India');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT_INFO" VALUES(2,'Jackets', 'India');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT_INFO" VALUES(3,'Watches', 'Japan');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT_INFO" VALUES(4,'Mobile','USA');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT_INFO" VALUES(5,'Purse', 'India');

Example:

Create an XSJS file and paste the below code. Make sure you change the schema name to your schema name.

function getDataFromTable(){
var supplierCountry = $.request.parameters.get('supplierCountry');
var conn = $.db.getConnection();
var pstmt;
var rs;
var query;
var output = {results: [] };
try {
query = 'SELECT PRODUCT_ID, PRODUCT_NAME, SUPPLIER_COUNTRY FROM \"SAP_HANA_TUTORIAL\".\"PRODUCT_INFO\" WHERE SUPPLIER_COUNTRY = ?';
pstmt = conn.prepareStatement(query);
pstmt.setString(1, supplierCountry);
rs = pstmt.executeQuery();

var record = {};
while (rs.next()) {
record.productId = rs.getString(1);
record.productName = rs.getString(2);
record.supplierCountry = rs.getString(3);
output.results.push(record);
}
rs.close();
pstmt.close();
conn.close();
}
catch (e) {
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.setBody(e.message);
return;
}
var body = JSON.stringify(output);
$.response.contentType = 'application/json';
$.response.setBody(body);
$.response.status = $.net.http.OK;
}

var aCmd = $.request.parameters.get('cmd');
switch (aCmd) {
case "select":
getDataFromTable();
break;
default:
$.response.status = $.net.http.INTERNAL_SERVER_ERROR;
$.response.setBody('Invalid Command: ', aCmd);
}

Run XSJS Service:

Run the XSJS service by passing query parameter as
http://hana_host:<port_no>/mypackage/DatabaseConnectivity.xsjs?cmd=select&supplierCountry=India
The output will give products of supplier country India as below