CAP - DELETE statement causing "cannot read property of undefined error"

[WilliamW]

On running a standard DELETE statement like the below, error is thrown by the CAP standard code "cannot read property of undefined (reading 'attachment_ID'

The attachment ID being passed as the key definitely exists in the database. I have posted the structure of the entity below. It is used as a composition in some parent entities

entity Attachments : cuid, managed { dmsID : String(50);
@Core.MediaType : contentType // @Core.ContentDisposition.Filename : fileName @Core.Computed : false virtual content : LargeBinary @stream;
@Core.IsMediaType: true contentType : String; fileName : String(100); fileSize : Integer; dmsRecoveryFlag : Boolean default true; recallRequired : Boolean default false;}

await db.run(DELETE.from(Attachments).byKey(sAttachmentID));

[Ivan-Mirisola]

Hi wwrigley,

When you use a managed entity, it introduces a key named 'ID' which is not a "Edm.String" but it is "Edm.Guid"

Are you trying to delete a row where the entity ID is dmsID (which is a String) or are you passing the auto-generated GUID to the delete CQL you are getting from the table?

You could also try changing your entity's ID type like so:

entity Attachments : cuid, managed {
  key ID : UUID @odata.Type:'Edm.String';
  dmsID                               : String(50);
  @Core.MediaType  : contentType // @Core.ContentDisposition.Filename : fileName @Core.Computed : false virtual content : LargeBinary @stream;
  @Core.IsMediaType: true contentType : String;
  fileName                            : String(100);
  fileSize                            : Integer;
  dmsRecoveryFlag                     : Boolean default true;
  recallRequired                      : Boolean default false;
}

Or you could change the CQL into:

DELETE.from `Attachments` .where `dmsID=${sAttachmentID}`

NOTE:Could you please share the complete cds artifacts here (including the service where you define the delete 'action')?

Best regards,
Ivan

[WilliamW]

Hi Ivan,

Here is the full error stack and the method. The key is definitely GUID that is being passed to the DELETE statement

Note we are using DMS so we have a custom implementation where we delete attachment from DMS then proceed to delete from HANA DB

"TypeError: Cannot read properties of undefined (reading 'attachment_ID')\n at /home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/delete.js:223:21\n at Array.reduce (<anonymous>)\n at _getDataFromOncond (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/delete.js:217:17)\n at /home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/delete.js:219:33\n at Array.reduce (<anonymous>)\n at _getDataFromOncond (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/delete.js:217:17)\n at getSetNullParentForeignKeyCQNs (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/delete.js:255:20)\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)\n at async HanaDatabase._delete (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/db/query/delete.js:29:26)\n at async next (/home/vcap/app/node_modules/@sap/cds/lib/srv/srv-dispatch.js:76:17)"

Function:

handleAttachmentDelete: async (req) => { const db = await cds.connect.to("db"); const { Attachments } = db.entities; const sAttachmentID = req.data.ID; const oAttachment = await db.run(SELECT.from(Attachments).byKey(sAttachmentID)); if (!oAttachment) { return req.reject(404, `No attachment found for ID ${sAttachmentID}`); } try { await DMSUtil.deleteDMSDocument(oAttachment.dmsID); await db.run(DELETE.from(Attachments).byKey(sAttachmentID)); } catch (error) { if (error.response && error.response.status === 404) { //not found in DMS await db.run(DELETE.from(Attachments).byKey(sAttachmentID)); } else { throw error; } } },

[Ivan-Mirisola]

Hi wwrigley,

I am not sure why you share code outside of the code-box you are entering - which is really annoying and not very helpful to read lines of code unformatted. I understand that the Q&A editor is not the best editor of all times, but if you edit your answer/comment on a separate editor, you can easily format your code later on, by simply pasting the code inside the box.

The error message starts when you try to delete from the Hana Database. The deletion triggers a database constraint check for a parent relationship and that is where the issue rises. As it seems (and with the little information you've provided so far), it is trying to delete a record on a parent entity, but is not able to find the same ID there to perform a cascade delete - which violates the constraint and thus makes your data inconsistent.

Since I don't have the complete "picture", I can't recommend you to do any specific changes. The best recommendation I have for you is to please review your CDS schema for your Hana database and check which entities do require constraints or not (the documentation below has the instructions on how to enable/disable this feature).

https://cap.cloud.sap/docs/guides/databases#db-constraints

Best regards,
Ivan

[WilliamW]

Ivan, I have posted above the function definition and the parent entity which attachments entity has an association to

The overall process is creating an invoice. The current steps that are happening are:

1. Create entry in Attachment entity

2. In before handler, when creating an Invoice entity, we check for duplicate invoice being submitted and reject the request if this is the case.

3. After this, we want to delete the entry in Attachments table, as the parent entity wasn't created. This is the step that is throwing the error.

Please let me know if you need more information

[Ivan-Mirisola]

Hi wwrigley,

Therefore, what you have defined currently is the following ER diagram:

Invoices (1) --> Attachments (1)

So, what you want is to delete the attachment, but you want to avoid deleting the parent record that refers back to this attachment?

From what I could understand I see a possible database integrity issue here. Because you have a record on Invoices which would have an ID on the field Attachment which is the foreign key for a record of Attachments table. If you allow that record on Attachments table to be deleted without deleting/updating the referred record on Invoices which points back to it, then you will have a database integrity issue. Meaning, you would still see the Attachment ID on the Invoices table. However, you won't see any records with that ID on the Attachments table.

So, either you allow both records to be created before you attempt a deletion - using automatic transaction management of CAP. Or, you have to work with both records on a manual transaction. Please read:

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

Even better, don't allow users to add any attachments before you are able to check if the invoice is already present on the system.

That way, you won't need to delete any attachments that were created before you submit the invoice entity.

Best regards,
Ivan