cancel
Showing results for 
Search instead for 
Did you mean: 

How to get the JSON output format from an procedure call using XSJS

Former Member
0 Kudos

Hello All,

can some one help me in guiding how to set the response of a procedure call in json format.

I am struck after the below statement how to parse and execute the result:

query = "call \"_SYS_BIC\".\"development.stats/SP_TEST\"('" + year + "',?)";

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Just a little preview that in SP9, not of this processing will be necessary. We have a new library that is already finished and will ship in SP9 called XSProcedures. It simplifies calling stored procedures from XSJS and allows the use of JSON for input parameter tables. It also returns all results not as Record Set objects but as JSON.

Here is an example from the content we will teach at TechEd && d-code this year:


$.import("sap.hana.xs.libs.dbutils", "procedures");

var XSProc = $.sap.hana.xs.libs.dbutils.procedures;

XSProc.setTempSchema($.session.getUsername().toUpperCase());

function getAddressesByRole() {

var partnerRole = $.request.parameters.get("PartnerRole").toUpperCase();

  var conn = $.db.getConnection();

//*********************************************

//     Call procedure using the xsProcedure API

//*********************************************

var getBpAddressesByRole = XSProc.procedureOfSchema("SAP_HANA_EPM_NEXT",

           "sap.hana.democontent.epmNext.procedures::get_bp_addresses_by_role");

    var results = getBpAddressesByRole( { IM_PARTNERROLE: partnerRole}, conn );

//You can reference the output parameter directly as well ... results.EX_BP_ADDRESSES

    var jsonOut = results;   //You can reference the output parameter directly as well ... results.EX_BP_ADDRESSES

    conn.close();

// Pass output to response
$.response.status = $.net.http.OK;
$.response.contentType = "application/json";
$.response.setBody(JSON.stringify(jsonOut));

}

Former Member
0 Kudos

Hi Thomas,

This is really a cool feature of XS. I am looking forward to using it in SPS09.

Best regards,

Wenjun

Former Member
0 Kudos

Thanks Thomas and Wenjun,

That's is what I was looking for also , if we don't have any thing to process why cant we directly have an option instead of looping the records and formatting as required.

We will check out once we have XSProcedures available.

Also are we going to have anything similar to download the data in XML format..?

In SPS07 as of now don't we have any other alternative of directly taking that results and displaying in JSON format.

This is my first XSJS application using procedures, so can you help me in  pointing to an example where the parsing of data is done in JSON format by calling a procedure.

Former Member
0 Kudos

Hi.


var pcall = conn.prepareCall(query);

pcall.execute();

var rs = pcall.getResultSet();

while (rs.next()){

    res.push({res1:rs.getNString(1),

              res2:rs.getNString(2)

              ...

            }); 

}

Former Member
0 Kudos

Hi,

You can also find examples in SAP HANA developer guide Using the Server-Side JavaScript APIs - SAP HANA Developer Guide - SAP Library

For the complete Database API in XSJS, you can refer JSDoc: Namespace: db

Best regards,

Wenjun

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

>Also are we going to have anything similar to download the data in XML format..?

No, not at present.  We are focused on JSON or RecordSet object.  If you want to convert the JSON to XML, there might well be an open source library for that.

>In SPS07 as of now don't we have any other alternative of directly taking that results and displaying in JSON format.

Nothing provided centrally. However it isn't very difficult to write a reusable library for this conversion generically.  We did that as an example in the SHINE content.  Look at the session.xsjslib in sap.hana.democontent.epm.services.  That library is used through out the demos in SHINE to convert from query results to JSON for easy processing and output.

Former Member
0 Kudos

Sure Thomas,

I liked the reusable function that you mentioned in the below URL: dynamically building the JSON format..

Thanks

Former Member
0 Kudos

Hi Wenjun,

Can you help me in resolving the below issue:

I have created one .xsjs file and called my procedure as shown.,

query = "call \"_SYS_BIC\".\"pkg-airbus/CALC_BURNING_ALL\"('"+date1+"','"+date2+"',?)";

But I am getting an exception at the below statement in my .xsjs file.

var pcall = conn.prepareStatement(query);

Error Details:

dberror(Connection.prepareStatement): 257 - sql syntax error: incorrect syntax near "2014": line 1 col 49 (at pos 49)

This is the url i have used to call my XSJS file from browser:

http://xxx.com:8000/SAMPLE/TEST/test_curve.xsjs?&date1='2014-01-01'&date2='2014-06-06'

Former Member
0 Kudos

Hi,

First please use conn.prepareCall() instead of conn.prepareStatement() since your query is calling a stored procedure.

