Skip to Content

While most people think XS Engine is only based on OData entities (i.e. for reading data), it actually has a powerful JavaScript Database API that enables not only querying but executing any SQL statement (including inserting, updating and deleting records or running procedure calls).

This API is described in the “9.4 Server-Side JavaScript APIs” section of the HANA Developer Guide: http://help.sap.com/hana/hana_dev_en.pdf.

Below is a simple example of a server side JavaScript code (.xsjs file) for inserting data into HANA.

insert.xsjs

var id = $.request.getParameter("id");
if (id === null) {
    $.response.setContentType("text/plain");
    $.response.addBody("id is null!");
}
var val1 = $.request.getParameter("val1");
if (val1 === null) {
    $.response.setContentType("text/plain");
    $.response.addBody("val1 is null!");
}
 var output = {};
output.data = [];
var conn = $.db.getConnection();
conn.prepareStatement("SET SCHEMA \"TEST\"").execute();
var st = prepareStatement("INSERT INTO \"TABLE1\" values(?,?)");
st.setString(1,id);
st.setString(2,val1);
st.execute();
conn.commit();
var record = [];
record.push(id);
record.push(val1);
output.data.push(record);
conn.close();
$.response.setContentType("text/json");
$.response.addBody(JSON.stringify(output));

In order to call it, just do a HTTP GET to the URL pointing to your .xsjs file, passing the URL parameters.

For testing purposes, it’s possible to just open the URL in the browser directly.

If everything goes as expected, you should see the inserted record sent back as the response to the HTTP GET command (in JSON format).

Anything else should be interpreted as an error.

For example, calling http://<host>:<port>/test/logic/insert.xsjs?id=1&val1=10.5 in your browser, will insert this record in the “TABLE1” table:

/wp-content/uploads/2012/12/table1_159930.png

And this is the output you should see in your browser:

/wp-content/uploads/2012/12/json_159931.png

In the above case, TABLE1 had “ID” as a Primary Key. Meaning, if I just call the same URL again, it will try to insert another record with the same key, and hence it will fail. But since I didn’t handle the exception in my code, it will throw a HTTP 500 error. If you want to avoid that, you’ll need to do some exception handling in your .xsjs file.

Finally, in order to properly test your .xsjs file, you’ll need to define a XS application for the package that contains it.

Just make sure to create a .xsapp file like below in the root folder of that package in order to tell HANA that this is an existing XS Application, or else trying to call that URL above will just throw a HTTP 404 error.

test.xsapp

{
          "auth_required": true
}
To report this post you need to login first.

41 Comments

