SAP HANA XSJS- Displaying no output on the body of the webpage while calling a select statement

[former_member504474]

Hi Experts,

I have written following lines of code to display all the schema name returned by the xsjs script on the content body. But it's not showing any schema name on the content body of the webpage. Could you please guide me if I am missing something?

var connection=$.db.getConnection();

var output="";

output=connection.prepareStatement("SELECT SCHEMA_NAME FROM SYS.SCHEMAS WHERE HAS_PRIVILEGES = \'TRUE\' AND SCHEMA_OWNER NOT IN (\'_SYS_REPO\',\'SYSTEM\',\'_SYS_STATISTICS\')").execute();

connection.commit();

$.response.contentType = "text/html";

$.response.setBody(output);

Regards,

Shawon

[pfefferf · Accepted solution]

Hello Shawon,

there are different issues in your coding:

• The "execute" method you used returns no result set, it returns a boolean value if a result is available or not. If you do it this way you have to get the result set using method getResultSet in combination with method getMoreResults.
• The result set reponse is by default no html, therefore the content type "text/html" is not appropriate if you do not create an html response.
• If you did get the result set, for the legacy DB interface you need to "convert" the data to the required response type (html in your case, json in most other cases).

Following returns the result of the same query using the new HDB interface ($.hdb). The result is returned as json. No preparation is necessary, because the result procuced by the new HDB interface is a json object.

let connection = $.hdb.getConnection();

let result = connection.executeQuery("SELECT SCHEMA_NAME FROM SYS.SCHEMAS WHERE HAS_PRIVILEGES = \'TRUE\' AND SCHEMA_OWNER NOT IN (\'_SYS_REPO\',\'SYSTEM\',\'_SYS_STATISTICS\')");

$.response.contentType = "application/json; charset=utf-8";
$.response.setBody(JSON.stringify(result));

Regards,

[former_member504474 · Accepted solution]

Hi Florian,

Thank you so much for your kind and timely response. Your code works perfectly.

But you have used different syntax like "letconnection=$.hdb.getConnection();" . So I am just wondering if you have any document which I can follow to get acquainted with the syntax that you used and get some knowledge on this. It will be a great help if you can share some document or link to follow.

I sincerely appreciate your help.

Regards,

Shawon

[pfefferf · Accepted solution]

The reference for the new $.hdb interface can be found here: $.hdb