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?

[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]

.