SQL Server & SQLite Database Access

The Viabl.ai Platform provides two in-built options to support runtime database access:

• SQLite Version 3 (SQLite3) which is mainly designed for quick database Prototyping
• SQL Server

Both database access options are automatically supported in Silent & Chat deployments. However if access is required under Dialog deployment then we recommend that the Scripting Object that is used for the integration is set to Run on Server. Alternatively a separate Silent Knowledge-base which deals with the database access can be set up to be accessed from the Dialog Knowledge-base as a Web Service.

SQLite3

If SQLite option is enabled then the user can add runtime access from the Knowledge-base to their database via the xrSqlite Object. There are 2 methods on the object (the choice of which depends upon whether you have data returned from your SQL):

• xrSqlite.setup()
• xrSqlite.run()
• xrSqlite.get()

For example:

xrSqlite.run("DROP TABLE IF EXISTS people");
xrSqlite.run("CREATE TABLE people (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, name TEXT, nickname TEXT)");
for (let c = 0; c < 3; c++) {
     // pre-build SQL (IMPORTANT, you should ESCAPE the value to prevent SQL injection)
     xrSqlite.run("INSERT INTO people (name, nickname) VALUES('john" + c + "', 'TTV" + c + "')");
     // parameters passed in an array (matched up by index)
     xrSqlite.run("INSERT INTO people (name, nickname) VALUES(?, ?)", ["erik" + c, "humble" + c]);
     // named parameters passed as an object
     xrSqlite.run("INSERT INTO people (name, nickname) VALUES($name, $nick)", {$name: "bob" + c, $nick: "build" + c});
 }
 let rows2 = xrSqlite.get("SELECT * FROM people");

The location of the database file is handled automatically by the engine. However, if you wish to specify the location manually, you can do so via the xrSqlite.setup method...

xrSqlite.setup({
    filename: "C:/databases/test.db"
});
xrSqlite.run("DROP TABLE IF EXISTS people");
...

n.b. Please make sure that you have read & write access to the supplied database location!

SQL Server

You can access a production SQL Server database via the xrSqlsvr object. There are 3 methods on the object:

• xrSqlsvr.setup()
• xrSqlsvr.run()
• xrSqlsvr.get()

Unlike the xrSqlite Object, you must self-host your own SQL Server database and provide connection details via the xrSqlsvr.setup method.

For example:

xrSqlsvr.setup({
    db_host: "192.168.1.238",   // IP or domain name of SQLserver
    db_user: "Viabl Platform",  // SQLserver login name
    db_password: "password",    // SQLserver login password
    db_database: "data_mining", // SQLserver database (schema)
    //  db_port: 1433,                         // SQLserver default
    //  db_instanceName: "MYINSTANCENAME",     // SQLserver name
    //  trustedConnection: true,               // add the trustedConnection setting to the tedious connect options
    //  trustServerCertificate: false,         // add the trustServerCertificatesetting to the tedious connect options
    timeout: 2000               // maximum length of single SQL call in ms
});
xrSqlsvr.run("IF EXISTS(SELECT * FROM people) DROP TABLE people");
xrSqlsvr.run("CREATE TABLE people (name TEXT, nickname TEXT)");
for (let c = 0; c < 3; c++) {
    // pre-build SQL (IMPORTANT, you should ESCAPE the value to prevent SQL injection)
    xrSqlsvr.run("INSERT INTO people (name, nickname) VALUES('john" + c + "', 'TTV" + c + "')");
    // named parameters passed as an object
    xrSqlsvr.run("INSERT INTO people (name, nickname) VALUES(@name, @nick)", {name: "bob" + c, nick: "build" + c});
}
let rows1 = xrSqlsvr.get("SELECT * FROM people");

Advanced Connection Settings (viabl.ai >= Version 5.3)

If you require more control over the tedious connection settings, you can provide the configuration directly to tedious via the connection parameter. This completely overrides the viabl.ai connection settings...

xrSqlsvr.setup({
    connection: {
        server: "database.acme.com",
        authentication: {
            type: "default",
            options: {
                userName: "joe",
                password: "password"
            }
        },
        options: {
            database: "MY_DATABASE",
            rowCollectionOnRequestCompletion: true,
            useColumnNames: true,
            connectTimeout: 15000,
            requestTimeout: 15000,
            cancelTimeout: 15000,
            encrypt: false,
            debug: {
            }
        }
    }
});