Is there a way to get Node.JS to execute my MySQL functions first before it executes my Dialogflow output function?

Question

I am setting up a Dialogflow chatbot that connects to a local MySQL database and it executes the statement the user is supposed to see before it pulls the information that is necessary to fill it and I am not sure how to address this issue.

I have attempted to make a loop that would force the execution of the MySQL data retrieval statements but that didn't work. I attempted to just execute the agent.add() components within the MySQL component but that wont execute/display on Dialogflow for some reason. I have attempted to use the Node.JS async instructions but my code would simply brick.

const express = require('express')
const { WebhookClient } = require('dialogflow-fulfillment')
const app = express()
const mysql = require('mysql');
const connection = mysql.createConnection({
    host: 'localhost',
    user: '',
    password: '',
    database: '',
    port: '3306'
});

var someVar;
app.get('/', (req, res) => res.send('online'))
app.post('/dialogflow', express.json(), (req, res) => {
    const agent = new WebhookClient({ request: req, response: res })

    function setValue(value) {
        someVar = value;
        console.log(someVar);
    }

    function database(mnumber) {
        connection.query('SELECT name FROM data WHERE mnum = ' +    connection.escape(mnumber), function(err, rows){
            if(err) {
                throw err;
            } else {
                setValue(rows[0].name);
                console.log(String(someVar));
            }
        });
    }

    function welcome () {
        agent.add('Welcome to my agent! Is it working?')
    }

    function userinfo () {
        const mnumber = agent.parameters.mnumber;
        database(mnumber);
        var testVar = String(someVar);
        agent.add("Hello " + testVar + "!");
        console.log("Hello " + testVar + "!");
    }

    let intentMap = new Map()
    intentMap.set('Default Welcome Intent', welcome)
    intentMap.set('userInformation', userinfo)
    agent.handleRequest(intentMap)
})

app.listen(process.env.PORT || 8080)

The expected output would be:

Hello name!

But in reality the output is:

Hello undefined!

I confirmed that it actually gets the information through console as I ask it to put out the name in the MySQL reading function and it confirms that it reads it non-sequentially as it will show:

Hello undefined! name name

Rather than:

name name Hello name!

Possible duplicate of [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Sterling Archer, Jun 21 '19 at 15:13

score 2 · Accepted Answer · answered Jun 21 '19 at 15:18

First, return a promise from your database function:

function database(mnumber) {
    return new Promise((resolve, reject)=>{
        connection.query('SELECT name FROM data WHERE mnum = '+connection.escape(mnumber), function(err, rows){
            if(err) {
                reject(err);
            } else {
                setValue(rows[0].name);
                console.log(String(someVar));
                resolve();
            }
        });
    });
}

Then make your userinfo function async and have it wait for the database to finish before continuing.

async function userinfo () {
    const mnumber = agent.parameters.mnumber;
    await database(mnumber);
    var testVar = String(someVar);
    agent.add("Hello " + testVar + "!");
    console.log("Hello " + testVar + "!");
}

Is there a way to get Node.JS to execute my MySQL functions first before it executes my Dialogflow output function?

1 Answers1