Unable to run a query with secondary database in capm

[c_chowdary]

Hi,

I have created a CAPM project which contains a primary database. Now i want to add secondary database to the same project and run raw queries specific to secondary database. 

 1. Added secondary db in package.json

2. created a custom event handler to fetch the records

this.on("getRecords",async(req) => {
  try {
    //Fetch data from primary database
    const primary_db_response = await cds.db.run(`<---Query1-->`); //Works fine
  
    //connect to secondary database
    const secondary_db = await cds.connect.to('secondary-database'); //Works fine
    let secondary_db_response = []

    //Create a transaction for secondary database
    await secondary_db.tx(async(tx) => {
      secondary_db_response = await tx.run(`<---Query2-->`) //Fails as it runs under the primary database connection
    })
  } catch (error) {
    //Handle on error
  }
})

When trying to run a Query2 (SELECT Query) with secondary db transaction (Line 14), it results in error as tx.run() is still running under the primary database connection.

tx.run(`<---Query2-->`) results below error

Error: insufficient privilege: Detailed info for this error can be found

Note: Query2 statement only contains the database table which are part of secondary database.

How to resolve above problem and make sure tx.run() runs under the secondary database connection?

[Dinu]

Perhaps you can try exactly as described in documentation:

const db1 = cds.connect.to('db')
const db2 = cds.connect.to('secondary-database')

this.on ('getRecords', async(req) => {
  await db1.run('query 1'),
  await db2.run('query 2'),
})

The issue you have is not with transactions, but with the service to which the query is dispatched.  Leave the transaction handling to the framework. 

Note 1: It does not matter whether cds.connect.to("a_service") is in the event handler or outside so long as cds is initialized before this call. Connected services are all cached in cds.services. 

Note 2: But transactions should not normally be created outside of the request processing. Transaction captures the context from the request. If you follow this guidance, you don't have to manually manage the transactions. This is neatly explained in this must watch presentation. 

[c_chowdary]

Tried with above approach but still db2.run('query 2') is failing as its running under the connection of 'db1'.

Error: insufficient privilege: Detailed info for this error can be found

Did a little bit digging into how CDS handles the connection pooling. Seems like pools are maintained per tenant. In my case  'tenant' value is 'undefined' in the context. So the second transaction is also using the same database connection as the initial one.

As there is no multi tenant in my scenario, 'tenant' value is coming as 'undefined' which results in a single pool.

const pools = new Map()

async function pool4(tenant, db) {
  if (!pools.get(tenant)) {
    const poolPromise = new Promise((resolve, reject) => {
      credentials4(tenant, db)
        .then(creds => {
          const config = _getPoolConfig()
          LOG._info && LOG.info('effective pool configuration:', config)
          const p = pool.createPool(factory4(creds, tenant), config)
          const INVALID_CREDENTIALS_WARNING = `Could not establish connection for tenant "${tenant}". Existing pool will be drained.`
          const INVALID_CREDENTIALS_ERROR = new Error(
            `Create is blocked for tenant "${tenant}" due to invalid credentials.`
          )

          // The error listener for `factoryCreateError` is registered to detect failed connection attempts.
          // If it fails due to invalid credentials, we delete the current pool from the pools map and overwrite the
          // pool factory create function.
          // The background is that the generic pool will keep trying to establish a connection by invoking the factory
          // create function until the `acquireTimeoutMillis` is reached.
          // This leads to numerous connection attempts for a single request, even when the credentials are invalid.
          // Due to the deletion in the map, subsequent requests will retrieve the credentials again.
          p.on('factoryCreateError', async function (err) {
            if (err._connectError) {
              LOG._warn && LOG.warn(INVALID_CREDENTIALS_WARNING)
              pools.delete(tenant)
              if (p._factory && p._factory.create) {
                // reject after 100 ms to not block CPU completely
                p._factory.create = () =>
                  new Promise((resolve, reject) => setTimeout(() => reject(INVALID_CREDENTIALS_ERROR), 100))
              }
              await p.drain()
              await p.clear()
            }
          })

          resolve(p)
        })
        .catch(e => {
          // delete pools entry if fetching credentials failed
          pools.delete(tenant)
          reject(e)
        })
    })

    pools.set(tenant, poolPromise)
  }

  if ('then' in pools.get(tenant)) {
    pools.set(tenant, await pools.get(tenant))
  }

  return pools.get(tenant)
}

[Ivan-Mirisola]

Hi @c_chowdary,

1) Please update your CDS installation to the latest before continuing to avoid known bugs

2) You must specify the credentials for the secondary database as automatic credentials lookup is only implemented for the primary database 'db'.

I believe this works this way because CAP doesn't expect you to use two HANA databases at the same time.

{"cds":{
  "requires": {
    "db": {
      "kind": "hana",
      "vcap": {
        "name": <primary-database>
      }
    },
    "secondary-database": {
      "kind": "hana",
      "credentials": {
        "url": "..."
         ....
      }
    }
  }
}}

However, I don't really quite get the need to access multiple Hana Database Tenants on a single application.

Usually you have a single database tenant running on BTP and CAP will actually access an HDI container.

AND, an HDI container, when properly configured with SYNONYMS and GRANTS, is able to make cross container access:

https://community.sap.com/t5/technology-blogs-by-sap/working-with-cross-hdi-container-access-scenari...

https://community.sap.com/t5/technology-blogs-by-sap/cross-hdi-container-access-using-user-provided-...

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

Best regards,
Ivan 

[Dinu]

Try the new @cap-js/hana. It does not have the issue of shared pool between services. But, it is still not released for production.

[Ivan-Mirisola]

.

[c_chowdary]

1. As per capm documentation, Service instances are cached in cds.services, thus subsequent connects with the same service name return the initially connected one.

2. cds.connect.to('db') needs to be awaited until the promise is fulfilled. So it needs to be inside an async function.

[c_chowdary]

.