@cap/js-hana conflicts with HANA System Versioning

[dimrat]

Description of erroneous behaviour

We are using HANA System Versioning in combination with CAP. When we switch to CAP8 and @cap-js/hana respectively, we cannot insert new data anymore, because the way the SQL is generated conflicts with hana system versioning. More precisely, if we try to make a simple post request on a system versioned entity, e.g.

###
POST http://localhost:4004/odata/v4/catalog/Book
Content-type: application/json
Authorization: Basic admin:

{
  "ID": 1,
  "title": "How To Kill a MockingBird"
}

we are getting the following error

[cds] - [SqlError: INSERT, UPDATE and UPSERT are disallowed on the generated field: cannot insert into generated field FROMTIMESTAMP: line 1 col 48 (at pos 47)] {
  code: 406,
  sqlState: 'HY000',
  level: 1,
  position: 0,
  query: 'INSERT INTO my_bookshop_Book (ID,title,content,fromTimestamp,toTimestamp) WITH SRC AS (SELECT ? AS JSON FROM DUMMY UNION ALL SELECT TO_NCLOB(NULL) AS JSON FROM DUMMY)\n' +
    `      SELECT ID AS ID,title AS title,content AS content,fromTimestamp AS fromTimestamp,toTimestamp AS toTimestamp FROM JSON_TABLE(SRC.JSON, '$' COLUMNS(ID INT PATH '$.ID', "$.ID" NVARCHAR(2147483647) FORMAT JSON PATH '$.ID',title NVARCHAR(20000) PATH '$.title', "$.TITLE" NVARCHAR(2147483647) FORMAT JSON PATH '$.title',content NVARCHAR(20000) PATH '$.content', "$.CONTENT" NVARCHAR(2147483647) FORMAT JSON PATH '$.content',fromTimestamp TIMESTAMP PATH '$.fromTimestamp', "$.FROMTIMESTAMP" NVARCHAR(2147483647) FORMAT JSON PATH '$.fromTimestamp',toTimestamp TIMESTAMP PATH '$.toTimestamp', "$.TOTIMESTAMP" NVARCHAR(2147483647) FORMAT JSON PATH '$.toTimestamp') ERROR ON ERROR)`
}
[error] - 500 > {
  message: 'INSERT, UPDATE and UPSERT are disallowed on the generated field: cannot insert into generated field FROMTIMESTAMP: line 1 col 48 (at pos 47)',
  code: '406'
}

Detailed steps to reproduce

For example (→ replace by appropriate ones for your case):

1. git clone https://github.com/dimrat/cap-js-hana-systemversioning.git
2. npm install
3. cds deploy -2 hana
4. cds w --profile hybrid
5. Run the request in test.http

Details about your project

Remove the lines not applicable, and fill in versions for remaining ones:

| @cap-js/asyncapi | 1.0.2 | | @cap-js/openapi | 1.0.6 | | @sap/cds | 8.3.0 | | @sap/cds-compiler | 5.3.0 | | @sap/cds-dk (global) | 8.3.0 | | @sap/cds-fiori | 1.2.7 | | @sap/cds-foss | 5.0.1 | | @sap/cds-mtxs | 2.2.0 | | @sap/eslint-plugin-cds | 3.1.0 | | Node.js | v20.17.0 |

[patricebender]

Hi @dimrat,

currently there is no convenient modeling approach available, that solves your issue. We will transform this bug report to a feature request and will discuss, how a proper solution for this (and similiar) issue(s) could look like.

For the time being, I have created a workaround which does the following:

• with a custom server.js hijack the timestamp elements and attach a dummy value property to them → This mocks a calculated element which are by default not inserted into the database
• exclude the timestamps from your service projection, if you like. They seem to always return the max time stamp in the non-history version of the Book table

I hope this helps you for now, we will keep you updated with what real solution we come up with.

BR Patrice