使用node.js和AWS Lambda将Alexa技能连接到mysql数据库 [英] Connecting Alexa skill to mysql database using node.js and aws lambda

问题描述

我正在尝试使用AWS Lambda中的node.js将Alexa技能连接到Amazon RDS mySQL数据库.在将连接上传到lambda之前，我已经测试了该连接，并且可以正常工作，但是当我将其上传时，出现在完成请求之前退出流程"或技能响应出现问题"错误.

I am trying to connect my Alexa skill to an Amazon RDS mySQL database using node.js in AWS Lambda. I tested the connection before uploading it to lambda and it worked but when I upload it I get a 'process exited before completing request' or a 'There was a problem with the skills response' error.

'use strict';
const Alexa = require('alexa-sdk');

const APP_ID = 'amzn1.ask.skill.11069fc0-53bc-4cd0-8961-dd41e2d812f8';

var testSQL = 'SELECT weight, height from users where pin=1100';
//=========================================================================================================================================
//Database connection settings
//=========================================================================================================================================
var mysql = require('mysql');
var config = require('./config.json');
// Add connection details for dB
var pool  = mysql.createPool({
    host     : config.dbhost,
    user     : config.dbuser,
    password : config.dbpassword,
    database : config.dbname
  });

// var dbHeight, dbWeight, dbMuscle, dbExerciseOne, dbExerciseTwo, dbExerciseThree, dbExerciseFour; 
var dbResult;

function searchDB(quest) {
    pool.getConnection(function(err, connection) {
      // Use the connection
        console.log(quest);
        connection.query(quest, function (error, results, fields) {
            // And done with the connection.
            connection.release();
            // Handle error after the release.
            if (!!error) {
                console.log('error')
            }
            else {
                console.log(results[0]);
                dbResult = results[0];
                return dbResult;
                console.log(dbResult.height);
            }
            process.exit();
        });
    });
};

//searchDB(testSQL);

//=========================================================================================================================================
//TODO: The items below this comment need your attention.
//=========================================================================================================================================



const SKILL_NAME = 'My Application';
const GET_FACT_MESSAGE = "Here's your fact: ";
const HELP_MESSAGE = 'You can say tell me a space fact, or, you can say exit... What can I help you with?';
const HELP_REPROMPT = 'What can I help you with?';
const STOP_MESSAGE = 'Goodbye!';

var name, pinNumber;


//=========================================================================================================================================
//Editing anything below this line might break your skill.
//=========================================================================================================================================

const handlers = {
    'LaunchRequest': function () {
        if(Object.keys(this.attributes).length === 0){
            this.attributes.userInfo = {
                'userName': '',
                'pinNo': 0
                }
            this.emit('GetPinIntent');
        }
        else{

            name = this.attributes.userInfo.userName;
            pinNumber = this.attributes.userInfo.pinNo;
            var sql = "";
            //var result = searchDB(sql);
            //var uWeight = result.weight;
            //var uHeight = result.height;
            var speechOutput = 'Welcome ' + name + 'Please select an option: Check My BMI, Create Exercise Plan, Create Meal Plan, Update Height and Weight, Update workout status?';
            this.emit(':ask', speechOutput);
        }
    },

    'GetPinIntent': function (){
      this.emit(':ask','Welcome to my Application, as this is your first time please say your name followed by your pin. For example, my name is Jason and my pin is zero one zero one');
      //this.emit(':responseReady');
   },

    'RememberNameID': function (){
        var filledSlots = delegateSlotCollection.call(this);
        this.attributes.userInfo.userName = this.event.request.intent.slots.name.value;
        this.attributes.userInfo.pinNo = this.event.request.intent.slots.pin.value;

        var speechOutput = 'Welcome ' + this.attributes.userInfo.userName + ' we have stored your Pin Number and we will call you by name next time. Please select an option: BMI or exercise';

        this.response.speak(speechOutput);
        this.emit(':responseReady');

    },

    'CheckBMI': function(){

        var sql = 'SELECT height, weight FROM users WHERE pin=' + this.attributes.userInfo.pinNo;
        var heightWeight = searchDB(sql);
        dbHeight = parseInt(heightWeight.height);
        dbWeight = parseInt(heightWeight.weight);
        var speechOutput = bmiCalculator(dbHeight, dbWeight);
        this.emit(':ask', speechOutput);  

    },

    'AMAZON.HelpIntent': function () {
        const speechOutput = HELP_MESSAGE;
        const reprompt = HELP_REPROMPT;

        this.response.speak(speechOutput).listen(reprompt);
        this.emit(':responseReady');
    },
    'AMAZON.CancelIntent': function () {
        this.response.speak(STOP_MESSAGE);
        this.emit(':responseReady');
    },
    'AMAZON.StopIntent': function () {
        this.response.speak(STOP_MESSAGE);
        this.emit(':responseReady');
    },
    'SessionEndedRequest': function() {
        console.log('session ended!');
        this.emit(':saveState', true);
    }
};

