Compositions causing invalid column name error in sap capm nodejs service

[ramana_saps4hana43]

Hi,

I'm trying post Deep Insert using Compositions in CAPM NodeJS service but getting error "Invalid Column Name <Composition Name>" during insert into table.

data.model.cds :

namespace orders.db;

entity Orders : uuid {

date: Date;

description: String(40;

Items: Compositions to many OrderItems on Items.order = $self;

};

entity OrderItems: uuid {

description: String(40);

quantity: Decimal(13,3);

unit: String(3);

price: Decimal(13,2);

currency: String(3);

order: Association to Orders

};

cat--service.cds:

service CatalogService {

entity Orders projection on db.Orders;

entity OrderItems projection on db.OrderItems;

action postOrders(data: array of <typename>) retunrs String;

};

cat-service.js:

module.exports = cds.service.impl( aysnc function() {

this.on("postOrder", async function(req, res) {

let data = req.data;

let query = INSERT.into("orders_db_Orders", data);

let result = await cds.tx(req).run(query).catch((error) => {

return req.error({ code: 500, message: error.message }); });

});

});

Payload for testing the service:

{

"data" : [

{

"date": "2023-04-21",

"description": "Test 0001",

"Items" : [

{

"description": "Order Item 10",

"quantity": 10,

"unit": "KG",

"price": 100,

"currency": "USD"

}

]

}

]

}

once posting the above payload, i'm getting an error as "Invalid Column Name Items" in table "orders_db_Orders".

Could you please help me to solve the above error.

Thanks in advance.

Regards,

Ramana.

[Epena · Accepted solution]

Hello,

When you define a composition from Orders to OrderItems, the generated DB table OrderItems will contain a column "Order_ID" which references the order to which this item belongs to. There is no column "Items" in table Orders and hence you can't add any values to it via DB query.

If instead of creating an action, you perform a POST on Orders, the generic handler will accept your payload and insert the values correctly in both Orders and OrderItems table with the right reference in "Order_ID", but if you enable debug for db, you'll notice the generic handler executes two separate queries: one for creating the Order row and then another for creating the OrderItems row.

Bottom line, you have two options:

- You either perform Post requests to Orders with it's items in the payload (you can send individual post requests for each order, or you can create a batch request with multiple individual posts whithin)

- If you decide to continue using the action with the array of Orders, you must create 2 insert statements: one for Orders and one for OrderItems for each item in your array.

Regards.

[ramana_saps4hana43 · Accepted solution]

Hi Edgar,

Thank you very much for your kind response.

The problem for not able to use entity is, I do have custom logic to be written like PDF file read and store the content into HANA Cloud DB and read it back when user requested into UI.

Also Array of Orders I want to Insert into Orders & OrderItems at once with consistency i.e. either both tables must be Inserted or none.

So request you kindly help me a way to achieve my goal.

Regards,

Ramana.

[Epena · Accepted solution]

Hello Ramana.

if you need to perform actions (pdf read, etc) before, during or after the generic handler inserts the records into the database, you can implement all that logic in custom handlers and let the generic handlers do the rest (save the main entities into db). In custom handlers you can perform any action you want before or after the records are saved. Take a look at the documentation:

https://cap.cloud.sap/docs/node.js/services#event-handlers

And regarding what you said about needing to write to the database consistently (both tables get updated or none), that’s exactly how CAP works out of the box, read here for a deeper explanation:

https://cap.cloud.sap/docs/node.js/cds-context-tx

Regards.