Skip to Content
Technical Articles
Author's profile photo Mio Yasutake

How to share tables across different CAP projects

Introduction

The purpose of this blog is to demonstrate several patterns of table sharing across different CAP projects. I’ve been using CAP for about a year, but I’m still new to HANA db, so comments and suggestions are appreciated!

 

Updates (2021/10/15)

  • Scenario 3 and 4
    • .hdbsynonym requires only synonym name, because the actual configuration is taken from .hdbsynonymconfig.
    • use SERVICE_REPLACEMENTS in mta.yaml so that we don’t have to specify the actual service name in .hdbsynonymconfig and .hdbgrants files. – thanks to the advice by Dinu PAVITHRAN
    • it is not required to bind the reused HDI container (or user provided service) to srv module.
  • Scenario4
    • use the credentials of reused HDI container for the user provided service, so no need to create a database user. – again, thanks to Dinu PAVITHRAN

 

Scenarios

I’ve come up with the following scenarios.
Each pale blue box represents a CAP project, and “Customers” in the left most box is the entity to be reused.

* Keep in mind that these scenarios are purely for learning purpose. There are cases where consuming OData service is more appropriate than accessing database directly.

  1. A project sharing the same HDI container and namespace with the reused entity
  2. A project sharing the same HDI container but using different namespace from the reused entity
  3. A project using a different HDI container from the reused entity
  4. A project in a different space from the reused entity

 

Summary

The matrix below shows an overview of required artifacts per each scenario.

In the following sections, I’ll show the settings of each project. These projects are developed using HANA Cloud trial. The entire code is available at GitHub repository below.

https://github.com/miyasuta/cap-cross-container

After you’ve completed the settings, you can access reused entity from your OData service, either with /Customers or /<mainenity>?$expand=customer endpoints.

 

Scenario 1

When your project is sharing the same HDI container and namespace with the reused entity, you need to:

  • Annotate the reused entity with @cds.persistence.exists.
namespace master.partners;

entity Vendors {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}
  • Use the same HDI container service instance as the reused entity. In the mta.yaml, specify resource type as org.cloudfoundry.existing-service.
resources:
 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   

 

Scenario 2

When your project is sharing the same HDI container but using different namespace from the reused entity, you need to:

  • Annotate the reused entity with @cds.persistence.exists.
namespace master.orgs;

entity Plants {
    key ID: Integer;
    name: String;
    customer: Association to Customers
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String
}
  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.

 

{
    "MASTER_ORGS_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS"
        }
    }
}

 

  • use the same HDI container service instance as the reused entity (same as the scenario 1).

 

Scenario 3

When your project is using a different HDI container from the reused entity, you need to:

  • First in the reused project, create roles to allow access from external containers.

 

*File name can be any name, only the extension matters.

MASTER_PARTNERS_EXTERNAL_ACCESS.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[ "SELECT" ], 
                "privileges_with_grant_option":[] 
            }                          
        ]
    }
}

MASTER_PARTNERS_EXTERNAL_ACCESS_G.hdbrole

{
    "role": {
        "name": "MASTER_PARTNERS_EXTERNAL_ACCESS_G#",
        "object_privileges": [
            { 
                "name":"MASTER_PARTNERS_CUSTOMERS", 
                "type":"TABLE", 
                "privileges":[], 
                "privileges_with_grant_option":["SELECT"]
            }                          
        ]
    }
}

These files look similar, only difference being that the first one has the “privileges” for “SELECT”, and the second one has “privileges_with_grant_option” for “SELECT”. Later, the first role will be assigned to an application user of a newly created HDI container and the second role to an object owner of the same. Once you’ve made above changes, deploy the project to the Cloud Foundry.

 

Next steps will be performed in the “sales” project.

 

  • Annotate the reused entity with @cds.persistence.exists.
namespace sales;

entity Orders {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers;
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

 

  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.
{
    "SALES_CUSTOMERS": {}
}

This file is almost empty, as the actual configuration is coming from .hdbsynonymconfig. This file needs to be placed under db/cfg as below. The artifacts in db/cfg are processed first in deployment time.

 

  • Create .hdbsynonymconfig file to supply schema name.
{
    "SALES_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "master-db-hdi/schema"
        }
    }
}

Schema name will be taken from “schema” property of “master-db-hdi” service instance.

* “master-db-hdi” is an alias for the HDI container “master-db”, which will be defined in SERVICE_REPLACEMENTS in mta.yaml. Thanks to this SERVICE_REPLACEMENTS concept, we don’t have to specify actual service instance names in our development artifacts.

 

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "master-db-hdi": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

master-db-hdi” at the top points to the reused HDI container service instance, which is the grantor of these privileges.

 

  • Add the HDI container service instance of the reused entity (master-db) to mta.yaml.
 # --------------------- SERVER MODULE ------------------------
 - name: sales-srv
 # ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # Resources extracted from CAP configuration
    - name: sales-db 
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: sales-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: sales-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}       
    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   


resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: sales-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}

 - name: master-db
   type: org.cloudfoundry.existing-service
   parameters:
     service-name: master-db   
   properties:
      master-db-hdi: ${service-name}          

Here, two HDI container are used by srv module and db deployer module each. To let the deployer know which container to deploy the project’s own artifacts, property TARGET_CONTAINER is specified for sales-db.

Also, note that the alias for “master-db” is defined in the SERVICE _REPLACEMENTS section as below.

    - name: master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: master-db-hdi
        service: ~{master-db-hdi}   

 

Scenario 4

