Multple SQL queries in Node with oracledb

Question

I'm new to Node and am having problems reading from Oracle.

I have the basic examples all set up and can issue basic queries, and process the results etc..

The problem I'm having is that I need to;

1. Execute one query (Q1)
2. For each item in the results of Q1 I need to execute a second query (Q2)
3. I need to combine the results of Q1 and Q2s into an array to return as a promise

I am struggling to find an example where I can perform #2 - call the same query multiple times for each item returned from Q1, using the same connection which was used for Q1.

My code is below - I first perform a read, then iterate through the results storing connection.execute objects which I then run via the Promise.all line - the result of which I just output as I want to get this working before I code the logic to combine the results of Q1 and Q2.

When I run this via mocha, the results of don't contain any data - I see the column headings but no data.

So what am I missing here?

// placeholder for the connection
let conn;

// return case list array
var caseList = [];
var queryList = [];

return new Promise((resolve, reject) => {

    // retrieve connection
    oracledb.getConnection({
            user: dbconfig.user,
            password: dbconfig.password,
            connectString: dbconfig.connectString
        }) // the connection is returned as a promise
        .then(connection => {

            console.log('Connected to the DB!');

            // assign connection
            conn = connection;

            // execute statement
            return connection.execute(
                `select caseid, casereference, startdate from caseheader inner join orgobjectlink on caseheader.ownerorgobjectlinkid = orgobjectlink.orgobjectlinkid where orgobjectlink.username = :username`,
                [params.username], {
                    outFormat: oracledb.OBJECT // set the output format to be object
                }
            );
        })
        .then(result => {

            // iterate around rows
            result.rows.forEach(row => {

                var caseObj = {
                    caseID: row.CASEID,
                    reference: row.CASEREFERENCE,
                    dateAssigned: moment(row.STARTDATE).format('YYYY-MM-DD'),
                    username: params.username,
                }
                caseList.push(caseObj);

                console.log(caseObj.caseID)
                queryList.push(conn.execute(`select concernroleid, concernrolename from concernrole inner join caseparticipantrole on concernrole.concernroleid = caseparticipantrole.participantroleid where caseparticipantrole.caseid = :caseID and (caseparticipantrole.typecode = 'PRI' or caseparticipantrole.typecode = 'MEM')`,
                    [caseObj.caseID], {
                        outFormat: oracledb.OBJECT
                    }));

            });

            // build up queries
            return Promise.all(queryList).then(results => {
                console.log(results);

                Promise.resolve(results);
            }, err => {
                console.log(err);
            });
        }).then({
            if(conn){
                console.log("Closing DB connection");
                conn.close();

            }
        }).catch(err => {
            console.log('Error', err);
        });

});

Answer 1

One problem is the Promise.all().then... function doesn't return anything (and doesn't need the additional resolve()). The way to get this sorted is build small, testable, promise returning functions, and test them individually.

Starting simply, write a mocha test to connect to the database...

function connect() {
    return oracledb.getConnection({
        user: dbconfig.user,
        password: dbconfig.password,
        connectString: dbconfig.connectString
    });
}

Here's one that can run a command on the db. Test this with a simple query that you know will return some results.

function executeCmd(connection, cmd, params) {
    return connection.execute(cmd, params, { outFormat: oracledb.OBJECT });
}

With just these two (and one more) we can outline a simple function that does the job: connect to the database, run a select, process each result asynchronously, then disconnect.

function connectAndQuery(username) {
    let connection;
    return connect().then(result => {
        connection = result;
        let cmd = `select caseid, casereference, startdate from caseheader inner join orgobjectlink on caseheader.ownerorgobjectlinkid = orgobjectlink.orgobjectlinkid where orgobjectlink.username = :username`;
        return executeCmd(connection, cmd, [username]);
    }).then(result => {
        let promises = result.rows.map(row => processCaseRow(connection, row, username));
        return Promise.all(promises);
    }).then(result => {
        // result should be an array of caseObj's 
        return connection.close().then(() => result);
    });
}

The last thing to build and test is a promise-returning function which processes a row from the main function above.

I had to take some liberty with this, but I think the objective is -- given a row representing a "case" -- build a case object, including a collection of "concernedRoles" that can be queried with the caseID. (that last bit was my idea, but you can build a separate collection if you like)

// return a promise that resolves to an object with the following properties...
// caseID, reference, dateAssigned, username, concernedRoles
// get concernedRoles by querying the db
function processCaseRow(connection, row, username) {
    var caseObj = {
        caseID: row.CASEID,
        reference: row.CASEREFERENCE,
        dateAssigned: moment(row.STARTDATE).format('YYYY-MM-DD'),
        username: username
    }
    let cmd = `select concernroleid, concernrolename from concernrole inner join caseparticipantrole on concernrole.concernroleid = caseparticipantrole.participantroleid where caseparticipantrole.caseid = :caseID and (caseparticipantrole.typecode = 'PRI' or caseparticipantrole.typecode = 'MEM')`;
    return executeCmd(connection, cmd, row.CASEID).then(result => {
        caseObj.concernedRole = result
        return caseObj
    })
}