Update doesn't insert new entries

[mrkarthi]

Hi Team,

I am trying to insert an entry in PUT method in $batch mode. I want to update the entry if already present in db, insert if not present. The response I received in postman client is 200 Ok. But, the data is not available in postgresql.

Request
--batch_1d53-d596-e511
Content-Type: multipart/mixed; boundary=changeset_892a-a8e3-582b

--changeset_892a-a8e3-582b
Content-Type: application/http
Content-Transfer-Encoding: binary

PUT data(id='5',code='5') HTTP/1.1
Accept: application/json
Content-Type: application/json

{"id":"5","code":"5","name":"Desc 005"}

--changeset_892a-a8e3-582b--

--batch_1d53-d596-e511--

Response
--changeset_892a-a8e3-582b
content-type: application/http
content-transfer-encoding: binary

HTTP/1.1 200 OK
odata-version: 4.0
content-type: application/json;odata.metadata=minimal

{"@odata.context":"$metadata#data/$entity","id":"5","code":"5","name":"Desc 005"}
--changeset_892a-a8e3-582b--

Please advice if I missed any steps.

Thank you.

Regards,
Karthi M R.

[gregorwolf]

Hi @mrkarthi,

according to the CAP Documentation PUT Requests = UPDATE or PATCH? you should not use PUT to create a record.

Best regards
Gregor

[mrkarthi]

Hi @gregorwolf,

I had raised this question in SAP community for UPSERT operation and the scenario worked with SQLITE as DB.
I am trying now to use the same kind of approach with postgresql.

https://answers.sap.com/questions/13355492/multiple-records-in-post-method-in-capm.html

Please advice...

Thank you.
Regards,
Karthi M R.

[gregorwolf]

Please create a test like I've described in #123. You can start the test project also with:

npm run test:as-sqlite

to check the behaviour in SQLITE.

[mrkarthi]

Hi @gregorwolf,

I have deployed the app in the git repo and sharing the link here.
As, I had difficulties in 'cds deploy' to sqlite --in-memory, I have modified the cds.requires.db.kind parameter in package.json to 'sqlite' to run the testing in sqlite db.

https://github.com/mrkarthi/pg-app

Please check and let me know your valuable suggestions.

Thank you.

Regards,
Karthi M R.

[gregorwolf]

I've created https://github.com/gregorwolf/cds-pg/tree/PUT-for-create where I'm able to replicate your issue using the REST Client requests in tests/assets/cap-proj/rest-client-test/beers.http. So when a PUT request is sent to cds-pg the following SQL_ statements are issued:

[cds] - PUT /beershop/Beers(4c11c6a5-1b96-48da-8af4-02c91ec27716)
[cds.cds-pg] - sql >  UPDATE csw_Beers SET name = $1, ID = $2, abv = $3, ibu = $4, brewery_ID = $5, modifiedAt = $6, modifiedBy = $7 WHERE ID = $8
[cds.cds-pg] - values >  [
  'Testbier with PUT',
  '4c11c6a5-1b96-48da-8af4-02c91ec27716',
  null,
  null,
  null,
  'NOW ()',
  'ANONYMOUS',
  '4c11c6a5-1b96-48da-8af4-02c91ec27716'
]
[cds.cds-pg] - sql >  SELECT ID AS "ID", createdAt AS "createdAt", createdBy AS "createdBy", modifiedAt AS "modifiedAt", modifiedBy AS "modifiedBy", name AS "name", abv AS "abv", ibu AS "ibu", brewery_ID AS "brewery_ID" FROM BeershopService_Beers ALIAS_1 WHERE ID = $1
[cds.cds-pg] - values >  [ '4c11c6a5-1b96-48da-8af4-02c91ec27716' ]

With sqlite I see:

[cds] - PUT /beershop/Beers(4c11c6a5-1b96-48da-8af4-02c91ec27716)
[cds.sqlite] - BEGIN 
[cds.sqlite] - UPDATE csw_Beers SET name = ?, ID = ?, abv = ?, ibu = ?, brewery_ID = ?, modifiedAt = ?, modifiedBy = ? WHERE ID = ? [
  'Testbier with PUT',
  '4c11c6a5-1b96-48da-8af4-02c91ec27716',
  null,
  null,
  null,
  '2021-05-07T20:44:20.041Z',
  'anonymous',
  '4c11c6a5-1b96-48da-8af4-02c91ec27716'
]
[cds.sqlite] - SELECT 1 FROM BeershopService_Beers ALIAS_1 WHERE ID = ? [ '4c11c6a5-1b96-48da-8af4-02c91ec27716' ]
[cds.sqlite] - INSERT INTO csw_Beers ( name, ID, createdAt, createdBy, modifiedAt, modifiedBy ) VALUES ( ?, ?, ?, ?, ?, ? ) [
  'Testbier with PUT',
  '4c11c6a5-1b96-48da-8af4-02c91ec27716',
  '2021-05-07T20:44:20.041Z',
  'anonymous',
  '2021-05-07T20:44:20.041Z',
  'anonymous'
]
[cds.sqlite] - SELECT ID, createdAt, createdBy, modifiedAt, modifiedBy, name, abv, ibu, brewery_ID FROM BeershopService_Beers ALIAS_1 WHERE ID = ? [ '4c11c6a5-1b96-48da-8af4-02c91ec27716' ]

So the issue is that in cds-pg we do not test if the entry was created and do only the UPDATE and not the INSERT. Perhaps @sjvans can guide us in the right direction.

[sjvans]

hi @gregorwolf

we expect number of affected rows as result of an UPDATE. below lib/pg/execute.js fixes this issue (i didn't run the tests!).

best,
sebastian

[...]

const executeUpdateCQN = async (model, dbc, cqn) => {
  const result = await executeGenericCQN(model, dbc, cqn)
  return Array.isArray(result) ? result.length : result
}

module.exports = {
  delete: executeGenericCQN,
  insert: executeInsertCQN,
  update: executeUpdateCQN,
  read: executeSelectCQN,
  //stream: executeSelectStreamCQN,
  cqn: executeGenericCQN,
  sql: executePlainSQL,
}

[gregorwolf]

Thank you @sjvans I've added the code and a test. Works now like the sqlite version.