Skip to Content
0
Jan 26, 2023 at 11:05 AM

Error running a SAP CAP Nodejs service with PostgreSQL db from the SAP BTP Job Scheduler

587 Views Last edit Jan 26, 2023 at 12:54 PM 6 rev

We currently have a SAP CAP project with a HANA DB deployed in production where we have several services scheduled via the BTP Job Scheduler. Via the job scheduler the services are called and they read OData services from the S/4 backend system and update data in a SAP HANA DB on BTP. Several Fiori apps available for end users read those SAP HANA BTP tables .

We are now trying to do a PoC to use a PostgreSQL db instead of a SAP HANA DB but we face several issues. This is one of those issues.

What we did was changing the package.json and the mta.yaml files to make it work with PostgreSQL. The deployment in BTP is successful. The database is being generated and we already exported data from the HANA DB to CSV files and imported them in the PostgreSQL db. Also if we open an Fiori app via the BTP launchpad data gets successfully read from the PostgreSQL db via OData calls.

When we run a job from the SAP BTP Job Scheduler the service gets triggered. We can see that in the BTP logs. However it dumps and we cannot find out the reason why. We don’t have replace statements in the code.

Error message:

2023-01-26T07:32:31.235+0000 [APP/PROC/WEB/0] OUT [cds] - POST /get-segment/execute
2023-01-26T07:32:31.293+0000 [APP/PROC/WEB/0] OUT {"component_type":"application","component_id":"0bfa9ced-e3da-45d9-8210-0bb50f3152ba","component_name":"pal2-service-srv","component_instance":0,"layer":"[NODEJS]","organization_name":"REMOVED-Dev-b3902382-0c03-442b-9b45-3c746f4ff5b9","organization_id":"REMOVED","space_name":"SCM-PAL2-Dev","space_id":"378af2dc-1bdb-459e-bf56-fbc480cf6fe0","container_id":"10.36.131.12","logger":"nodejs-logger","source_instance":0,"written_at":"2023-01-26T07:32:31.293Z","written_ts":1674718351293843700,"level":"info","msg":"Job started Thu Jan 26 2023 07:32:31 GMT+0000 (Coordinated Universal Time)","type":"log"}
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR [INTERNAL ERROR] TypeError: name.replace is not a function
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at ensureNoDraftsSuffix (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/utils/draft.js:29:43)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at _entityFromINSERT (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/insert.js:58:24)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at hasDeepInsert (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/composition/insert.js:77:20)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase._insert (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/db/query/insert.js:9:7)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.module.exports [as _CREATE] (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/db/generic/create.js:28:32)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at next (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:74:36)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at PostgresDatabase.handle (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:78:6)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at processTicksAndRejections (node:internal/process/task_queues:96:5)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at async ApplicationService.<anonymous> (/home/vcap/app/node_modules/@sap/cds/libx/_runtime/common/generic/crud.js:103:16)
2023-01-26T07:32:31.318+0000 [APP/PROC/WEB/0] ERR at async next (/home/vcap/app/node_modules/@sap/cds/lib/serve/Service-dispatch.js:74:17)
2023-01-26T07:32:31.334+0000 [APP/PROC/WEB/0] ERR npm notice
2023-01-26T07:32:31.334+0000 [APP/PROC/WEB/0] ERR npm notice New major version of npm available! 8.19.2 -> 9.4.0
2023-01-26T07:32:31.334+0000 [APP/PROC/WEB/0] ERR npm notice Changelog: <https://github.com/npm/cli/releases/tag/v9.4.0>
2023-01-26T07:32:31.334+0000 [APP/PROC/WEB/0] ERR npm notice Run `npm install -g npm@9.4.0` to update!
2023-01-26T07:32:31.334+0000 [APP/PROC/WEB/0] ERR npm notice
2023-01-26T07:32:31.351+0000 [APP/PROC/WEB/0] OUT Exit status 1
2023-01-26T07:32:36.717+0000 [CELL/0] OUT Cell 4f80242f-969f-48f2-a832-7bdbed3edd01 stopping instance c554368f-9357-451b-50f6-12ea<br>

Piece of code where it maybe fails. 1st INSERT statement