When your project is in a different space from the HDI container of the reused entity, you need to create a user provided service which contains a database user who has the privileges to grant access to reused entities.

For this, we will first create a service key for master-db service instance, and based on the service key we will create a user provided service.

 

  • Create a service key for master-db.
cf create-service-key master-db grantor-key
  • In the accounting project directory, dump the key into a file.
cf service-key master-db grantor-key > grantor-key.json
  • Remove a few lines before the first curly bracket and make it a valid JSON file
  • Remove the outmost {“credentials”: …}
  • Tag the service as hana by adding the following property to JSON
    “tags”:  “hana” 
{
	"certificate": "-----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE-----",
        "database_id": "2f4af70e-8103-40fd-ab04-e0224a420803",
	"driver": "com.sap.db.jdbc.Driver",
	"hdi_password": "password",
	"hdi_user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_DT",
	"host": "6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com",
	"password": "password",
	"port": "443",
	"schema": "349472F3D2FB4B6BBBF430B47451A7B2",
	"url": "jdbc:sap://6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com:443?encrypt=true\u0026validateCertificate=true\u0026currentschema=349472F3D2FB4B6BBBF430B47451A7B2",
	"user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_RT",
	"tags": "hana"
}
  • Switch to dev2 space and create a user provided service.
cf create-user-provided-service ups-master-db -p grantor-key.json

 

The remaining steps are similar to the scenario 3.

  • Annotate the reused entity with @cds.persistence.exists
namespace accounting;

entity Invoices {
    key ID: Integer;
    amount: Integer;
    customer: Association to Customers;    
}

@cds.persistence.exists
entity Customers {
    key ID: Integer;
    name: String;
}

 

  • Create .hdbsynonym file to fit the reused entity’s name to your namespace.
{
    "ACCOUNTING_CUSTOMERS": {}
}

 

  • Create .hdbsynonymconfig file to supply schema name. This file should to be placed under db/cfg.
{
    "ACCOUNTING_CUSTOMERS": {
        "target": {
            "object": "MASTER_PARTNERS_CUSTOMERS",
            "schema.configure": "cross-schema-ups/schema"
        }
    }
}

Schema name will be taken from “schema” property of “cross-schema-ups” service instance, which is the user provided service we created in the previous step.

* “cross-schema-ups” is an alias for the actual ups name.

 

  • Create .hdbgrants file to assign roles to HDI container users.
{
    "cross-schema-ups": {
        "object_owner": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS_G#"
            ]
        },
        "application_user": {
            "container_roles": [
                "MASTER_PARTNERS_EXTERNAL_ACCESS"
            ]
        }
    }
}

 

  • Add the user provided service instance (ups-master-db) to mta.yaml.
# --------------------- SERVER MODULE ------------------------
 - name: accounting-srv
# ------------------------------------------------------------
   type: nodejs
   path: gen/srv
   parameters:
     buildpack: nodejs_buildpack
     memory: 256M
     disk-quota: 1024M      
   requires:
    # Resources extracted from CAP configuration
    - name: accounting-db
   provides:
    - name: srv-api      # required by consumers of CAP services (e.g. approuter)
      properties:
        srv-url: ${default-url}

 # -------------------- SIDECAR MODULE ------------------------
 - name: accounting-db-deployer
 # ------------------------------------------------------------
   type: hdb
   path: gen/db  
   parameters:
     buildpack: nodejs_buildpack     
   requires:
    # 'hana' and 'xsuaa' resources extracted from CAP configuration
    - name: accounting-db
      properties: 
        TARGET_CONTAINER: ~{hdi-service-name}     
    - name: ups-master-db
      group: SERVICE_REPLACEMENTS
      properties:
        key: cross-schema-ups
        service: ~{cross-schema-ups}

resources:
 # services extracted from CAP configuration
 # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
# ------------------------------------------------------------
 - name: accounting-db
# ------------------------------------------------------------
   type: com.sap.xs.hdi-container
   parameters:
     service: hana  # or 'hanatrial' on trial landscapes
     service-plan: hdi-shared
   properties:
     hdi-service-name: ${service-name}      

 - name: ups-master-db
   type: org.cloudfoundry.existing-service
   parameters:
      service-name: ups-master-db   
   properties:
      cross-schema-ups: ${service-name}

 

Conclusion

In this blog, I explained four patterns of table sharing across different CAP projects.

  • A project sharing the same HDI container and namespace with the reused entity
    • No special setting is required. Just use the same HDI container service instance as the reused entity.
  • A project sharing the same HDI container but using different namespace from the reused entity
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
  • A project using a different HDI container from the reused entity
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
    • .hdbsynonymconfig is required to supply schema name to the synonym.
    • .hdbgrants is required to grant HDI container users privileges to access reused entities.
  • A project in a different space from the reused entity
    • A user provided service is required to enable cross-space access.
    • .hdbsynonym is required to fit the reused entity’s namespace to your namespace.
    • .hdbsynonymconfig is required to supply schema name to the synonym.
    • .hdbgrants is required to assign HDI container users privileges to access reused entities.

References

YouTube

CAP document

SAP Help

 

