How to set up an Asterisk ODBC data source on Linux

From voipsupport
Jump to: navigation, search

These steps were tested on Centos 6.3

1. Install unixODBC and mysql ODBC connector

yum install unixODBC mysql-connector-odbc

2.Check that /etc/odbcinst.ini should contain something like the following for mysql

[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

3. Setup /etc/odbc.ini

The name of the section between [ ] becomes the data source name (dsn) of the ODBC connection. The other values should be customized as required, in particular the DATABASE should contain the name of the database to connect to.

[dsn]
Description     = your text
Trace           = Off
TraceFile       = stderr
Driver          = MySQL
SERVER          = localhost
PORT            = 3306
DATABASE        = database_name

4. Test the connection

the name should be the same as the data source name (dsn) in /etc/odbc.ini. The user and password should be substituted with the correct credentials to access the database specified in /etc/odbc.ini

isql dsn user password

You should get an sql prompt like this

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

You may then type sql commands, for example if you have a table in your database named callerid:

select * from callerid

You should see the result of the select statement.

5. Optionally configure SSL

One of the advantages of using ODBC instead of the Asterisk MySQL module is that the ODBC can be configured to connect over a secure connection.

In order to use SSL your /etc/odbc.ini needs some additional parameters: SSLCA, SSLKEY, SSLCERT, SSLCIPHER.

Your MySQL server should be compiled with SSL support and configured with the relevant certificates. Since it can be tricky, it is recommended that you verify that you can connect to your MySQL using SSL using the same certificates and cipher before proceeding to configure an ODBC to work with SSL.

[dsn]
Description     = your text
Trace           = Off
TraceFile       = stderr
Driver          = MySQL
SERVER          = localhost
PORT            = 3306
DATABASE        = database_name
SSLCA           = /etc/mysql/ca-cert.pem
SSLKEY          = /etc/mysql/client-key.pem
SSLCERT         = /etc/mysql/client-cert.pem
SSLCIPHER       = DHE-RSA-AES256-SHA

6. Setup asterisk to use ODBC

FreePBX

Edit the file /etc/asterisk/res_odbc_custom.conf

Asterisk without FreePBX

Edit the file res_odbc.conf

In both cases the contents are the same. Ensure that enabled is set to yes. Set the dsn equal to the name used in /etc/odbc.ini. Set the username and password credentials.

[asterisk_dsn]
enabled => yes
dsn => dsn
username => calleriduser
password => XXXXXXXXX
pre-connect => yes

7. Setup asterisk to use Asterisk dsn

Edit /etc/asterisk/func_odbc.conf

The name of the section, in the example below asterisk_function, is the name of a new function which will then be available in Asterisk and can be called to run SQL queries via the ODBC driver.

The dsn must be setup to the asterisk_dsn from the previous step (the name between the [ ]). Do not set it to dsn used in /etc/odbc.ini unless you called your asterisk_dsn the same as the dsn.

readsql is set to the SQL to run. The SQL may contain parameters which will be passed when calling the ODBC. If you want to run completely dynamic SQL, just use the values set up below so that you may pass the whole query as the first parameter.

[asterisk_function]
dsn=asterisk_dsn
readsql=${ARG1}

8. Restart asterisk

For example if using the latest version of FreePBX you can type the following at the command line:

fwconsole restart

9. Check for the new ODBC function in asterisk

The function will be the name used at step 7 (asterisk_function) with a prefix of ODBC_

core show functions

Should return something like this where asterisk_function will be substituted with whatever name used in step 7.

...
ODBC_asterisk_function              ODBC_asterisk_function(<arg1>[...[,<argN>]])       Runs the referenced query with the specified arguments
...

10. Using the ODBC function

FreePBX

You may now use the ODBC_asterisk_function within Dynamic Routes. Set the ODBC Function to ODBC_asterisk_function (or whatever you called it in step 7 prefixed by ODBC_). Set the ODBC query to the SQL you want to execute.

Asterisk without FreePBX

You may now use the function with Asterisk dial plan. For example:

exten => s,1,Set(returnvalue=${ODBC_asterisk_function("select callername from calleridlookup where calleridnum like concat('%','${CALLERID(num)}')")})

Be careful to match the brackets.