How could I connect to SAP HXE schema via Node and create an OData service from view?

[former_member233452]

Dear experts,

I use the SAP HANA Express edition to create a MTA.

1. Question)

I would like to connect from the Node.js-module to the SAP HXE and then I would like to SELECT data and INSERT data INTO Tables and Views in the schema "SYSTEM", which I created with a .hdbcds-file (I didn't use a hdi-container to create the database tables). How could I SELECT Data as json from SYSTEM views from a node.js-module.

I tried to use the sap-hdbext and the node-hdb javascript package for node.js to establish the connection and then I tried to execute simple SQL commands?

https://github.com/SAP/node-hdb

https://help.sap.com/saphelp_hanaplatform/helpdata/en/54/513272339246049bf438a03a8095e4/content.htm#...

http://www.sap.com/developer/tutorials/xsa-node-dbaccess.html

2. Question)

I would like to create an OData service from the CDS views in the SYSTEM schema. How can I create an OData service from views which are not in a hdi-container?I only found examples which use tables from a hdi-container. I will use the OData service for a master-detail fiori application.


This is my app.js file :

"use strict";

var xsjs = require("sap-xsjs");
var xsenv = require("sap-xsenv");
var port = process.env.PORT || 3000;

var options = {
anonymous : true, // remove to authenticate calls
redirectUrl : "/index.xsjs"
};

// configure HANA
try {
options = Object.assign(options, xsenv.getServices({ hana: {tag: "hana"} }));
} catch (err) {
console.log("[WARN]", err.message);
}

// configure UAA
try {
options = Object.assign(options, xsenv.getServices({ uaa: {tag: "xsuaa"} }));
} catch (err) {
console.log("[WARN]", err.message);
}

var hdbext = require("sap-hdbext");
var hanaConfig = {
host : "hxehost",
port : 39015,
user : "SYSTEM",
password : "PW"
};

hdbext.createConnection(hanaConfig, function(error, client) {
if (error) {
return console.error(error);
}
client.exec("select * from SYSTEM.TableXY", function (err, rows) {
client.end();
if (err) {
return console.error("Execute error:", err);
}
console.log("Results:", rows);
});
});

app.listen(port);
console.log("listening at:", port);


This is my package.json file :

{
"dependencies": {
"sap-xsenv": "1.2.4",
"sap-xsjs": "1.11.3",
"body-parser": "^1.15.2",
"express": "~4.13.4",
"sap-hdbext":"latest"

},
"devDependencies": {
"sap-xsjs-test": "1.2.2"
},
"files": [],
"main": "app.js",
"name": "tinyjs",
"scripts": {
"start": "node app.js",
"test": "node app.js"
},
"engines": {
"node": "6.x"
},
"version": "1.0.0"
}

_schema-version: '2.0'
ID: supplier
version: 0.0.1

modules:
- name: tinyjs
type: nodejs
path: tinyjs

[pfefferf · Accepted solution]

Regarding question 1)

Beside all the "template" code which you are not using as I understood (e.g. get service tagged with "hana"), your code would from a technical perspective if you correct the port pointing to your tenant database. As HXE is a multitenant DB the port for the SystemDB (which you are using I assume) is 39013. Also the schema and table name in the direct query string should be escaped in double quotes. Following is a simple dummy example which works for a valid object in the SYSTEM schema when you connect with a user which has select privileges:

var hdbext = require("sap-hdbext");
var hanaConfig = {
	host: "hxehost",
	port: 39013,
	user: "SYSTEM",
	password: "password"
};


hdbext.createConnection(hanaConfig, function(error, client) {
	if (error) {
		console.log("hdbext createConnection:" + error);
	}else{
		console.log("Connection successfull.");
		
		client.exec("SELECT * FROM \"SYSTEM\".\"TABLE\"", function(error, rows) {
			if(error){
				console.log("Error during direct statement execution: " + error);
			}else{
				console.log(rows);
			}
		});
	}
});

But not just looking from the pure technical perspective of your question, the question is why you are creating objects in the SYSTEM schema. This schema should not be used for your specific implementations. A good way in an XSA environment would be to create your HDI container (with the underlying generated schema) and create your stuff in that. If you need to access objects from other schemas you can work with user defined services and synonyms. Using an HDI container service and an UAA service also gives you a good security setup, so you do not have to deal with credentials manually as you do it at the moment.

Regarding question 2)

With your current approach to get the data via the hdbext module, you will get happy to create an OData service. You should go the standard ways available. For XSA that means

• use an xsodata service (in the Node.js module)
• use an Apache Olingo OData service via a Java module

In both cases access your data via the HDI container service - as mentioned if necessary you can access from other schemas via user defined services and synonyms.

Regards,
Florian

[former_member233452 · Accepted solution]

Thanks to you, Florian

Regarding question 1)

I used the wrong hxehost port to establisch a connection. The SYSTEM should only be an example for a schema in the database. How could I select my table to get such a JSON-Output of my rows and store it in a variable?key, name, price, weight, quality are the attributes of my table.

<code>"table": [
      {
        "key": "1",
        "name": "Name1",
        "values": {
          "price": 249,
          "weight": 130,
          "quality": "100"
        }
      },
      {
        ...
        }

Regarding question 2 ) Is the best way to create a hdi-container for my schema to create a service, which I could use for the master-detail fiori template?Or are there other easy examples to access only a schema?

[pfefferf · Accepted solution]

As the "exec" method already returns an object array it should be very easy to transform the result to the required result structure if required (just with simple JS functionality, e.g. using map on the returned rows object array).

Regarding the data access I would recommend that you check the latest OpenSAP course for HANA Development. (https://open.sap.com/courses/hana5) There you get step by step information how to do that.