insert data in HANA through JavaScript (with XS Engine)

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

1. var id = $.request.getParameter("id");  
2. if (id === null) {  
3.     $.response.setContentType("text/plain");  
4.     $.response.addBody("id is null!");  
5. }  
6.   
7. var val1 = $.request.getParameter("val1");  
8. if (val1 === null) {  
9.     $.response.setContentType("text/plain");  
10.     $.response.addBody("val1 is null!");  
11. }  
12.   
13.  var output = {};  
14. output.data = [];  
15.   
16. var conn = $.db.getConnection();  
17. conn.prepareStatement("SET SCHEMA \"TEST\"").execute();  
18.   
19. var st = prepareStatement("INSERT INTO \"TABLE1\" values(?,?)");  
20. st.setString(1,id);  
21. st.setString(2,val1);  
22. st.execute();  
23. conn.commit();  
24.   
25. var record = [];  
26. record.push(id);  
27. record.push(val1);  
28. output.data.push(record);  
29.   
30. conn.close();  
31.   
32. $.response.setContentType("text/json");  
33. $.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:

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

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

1. {  
2.           "auth_required": true  
3. }