如何使用node.js MySQL从MySql DB获取结果并将其发送回API.ai-DialogFlow [英] How to get results from MySql DB using node.js MySQL and send them back to API.ai - DialogFlow
问题描述
我在从MySql数据库检索结果并将结果发送到API.ai时遇到问题.具体问题是如何等待结果可用，然后将Json对象中的结果发送回API.ai
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
这就是我所拥有的:
在Webhook或服务中，收到Json请求后，我调用一个方法:
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));
});
}
调用执行数据库调用的方法callDB():
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);
}
}
);
}
我在API.ai中收到Json响应，例如:
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.
推荐答案
通过声明var mysql = require('mysql');来解决.作为const mysql = require('mysql');不是在函数内部，而是在exports.myfunction声明之前.下面的工作示例代码通过使用node.js MySQL从MySql DB获取结果，并将其发送回API.ai，如下所示:
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);
}
}
);
}
这篇关于如何使用node.js MySQL从MySql DB获取结果并将其发送回API.ai-DialogFlow的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
