Database

From SoftIVR

Database support in SoftIVR

Each IVR has its own private database. This is designed for relatively lightweight tasks: filling a table with a million CDRs and then attempting to extract summary information on the fly is likely to result in disappointment. The database is stored along with the IVR's audio prompts, and so is subject to the IVR's storage size limit, which is 100MB for a basic IVR.

The database is accessed using the queryDB, fetchRow and getField functions. As there are no tools - yet - to allow access to the database in any way other than via a script, there are a few tricks needed to make things work.

Creating tables

Tables need to be created within a script. To do this cleanly, use the 'CREATE TABLE IF NOT EXISTS' syntax, which does exactly what it says. Tables will be created the first time a script is run. An example:

queryDB("create table if not exists active (uid char(16) not null, start datetime not null, callerid char(32) not null, ddi char(32) not null)");
queryDB("insert into active(uid, start, callerid, ddi) values('" + call.UID + "', NOW(), '" + call.callerID + "', '" + call.DDI + "')");
// Get our start time
queryDB("select * from active where uid='" + call.UID + "'");
if (fetchRow()) {
  start_time = getField("start");
}

An alternative way of manipulating the database is through the SQL console in the web portal.