How to get results from MySql DB using node.js MyS

Question

I am having issues retrieving and sending results from a MySql database to API.ai. The concrete question is how to wait for the results to be available, and then send the results in the Json object back to API.ai

This is what I have:

In the webhook or service, after receiving the Json request, I call a method:

if (action === 'get.data') {
    // Call the callDBJokes method
    callDB().then((output) => {
        // Return the results to API.AI
        res.setHeader('Content-Type', 'application/json');
        res.send(JSON.stringify(output));
    }).catch((error) => {
        // If there is an error let the user know
        res.setHeader('Content-Type', 'application/json');
        res.send(JSON.stringify(error));
    });

}

which calls the method callDB() where the database call is executed:

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

    try {

        var connection = mysql.createConnection({
            host: "127.0.0.1",
            user: "root",
            password: "x",
            database: 'y'
        });

        connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
            if (!error) {

                let response = "The solution is: " + results[0].solution;
                response = response.toString();
                let output = {'speech': response, 'displayText': response};
                console.log(output);
                resolve(output);

            } else {

                let output = {'speech': 'Error. Query Failed.', 'displayText': 'Error. Query Failed.'};
                console.log(output);
                reject(output);

            }
        });
        connection.end();

    } catch (err) {
        let output = {'speech': 'try-cacth block error', 'displayText': 'try-cacth block error'};
        console.log(output);
        reject(output);

    }

}
);

}

I get a Json response in API.ai like:

{
  "id": "5daf182b-009f-4c11-a654-f2c65caa415e",
  "timestamp": "2017-08-29T07:24:39.709Z",
  "lang": "en",
  "result": {
    "source": "agent",
    "resolvedQuery": "get data",
    "action": "get.data",
    "actionIncomplete": false,
    "parameters": {},
    "contexts": [
      {
        "name": "location",
        "parameters": {
          "date": "",
          "geo-city": "Perth",
          "date.original": "",
          "geo-city.original": "perth"
        },
        "lifespan": 2
      },
      {
        "name": "smalltalkagentgeneral-followup",
        "parameters": {},
        "lifespan": 2
      }
    ],
    "metadata": {
      "intentId": "4043ad70-289f-441c-9381-e82fdd9a9985",
      "webhookUsed": "true",
      "webhookForSlotFillingUsed": "false",
      "webhookResponseTime": 387,
      "intentName": "smalltalk.agent.general"
    },
    **"fulfillment": {
      "speech": "error",
      "displayText": "error",
      "messages": [
        {
          "type": 0,
          "speech": "error"**
        }
      ]
    },
    "score": 1
  },
  **"status": {
    "code": 200,
    "errorType": "success"**
  },
  "sessionId": "c326c828-aa47-490c-9ca0-37827a4e348a"
}

I am getting only the error message but not the result from the database. I read that it could be done using callbacks as well, but I could not figure it out yet. I can see that the database connection is working, because the logs of the connections shows the connection attempts.

Any help will be appreciated. Thanks.

Answer 1

Solved by declaring the var mysql = require('mysql'); as const mysql = require('mysql'); not inside the function, but before the exports.myfunction declaration. Working example code to get results from MySql DB using node.js MySQL, and send them back to API.ai is as follows:

    'use strict';
    const mysql = require('mysql');

    exports.her_goes_your_function_name = (req, res) => { //add your function name
        //Determine the required action
        let action = req.body.result['action'];

    if (action === 'get.data') {

        // Call the callDBJokes method
        callDB().then((output) => {
            // Return the results of the weather API to API.AI
            res.setHeader('Content-Type', 'application/json');
            res.send(JSON.stringify(output));
        }).catch((error) => {
            // If there is an error let the user know
            res.setHeader('Content-Type', 'application/json');
            res.send(JSON.stringify(error));
        });

    }
    };

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

        try {

            var connection = mysql.createConnection({
                host: "127.0.0.1",
                user: "your_user",
                password: "your_pass",
                database: "your_DB"
            });

            connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
                if (!error) {

                    let response = "The solution is: " + results[0].solution;
                    response = response.toString();
                    let output = {'speech': response, 'displayText': response};
                    console.log(output);
                    resolve(output);

                } else {

                    let output = {'speech': 'Error. Query Failed.', 'displayText': 'Error. Query Failed.'};
                    console.log(output);
                    reject(output);

                }
            });
            connection.end();

        } catch (err) {
            let output = {'speech': 'try-cacth block error', 'displayText': 'try-cacth block error'};
            console.log(output);
            reject(output);

        }

    }
    );
}