I'm using Twilio Studio to generate an IVR. I want it to search a database for their records via them inputting an account number or something similar.
I have followed their documentation on using ngrok to connect to the database and used their sample 'read database' code, but I have an issue and a couple of questions.
Upon calling into the IVR how would I grab the input from the input widget? I assume "SELECT * FROM Users WHERE account_number = "[input_from_user]", but how does one refer to that user input to plug it into the query?
After that how does one retrieve just the name from the database for the 'say widget' to play a message like "John Doe, you have an unpaid balance...."? In the sample code i saw something like {{widgets.function_read_db.function.body}} to include the data in a text, but they didn't go more in-depth as to how to handle the data.
As for the error I followed all setup steps and replaced the names with my own database names and tables and it connects to the DB, but I get an error
Error: Packets out of order. Got: 45 Expected: 0
My full modified code:
const mysql = require("mysql");
exports.handler = async function(context, event, callback) {
context.callbackWaitsForEmptyEventLoop = false;
const config = {
host: context.host,
port: context.port,
user: context.user,
password: context.password,
database: context.database
};
console.log("connected", config);
try {
const db = new Database(config);
db.connection.connect();
const users = await db.query("select * from Patients");
await db.close();
console.log(Patients);
callback(null, Patients);
} catch (e) {
callback(e);
}
};
class Database {
constructor(config) {
this.connection = mysql.createConnection(config);
}
query(sql, args) {
return new Promise((resolve, reject) => {
this.connection.query(sql, args, (err, rows) => {
if (err) return reject(err);
resolve(rows);
});
});
}
close() {
return new Promise((resolve, reject) => {
this.connection.end(err => {
if (err) return reject(err);
resolve();
});
});
}
}
