How to Insert values into new table using xsjs in SAP HANA Web IDE?

[former_member594414]

Hi All,

I'm currently working on Web IDE for connecting to a DB using node js module. I have done it successfully, but now I'm trying to filter a table in my DB and put those filtered values into a new table in the same HDI container. I have written a code for the same but I dont get the desired output. The code runs successfully but the values are not inserted into the table.

Table to fetch data : csduser.CustomerData

Table to push filtered data : new.new

Below is the code :

Kindly help with the errors in the code

"use strict";


var conn = $.hdb.getConnection();
var query = "SELECT FROM csduser.CustomerData { " +
	        " Customer_ID as \"Unique_ID\", " +
            " Customer_FName as \"Name\", " +
            " Annual_Income as \"Amount\" " +
            " } ";
var rs = conn.executeQuery(query);


//console.log("BEFORE BODY");


var body = "";
var tuples = [];
for(var item of rs){
   if(item.Amount < 2000000){
   	var temp = {};
   	temp.Unique_ID = item.Unique_ID;
   	temp.Name = item.Name;
   	temp.Amount = item.Amount;
   	
   	tuples.push(temp);
   }
}


// console.log("BEFORE FOREACH");


tuples.forEach(function(ele)  {
	console.log(ele);
	
	// insert into "new.new" values(1, 'abc', 1000);
	var sql = "insert into \"new.new\" values(" + ele.Unique_ID + ", '" + ele.Name +"', " + ele.Amount + ");";
	console.log(sql);
	var retVal = conn.executeQuery(sql);
	console.log(retVal);
});

[pfefferf]

Instead of doing a "executeQuery" function call you have to do a "executeUpdate" function call on the connection object when you wanna insert data (check the details here). In addition you have to execute function "commit" on the connection object to commit the changes. W/o the commit function call your changes would not be persisted, because for the $.hdb interface the autocommit mode is disabled.

Regards,
Florian

PS: Instead of doing single inserts, you can call the executeUpdate function with an arguments array (details can be found in the linked share above).

[SergioG_TX]

what error are you getting? or is it just ending successfully w/o errors?

your first select statement that is assigned to the query variable looks weird.. shouldn't you select * or specific columns from.... instead of SELECT FROM ?