javascript - 如何使用 MySql DB 获取结果并将其发送回 API.ai

Question

我需要一些帮助来弄清楚 SQL 查询的语法，同时通过 api.ai webhook 使用它们并连接到 Google 云 Mysql 数据库。尽管查询正在运行，但“请求超时”

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

exports.name = (req, res) => { 

    let action = req.body.result['action'];

if (action === 'apple') {


    callDB().then((output) => {

        res.setHeader('Content-Type', 'application/json');
        res.send(JSON.stringify(output));
    }).catch((error) => {

        res.setHeader('Content-Type', 'application/json');
        res.send(JSON.stringify(error));
    });

}
};

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

    try {

        var connection = mysql.createConnection({
            host: "<host>",
            user: "<user>",
            password: "<pass>",
            database: "<DB>"
        });

        connection.query("SELECT description AS solution FROM mtable WHERE title LIKE '%Breakfast%'", function (error, results, fields) {
            if (!error) {

                let response = "The result 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 Failed4.'};
                console.log(output);
                reject(output);

            }
        });
        connection.end();

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

    }

}
);
}

如果我用这个替换查询，它会起作用:

“从 mtable WHERE id LIKE 1001 中选择描述 AS 解决方案”

id 是只有 id 的列名

title 是一个列名称，带有诸如早餐包装等标题。

这是 webhook json 上显示的错误的一部分:

"metadata": {
  "intentId": "<id>",
  "webhookUsed": "true",
  "webhookForSlotFillingUsed": "false",
  "webhookResponseTime": 5000,

 "status": {
 "code": 206,
 "errorType": "partial_content",
 "errorDetails": "Webhook call failed. Error: Request timeout.",
 "webhookTimedOut": true
  },

我引用了以下线程的代码， How to get results from MySql DB using node.js MySQL and send them back to API.ai

Answer 1

您的查询字符串声明中似乎存在拼写错误(“%Breakfast%”附近):

connection.query('SELECT description AS solution FROM mtable WHERE title LIKE '%Breakfast%'', function (error, results, fields) {

将查询字符串分配给变量时，“SELECT description AS Solution FROM mtable WHERE title LIKE '%Breakfast%'” 被解释为数字(由于 % 运算符)。

修复单引号有什么帮助吗？

connection.query("SELECT description AS solution FROM mtable WHERE title LIKE '%Breakfast%'", function (error, results, fields) {