async info(logLevel, msg) {

        if (this.logLevel >= logLevel) {
            var tx = this.db.tx();
            applog.info(msg); // application log info message
            try {
                var generatedAt = new Date();
                await tx.run(INSERT.into(this.logging)
                    .entries([{
                        jobID: this.jobID, object: this.object, subobject: this.subobject, externalID: this.externalID,
                        generatedAt: generatedAt, msgtype: 'I', message: msg
                    }]));
                    await tx.commit(); //(no tx = db.tx() after commit needed);
               
            } catch (e) {
                await tx.rollback(e);
                console.log(msg);
            }
        }
    }<br>

Package.json

{
  "name": "pal2-service",
  "version": "1.0.0",
  "description": "A simple CAP project.",
  "repository": "<Add your repository here>",
  "license": "UNLICENSED",
  "private": true,
  "engines": {
    "node": "^12.0.0 || ^14.0.0 || ^16.0.0"
  },
  "dependencies": {
    "@routes/index": "^1.2.1",
    "@sap/cds": "5.5.5",
    "@sap/cds-odata-v2-adapter-proxy": "^1.8.5",
    "@sap/jobs-client": "^1.7.30",
    "@sap/textbundle": "^4.0.0",
    "@sap/xsenv": "^3.3.2",
    "@sap/xssec": "^3.2.11",
    "cds-pg": "0.1.30",
    "cds-dbm": "^0.0.36",
    "cf-nodejs-logging-support": "^6.13.1",
    "cfenv": "^1.2.4",
    "cookie-parser": "~1.4.4",
    "debug": "~2.6.9",
    "ejs": "~2.6.1",
    "express": "^4.18.1",
    "hdb": "^0.18.3",
    "helmet": "^4.6.0",
    "http-errors": "~1.6.3",
    "jwt-decode": "^3.1.2",
    "morgan": "~1.9.1",
    "passport": "^0.4.1",
    "request": "*",
    "pg": "^8.8.0"
  },
  "devDependencies": {
    "npm-run-all": "^4.1.5",
    "@sap/ux-specification": "^1.100.4"
  },
  "scripts": {
    "start": "cds run",
    "start:deploy": "./deploy.sh",
    "deploy:pg": "npx cds-dbm deploy --create-db",
    "deploy:pg:load": "npx cds-dbm deploy --create-db --load-via delta",
    "deploy:pg:data:delta": "npx cds-dbm load --via delta",
    "deploy:pg:data:full": "npx cds-dbm load --via full",
    "build:cds": "npx -p @sap/cds-dk cds build --production",
    "build:cf": "npm run build:cds"
  },
  "eslintConfig": {
    "extends": "eslint:recommended",
    "env": {
      "es2020": true,
      "node": true,
      "jest": true,
      "mocha": true
    },
    "globals": {
      "SELECT": true,
      "INSERT": true,
      "UPDATE": true,
      "DELETE": true,
      "CREATE": true,
      "DROP": true,
      "CDL": true,
      "CQL": true,
      "CXL": true,
      "cds": true
    },
    "rules": {
      "no-console": "off",
      "require-atomic-updates": "off"
    }
  },
  "cds": 
  {
    "requires": {
      "db": {
        "kind": "database"
      },
      "database": {
        "dialect": "plain",
        "impl": "cds-pg",
        "model": [
          "srv"
        ]
      },
      "xsuaa": {
        "kind": "xsuaa"
      },

      "md_c_product_article_hier": {
        "kind": "odata",
        "model": "srv/external/md_c_product_article_hier",
        "credentials": {
          "destination": "S4_Backend",
          "path": "/sap/opu/odata/sap/MD_C_PRODUCT_ARTICLE_HIER_SRV",
          "requestTimeout": 300000
        }
      },
      "api_product": {
        "kind": "odata",
        "model": "srv/external/api_product",
        "credentials": {
          "destination": "S4_Backend",
          "path": "/sap/opu/odata/sap/API_PRODUCT_SRV",
          "requestTimeout": 300000
        }
      },
      "api_rfm_assortment_module.sap.gateway.srvd_a2x.api_rfm_assortment_module.v0001": {
        "kind": "odata",
        "model": "srv/external/api_rfm_assortment_module",
        "credentials": {
          "destination": "S4_Backend",
          "path": "/sap/opu/odata4/sap/api_rfm_assortment_module/srvd_a2x/sap/api_rfm_assortment_module/0001",
          "requestTimeout": 300000
        }
      },
      "z_get_listing": {
        "kind": "odata",
        "model": "srv/external/z_get_listing",
        "credentials": {
          "destination": "S4_Backend",
          "path": "/sap/opu/odata/sap/Z_GET_LISTING_SRV",
          "requestTimeout": 300000
        }
      },
      "ZC_SC_IntradayForecast": {
        "kind": "odata",
        "model": "srv/external/ZC_SC_IntradayForecast",
        "credentials": {
          "destination": "CAR_ABAP_Backend",
          "path": "/sap/opu/odata/sap/ZC_SC_INTRADAYFORECAST_CDS",
          "requestTimeout": 300000
        }
      },
      "ZC_ALL_IntradayForecast": {
        "kind": "odata",
        "model": "srv/external/ZC_ALL_INTRADAYFORECAST",
        "credentials": {
          "destination": "CAR_ABAP_Backend",
          "path": "/sap/opu/odata/sap/ZC_ALL_INTRADAYFORECAST_CDS",
          "requestTimeout": 300000
        }
      },
      "offer_management_v2": {
        "kind": "odata",
        "model": "srv/external/offer_management_v2",
        "credentials": {
          "destination": "CAR_ABAP_Backend",
          "path": "/sap/opu/odata/DMF/OFFER_MANAGEMENT_V2_SRV",
          "requestTimeout": 300000
        }
      },
      "ExtAssortment": {
          "kind": "odata",
          "credentials": {
            "destination": "fmcocapassortmentservice",
            "path": "/assortment"
          }
      }
    },
    "migrations": {
      "db": {
        "schema": {
          "default": "public",
          "clone": "_cdsdbm_clone",
          "reference": "_cdsdbm_ref"
        },
        "deploy": {
          "tmpFile": "tmp/_autodeploy.json",
          "undeployFile": "db/undeploy.json"
        }
      }
    },
    "build": {
      "target": "gen",
      "tasks": [
        {
          "for": "node-cf",
          "src": "srv",
          "options": {
            "model": [
              "db",
              "srv",
              "app"
            ]
          }
        },
        {
          "use": "cds-dbm/dist/build/postgres-cf",
          "for": "postgres-cf",
          "src": "db",
          "options": {
            "deployCmd": "npx cds-dbm deploy --load-via delta --auto-undeploy"
          }
        }
      ]
    }
  },
  "sapux": [
    "app/maintaincharge",
    "app/excludebatches",
    "app/productadvice",
    "app/maintainsafetystock"
  ]
}<br>

