SAP CAP Hana Cloud Table access over synonym not working

[Cristian]

Hello experts!

We have a CAP service that needs to access a Hana Cloud DB over synonyms.

The DB table looks as follows:

Our cds data model:

namespace reverse.logix;

@cds.persistence.exists 
entity Addresses {
  key ShippingPoint : String(4);
  AddressID  : String(10);
}

Our synonym file:

{
  "REVERSE_LOGIX_ADDRESSES": {
    "target": {
        "schema": "SBX",        
        "object": "2VS_YY1_SHIPPT_ADDR"
    }
  }
}

Because the DB Table columns are in CamelCase, we have added the property cdsc in the package.json file (otherwise we were getting an error that the column was not recognized).

  "cds": {
    "cdsc": {  "sqlMapping": "quoted"  },
    "requires": {
      "auth": "xsuaa"
    },
    "sql": {
      "native_hana_associations": false
    }
  }

But we are getting the error as follows:

Error: "src/gen/ReverseLogixService.ShipmentAddresses.hdbview": the file requires "db://reverse.logix.Addresses" which is not provided by any file [8212001] (STDERR, APP/TASK/deploy)#

 Any idea about how can be wrong?

Thanks!

C.

[steffen_weinstock · Accepted solution]

Hi Cristian,

you shouldn't use option

"cdsc": {  "sqlMapping": "quoted"  },

It is not officially supported. In some situations it may work, but sometimes you will encounter errors (like you did with "invalid table name: Could not find table/view REVERSELOGIXSERVICE_ADDRESSES ...").

Currently the way to "connect" to native HANA objects that use camel case names is to define a mapping view that aligns the names, and then in the CDS model define an entity with "@cds.persistence.exists" that refers to the mapping view. See also https://cap.cloud.sap/docs/advanced/hana#quoted-table-name-quoted-column-names

Admittedly, having to introduce this mapping view is a bit clumsy. We are currently looking into the topic of "Data Integration" in general, and one aspect indeed is to be able to deal with existing tables/views that have camel case names. I cannot tell, however, when there will be a nices solution than the mapping view.

[Cristian · Accepted solution]

Many thanks!

[breglerj]

Hi,

the error message suggests that the name of your synonym is wrong. Since you changed the SQL mapping mode you also have to adjust the synonym definition to match the expected mapped name:

{
  "reverse.logix.Addresses": {
    "target": {
        "schema": "SBX",        
        "object": "2VS_YY1_SHIPPT_ADDR"
    }
  }
}

Best regards,

Jonathan

[Cristian]

@breglerj many thanks for your reply.

I have tried that and now the deployment is working properly but when executing the ODATA call I am getting the below error:

"msg":"invalid table name: Could not find table/view REVERSELOGIXSERVICE_ADDRESSES in schema LOG_REVERSE_LOGIX_DB_HDI: line 1 col 206 (at pos 205)",
"message":"invalid table name: Could not find table/view REVERSELOGIXSERVICE_ADDRESSES in schema LOG_REVERSE_LOGIX_DB_HDI: line 1 col 206 (at pos 205)",

So I am assuming that somehow the underlying view associated to the service cannot be resolved properly.

Synonyms file now:

{
"reverse.logix.Addresses": {
"target": {
"schema": "SBX",
"object": "2VS_YY1_SHIPPT_ADDR"
}
}
}

View being created in gen folder after build:

VIEW "ReverseLogixService.Addresses" AS SELECT
"Addresses_0"."ShippingPoint",
"Addresses_0"."AddressID"
FROM "reverse.logix.Addresses" AS "Addresses_0"

Any idea about what can be wrong now? I am tend to think that this scenario cannot be achieved unless all the properties in the backend are UPPER CASE.

Thanks in advance.

Regards,

C.