如何使用node.js MySql从MySQL DB获取结果并将其发送回API.ai - DialogFlow

Question

我在从MySql数据库检索结果并将结果发送到API.ai时遇到问题。具体的问题是如何等待结果可用，然后将Json对象中的结果发送回API.ai

这就是我所拥有的：

在webhook或服务中，在收到Json请求后，我调用一个方法：

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));
    });

}

它调用执行数据库调用的方法callDB()：

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);

    }

}
);

}

我在API.ai中得到了Json响应，如下所示：

{
  "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"
}

我只从数据库中得到了错误消息，而没有得到结果。我读到它也可以使用回调来完成，但我还不能弄清楚。我可以看到数据库连接正在工作，因为连接的日志显示了连接尝试。

任何帮助都将不胜感激。谢谢。

Answer 1

通过声明var mysql = require(' mysql ')；as const mysql= require('mysql')；而不是在函数内部，但在exports.myfunction声明之前。使用node.js MySQL从MySql DB获取结果并将其发送回API.ai的工作示例代码如下：

    '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);

        }

    }
    );
}