1. Installing and Configuring ODBC
The ODBC connector is a database abstraction layer that makes it possible for Asterisk to communicate with a wide range of databases without requiring the developers to create a separate database connector for every database Asterisk wants to support.
First Install the ODBC LINUX
yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-develunixODBC-devel package because it is used by Asterisk to create the ODBC modules Next,
Configure the PostgreSQL ODBC driver
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/psqlodbc.so Setup = /usr/lib/libodbcpsqlS.so Driver64 = /usr/lib64/psqlodbc.so Setup64 = /usr/lib64/libodbcpsqlS.so FileUsage = 1 UsageCount = 5To test execute
# odbcinst -q -d [PostgreSQL]
Configuring ODBC for MySQL
On CENTOS
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc3.so Setup = /usr/lib/libodbcmyS.so FileUsage = 1On UBUNTU
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/odbc/libmyodbc.so Setup = /usr/lib/odbc/libodbcmyS.so FileUsage = 1NOTE: On 64-bit systems, you will need to change the path of the libraries from /usr/lib/ to /usr/lib64/ in order to access the correct library files.
On my 64 bit CENTOS
[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 UsageCount = 5Verify the driver
# odbcinst -q -d [PostgreSQL] [MySQL]Next, configure the /etc/odbc.ini file, which is used to create an identifier that Asterisk will use to reference this configuration.
[ConfConnection] Description = MySQL connection to 'asterisk' database Driver = MySQL Database = asteriskdb Server = localhost UserName = root Password = allysamae Port = 3306 Socket = /var/lib/mysql/mysql.sockNOTE: On Ubuntu 10.10, the socket location is /var/run/mysqld/mysqld.sock.
Validating the ODBC Connector
Now, verify that you can connect to your database using the isql application. echo the select 1 statement and pipe it into isql, which will then connect using the asterisk-connector section you added to /etc/odbc.ini. You should get the following output (or at least something similar; we’re looking for a result of 1 rows fetched):
# echo "select 1" | isql -v ConfConnection +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> select 1 +---------------------+ | 1 | +---------------------+ | 1 | +---------------------+ SQLRowCount returns 1 1 rows fetched
Recompile Asterisk
With unixODBC installed, configured, and verified to work, you need to recompile Asterisk so that the ODBC modules are created and installed. Change back to your Asterisk source directory and run the ./configure script so it knows you have installed unixODBC:
# cd /usr/src/asterisk* # ./configure # make menuselect # make installNOTE: You will want to run make menuselect to verify that the ODBC-related modules are enabled. These include cdr_odbc, cdr_adaptive_odbc, func_odbc, func_realtime, pbx_realtime, res_config_odbc, and res_odbc. For voicemail stored in an ODBC database, be sure to select ODBC_STORAGE from the Voicemail Build Options menu. You can verify that the modules exist in the /usr/lib/asterisk/modules/ directory.
Configuring res_odbc to Allow Asterisk to Connect Through ODBC
Asterisk ODBC connections are configured in the res_odbc.conf file located in /etc/asterisk. The res_odbc.conf file sets the parameters that various Asterisk modules will use to connect to the database.
NOTE: The pooling and limit options are quite useful for MS SQL and Sybase databases. These permit you to establish multiple connections (up to limit connections) to a database while ensuring that each connection has only one statement executing at once (this is due to a limitation in the protocol used by these database servers).
; The context name is what will be used in other configuration files, such ; as extconfig.conf and func_odbc.conf, to reference this connection. [asteriskdb] ; ; Permit disabling sections without needing to comment them out. ; If not specified, it is assumed the section is enabled. enabled => yes ; ; This value should match an entry in /etc/odbc.ini ; (or /usr/local/etc/odbc.ini, on FreeBSD and similar systems). dsn => ConfConnection ; ; Username for connecting to the database. The user defaults to the context name if unspecified. username => root ; ; Password for authenticating the user to the database. The default ; password is blank. password => mypassword ; ; Build a connection at startup? pre-connect => yesThe dsn option points at the database connection you configured in /etc/odbc.ini, and the pre-connect option tells Asterisk to open up and maintain a connection to the database when loading the res_odbc.so module. This lowers some of the overhead that would come from repeatedly setting up and tearing down the connection to the database.
Once you’ve configured res_odbc.conf, start Asterisk and verify the database connection with the odbc show CLI command:
*CLI> odbc show asteriskdb
ODBC DSN Settings
-----------------
Name: asteriskdb
DSN: ConfConnection
Last connection attempt: 2013-11-19 09:29:44
Pooled: No
Connected: Yes
grant all privileges on *.* to root@172.16.2.133 identified by '!green0909!';
sources: http://www.asteriskdocs.org/en/2nd_Edition/asterisk-book-html-chunk/installing_configuring_odbc.html
