Sunday 19 March 2017

Alexa Called Lambda Function that Queries MySQL database loops forever

When I try running the following lambda function I get the appropriate value added to my MySQL database, however, the Alexa skill outputs an error and trying to delete the value from my database shows that it immediately gets added again forever until I change the name of the table (then I guess it errors and eventually stops).

I have the MySQL bit firing when I call any Alexa Intent from my skill. I don't know callbacks and Alexa contexts that well, so I imagine I'm doing something incorrect handling those. It seems like the query command is getting called many times, which I don't understand since at the end of every callback "route" I have either context.succeeds or context.fails, which should end my Lambda function and give Alexa what it needs to continue with the output needed by the skill.

Here's my current code:

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: '-',
    user: '-',
    password: '-',
    database: '-'
});

exports.handler = (event, context) => {
    try {

        if (event.session.new) {
            // New Session
            console.log("NEW SESSION");
        }


        switch (event.request.type) {

            case "LaunchRequest":
                // Launch Request
                console.log(`LAUNCH REQUEST`);
                context.succeed(
                    generateResponse({},
                        buildSpeechletResponse("Welcome to an Alexa Skill, this is running on a deployed lamda function", true)
                    )
                );
                break;

            case "IntentRequest":
                // Intent Request
                console.log(`Intent Request`);
                console.log('Then run MySQL code:');
                connection.connect(function(err) {
                    console.log('Inside connection.connect() callback');
                    if (!err) {
                        console.log("Database is connected ... ");
                        connection.query("INSERT INTO Users (user_id) VALUES ('TESTNAME')",
                            function(err2, result) {
                                console.log("Inside connection.query() callback");
                                if (!err2) {
                                    console.log("Query Successful! Ending Connection.");
                                    connection.end(function(err3) {
                                        if (!err3) {
                                            console.log("Connection ended!")
                                            context.succeed(
                                                generateResponse({},
                                                    buildSpeechletResponse("Welcome to the incredible intelligent MySQLable Alexa!", true)
                                                )
                                            );
                                        } else {
                                            console.log("Ending connection failure!");
                                            context.fail("Ending connection failure! " + err3.message);
                                        }
                                    });
                                } else {
                                    connection.end(function(err3) {
                                        if (!err3) {
                                            console.log("Query error! Ending Connection!");
                                            context.fail(`Query error! Ending Connection!` + err2.message);
                                        } else {
                                            console.log("Query error and Error ending connection!" + err2.message + " " + err3.message);
                                            context.fail("Query error and Error ending connection!" + err2.message + " " + err3.message);
                                        }
                                    });
                                }
                            });
                    } else {
                        console.log("Error connecting database3 ..." + err.message);
                        context.fail("Error connecting database3 ... " + err.message);
                    }
                });

                break;

            case "SessionEndedRequest":
                // Session Ended Request
                console.log(`SESSION ENDED REQUEST`);
                break;

            default:
                context.fail(`INVALID REQUEST TYPE: ${event.request.type}`);

        }

    } catch (error) {
        context.fail(`Exception: ${error}`);
    }

};

//Helpers
buildSpeechletResponse = (outputText, shouldEndSession) => {

    return {
        outputSpeech: {
            type: "PlainText",
            text: outputText
        },
        shouldEndSession: shouldEndSession
    };
};

generateResponse = (sessionAttributes, speechletResponse) => {
    return {
        version: "1.0",
        sessionAttributes: sessionAttributes,
        response: speechletResponse
    };
};



via Paul McElroy

No comments:

Post a Comment