Mta.yaml

_schema-version: "3.1"
ID: pal2-service
description: A simple CAP project.
version: 1.0.0
modules:
- name: pal2-service-srv
  type: nodejs
  path: gen/srv
  requires:
  - name: pal2-service-db
  - name: pal2-service-xsuaa
  - name: pal2-service-destination
  - name: pal2-service-connectivity
  - name: pal2-job-scheduler
  - name: application_logger
  provides:
  - name: srv-api
    properties:
      srv-url: ${default-url}
  parameters:
    memory: 1536MB
    buildpack: nodejs_buildpack
- name: pal2-service-db-deployer
  type: custom
  path: gen/db
  requires:
  - name: pal2-service-db
  - name: pal2-service-xsuaa
  parameters:
    buildpacks:
    - https://github.com/cloudfoundry/apt-buildpack#v0.2.10
    - nodejs_buildpack
    disk-quota: 3GB
    memory: 512MB
    no-route: true
    no-start: true
    tasks:
    - command: chmod 755 deploy.sh && ./deploy.sh
      disk-quota: 3GB
      memory: 512MB
      name: deploy_to_postgresql
  build-parameters:
    ignore:
    - node_modules/
- name: pal2-service-postgreswebinterface
  type: application
  requires:
  - name: pal2-service-db
  parameters:
    disk-quota: 256MB
    docker:
      image: dockette/adminer:pgsql
    instances: 1
    memory: 128MB
    no-route: false
    no-start: false
  build-parameters:
    no-source: true    
