View source for How to access a database from Yate's Javascript module

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

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.<br>
The path to the file is set in parameter '''scripts_dir'''. If not set, the default is 'share/scripts'.<br>

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 [[Including other files|include other scripts]]

===Configure database account===

Yate has support for MySQL and PostGreSQL database.

====Requirements ====

 '''Note''' Before continuing check that you have module mysqldb or pgsqldb loaded.

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

If not loaded:

*  it is possible that you don't have the devel packages installed. 
If this is your case, after installing the devel packages, run '''./configure''' and '''make''' in Yate's sources. 

Then start Yate and send status command from [[rmanager]].

If still not loaded:
*  check if in yate.conf parameter modload is disabled (modload=no). If true, you have to list the module name that you need to load in [modules] section.

==== Set database account in Yate====

If you want to use MySQL the configuration file is [[MySQL|mysqldb.conf]] and for PostgreSQL the configuration file is [[PostgreSQL|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.<br>
To make a query, you have to send a ''database'' message.<br> 
Specify the connection to use by setting in parameter '''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]]
* [[Modules#Database_Drivers|Database Modules]]
* [[Database| Database Message]]

[[Category:Javascript]] [[Category:Programmers]] [[Category:Database]]