AdonisJS unable to return response within a function (SAP HANA)

Question

I am using SAP HANA CLIENT for NodeJS in my AdonisJS project. I am unable to return the response as json from the the function that connects to the database. Here is the code

The controller method validateInvoice is first called

async validateInvoice({request, response}) {
  const req = request.all()
  const inv_num = req.invoice_num;
  const data = await this.fetchInvStatus(inv_num);
  return response.json({success: true, data: data});
}

This in turn calls the fetchInvStatus method which actually connects to HANA DB

var conn = hana.createConnection();
var conn_params = {
  serverNode: '127.0.0.1:30015',
  uid: 'CUST_USER_ROLE_ADMIN',
  pwd: 'Welcome@1234',
  database: 'DED'
};

conn.connect(conn_params, (err) => {
  if(err) {
    return err;
  }
  conn.exec("Select * FROM SAPDED.YACSF_RRHD where INVOICE_NUMBER = ?", ['BOMT000005'],  (err, result) => {
    if (err) {
      return err;
    }
    console.log(result);
    return result;
  })
});

In the console I'm able to see the result but this result is not being passed to the validateInvoice method so that the API could return the response.

The line in the first method response.json() is executed even before the data from DB is returned. How can I overcome this problem? I've tried adding return statement to conn.connect and conn.exec but nothing helps!

Answer 1

You have to return promise in the fetchInvStatus method.

function fetchInvStatus() {
 return new Promise(resolve, reject) {
  var conn = hana.createConnection();
  var conn_params = {
    serverNode: '127.0.0.1:30015',
    uid: 'CUST_USER_ROLE_ADMIN',
    pwd: 'Welcome@1234',
    database: 'DED'
  };

  conn.connect(conn_params, (err) => {
    if(err) {
      reject(err);
    }

    conn.exec("Select * FROM SAPDED.YACSF_RRHD where INVOICE_NUMBER = ?", ['BOMT000005'],  (err, result) => {
      if (err) {
        reject(err);
      }
      console.log(result);
      resolve(result);
     })
    });
   }  
}