HANA CLOUD PLATFORM

[Former Member]

Hello Experts,

I am new to HANA

I have created a code, which inserts entries into table manually ( on URL).

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

Code is as below :

var ID = $.request.parameters.get("ID");

if (ID === null) { 

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

  $.response.setBody("ID is null!") ;

  } 

  var VAL1 = $.request.parameters.get("VAL1"); 

  if (VAL1 === null) { 

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

  $.response.setBody("VAL1 is null!");

  } 

  var output = {}; 

  output.data = []; 

  var conn = $.db.getConnection();

conn.prepareStatement("SET SCHEMA \"NEO_7xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"").execute(); 

var st = conn.prepareStatement("INSERT INTO \"sxxxxxxxxxxtrial.vinni.insert.SAPSF::TEST\"  values(?,?)");

st.setString(1,ID); 

st.setString(2,VAL1);

st.executeQuery();

st.execute(); 

  conn.commit(); 

  var record = []; 

  record.push(ID); 

  record.push(VAL1); 

  output.data.push(record); 

  conn.close(); 

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

I have also created hdbtable as below,

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

table.schemaName = "NEO_xxxxxxxxxxxxxx";

table.tableType = COLUMNSTORE;

table.columns = [

{name = "ID"; sqlType = INTEGER; nullable = false;},

{name = "VAL1"; sqlType = DECIMAL; nullable = false; precision = 12; scale = 3;}];

table.indexes = [ {name = "INDEX1"; unique = true; indexColumns = ["VAL1"];}];

table.primaryKey.pkcolumns = ["ID"];

Created xsaccess, xsapp, xsprivileges,model_access.hdbrole files.

When I click the URL in xs applications ( added id=5&val1=7 to URL)

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

Below is the error I am facing :

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

InternalError: dberror(PreparedStatement.execute): 301 - unique constraint violated: Table(sxxxxxxxxxxxxtrial.vinni.insert.SAPSF::TEST) at ptime/session/eapi/jdbc/ExternalStatement.cc:927 (line 22 position 0 in /sxxxxxxxxxxtrial/vinni/insert/SAPSF/insert.xsjs)

Could you please help me out.

Thanks ,

VINNI

[pfefferf · Accepted solution]

Hello Vinni,

what entries are already available in your table? According to the message a unique constraint is violated which could be either a violation of your primary key or your unique "INDEX1".

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

Thanks for your reply.

-> No entries are available, but I have made few minor changes [by commenting st.execute(); ] ,  I'm able to view the entries in the table now.

-> We have to consume/get json data from client, when client updates/add ID and Values in client json api, I need to GET and update in hana table [above thread scenario]

Could you please help me with the steps on how to call JSON API URL by using XSJS to update values in the table. [I'm thinking to implement by using AJAX call, based on my google search]

Thanks,

Vinni

[pfefferf · Accepted solution]

Hello Vinni,

yes, with jQuery which is the base of UI5 you can use the $.ajax method to call your xsjs script.

For the documentation please check http://api.jquery.com/jquery.ajax/.

A simple example:

var iID = 5;
var sVAL1 = "test value";

$.ajax({
            url : "../services/test.xsjs",
            type : "GET",
            data : {"ID": iID, "VAL1": sVAL1 },
            dataType : "json"
         }
  )
  .done(function(data){
            document.write(JSON.stringify(data));
       }
  )
  .fail(
      function(error){
           document.write(JSON.stringify(error));
       }
  );

The url is the url to your xsjs service, with data you define the parameters you wanna transfer. The "dataType" defines in which format you expect the returned value. The "done" method is called in case of a success, the "fail" method is called in case of an error.

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

Thanks for link, this will be very helpful.

Currently, I am using hana trial version. Is that above method possible in trial version?

If possible, May I know below details,

-> Inorder to start with jQuery which is the base of UI5,  Is eclipse ( with UI developement toolkit for HTML 5 )

sufficient or Do I require any other installations.

Thanks ,

Vinni

[pfefferf · Accepted solution]

Yes, this is possible on HCP trial too. jQuery is delivered as part of UI5, so you can use it.

Which tool you use does not matter (either Eclipse or e.g. the web-based dev workbench).

[Former Member · Accepted solution]

Thanks Florian , will try this.

I also have one more question.

My data is in below kind of format , having columns inside a column.

When I create csv and trying to import, I am unable to visible the data in below format in hana it is showing '?' in the spaces. It is assuming as column in hana, for merged cells also.

Could you please tell me which is the better way to import this kind of data.

Will flat file works or do we need to go with stored procedure or any other method.

scores				efficiency
total	abc	deg	ghj	total			passing	rushing	penalty
				xxxx	xxxx	xxxx

[pfefferf · Accepted solution]

A "really" flat file will work.

[Former Member · Accepted solution]

HI ,

I have imported on flat method ,

But the data is showing in this way (i have changed the column names)

Instead of merge row, It is showing column1, column2 and so on && also showing '?' in the spaces.

Persons	column1	column2	column3	efficency	column5	column6	column7	column8
total	boston	IL	WA	total	?	?	abc	def
				sdf	wer	tyu

[pfefferf · Accepted solution]

As I said, the file has to be really flat.

So instead of

total

xxx     xxx     xxx

you need

total1     total2     total3

xxx        xxx       xxx

[Former Member · Accepted solution]

Hi Florian,

Files created , xsapp, xsprivileges,.xsaccess,xsjs, model.hdbrole, TABLE.hdbtable

Below is the code I have prepared ,

$.ajax({

    url: 'http://xxxxxxx.json',    (fetching jsonapi URL to execute and update db)

    type: 'GET'

}).done(function(data){

var output = {}; 

output.data = []; 

var conn = $.db.getConnection();

conn.prepareStatement("SET SCHEMA \"NEO_xxxxx\"").execute();

var st = conn.prepareStatement("INSERT INTO \"TABLE\"  values(?)");

st.setString(1,id);

st.executeQuery();

conn.commit(); 

var record = []; 

record.push(id); 

output.data.push(record); 

conn.close(); 

$.response.setContentType("text/json");  

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

})

.fail(function(data){

    $.response.setBody("Error: "+data);

});

->

This (Json api) is the dynamic data, So I cannot insert manually. I am trying to use ajax call in xsjs file when I'm hitting the file, it is showing the below

Error:- " Ajax is not a function"

To run ajax, Jquery library is required. Could you please let me know how to insert a Jquery library in xsjs.

tried with below by adding in top of the code. but no luck.

<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>

<script>

Error:- syntax error, missing ;

In order to get data from dynamic json api URL to hana db,  is this a correct method, or else could you please let me know the best approach.

Can you share some sample examples related to this use-case if possible.

Thanks in Advance,

Vinni

[pfefferf · Accepted solution]

Hello Vinni,

you are implementing a server-side XSJS. So jQuery is not available, cause jQuery is for the client side.

To make your request in XSJS you have to use the Outbound API. More information on that can be found in the documentation, e.g.:

• SAP HANA Cloud Platform
• Tutorial: Use the XSJS Outbound API - SAP HANA Developer Guide for SAP HANA Studio - SAP Library

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

Your docs are very helpful, Thanks for the docs and your patience. I have created xsjs and xshttpdest files. I tried to run the xsjs file , When I run the file Instead of pulling the data from json api, it is importing as '?' and 'undefined' into the tables which I have created in modeler perspective.

Version : Web based developemnt workbench of Productive Landscape.

Below is the xsjs file:

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

var id = $.request.parameters.get("id");

if (id === null) { 

  $.response.setContentType = "text/plain";

  $.response.addBody("id is null!") ;

  } 

  var status = $.request.parameters.get("status"); 

  if (status === null) { 

  $.response.setContentType="text/plain";

  $.response.addBody("status is null!");

  }

  var output = {}; 

  output.data = []; 

  var conn = $.db.getConnection();

conn.prepareStatement("SET SCHEMA \"sxxxxxxxxx\"").execute(); 

conn.prepareStatement("INSERT INTO \"TABLE \" values('" + id + "','" + status + "')").execute();

conn.commit(); 

  var record = []; 

  record.push(id); 

  record.push(status); 

  output.data.push(record); 

  $.response.contentType = "application/json";  

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

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

xshttpdest file is created as per your document. Are there any changes to be made in this code to work . Could you please help me out.

I have also one more question,

Could you please provide me information on the statement $.request.

Thanks in Advance,

Vinni

[pfefferf · Accepted solution]

Hello Vinni,

please can you explain a little bit more what you wanna try to do. From the coding in your last post I cannot really see how the outbound call is relevant for the scenario.

Best Regards,

Florian

[Former Member · Accepted solution]

Hi florian,

My requirement  : Data is  coming from json api, it is dynamic data which frequently changes. I  would like to import the data into hana DB. So as we discussed earlier, I am trying to do this with XS service.

I have created a xsjs file and xshttpdest file( my Jsonapi is given in thi file). when I run the file, and give values of id and val1 in the URL. I am able to see the data in an array form.

But I would like to import the jsonapi directly into table in HANADB, without entering manually each record in the URL . Could you please  provide some information how to start this.

Could you please help me this scenario.

[pfefferf · Accepted solution]

Hello Vinni,

in the coding of your last post I cannot see the usage of the $.net.http.readDestination, $.net.http.Client and $.web.WebRequest functions/classes.

If you wanna import all data of your "jsonapi" you need to create an XSJS which uses the mentioned functions/classes to get the values of the API. Then you can go ahead to insert the data.

Best Regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

Sorry for troubling, Since I am new to Hana  I  couldn't able to clear..

Your answer was helpful.

I have added functions as you mentioned , in the xshttpdest file.

But Still I am getting error :

Internal Error : dberror(connection.preparestatment) - 270- not enough values : undefined (line 23 in xsjs file).

I also have one more basic Question, In xsjs file, I have mentioned $.request.parameter.get , Do I need to do any connections between hana and jsonapi fo r this.

Thanks in Advance.

Vinni

[pfefferf · Accepted solution]

Regarding the error: Check if all columns of the table are supplied in your insert statement.

For the other points: I do not really understand why you added the method calls to the xshttpdest file.The xshttdest file contains just the information where your api can be reached. In your xsjs file then the mentioned functions/classes have to be used to use the api. The tutorial I posted above describes that in detail. So please read the tutorial in detail.

[Former Member · Accepted solution]

Hi Florian, I was mistaken while typing.  I have mentioned functions in xsjs file itself.

Columns - I have created only one column and trying to insert from jsonapi into that table.

error :{'data":[['null']]}

If I enter value in URL like id=56, I am able to see the entry in table and array . But My task is not to insert value by entering manually, It should read the data from  jsonapi an table and insert the value into table.

functions in xsjs file :

var dest = $.net.http.readDestination("xxxxx","xxxx");

var client = new $.net.http.client ();

var req = new $.web.WebRequest($.net.http.GET,"id");

( I have tried without giving id in the above statement)

var response = client.getResponse();