Hello, I am doing a "custom skill - provision your own" (not used alexa-hosted-based) using ask-dsk-v2 on node.js and I want to basic query a table that is in a MySql in RDS. Lamba function and RDS are in eu-west-1 (Ireland). Lambda and ask CLI used Runtime Node.js 10.x.
My function getPrimero() is executed when done Test on Alexa Dev Console, but return empty on "ssml": "" (see below JSON Output 1).
NOTE: If executed alone function getPrimero() on local node.js (on my PC) the query works and return the data of the table of Mysql on RDS (below):
MySql query on RDS Succees!, the result: Ensalada de Bogavante
About config of RDS:
- Public accessibility: YES
- IAM DB authentication: DISABLE
About Lambda:
- Virtual Private Cloud (VPC): NO VPC
- trigger: Alexa Skill Kit (asociated ID alexa skill)
About the IAM permission:
- This step is confuse for my ( I´ve not found the exact doc to applied). I´ve applied this doc: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html
About the problem, my questions:
IAM permissions, Lambda / RDS configuration or code, what may be failing on alexa custom skill?
How can you easily test a query and connection (to discover what fail) to MySql in RDS?
Has anyone successfully managed a query to Mysql in RDS with nodejs and alexa custom skill and ask-sdk-v2 on nodejs?
Any help is welcome!
Thank you in advance!
const Alexa = require('ask-sdk-core');
const i18n = require('i18next');
var config = require('./db_config.json');
var mysql = require('mysql');
const utf8 = require('utf8');
var Promise = require('promise');
var async = require("async");
const PrimerosIntentHandler = {
canHandle(handlerInput) {
return Alexa.getRequestType(handlerInput.requestEnvelope) === 'IntentRequest'
&& Alexa.getIntentName(handlerInput.requestEnvelope) === 'PrimerosIntent';
},
async handle(handlerInput) {
var speakOutput = await getPrimero();
return handlerInput.responseBuilder
.speak(speakOutput)
.reprompt('¿Quieres saber algún plato más?')
.withShouldEndSession(false)
.getResponse();
}
};
const ErrorHandler = {
canHandle() {
return true;
},
handle(handlerInput, error) {
const speakOutput = "Perdona, no entiendo lo que dices. Por favor prueba de nuevo";
console.log(`~~~~ Error handled: ${JSON.stringify(error)}`);
return handlerInput.responseBuilder
.speak(speakOutput)
.reprompt(speakOutput)
.getResponse();
}
};
My function , just below the above code "const PrimerosIntentHandler{..}" :
function getPrimero(){
var connection = mysql.createConnection({
host : config.dbhost,
user : config.dbuser,
password : config.dbpassword,
database : config.dbname
});
connection.connect(async function(err) {
if (err) {
console.error('error connecting: ' + err.stack); // show error, if happen
return;
}
// if no error, to do the query:
//console.log('connected as id ' + connection.threadId); // ONLY TEST
var sqlQuery = "SELECT nombre FROM r_menus WHERE site=342800010 AND es_un='Primeros'";
var returnValue = "";
returnValueFinal = "";
connection.query(sqlQuery, function(error, rows) {
if (error) {
returnValue = "An error on connection.query";
} else {
returnValue = utf8.decode(rows[0]['nombre']); // also: rows[1]['nombre'] ; rows[2]['nombre']
console.log("MySql query on RDS Success!, the result: " + returnValue);
return (returnValue);
}
}); // .connection.query()
connection.end();
}); // .connection.connect()
};
Alexa JSON Output 1 ( "response":, outputSpeech":, "ssml": """ ) is empty:
{
"body": {
"version": "1.0",
"response": {
"outputSpeech": {
"type": "SSML",
"ssml": "<speak></speak>"
},
"reprompt": {
"outputSpeech": {
"type": "SSML",
"ssml": "<speak>¿Quieres saber algun plato más?</speak>"
}
},
"shouldEndSession": false,
"type": "_DEFAULT_RESPONSE"
},
"sessionAttributes": {},
"userAgent": "ask-node/2.7.0 Node/v10.17.0 sample/hello-world/v1.2"
}
}
CloudWatch output when to do Test on Lambda function (executed my code of "ErrorHandler{..}"):
{
"version": "1.0",
"response": {
"outputSpeech": {
"type": "SSML",
"ssml": "<speak>Perdona, no entiendo lo que dices. Por favor prueba de nuevo</speak>"
},
"reprompt": {
"outputSpeech": {
"type": "SSML",
"ssml": "<speak>Perdona, no entiendo lo que dices. Por favor prueba de nuevo</speak>"
}
},
"shouldEndSession": false
},
"userAgent": "ask-node/2.7.0 Node/v10.17.0 sample/hello-world/v1.2"
}
START RequestId: 21685e97-1537-4017-a8a7-734961bfbed1 Version: $LATEST
2020-01-08T13:46:34.556Z 21685e97-1537-4017-a8a7-734961bfbed1 INFO ~~~~ Error handled: {}
END RequestId: 21685e97-1537-4017-a8a7-734961bfbed1
REPORT RequestId: 21685e97-1537-4017-a8a7-734961bfbed1 Duration: 25.47 ms Billed Duration: 100 ms Memory Size: 128 MB Max Memory Used: 88 MB