Assigned Tags

      59 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dinu PAVITHRAN
      Dinu PAVITHRAN

      Thanks for posting this lucid explanation.  A few suggestions for you to try out:

      It would be great if you could try out SERVICE_REPLACEMENTS[1] as an way to decouple the service names in hdbsynonymconfig  from the actual name of the service referenced.

      https://www.npmjs.com/package/@sap/hdi-deploy

      In scenario 4, you could try creating another key for the service instance. This key would have the users with the required permissions. This has the added advantage of being a documentation for this usage. It can be done easily and without DBADMIN user access. The user names are ugly though.

      Looking forward to you clear walkthrough of these too.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Dinu PAVITHRAN,

      Thank you for your advice! I will try using SERVICE_REPLACEMENTS and update this post.

      I'd like to confirm the second piece of your advice.

      Does "creating another key for the service instance" means to create a service key for HDI container master-db in my case? If so, how can this key be referenced from a CAP project in a different space?

      Best regards,

      Mio

       

      Author's profile photo Dinu PAVITHRAN
      Dinu PAVITHRAN

      I dont think space matters if you give the credentials in user provided service. Please try the following:

      - Create a service key
      cf create-service-key master-db grantor-key

      - Dump the key into a file:
      cf service-key master-db grantor-key > grantor-key.json

      - Edit the file (remove a few lines before { ) and make it a valid json file.
      - Tag the service as hana by adding the following property to json
      "tags": [ "hana" ]

      - Switch space
      - Create user provided service
      cf crete-user-provided-service my-db-mine -p grantor-key.json

      That should do the trick.
      Using a json file makes commands simpler.

      PS: This blog is a good reference for syntax in mta for SERVICE_REPLACEMENTS, even though it is in the context of XSA and webide.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you for providing the steps. it worked!

      This is indeed a better way than to create a new DB user.

      I have updated my post.

       

      Author's profile photo Jason Scott
      Jason Scott

      Excellent post with good simple explanations of difficult concepts.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you so much for your encouraging comment!

      Author's profile photo smith brown
      smith brown

      Thanks for introducing that opportunity and methods about table sharing. It's a very guiding code and especially all scenarios very helpful. Regards: @smith

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Thank you for your comment! Glad to hear that this content was helpful to you.

      Author's profile photo Shubham Bathla
      Shubham Bathla

      Hi Mio,

      I tried Scenario 3 (trying to access a table from a different HDI container) under same Dev Space and both HDI deployed on same HanaCloud instance.

       

      However, I'm getting an error "service definition does not exist" (screenshot attached "testdb.jpg").

       

      I also tried running your sample project from GIT repository (https://github.com/miyasuta/cap-cross-container).

      1. Tried cloning this project to my Trial BAS.
      2. Installed the NPM modules (npm install) for both projects ('master-partner' & 'sales').
      3. Executed command 'CDS Deploy' to create the HDI containers (for master-partner).
      4. Executed command 'CDS Deploy' to create the HDI containers (Sales).
      5. Getting the similar error 'service definition not found' (screenshot attached).

       

      Could you please help me with this? I'm not sure if I'm missing any step from my end or any additional change is required in my project.

       

      Thanks,

      Shubham

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Shubham Bathla,

      Thanks for pointing it out.

      I noticed that the grantor of MASTER_PARTNERS.hdbgrants needs to be "master-db-hdi", not "master-db". This is the key specified in SERVICE_REPLACEMENTS part of mta.yaml.

      {
          "master-db-hdi": {
              ...
          }
      }

      I have updated the git repository and it should work now.

       

      Regards,

      Mio

      Author's profile photo Shubham Bathla
      Shubham Bathla

      Hi Mio,

      Tried cloning the project again but still getting the issue.

       

      Regards,

      Shubham

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Shubham Bathla,

      Today I tried deleting all the deployed artifacts, pulling from Git and re-deploying. It was successful.

      Please try the following steps.

      1. Undeploy mtar applications with the following command
        • cf undeploy sales --delete-services --delete-service-keys
          cf undeploy master-partners --delete-services --delete-service-keys
      2. Build and re-deploy mtar applilcations (master-partners -> sales)
      Author's profile photo Shubham Bathla
      Shubham Bathla

      Thanks Mio.

      It's working as expected now.

      Author's profile photo Mary Silvester
      Mary Silvester

      I was trying Scenario 3 in the existing table. But whenever I add the synonyms , deployment is failing. Getting error "HDI make failed".

      Can you please guide how to update the existing table configuration without using undeploy command.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Mary Silvester,

      Could you share the error detail?

      Author's profile photo Mary Silvester
      Mary Silvester

      Hi Mio Yasutake,

      Please find the error details.

       

      Thank you

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      The following GitHub issue looks similar to your case.

      https://github.com/SAP-archive/com.sap.openSAP.hana5.example/issues/6

      Author's profile photo Rathish K
      Rathish K

      Hi Mio,

      Very nice blog!!

      I am trying to deploy db artifacts in master-org, I am getting below error , Am i missing any step here? Synonym file is present under src folder.

      "The file requires "db://MASTER_PARTNERS_CUSTOMERS" which is not provided by any file [8212001]
      at "src/MASTER_PARTNERS_CUSTOMERS.hdbsynonym$MASTER_ORG_CUSTOMER.defaults" (0:0)"

       

      Kindly help.

       

      Thanks

      Rathish

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Rathish K,

      Thanks for your comment.

      Have you deployed master-partners project already?

      Author's profile photo Rathish Karunakaran
      Rathish Karunakaran

      Hi Mio,

      Yes, I have deployed at DB, Do I need to add schema name in synonym file?

      When I add Schema name in synonym and deploy, it gives me privilege access issue for the user.

      Am I missing any step here? Attach the screen shot with table name present in HDI container.

      Thanks

      Rathish

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Rathish,

      No, you don't need to add schema name in synonym file. My synonym definition is below and it's working fine.

      {
          "MASTER_ORGS_CUSTOMERS": {
              "target": {
                  "object": "MASTER_PARTNERS_CUSTOMERS"
              }
          }
      }

      Please check in mta.yaml if you have bound your srv and db modules to the existing master-db instance.

      resources:
       - name: master-db
         type: org.cloudfoundry.existing-service
         parameters:
           service-name: master-db   
      

      Regards,

      Mio

      Author's profile photo Ahmet Bueyuekodabasi
      Ahmet Bueyuekodabasi

      Hi Mio,

      nice blog! Thank you.

      I successfully deployed it on my trial account.

      I was using the scenario 4 (2 container in 2 different spaces via user provided service).

      But I'm not able to delete the synonyms afterwards (even I have removed the synonyms in the db/src folder locally).

      What I was trying was to undeploy the synonyms via the undeploy.json.

      My undeploy.json (file is inside the db folder) looks like that:

      [
        "src/gen/**/*.hdbview",
        "src/gen/**/*.hdbindex",
        "src/gen/**/*.hdbconstraint",
        "src/gen/**/*.hdbsynonym",
        "src/gen/**/*.hdbsynonymconfig",
        "src/gen/**/*.hdbgrants",
        "src/gen/**/*.hdbrole"
      ]
      

      So my question here is:

      Maybe you can update your blog with "How to remove/undeploy synonyms"?

      THX

      Ahmet

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Ahmet Bueyuekodabasi,

      Thanks for your comment. Actually I haven't tried undeploying anything from CAP project, but I will give it a try.

      Author's profile photo Ahmet Bueyuekodabasi
      Ahmet Bueyuekodabasi

      Hi Mio,

      thanks for your help.

      I think you do not need to try out any more.

      In the meantime we figured out that the *.hdbsynonym files are located directly within the src folder when deployed.

      So the correct undeploy.json should look like this:

      [
        "src/gen/**/*.hdbview",
        "src/gen/**/*.hdbindex",
        "src/gen/**/*.hdbconstraint",
        "src/*.hdbgrants",
        "src/*.hdbsynonym",
        "cfg/*.hdbsynonymconfig"
      ]

       

      THX Ahmet

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Ahmet,

      Great that you figured it out, and thanks for sharing!

      Author's profile photo Kirti Mittal
      Kirti Mittal

      Mio Yasutake

      Hi,

      I'm following Scenario1, In this while doing the deployment to cf I'm getting error when I run the command "cds deploy --to hana" (Error: The file requires "db://WI_WORKITEMS" which is not provided by any file [8212001]).

      Then I have removed the annotation "@cds.persistence.exists" and then it allowed me to deployed the code.

      After that I have added the annotation again, and then it allowed me to deploy the code again.

      So the question here is that when I'm deploying the code with the annotation for the first time then it doesn't allow me to deploy the code and after that It allowed. So how can we resolve this issue?

       

      Thanks,

      Kirti

       

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Kirti Mittal,

      Thanks for trying the scenario.

      I used "cf deploy", rather than "cds deploy --to hana", and this worked fine.

       

      Author's profile photo jatindranath patil
      jatindranath patil

      Hi Mio Yasutake,

      Thank you for sharing great blog with proper information.

      I am trying 3rd scenario with custom project/entities.

      for eg. project name "D1" & "D2"

      so D1 gets deployed but while deploying D2 it is giving me following error:

      Error: service D1-db-hdi not found; the service definition does not exist.

      Kindly help me out with this. Thank you in advance.

      Regards,

      Jatin Patil.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi jatindranath patil,

      Thanks for your comment.

      How did you deploy your projects? Did you use "cds deploy --to hana" or "cf deploy"?

      I used the latter and this worked just fine.

      Author's profile photo jatindranath patil
      jatindranath patil

      I used "cds deploy --to hana" and then it give an error.

      PFA mta.yaml. let me know is there any issue in this file?

      Error: [deploy] - Error: service D1-db-hdi not found; the service definition does not exist.

      _schema-version: '3.1'
      ID: D2
      version: 1.0.0
      description: "A simple CAP project."
      parameters:
        enable-parallel-deployments: true
      
      build-parameters:
        before-all:
          - builder: custom
            commands:
              - npm install --production
              - npx -p @sap/cds-dk cds build --production
      
      modules:
        # --------------------- SERVER MODULE ------------------------
        - name: D2-srv
          # ------------------------------------------------------------
          type: nodejs
          path: gen/srv
          parameters:
            buildpack: nodejs_buildpack
            memory: 256M
            disk-quota: 512M
          requires:
            # Resources extracted from CAP configuration
            - name: D2-db
            # - name: D1-db
          provides:
            - name: srv-api # required by consumers of CAP services (e.g. approuter)
              properties:
                srv-url: ${default-url}
      
        # -------------------- SIDECAR MODULE ------------------------
        - name: D2-db-db-deployer
          # ------------------------------------------------------------
          type: hdb
          path: gen/db
          parameters:
            buildpack: nodejs_buildpack
          requires:
            # 'hana' and 'xsuaa' resources extracted from CAP configuration
            - name: D2-db
              properties:
                TARGET_CONTAINER: ~{hdi-service-name}
            - name: D1-db
              group: SERVICE_REPLACEMENTS
              properties:
                key: D1-db-hdi
                # key: D1-db
                # service: ~{D1-db}   
                service: ~{D1-db-hdi}
      
      resources:
        # services extracted from CAP configuration
        # 'service-plan' can be configured via 'cds.requires.<name>.vcap.plan'
        # ------------------------------------------------------------
        - name: D2-db
          # ------------------------------------------------------------
          type: com.sap.xs.hdi-container
          parameters:
            service: hanatrial #hana # or 'hanatrial' on trial landscapes
            service-plan: hdi-shared
          properties:
            hdi-service-name: ${service-name}
      
        - name: D1-db
          type: org.cloudfoundry.existing-service
          parameters:
            service-name: D1-db
          properties:
            D1-db-hdi: ${service-name}
      Author's profile photo DEO DEO
      DEO DEO

      Hello ,

      Mio Yasutake  thanks for your gt post.

      Just wanted to check with you i am facing issues with the

       

      [deploy] - Processing revoke files...
      Processing revoke files... ok (0s 0ms)

      [deploy] - Processing grants files...

      [deploy] - Processing "src/MASTER_PARTNERS..hdbgrants"...

      [deploy] - Deployment ended at 2022-11-30 13:49:20

      [deploy] - Error: service master-partners-db-hdi not found; the service definition does not exist.

      [deploy] - (0s 980ms)

       

      _schema-version: '3.1'
      ID: sales
      description: A simple CAP project.
      version: 1.0.0
      modules:
        - name: sales-srv
          type: nodejs
          path: gen/srv
          requires:
            - name: sales-db
          provides:
            - name: srv-api
              properties:
                srv-url: '${default-url}'
          parameters:
            buildpack: nodejs_buildpack
          build-parameters:
            builder: npm-ci
        - name: sales-db-deployer
          type: hdb
          path: gen/db
          requires:
            - name: sales-db
              properties:
                 TARGET_CONTAINER: ~ {hdi-service-name}
            - name: master-partners-db
              group: SERVICE_REPLACEMENTS
              properties:
                key: master-partners-db-hdi
                service: ~{master-partners-db-hdi}
      resources:
        - name: sales-db
          type: com.sap.xs.hdi-container
          parameters:
            service: hana
            service-plan: hdi-shared
        - name: master-partners-db
          type: org.cloudfoundry.existing-service
          parameters:
            service-name: master-partners-db
          properties:
            master-partners-db-hdi: ${service-name}
      Any  hint?
      Br
      Dibya
      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi DEO DEO,

      What I observe is when you deploy with "cf deploy mta_archives/....", deployment succeeds.

      If you deploy with "cds deploy --to hana", you see the "service definition does not exist" error.

      Author's profile photo jatindranath patil
      jatindranath patil

      Hi Mio,

      I tried with "cf deploy" but facing below error

      user: D2 $ cf deploy mta_archives/D2_1.0.0.mtar
      Deploying multi-target app archive mta_archives/D2_1.0.0.mtar in org c9bc4b11trial / space dev as jatindranath.patil@wipro.com...
      
      
      There is an ongoing operation for multi-target app D2. Do you want to abort it? (y/n)> y
      Executing action 'abort' on operation 5f0df467-7baa-11ed-807e-eeee0a826bae...
      OK
      Uploading 1 files...
        /home/user/projects/D2/mta_archives/D2_1.0.0.mtar
      OK
      Operation ID: ad2f5e03-7bab-11ed-807e-eeee0a826bae
      Deploying in org "c9bc4b11trial" and space "dev"
      Detected MTA schema version: "3"
      Detected deployed MTA with ID "D2" and version "1.0.0"
      Detected new MTA version: "1.0.0"
      Deployed MTA version: "1.0.0"
      Processing service "D2-db"...
      Updating application "D2-db-db-deployer"...
      Updating application "D2-srv"...
      Unbinding service instance "D2-db" from application "D2-db-db-deployer"...
      Unbinding service instance "D2-db" from application "D2-srv"...
      Binding service instance "D2-db" to application "D2-db-db-deployer"...
      Unbinding service instance "D1-db" from application "D2-db-db-deployer"...
      Binding service instance "D1-db" to application "D2-db-db-deployer"...
      Binding service instance "D2-db" to application "D2-srv"...
      Uploading application "D2-srv"...
      Uploading application "D2-db-db-deployer"...
      Started async upload of application "D2-srv"
      Started async upload of application "D2-db-db-deployer"
      Scaling application "D2-srv" to "1" instances... 
      Scaling application "D2-db-db-deployer" to "1" instances... 
      Staging application "D2-srv"...
      Staging application "D2-db-db-deployer"...
      Application "D2-srv" staged
      Starting application "D2-srv"...
      Application "D2-srv" started and available at "c9bc4b11trial-dev-d2-srv.cfapps.us10.hana.ondemand.com"
      Application "D2-db-db-deployer" staged
      Executing task "deploy" on application "D2-db-db-deployer"...
      Execution of task "deploy" on application "D2-db-db-deployer" failed.
      Download the application logs via the dmol command and check them for more information.
      A step of the process has failed. Retrying it may solve the issue.
      Proceeding with automatic retry... (3 of 3 attempts left)
      Executing task "deploy" on application "D2-db-db-deployer"...
      Execution of task "deploy" on application "D2-db-db-deployer" failed.
      Download the application logs via the dmol command and check them for more information.
      A step of the process has failed. Retrying it may solve the issue.
      Proceeding with automatic retry... (2 of 3 attempts left)
      Executing task "deploy" on application "D2-db-db-deployer"...
      Execution of task "deploy" on application "D2-db-db-deployer" failed.
      Download the application logs via the dmol command and check them for more information.
      A step of the process has failed. Retrying it may solve the issue.
      Proceeding with automatic retry... (1 of 3 attempts left)
      Executing task "deploy" on application "D2-db-db-deployer"...
      Execution of task "deploy" on application "D2-db-db-deployer" failed.
      Download the application logs via the dmol command and check them for more information.
      A step of the process has failed. Retrying it may solve the issue.
      Process failed.
      Use "cf deploy -i ad2f5e03-7bab-11ed-807e-eeee0a826bae -a abort" to abort the process.
      Use "cf deploy -i ad2f5e03-7bab-11ed-807e-eeee0a826bae -a retry" to retry the process.
      Use "cf dmol -i ad2f5e03-7bab-11ed-807e-eeee0a826bae" to download the logs of the process.
      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Please see the application log via "cf dmol -i ad2f5e03-7bab-11ed-807e-eeee0a826bae" command.

      Author's profile photo jatindranath patil
      jatindranath patil

      Hi Mio,

      I found the solution in following blog:

      https://answers.sap.com/questions/13048899/error-could-not-find-the-comsaphanadicds-build-plu.html

      ============================================================================

      Are you on the SAP HANA Cloud? If so this newest version of HANA no longer supports HDBCDS. In order to use CAP, you have to set the experimental feature in the package.json to use hdbtable/hdbview instead hdbcds as the generation target.

          "cds": {
               "hana": {
                  "deploy-format": "hdbtable"
              },
              "requires": {
                  "db": {
                      "kind": "hana"
                  }
              }
          }

       

      ============================================================

      Please go through it.

       

      Regards,

      Jatin Patil

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Good to know you found a solution.

      My app is also working on HANA Cloud. I don't know why, but I was able to deploy without specifying "deploy-format".

       

      Author's profile photo DEO DEO
      DEO DEO

       

      Hello Mio,

      As per your suggestion i have deployed in sap CF still no luck.

      2022-12-20T14:19:18.40+0000 [APP/TASK/deploy/0] ERR Error: service master-partners-db-hdi as replacement for service master-partners-db-hdi not found; the service definition does not exist.
      Can you please provide any clue , i have tried same as you explain?

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Your code looks good.

      Does master-partners-db already exist? I noticed that my example "sales" app uses "master-db", while yours uses "master-partners-db".

      Author's profile photo DEO DEO
      DEO DEO

      Hello Mio,

      Does master-partners-db already exist? Yes ,i have changed the name to master-partners-db(instead of master-db).One thing i suspect in mta.yml of sales app service properties with tilt (~)  it is not taking the properties properly , it could be the reason or any other reason ?seems alias of HDI is not taking properly? any clue

           - name: master-partners-db
              group: SERVICE_REPLACEMENTS
              properties:
                key: master-partners-db-hdi
                service: ~ {master-partners-db-hdi}
      Br
      DD

       

       

      Author's profile photo Kedar Kulkarni
      Kedar Kulkarni

      Hello,

      One observation, in the case of scenario 4, the HDI container is different but also the SAP HANA Cloud instance service is different. We can not create a common instance of SAP HANA Cloud instance across different spaces.

       

      I am facing below issue when I am trying scenario 4 where I want to share an entity from space1 to space2.

      Error: "ICH_ORG_MANAGEMENT_EXTERNAL_ACCESS_G#": cannot grant the role to the principal "CF7AF74F28A24BCBA5E9C8C10CEEAC7B#OO" in the "" schema; the role can only be granted to a container's object owner; the principal "CF7AF74F28A24BCBA5E9C8C10CEEAC7B#OO" in the "" schema is not an object owner of a known container [8214044] (STDOUT, APP/TASK/deploy)#
      Error: Granting schema roles in the container "58552029471B484589537132C2C0BB25" and the parameters "[]"... failed [8214221] (STDOUT, APP/TASK/deploy)#
      Can you please help out here to understand what this error means?
      Thanks,
      Kedar
      Author's profile photo Massimiliano Burzi
      Massimiliano Burzi

      Hi Mio,
      many thanks for this amazing blog!

      I'm writing to you because I'm facing two problems.
      I'm developing a CAP module which need to define a calculation view based on two tables. One table is from a different space and have independent name space and independent HDI container (so I'm in the fourth scenario).

      • First problem is that when I try to deploy the .hdbgrants file I get this error:

        Error: Error executing: GRANT "29BA77124B3E464AB3535E5466306090"."DIPENDENTI_EXTERNAL_ACCESS_G#" TO "C15107F74CF64E70AB1E70E645A20D4A#OO";
        (nested message: insufficient privilege: Detailed info for this error can be found with guid '6089B01EE1DE3344B56C8FD801E40A57')
        grantor service: "Vir_DB-hana", type: "sql", user: "29BA77124B3E464AB3535E5466306090_E2K8P018KCJ2SWJ4UE3DTKI8Z_RT"
        file name: cfg/IT_VF_VED.hdbgrants

        So in Database Explorer I've launched the query below discovering that the value of IS_MISSING_GRANT_OPTION is true for user 29BA77124B3E464AB3535E5466306090_E2K8P018KCJ2SWJ4UE3DTKI8Z_RT

        CALL SYS.GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS ('6089B01EE1DE3344B56C8FD801E40A57', ?)

        So as DBADMIN I've tried to grant option to the user through this command without success:
        grant select on schema 29BA77124B3E464AB3535E5466306090 to 29BA77124B3E464AB3535E5466306090_E2K8P018KCJ2SWJ4UE3DTKI8Z_RT with grant option;

        DBADMIN as well has column IS_MISSING_GRANT_OPTION with true value

      • Second problem is after creating of hdbsynonym and importing the table belonging to foreign container, I can't see its columns in the graphical tool for the creation of calculation views and for this reason I can't do the join definition.

      Do you ever experienced these kind of problems? Do you have any idea or suggestion to solve them?

      Thanks,

      Maurizio

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Massimiliano Burzi,

      Thanks for reading this blog.

      For calculation view scenarios, user provided service is used to establish connection to a foreign container in design time. You can find the steps in the following blog post.

      https://blogs.sap.com/2022/09/26/cross-hdi-container-access-using-user-provided-service-for-containers-within-same-sap-hana-cloud-instance/

      Author's profile photo Andre Kuller
      Andre Kuller

      Hi Mio Yasutake ,

       

      very good blog. It helps to understand the whole thing. I have a use case according to scenario 4 and have rebuilt the application accordingly. When deploying the following error message: Error: service ..-db as replacement for service cross-... not found; the service is user-provided, but is missing the tag 'hana' or the tag 'password' in the credentials properties.

      Ok, cloned the git repo, deployed partners, created key and service. Accounts deployed, same error (Error: service ups-master-db as replacement for service cross-schema-ups not found; the service is user-provided, but is missing the tag 'hana' or the tag 'password' in the credentials properties.) Has anything currently changed in the scenario or the path?

       

      key file (values truncated):

      {
        "credentials": {
          "certificate": "-----BEGIN CERTIFICATE----- -----END CERTIFICATE-----",
          "database_id": "598453ee-9dbd-47e9-9a87",
          "driver": "com.sap.db.jdbc.Driver",
          "hdi_password": "Xu9j_M3PpVPD6qXdxAbs7gGrs",
          "hdi_user": "D774F8EBDC2C490F906C7",
          "host": "598453ee-9dbd-47e9-.hana.prod-eu20.hanacloud.ondemand.com",
          "password": "Eo4aaMDqjKGI2erBfgueu3s",
          "port": "443",
          "schema": "D774F8EBDC2C490F906C7",
          "url": "jdbc:sap://598453ee-9dbd-47e9-9a87-.hana.prod-eu20.hanacloud.ondemand.com:443?encrypt=true&validateCertificate=true&currentschema=D774F8EBDC2C490F906C7",
          "user": "D774F8EBDC2C490F906C7_2PF1V5T2UYRL7Z_RT",
          "tags": ["hana"]
        }
      }

      public-db is my own scenario.

      For tags I also tried all variants known on the net ["hana"]|"hana"|"[\"hana\"]".

      Thanks a lot

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Andre Kuller,

      Unfortunately, I get the same error now. Something might have changed since I published this post.

      In the below thread there is a similar question. According to the answer you don't need the square brackets around "hana", but even I removed them I still get the same error.

      https://answers.sap.com/questions/13558556/user-provided-problem.html

      I would appreciate if you could share the solution once you have solved it.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Andre Kuller,

      I modified the definition of user provided service as below and the deploy was successful. The point is to remove the square brackets around "hana".

      {
      	"certificate": "-----BEGIN CERTIFICATE-----certificate-----END CERTIFICATE-----",
      	"driver": "com.sap.db.jdbc.Driver",
      	"hdi_password": "password",
      	"hdi_user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_DT",
      	"host": "6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com",
      	"password": "password",
      	"port": "443",
      	"schema": "349472F3D2FB4B6BBBF430B47451A7B2",
      	"url": "jdbc:sap://6f4ade47-353e-4a67-bbb0-431cba244981.hana.trial-eu10.hanacloud.ondemand.com:443?encrypt=true\u0026validateCertificate=true\u0026currentschema=349472F3D2FB4B6BBBF430B47451A7B2",
      	"user": "8C0524334BA3449487CF430CCCF279F4_2CPDC6Q1YEW5CA32U4OXI923P_RT",
      	"tags": "hana"
      }

       

       

      Author's profile photo Andre Kuller
      Andre Kuller

      Hi Mio Yasutake

       

      I tried it several times in all possible variations. Nothing worked until the moment I did an undeploy and deploy.

      It works for now, I'll test it a bit more with the actual application. It would be very inelegant to undeploy when changes etc. are made.

       

      Thank you for your test and feedback!

      Author's profile photo Andre Kuller
      Andre Kuller

      Hi Mio Yasutake

       

      I think I have found the problem now. When the key is created, the following file comes out:

      Getting key grantor-key for service instance master-db as ...
      {
        "credentials": {
          "certificate": "-----BEGIN CERTIFICATE---------END CERTIFICATE-----",
          "database_id": "598453ee-9...
        }
      }
      I have removed the first lines to get the valid JSON. But what I only need is the object of credentials, so now it works without problem. It was displayed correctly in the service instances (without credentials), but there seems to be a problem somewhere.Thanks again for your test! 

      You have written it correctly in the example in the blog, as well as in the comment. I would still expand the text in the blog.  "Edit the file (remove a few lines before { ) and make it a valid json file."

      Quick note, in the accounting schema a semicolon is missing behind customer.

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Andre Kuller,

      Thanks for  your comment. Actually, after receiving your first comment I followed my blog post and made the exactly same mistake as you (including "credentials" in the JSON). The explanation in the blog was not clear, so I improved it.

      Author's profile photo Diego Alejandro Guizzo
      Diego Alejandro Guizzo

       

      Hi

      I followed the steps of scenario 4, but it gives me an error

      2023-09-06T12:38:06.886+0000 [CELL/0] STDOUT Cell 0c28d879-0fe7-4f28-a504-45b1cdbe1f44 successfully destroyed container for instance 58037ff4-f781-4927-6f48-0726
      2023-09-06T12:40:30.098+0000 [APP/PROC/WEB/0] STDOUT [cov2ap] - [HPM] Proxy created: / -> http://localhost:8080
      2023-09-06T12:40:30.098+0000 [APP/PROC/WEB/0] STDOUT [cov2ap] - [HPM] Proxy rewrite rule created: "^/v2" ~> ""
      2023-09-06T12:40:30.102+0000 [APP/PROC/WEB/0] STDOUT [cds] - GET /catalog-service-main/histposiciones
      2023-09-06T12:40:30.122+0000 [APP/PROC/WEB/0] STDERR [cds] - Error: No database credentials provided

      user provider

       

      {
          "certificate": "-----BEGIN CERTIFICATE-----\xxxxxxxxx\n-----END CERTIFICATE-----",
          "driver": "com.sap.db.jdbc.Driver",
          "hdi_password": "xxxxx",
          "hdi_user": "xxxxxxx_DT",
          "host": "xxxxxxa01f43e9bc.hna0.prod-us10.hanacloud.ondemand.com",
          "password": "xxxxxxx",
          "port": "443",
          "schema": "E4B36C2011A24C4F8E6C6201B02A89D7",
          "url": "jdbc:sap://xxxxxxx.hna0.prod-us10.hanacloud.ondemand.com:443?encrypt=true\u0026validateCertificate=true\u0026currentschema=xxxxxx",
          "user": "xxxxxxx_RT",
          "tags": "hana"
      }

      greetings

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Diego Alejandro Guizzo,

      I tried deploying my services to a newly created subaccount, which was successful. One difference I noticed was that the credential for HDI container now contains "database_id" as below. Can you try adding the database_id to the user provided service ?

      {
          "certificate": "-----BEGIN CERTIFICATE-----xxx\n-----END CERTIFICATE-----",
          "database_id": "2f4af70e-8103-40fd-ab04-e0224a420803",
          "driver": "com.sap.db.jdbc.Driver",
          "hdi_password": "xxx",
          "hdi_user": "xxx_DT",
          "host": "2f4af70e-8103-40fd-ab04-e0224a420803.hana.trial-us10.hanacloud.ondemand.com",
          "password": "xxx",
          "port": "443",
          "schema": "B42087682BAF46219C81D1D614AA1F53",
          "url": "jdbc:sap://xxx.hana.trial-us10.hanacloud.ondemand.com:443?encrypt=true&validateCertificate=true&currentschema=B42087682BAF46219C81D1D614AA1F53",
          "user": "xxx_RT",
          "tags": "hana"
      }

       

      Author's profile photo Maximiliano Colman
      Maximiliano Colman

      Beautiful, just like that, thanks a lot for sharing it

      Author's profile photo Nils Janßen
      Nils Janßen

      Mio Yasutake

      Thank you very much for the blog post. We are currently checking out Scenario 2 and found out, that the deployment or the schema for the second project breaks when the first project deploys.

      It does look like the first project overwrites the db schema and thus removes the stuff that is deployed by project 2.

      How can we prevent this?

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Nils Janßen,

      If your base projects has db/undeploy.json file, deploying that project will delete artifacts specified in the file.

      One option is to remove the undeploy.json, but this can cause views removed from your first project not deleted from the database. The other option is to specify which artifacts can be deleted in the undeploy.json.

      [
        "src/gen/**/master.partners.*.hdbview",
        "src/gen/**/*.hdbindex",
        "src/gen/**/*.hdbconstraint"
      ]
      

       

      Best regards,

      Mio Yasutake

       

      Author's profile photo Nils Janßen
      Nils Janßen

      Thank you, I did not think of that. The undeploy.json of project 1 was to broad and undeployt the changes from project 2.

      Author's profile photo Apoorva Manjunath
      Apoorva Manjunath

      Hello Mio Yasutake

      Thank you for the amazing blog. I'm just getting started with CAP Java. Your blog was quite beneficial to me.

      I presume this blog is about hdi-shared service plan. I was curious whether this was the same as dealing with the hdi-schema service plan.

      The schema service plan which i am working on  includes virtual tables with names like "abc/XY.ZZ". I'd like to expose these tables as services, along with some custom logic.

      I attempted to go through numerous lessons, but they all appeared to be quite old. I am able connect through JDBC and query these tables, but I'm looking for a CAP way to accomplish it.

      Could you please suggest me what should I do differently with the hdi-schema Service Plans.

      Thanks and Regards,

      Apoorva manjunath

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Apoorva Manjunath,

      Thank you for your comment.

      For classic schemas, the same approach of creating grants and synonyms are taken. I would recommend going through the following developer tutorial.

      https://developers.sap.com/tutorials/hana-cloud-access-cross-container-schema.html

      https://www.youtube.com/watch?v=ItEvMHWGak0

      Best regards,

      Mio Yasutake

      Author's profile photo Apoorva Manjunath
      Apoorva Manjunath

      Hello Mio Yasutake,

      Thank you so much for your reply.

      Regards,

      Apoorva Manjunath

       

      Author's profile photo Florine Chen
      Florine Chen

      Hello Mio Yasutake

      Thank you for your amazing blog. I'm following the Scenario 4 and deploying the database, but the deployment step failed with such error. Do you see why this is raising this error?

       

      Author's profile photo Mio Yasutake
      Mio Yasutake
      Blog Post Author

      Hi Florine Chen,

      Thank you for your comment. I haven't encountered that error before. It indicates that user "9C877...#OO" is not an object owner of the HDI container you are trying to deploy to. I wonder why the schema name is blank (""). Have you specified the schema name in mta.yaml?