Welcome to SpiderWiki,
There are currently 131 articles.

Query MySQL from Asterisk with func odbc

From SpiderWiki
Jump to: navigation, search

I wanted to have my Asterisk server query a mySQL DB for details.

The main goal here was to have incoming calls checked against a white list or blacklist. I could have used the in built asterisk DB but I thought it would be useful to use mySQL for a more scalable option.

Contents

Adding func_odbc

If you haven't already got it then you'll need to compile in the func_odbc function. There are some prerequisites for this in unixodbc-dev so install this first.

Then go to your source directory and run

./configure

then

make menuconfig

Under resources make sure res_odbc is enabled.

Then run

make

and finally

make install

func_odbc should now be installed and available to Asterisk


Configuring ODBC

Edit /etc/odbcinst.ini

[MySQL]
Description     = MySQL driver
Driver          = libmyodbc.so
Setup           = libodbcmyS.so
CPTimeout       =
CPReuse         =

Then /etc/odbc.ini

[asterisk-connector]
Description           = MySQL connection to 'asterisk' database
Driver                = MySQL
Database              = asterisk
Server                = localhost
UserName              = myuser        
Password              = mypass
Port                  = 3306
Option                = 3


Configure Asterisk

Next we need to tell Asterisk what ODBC resources are available in res_odbc.conf

[asterisk]
enabled=>yes
dsn=>asterisk-connector
username=>myuser
password=>mypass
pooling=>no
pre-connect=>yes


Finally we need define some queries for Asterisk to run in func_odbc.conf

[CHECKID]
dsn=asterisk
readsql=SELECT COUNT(*) FROM contacts WHERE number='${SQL_ESC(${ARG1})}'
synopsis=Check if a caller is in the contacts table

Note: If you install the example config you'll find an example for testing if it's a crazy ex-girlfriend calling!

That's the config all done all that's left is adding something to your dial plan.

same => n,Set(ISCONTACT=${ODBC_CHECKID(${CALLERID(num)})})

Now you can use the variable ISCONTACT in a GOTOIF statement however you like.

Note: The name of the query as defined in func_odbc.conf is prefix ODBC_