XSJS Not enough values

[Former Member]

Hello Colleagues,

I checked the query, it has 15 columns 8 Attributes and 7 measures.I have used execute update with array to populate my table in hana

code goes as follows

//let payload = $.request.body.asString();
let payload = $.request.body.asString();
let parameters = JSON.parse(payload);
var i = parameters["root"]["row"].length;
var Ojson, j = 0;


var tempArray2 = [];
for (i; i > 0;) {
    var tempArray = [];
//var TRANSACTION_ID = parameters["root"]["row"][i - 1].TRANSACTION_ID; auto increment so no need
var MaterialNumber = parameters["root"]["row"][i - 1].MaterialNumber;
var MaterialDescription = parameters["root"]["row"][i - 1].MaterialDescription;
var Period = parameters["root"]["row"][i - 1].Period;
var Brand = parameters["root"]["row"][i - 1].Brand;
var Size = parameters["root"]["row"][i - 1].Size;
var Pack = parameters["root"]["row"][i - 1].Pack;
var Client = parameters["root"]["row"][i - 1].Client;
var ClientType = parameters["root"]["row"][i - 1].ClientType;
var Volume = parameters["root"]["row"][i - 1].Volume;
var GrossSales = parameters["root"]["row"][i - 1].GrossSales;
var Discounts = parameters["root"]["row"][i - 1].Discounts;
var NetSales = parameters["root"]["row"][i - 1].NetSales;
var CostofGoodsSold = parameters["root"]["row"][i - 1].CostofGoodsSold;
var Distribution = parameters["root"]["row"][i - 1].Distribution;
var Warehousing = parameters["root"]["row"][i - 1].Warehousing;


//tempArray.push(TRANSACTION_ID.toString());






tempArray.push(MaterialNumber.toString());
tempArray.push(MaterialDescription.toString());
tempArray.push(Period.toString());
tempArray.push(Brand.toString());
tempArray.push(Size.toString());
tempArray.push(Pack.toString());
tempArray.push(Client.toString());
tempArray.push(ClientType.toString());
tempArray.push(Volume.toString());
tempArray.push(GrossSales.toString());
tempArray.push(Discounts.toString());
tempArray.push(NetSales.toString());
tempArray.push(CostofGoodsSold.toString()); // in table these are integer / decimal
tempArray.push(Distribution.toString());// in table these are integer
tempArray.push(Warehousing.toString()); //in table these are integer


tempArray2.push(tempArray);
i = i - 1;
}


var conn = $.hdb.getConnection();
conn.executeUpdate('insert into "TEST" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',tempArray2);
conn.commit();
$.response.status = $.net.http.OK;
$.response.contentType = "text/html";


 $.response.setBody(tempArray.length + "  record inserted" + tempArray2);



////////////////////EOF///////////////////////////////////////////////////////////////
JSON Payload is like this 


{
   "root": {
      "row": [
         {
            "MaterialNumber": "11504000",
            "MaterialDescription": "Crocky 250ML 4X",
            "Period": "201601",
            "Brand": "Crocky",
            "Size": "250ML",
            "Pack": "4X",
            "Client": "Kroger",
            "ClientType": "Supermarkets",
            "Volume": "206.0",
            "GrossSales": "170.8",
            "Discounts": "-3.1",
            "NetSales": "167.7",
            "CostofGoodsSold": "-79.2",
            "Distribution": "-4.0",
            "Warehousing": "-1.5"
         },
         {
            "MaterialNumber": "11506000",
            "MaterialDescription": "Crocky 250ML 6X",
            "Period": "201601",
            "Brand": "Crocky",
            "Size": "250ML",
            "Pack": "6X",
            "Client": "Kroger",
            "ClientType": "Supermarkets",
            "Volume": "147.9",
            "GrossSales": "96.2",
            "Discounts": "-1.7",
            "NetSales": "94.4",
            "CostofGoodsSold": "-44.6",
            "Distribution": "-2.3",
            "Warehousing": "-0.8"
         }
      ]
   }
}

.error: "msg":"POST /index.xsjs returning status 500: not enough values: :15: line 1 col 56 (at pos 55)"}

Kindly help!!

[thomas_jung]

The problem is your insert statement isn't listing the columns, just the values:

'insert into "TEST" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'

But in your table earlier in the code you mention you have a column named TRANSACTION_ID which is auto increment:

//var TRANSACTION_ID =parameters["root"]["row"][i - 1].TRANSACTION_ID; auto increment so no need

However you aren't telling the INSERT statement the columns, so how does it know not to put the first value into this column? This is why you are a value short because you've left out this ID based column. This is the danger about assuming anything about the target columns. In my opinion you should also specific the target columns matching the value order in the insert statement.

[Former Member]

Thomas can you please show me an example of how to list columns inside insert statement in the XSJS code..

Second , do you handle the data in the columns for string and integer. as i have a confusion of toString method that converts to string . so what if i pass a integer value in to it will it work??

give me a small example.

Thanks a lot.

[thomas_jung]

>Thomas can you please show me an example of how to list columns inside insert statement in the XSJS code..

Use the Column List Clause as described in the INSERT Statement Help:

https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20f7f70975191014a76da70c91...

>Second , do you handle the data in the columns for string and integer. as i have a confusion of toString method that converts to string . so what if i pass a integer value in to it will it work??

Well this is JavaScript so its not strongly typed and will try and cast. Still its best if you know you are mixing the types to use the SQL TO_<TYPE> to be safe:
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/209ddefe75191014ac249bf78b...