How to set up an Asterisk ODBC data source on Linux
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.