- name: pal2-service_ui_deployer
  type: com.sap.application.content
  path: .
  requires:
  - name: pal2-service-destination
  - name: pal2-service-html5-repo-host
    parameters:
      content-target: true
  build-parameters:
    build-result: resources
    requires:
    - artifacts:
      - comjumboscmmaintaincharge.zip
      name: comjumboscmmaintaincharge
      target-path: resources/
    - artifacts:
      - comjumboscmmaintainsafetystock.zip
      name: comjumboscmmaintainsafetystock
      target-path: resources/
    - artifacts:
      - comjumboscmexcludebatches.zip
      name: comjumboscmexcludebatches
      target-path: resources/
    - artifacts:
      - comjumboscmproductadvice.zip
      name: comjumboscmproductadvice
      target-path: resources/
- name: comjumboscmmaintaincharge
  type: html5
  path: app/maintaincharge
  build-parameters:
    build-result: dist
    builder: custom
    commands:
    - npm install
    - npm run build:cf
    supported-platforms: []
- name: comjumboscmmaintainsafetystock
  type: html5
  path: app/maintainsafetystock
  build-parameters:
    build-result: dist
    builder: custom
    commands:
    - npm install
    - npm run build:cf
    supported-platforms: []
- name: comjumboscmexcludebatches
  type: html5
  path: app/excludebatches
  build-parameters:
    build-result: dist
    builder: custom
    commands:
    - npm install
    - npm run build:cf
    supported-platforms: []
- name: comjumboscmproductadvice
  type: html5
  path: app/productadvice
  build-parameters:
    build-result: dist
    builder: custom
    commands:
    - npm install
    - npm run build:cf
    supported-platforms: []
- name: pal2-service-destination-content
  type: com.sap.application.content
  requires:
  - name: pal2-service-destination
    parameters:
      content-target: true
  - name: pal2-service-html5-repo-host
    parameters:
      service-key:
        name: pal2-service-html5-repo-host-key
  - name: pal2-service-xsuaa
    parameters:
      service-key:
        name: pal2-service-xsuaa-key
  parameters:
    content:
      instance:
        destinations:
        - Name: pal2_service_pal2_service_html5_repo_host
          ServiceInstanceName: pal2-service-html5-app-host
          ServiceKeyName: pal2-service-html5-repo-host-key
          sap.cloud.service: pal2-service
        - Authentication: OAuth2UserTokenExchange
          Name: pal2_service_pal2_service_xsuaa
          ServiceInstanceName: pal2-service-xsuaa
          ServiceKeyName: pal2-service-xsuaa-key
          sap.cloud.service: pal2-service
        existing_destinations_policy: ignore
  build-parameters:
    no-source: true
resources:
- name: pal2-service-db
  type: org.cloudfoundry.managed-service
  parameters:
    path: ./pg-options.json
    service: postgresql-db
    service-keys:
    - name: pal2-key
    service-plan: free
    service-tags:
    - plain
    skip-service-updates:
      parameters: true
- name: pal2-job-scheduler
  type: org.cloudfoundry.managed-service
  parameters:
    config:
      enable-xsuaa-support: true
    service: jobscheduler
    service-name: pal2-job-scheduler
    service-plan: standard    
- name: pal2-service-xsuaa
  type: org.cloudfoundry.managed-service
  parameters:
    config: null
    path: ./xs-security.json
    service: xsuaa
    service-plan: application
    shared: true
- name: pal2-service-connectivity
  type: org.cloudfoundry.managed-service
  parameters:
    service: connectivity
    service-plan: lite
- name: pal2-service-destination
  type: org.cloudfoundry.managed-service
  requires:
      - name: srv-api
  parameters:
    config:
      HTML5Runtime_enabled: true
      init_data:
        instance:
          destinations:
           - Name: pal2-service-srv
             URL: ~{srv-api/srv-url}
             Authentication: NoAuthentication
             Type: HTTP
             ProxyType: Internet
             HTML5.ForwardAuthToken: true
             HTML5.DynamicDestination: true
          existing_destinations_policy: update
      version: 1.0.0
    service: destination
    service-plan: lite
- name: pal2-service-html5-repo-host
  type: org.cloudfoundry.managed-service
  parameters:
    service: html5-apps-repo
    service-name: pal2-service-html5-app-host
    service-plan: app-host  
- name: application_logger
  type: org.cloudfoundry.managed-service
  parameters:
    service-plan: standard
    service: application-logs    
parameters:
  deploy_mode: html5-repo
  enable-parallel-deployments: true
build-parameters:
  before-all:
  - builder: custom
    commands:
    - npm install --production
    - npx -p @sap/cds-dk cds build --production<br>