Then from the error message, it is a syntax error. You can print the query and try to find what's wrong. You can first run the desired SQL statement in e.g. SQL Console and see if the query is exactly the same with what you can run.

Best regards,

Wenjun

Former Member
0 Kudos

I have tested my procedure in SQL console and it is returning the data as expected...

call "_SYS_BIC"."pkg-test/SP_test"('2014-01-01','2014-06-06',?)

Also i have used conn.prepareCall() instead of conn.prepareStatement()

i am facing the same error.

Is there anything specific format we have to  follow while using date fields? or issue with my query string format ?

query = "call \"_SYS_BIC\".\"pkg-airbus/CALC_BURNING_ALL\"('"+date1+"','"+date2+"',?)";


Thanks  for your help..

Former Member
0 Kudos

Hi,

You can print the query to see if it is what you want or you can use setDate() method instead. JSDoc: Class: CallableStatement The latter is recommended to avoid SQL injection.

Former Member
0 Kudos

Thanks for the clarifications..

i am trying some work around to use date as string instead of DATE type..

Can you let me know what is the error in my query string of my .XSJS file shown below..

var date1 = $.request.parameters.get('date1');

var date2 = $.request.parameters.get('date2');

var date3 = $.request.parameters.get('date3');

query = "call \"_SYS_BIC\".\"test/CALC_BURNING_ALL\"('"+date1+"','"+date2+"','"+date3+"',?)";

URL to run the XSJS:

http://sap.com:8000/TEST/TEST_CURVE/test_curve.xsjs?&date1='20140515'&date2='20140618'&date3='20140730'

Below is the error i am getting..

dberror(Connection.prepareCall): 257 - sql syntax error: incorrect syntax near "20140515": line 1 col 49 (at pos 49) at qp_gram.y:39162

Former Member
0 Kudos

Please use date1=20140515&date2=20140618&date3=20140730 in your URL, because you've already addded ' in your query.

Former Member
0 Kudos

Hi Thomas,

When I call in JS

var XSProc = $.import("sap.hana.xs.libs.dbutils", "procedures");

I always get Uncaught TypeError: undefined is not a function

I am on SP9; Any ideas why?

Thanks and best regards,

Peter

thomas_jung
Developer Advocate
Developer Advocate
0 Kudos

Are you sure your server is SPS 09?  Did you check the repository and see if you have the procedures.xsjslib library in sap.hana.xs.libs.dbutils?  This library should be in SPS 09 as the DU HANA_XS_DBUTILS should have been installed automatically when you upgraded to SPS 09.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

Here is some sample code after your statement.

var conn = $.db.getConnection();

var pcall = conn.prepareCall(query);

pcall.execute();

var rs = pcall.getResultSet();

var output = {};

// Here you parse rs and put it to output


rs.close();

pcall.close();

conn.commit();

conn.close();

$.response.contentType = "application/json; charset=UTF-8";

$.response.setBody(JSON.stringify(output));

$.response.status = $.net.http.OK;

Best regards,

Wenjun

Former Member
0 Kudos

Thanks for the quick response Wenjun,

I have done the same thing but I am getting blank output in the browser with no data..{}

Below is the actual procedure call which is returning my required data when i run in SQL..

call "_SYS_BIC"."development.stats/SP_TEST"('2014',?)

Is there anything I am missing in the query command of my Procedure..

Thanks..

Former Member
0 Kudos

Hi,

First of all, please make sure you can call the procedure in SQL console successfully and get the result. Then can you paste the content of your xsjs file here? So I can find what is going wrong.

Best regards,

Wenjun

Former Member
0 Kudos

yes it is working hn the SQL console.. with the below call


call "_SYS_BIC"."development.stats/SP_TEST"('2014',?)


And below is the code i have used in .xsjs file:...


function test(){

  var query;

var year = $.request.parameters.get('year');

  try {

   

query = "call \"_SYS_BIC\".\"development.stats/SP_TEST\"('"+year+"',?)";

  } catch (e) {

  $.response.status = $.net.http.INTERNAL_SERVER_ERROR;

  $.response.setBody(e.message);

  return;

  }

var conn = $.db.getConnection();

//var pcall = conn.prepareCall(query);

var pcall = conn.prepareStatement(query);

pcall.execute();

var rs = pcall.getResultSet();

var output = {};

rs.close();

pcall.close();

conn.commit();

conn.close();

$.response.contentType = "application/json; charset=UTF-8";

$.response.setBody(JSON.stringify(output));

$.response.status = $.net.http.OK;

}

test();

Former Member
0 Kudos

Hi,

In my previous sample code, there is a line.

// Here you parse rs and put it to output

which means you need to add your logic here. But I did not see anything in your code. You can refer XSJS API JSDoc: Index

Best regards,

Wenjun