How to access a database from Yate's Javascript module

Revision as of 14:19, 27 March 2013

To interact with databases the Javascript module uses Yate's database connections.
To make a query you must send a database message.

Using the examples below you will learn how to:

• create a database connection in Yate
• retrieve information from database
• route calls to the result obtained from database

Configure script in Javascript module

To configure a script you have to list it in javascript.conf file.
The path to the file is set in parameter scripts_dir.If not set, the default is 'share/scripts'.

To configure routing script:

• set the name of the script in parameter routing from section [general].

[general]
routing=example.js

To configure other type of script:

• set it in section [scripts]. Each line has this form: name=script_file_name. The name must be unique and it will identify the running script instances.

[scripts]
faxes=fax_handler.js

Another way of including scripts from your javascript file:

• use #include to include other scripts

Configure database account

Yate has support for MySQL and PostGreSQL database.

Requirements

Note Before continuing check that you have MySQL or PostGreSQL loaded. If not, install the devel packages.

The easiest way to check this, is from rmanager to write command status and to see if module name is listed there.

Set database account in Yate

If you want to use MySQL the configuration file is mysqldb.conf and for PostgreSQL the configuration file is pgsqldb.conf.

Let's set account 'yateadmin' in mysqldb.conf, so we can use it in our examples:

[yateadmin]
database=yateadmin
user=mysql
password=yate

Create database in MySQL

Then in MySQL, the database and the table must be created:

• create database yateadmin;
• create table lines (location varchar(255));

Database Examples

This is the example.js script.
To make a query, you have to send a database message.
Specify the connection to use by setting in parameter m.account the name of the database account created in mysqldb.conf.

Retrieve information from database

// Get DB Object
var m = new Message("database");
// Specify connection to use
m.account = "yateadmin";
// Define Query
m.query = "SELECT * FROM lines WHERE location IS NOT NULL ORDER BY line LIMIT 5";
// Run the Query
if (m.dispatch()) {
    if (m.rows > 0) {
        Engine.output("Got " + m.rows + " records of " + m.columns + " fields");
        Engine.output("result[0,0] = " + m.getResult(0,0));
        var res = m.getColumn("location");
        for (var i = 0; i < res.length; i++) {
            Engine.output("location[" + i + "] = " + res[i]);
        }
    }
}
else {
    Engine.output("Query failed");
}

Example with call interaction

// Get DB Object, Account, Query
var m = new Message("database");
m.account = "yateadmin";
m.query = "SELECT location from lines where line = 'message.called'";

// Run the Query
if (m.dispatch())
{
	Engine.output("Query Ok");
	if (m.rows > 0) 
	{
		Engine.output("result[0,0] = " + m.getResult(0,0));

		// assuming m.getResult(0,0) returns "tone/busy" or "sip/sip:345@10.11.12.13"
		Channel.callJust(m.getResult(0,0));
	}
}

See also

• How to do routing using javascript
• Database Modules
• Database Message