exports.handler = function (event, context, callback) {
    var alexa = Alexa.handler(event, context, callback);
    alexa.APP_ID = APP_ID;
    alexa.dynamoDBTableName = 'fitnessDB';
    alexa.registerHandlers(handlers);
    alexa.execute();


};

function delegateSlotCollection(){
  console.log("in delegateSlotCollection");
  console.log("current dialogState: "+this.event.request.dialogState);
    if (this.event.request.dialogState === "STARTED") {
      console.log("in Beginning");
      var updatedIntent=this.event.request.intent;
      //optionally pre-fill slots: update the intent object with slot values for which
      //you have defaults, then return Dialog.Delegate with this updated intent
      // in the updatedIntent property
      this.emit(":delegate", updatedIntent);
    } else if (this.event.request.dialogState !== "COMPLETED") {
      console.log("in not completed");
      // return a Dialog.Delegate directive with no updatedIntent property.
      this.emit(":delegate");
    } else {
      console.log("in completed");
      console.log("returning: "+ JSON.stringify(this.event.request.intent));
      // Dialog is now complete and all required slots should be filled,
      // so call your normal intent handler.
      return this.event.request.intent;
    }
};
function bmiCalculator (userHeight, userWeight ) {


        var speechOutput = " "; 

        var h = userHeight/100;

        var calcBMI = 0;
        calcBMI = userWeight / (h*h);
        calcBMI = calcBMI.toFixed(2);


        if (calcBMI < 18.5) {
            speechOutput += "Based on your weight of " +weight+ " kilograms and your height of " + height + " metres, your BMI is " +calcBMI+ ". Meaning you are currently underweight.";
            speechOutput += " I would advise you to increase your calorie intake, whilst remaining active.";
            return speechOutput;

        }
        else if (calcBMI >=18.5 && calcBMI < 25){

            speechOutput += "Based on your weight of " +weight+ " kilograms and your height of" + height + " metres, your BMI is " +calcBMI+ ". Meaning you are currently at a normal weight.";
            speechOutput += " I would advise you to stay as you are but ensure you keep a healthy diet and lifestyle to avoid falling above or below this.";
            this.response.speak(speechOutput);
            return speechOutput;
        }
        else if (calcBMI >=25 && calcBMI < 29.9){

            speechOutput += "Based on your weight of " +weight+ " kilograms and your height of" + height + " metres, your BMI is " +calcBMI+ ". Meaning you are currently overweight.";
            speechOutput += " I would advise you to exercise more to fall below this range. A healthy BMI is ranged between 18.5 and 24.9";
            this.response.speak(speechOutput);
            return speechOutput;
        }
        else{

            speechOutput += "Based on your weight of " +weight+ " kilograms and your height of" + height + " metres, your BMI is " +calcBMI+ ". Meaning you are currently obese.";
            speechOutput += " I would advise you to reduce your calorie intake, eat more healthy and exercise more. A healthy BMI is ranged between 18.5 and 24.9";
            return speechOutput;

        }


 };

代码概述了我的数据库连接.我创建连接查询是一个功能，因为我需要根据上下文对数据库进行各种查询.有没有办法在exports.handler函数内创建一个仅在需要时才调用查询的函数?或是否有其他有关以这种方式连接数据库的解决方案.

The code outlines my database connection. I created the connection query as a function as I will need to make varying queries to the database based on the context. Is there a way to create a function within the exports.handler function that will only call the query when needed? Or are there any other solutions with regards to connecting to the database in such a way.

推荐答案

您遇到了多个问题，没有使用Promise或await，您的呼叫正在异步运行，并且您永远不会立即从RDS上获得关于lambda的答案称呼.您需要创建一个函数，该函数将等待答案，然后再继续其逻辑.您将遇到的另一个问题是MySQL RDS实例，如果它一直运行，则可能会出现冷启动问题.最后一件事是在AWS lambda控制台中，请确保在计算和时间上分配足够的资源来运行此功能，默认128 mb的内存和运行时间可以进行调整以提高性能

You are running into multiple issues, without using a Promise or await, your call is running async and you will never get an answer immediately from the RDS for the lambda call. you need to create a function that will wait for an answer before continuing its logic. The other issue you will run into is the MySQL RDS instance is it running constantly there may be a cold start issue. The last thing is in the AWS lambda console be sure to allocate enough resources in compute and time to run this function the default 128 mb of memory and the time to run the function can be adjusted to improve performance

这篇关于使用node.js和AWS Lambda将Alexa技能连接到mysql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！