You must be Logged on to comment or reply to a post.

  1. Thomas Jung

    This is a very nice example.  The only thing I would personally change is to use the parameterized insertion.

    Instead of:

    conn.prepareStatement(“INSERT INTO \”TABLE1\” values(‘” + id + “‘, ‘” + val1 + “‘)”).execute(); 

    conn.commit(); 

    Use the parameterized statement to protect against SQL Injection – particularly when using values from the request object:

    var st = prepareStatement(“INSERT INTO \”TABLE1\” values(?,?)”);

    st.setString(1,id);

    st.setString(2,val1);

    st.execute();

    conn.commit(); 

    (1) 
      1. Jon-Paul Boyd

        Thanks for the great post.  Do you have an example of how credential authentication could be set if the call to the server side js is made in batch without any user interaction, would it be something along the lines of basic authentication setting a name/password pair, or is the HTTPS route requid?

        I will also be interested in how AWS Hana will handle 100’s of calls per second to perform realtime updates from external non-sap linux system using this mechanism.

        Thanks.

        (0) 
        1. Aron MacDonald

          Great post.

          I don’t know how many calls a second (using XSJS to perfom insert/update)  HANA on AWS can handle. It’s probably difficult to answer because it will depend on many factors particular to your environment.e.g. network connection, table design , Memory availability, other processes running etc.

          In your external non-sap linux system you could test it out by writing a small program.

          If you are familiar with Java you could included the following logic to connect to HANA on AWS:

          URL url = new URL(“http://ec2-XX-XXX-XXX-XXX.compute-1.amazonaws.com:8000/test/logic/insert.xsjs?id=2&val1=11.5“);

          URLConnection uc = url.openConnection();

          String userpass = “SYSTEM” + “:” + “manager”;  //Or preferrably a user/password created just for this purpose

          String basicAuth = “Basic ” + javax.xml.bind.DatatypeConverter.printBase64Binary(userpass.getBytes());

          uc.setRequestProperty (“Authorization”, basicAuth);

          InputStream in = uc.getInputStream();

          NOTE: you would need to populate ‘id’ and ‘val’ values in the ‘url’ string with appropriate values.

          If it works for you then please let me know how many calls a second your environment handles. 🙂

          (0) 
    1. Karl Sanford

      Thomas-

      Should your code read as follows?

      var st = conn.prepareStatement(“INSERT INTO \”TABLE1\” values(?,?)”);


      as opposed to


      var st = prepareStatement(“INSERT INTO \”TABLE1\” values(?,?)”);


      prepareStatement is a method of the $.db.getConnection(); correct?

      (0) 
      1. Sumit Kumar

        Hey Carl ,

        In the above example

        var st = prepareStatement(“INSERT INTO \”TABLE1\” values(?,?)”);


        If u will write this line it shows an error .  so when u write it like


        var st = conn.prepareStatement(“INSERT INTO \”TABLE1\” values(?,?)”);


        Then the error is getting removed.

        So I think Henrique missed that word.


        And yes you are right prepareStatement is a method of the $.db.getConnection();


        You can see this link in case u have not gone through….


        http://help.sap.com/hana/SAP_HANA_XS_JavaScript_Reference_en/$.db.Connection.html

        (0) 
    2. Vikas Madaan

      Hi Thomas,

      Is it possible to read data from file placed in the application server or external sever using XSJS?

      Actually we are building one scenario where customer will place file (.CSV) file in one particular sever and we will run XSJSJOB to access the file on regular interval.

      We have prepared period job file and called XSJS in it but we don’t know how the access the file content and update the table.

      Please help.

      Regards,

      Vikas Madaan

      (0) 
      1. Jacob Tan

        Hi Vikas Madaan,

        Have you checked out HDBTI?

        import = [

          {

          table = “myTable”;

          schema = “mySchema”;

          file = “sap.ti2.demo:myData.csv”;

          header = false;

          delimField = “;”;

          keys = [ “GROUP_TYPE” : “BW_CUBE”];

          }

          ];

        I’m not sure if it’s possible, but here some scenarios I have in mind:
        (Maybe Thomas Jung can verify this)

        [1]

        – Customer uploads CSV file via front end UI into Server

        – Prefix file name to match filename in HDBTI

        – Create a procedure or XSJS call, making the call to import the file into HANA Tables.

        [2]

        – Read the CSV file stored in the server

        Found this 3rd party libraryto read CSV file using JS: Here

        – Parse the information into HANA tables either from XSJS or XSJSLIB calls

        Do let us know if it works!

        All the best!

        Cheers,

        Jacob

        (0) 
  2. Orel Stringa

    Hi Thomas, Henrique,

    does the JavaScript API provide any methods for mass/bulk update? What scaling options are there for extended application services?

    Thanks,

    Orel

    (0) 
      1. Thomas Jung

        >does the JavaScript API provide any methods for mass/bulk update

        Yes you use a bulk prepared statement. There is an example in the online help:

        http://help.sap.com/hana/jsapi/namespacexsruntime_1_1db.html

        var conn = $.db.getConnection();

        var BACTHSIZE = 100;

        var st = conn.prepareStatement(“insert into mytable values(?)”);

        st.setBatchSize(BACTHSIZE);

        var i;

        for(i=0;i<BACTHSIZE;i++) {

           st.setInt(1,i);

           st.addBatch();

        }

        st.executeBatch();

        st.close();

        conn.commit();

        However you could also consider using a stored procedure – particularly if you need to do additional processing to the data before insertion.  All your data intensive logic should be in SQL/SQLScript/Views not at the XSJS layer.

        >What scaling options are there for extended application services?

        More to the previous point, if you are using XS correctly you shouldn’t need much scaling.  XSJS should be a lightweight pass through layer.  Its always stateless and all the really heavily lifting should be done in SQL/SQLScript/Views – which is processed in the index server not the XS Engine process.  There are a few parameters to set the number of JavaScript VM threads and memory per thread but you should really only have to change those if SAP Support tells you to (in cases of very large numbers of requests per second).

        (0) 
        1. Orel Stringa

          Hi Thomas,

          thank you for your informative reply. In fact, I opened a message with SAP development on this and did already get an answer.

          One further question though on your comment:

          >> XSJS should be a lightweight pass through layer.  Its always stateless ..

          When you say stateless – you refer to the xsjs layer as a client right? Is state ever maintained on the server  e.g.when the server is orchestrating the output the client may go thru transitions..?

          (0) 
          1. Thomas Jung

            No I mean XSJS as a server.  It is completely stateless. I don’t understand what you mean by the “client may go thru transitions” or what that has to do with the server being stateless.

            (0) 
            1. Orel Stringa

              Hi Thomas,

              thank you for clarifying this. I thought for a moment that the server would have to manage the different states of the client. I realize this is incorrect.

              Going back to how you characterized XSJS as a lightweight pass thru layer sounds to me that XS is only an interface for getting/setting data from Hana database. no business logic, only call SQL scripted or modelled views or stored procedures (return JSON strings, save data into tables- parsing json input, validate etc…). no transaction support is provided (state less).

              The question I have then is: What is the distinct advantage that XS provides to me as an application developer compared to the alternatives?

              Thanks + regards,

              Orel

              (0) 
        2. vinni jadav

          Hi ,

          Currently I am using trial version eclipse hana, My data is in json api and i want to import into hana. I have also created schema and table with the entries mentioned in code. I was trying to execute below code , to get the details from json request and store in hana database and  to display the objects( as mentioned in below)

          I have also given call statement in hana  which send a request to XS . When I am trying to run the URL from hana xs applications ( HANA Cloud platform) . Facing below error ,

          Found the following errors: =========================== TypeError: $.request.body is undefined (line 1 position 0 in /p1941702538trial/p1234567/hello/hello.xsjs) 

          Could you please help me out ,

          var body = $.request.body.asString();

          var obj = JSON.parse(body);

          var id = obj.id;

          var name1 = obj.name1;

          var name = obj.name2;

          var name = obj.name3;

          var conn = $.db.getConnection();

          var output = {}; 

          output.data = [];

          conn.prepareStatement(“SET SCHEMA \”XXXXXX\””).execute();

          var st = prepareStatement(“INSERT INTO \”RESULT\” values(?,?)”);

          st.setString(name1,name1); 

          st.setString(name2,name2); 

          st.setString(name3,name3); 

          st.execute(); 

          conn.commit(); 

          var record = []; 

          record.push(name1); 

          record.push(name2);

          record.push(name3);

          output.data.push(record); 

          conn.close();

          Also files created : xsapp, xsprivileges, xsjs , .xsaccess, .hdbrole

          Please advise..

          (0) 
  3. Abhijeet Jangam

    Basic question (Sorry!) –

    How do you open connection?

    I see  –     var conn = $.db.getConnection(); 

    But don’t see any connection parameters such as user name /password/server…

    I was expecting connection parameters the way we do in python…

    (0) 
    1. Henrique Pinto Post author

      Hi Abhijeet,

      That is exactly the awesomeness of having XS Engine as an intrinsic component of SAP HANA! The logon data doesn’t need to be declared in the code level. Once you try to access the .html, .js or .xsjs residing in XS, it will ask for authentication, which can then be a user from HANA DB itself, with both permission to execute JavaScript as well as to query the underlying tables/views.

      Best regards,

      Henrique.

      (0) 
      1. Abhijeet Jangam

        Thanks for quick reply…

        So I write this script (HTML/JS) on any machine…and $.db.getConnection() will prompt me for credentials (server/user/password). and then if successful then continue with the script? Cool..

        (0) 
          1. yx zhu

            I had set the following content in xsaccess file.

            “authentication :null”

            but now I want to access the database in xsjs file for a A simple demo(dont want to use name/password by client).

            when i use $.db.getConnection() , I got a error like this

            [getConnection: expects an authenticated session].

            how can i get the connection with  any connection parameters such as user name /password.

            (0) 
            1. Henrique Pinto Post author

              Hi yx,

              first, this example above was created on SPS4 internal release of XS, so that’s why I didn’t have to create an .xsaccess file and you do (I suppose you’re in SPS5).

              Also, you need to have at least basic authentication. I suggest to use at least something like this in your .xsaccess file:

              {

                        “exposed” : true,

                        “authentication” : [ {“method”: “LogonTicket” }, { “method” : “Basic” } ]

              }

              Best regards,

              Henrique.

              (0) 
  4. Alexander Lenz

    Hi Henrique,

    your are using var id = $.request.getParameter(“id”);   to access the request parameters.

    For me this is not working, i get the error that the function “getParamter” is not declared.

    Actually instead using $.request.parameters.get(‘id’); does work for me.

    Same for:

    1. $.response.setContentType(“text/json”); 
    2. $.response.addBody(JSON.stringify(output)); 


    Instead use:

                   $.response.contentType = “application/javascript”;

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

    Best regards,

    Alexander

    (0) 
  5. Shashank Keshava

    Hi Henrique Pinto

    I got the following error

    Found the following errors:

    ===========================

    InternalError: dberror(Connection.prepareStatement): 258 – insufficient privilege: Not authorized at ptime/query/checker/query_check.cc:2547 (line 15 position 0 in /p1940812895trial/myhanaxs/audiencemarketing1/javaScriptServices/odataMultiply.xsjs)

    (0) 
        1. Henrique Pinto Post author

          That needs to happen at your user level (check the Security entry in HANA Studio)=.

          Just add the Schema you want to select from and give the user SELECT rights.

          (0) 
  6. Anil Vetsa

    Hi

    i am trying to insert data into a table .

    and the following error keeps persisting.

    Error isInternalError: dberror(PreparedStatement.executeQuery): 340 – not all variables bound: unbound parameter : 0 of 1 at ptime/query/query_param.h:546

    any help is much appreciated.

    A

    (0) 
  7. Jacob Tan

    Hi Henrique Pinto & Thomas Jung

    Thank you for the solution.

    May I please ask, how about inserting records (creating records) into B1 v9.1 tables via utilizing the service layer? I believe that I can’t do INSERT queries.

    Then, how do I call the service layer via XSJS / JS through my MVC Methodology?

    Looking forward to hear from you.

    Thank you.

    Regards,

    JacobTan

    (0) 
    1. John Omesili

      I havent done anything yet with B1 hence I do not know if my answer may help

      From my perspective, communication between your .js and your .xsjs file can be done as shown below,  all calls are made from the within the .js controller

      Basically, call your .xsjs file with ajax

      //used to check if ther are values returned for the query

      1. jQuery.ajax({
      2.         url:  “services/play.xsjs?USERNAME=”+oEntry.USERNAME+“&PASSWORD=”+oEntry.PASSWORD,
      3.         dataType: “json”,
      4.         success: function(data, textStatus, jqXHR){
      5. if(data===false){ sap.m.MessageToast.show(“Wrong Login details”);}
      6. else{
      7.              sap.ui.getCore().AppContext = new Object();
      8.          sap.ui.getCore().AppContext.UserName = oEntry.USERNAME;
      9.                 sap.m.MessageToast.show(“Logging you in “+sap.ui.getCore().AppContext.UserName);
      10. sap.ui.getCore().byId(“app”).getController().to(“Master”);
      11. sap.ui.getCore().byId(“app”).getController().to(“Chart”);
      12. }
      13. },
      14. error: function(jqXHR, textStatus, errorThrown){
      15.             alert(“Oh no, an error occurred”);
      16. }
      17. });

      Another method I use is to use  JSONModel interact

      1. function (oAction) {
      2.                  if (sap.m.MessageBox.Action.OK === oAction) {
      3.                  // notify user
      4.                  var SaveModel = new sap.ui.model.json.JSONModel(
      5.                               services/update.xsjs?ID=”+oEntry.ID
      6.                               +“&COMPANY_NAME=”+oEntry.COMPANY_NAME
      7.                               +“&COMPANY_WEBSITE=”+oEntry.COMPANY_WEBSITE
      8.                               +“&INDUSTRY_SECTOR=”+oEntry.INDUSTRY_SECTOR
      9.                               +“&FIRSTNAME=”+oEntry.FIRSTNAME
      10.                               +“&LASTNAME=”+oEntry.LASTNAME
      11.                               +“&POSITION=”+oEntry.POSITION
      12.                               +“&EMAIL_ADDRESS=”+oEntry.EMAIL_ADDRESS
      13.                               +“&PHONE_NUMBER=”+oEntry.PHONE_NUMBER
      14.                               +“&FRIEND_LEVEL=”+oEntry.FRIEND_LEVEL
      15.                               +“&BUSINESS_VALUE=”+oEntry.BUSINESS_VALUE
      16.                               +“&COLLABORATE=”+oEntry.COLLABORATE
      17.                               +“&DATE=”+oEntry.DATE);
      18.                  var successMsg = bundle.getText(“ApproveDialogSuccessMsg”);
      19.                  }
      (0) 
  8. Yoppie Ariesthio

    Dear all,

    i have been following this blog post

    I think my logic is already right, but i got response

    500 – Internal server error

    Don’t know how to debug it…

    Appreciate your response…

    (0) 
        1. Jon-Paul Boyd

          But that would be too easy Yoppie 😉

          Hopefully you will have access to the trace files via the administration console or workbench ide.  For debugging xsjs in workbench ide simply set breakpoint by clicking in left column next to line number – you should see a red arrow indicating breakpoint, then click the play icon to execute.  You might need to create a test wrapper, for example if the xsjs you want to debug exists in an xsjslib.

          (0) 
          1. Yoppie Ariesthio

            Hi John,

            i find what’s the problem.. but still can’t resolve it…

            my query be like

            INSERT INTO MYTABLE VALUES(SEQ.NEXTVAL,’VAR1′)

            (not work)


            when i try to hard code the id :

            INSERT INTO MYTABLE VALUES(99,’VAR1′)

            it works but it’s not what i want…


            Thank you…..

            (0) 
  9. Ahmed Khan

    Hi XS champs,

    I am very new to hana xs, i have a requirement that i have to create a hana xs application which will display a form in web and when user fill the fields and submit it will store data in hana tables,

    Still didn’t find any article on this, kindly help me please

    (0) 

Leave a Reply