SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
XSA Overview
+
-
XS Overview
+
-
Hello World Example
+
-
XS OData Service
+
-
HANA XSJS Service
+
-
Core Data Service
+
-
Others

XSJS - Database Connectivity

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();

        while (rs.next()) {
               var record = {};
                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

SAP HANA XSJS Database Connectivity

Download Full Source Code:

Click here to download the full source code. Follow the steps mentioned in “How to Run.txt” to run it.

Reference:

SAP HANA XS JavaScript Reference

Continue reading:




Support us by sharing this article.



Explore More
Close X
Close X

5 thoughts on “SAP HANA XSJS – Database Connectivity

  1. You have to move “var record = {};” into the “while (rs.next())” loop, otherwise you only get the last record several times.

  2. Admin says:

    Hi Norbert,
    Thanks. We have corrected it.

  3. Sarthak Srivastava says:

    Hi friends,
    When we run xsjs script we get a promt to enter userid and password.
    Is there a way that this promt doesn’t come and we can pass session userid in the script only..?

    • Admin says:

      Hello,

      Please, send us the steps you were following with screenshots at “admin@saphanatutorial.com”.

      Thanks,
      Admin

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy