1 Recommended UnixODBC settings for MySQL
Please refer to MySQL documentation to download necessary database driver for the corresponding platform.
For some additional information please refer to: installing unixODBC.
Configuration
ODBC configuration is done by editing odbcinst.ini and odbc.ini files. These configuration files can be found in /etc folder. The file odbcinst.ini may be missing and in this case it is necessary to create it manually.
odbcinst.ini
[mysql] Description = General ODBC for MySQL Driver = /usr/lib64/libmyodbc5.so Setup = /usr/lib64/libodbcmyS.so FileUsage = 1Please consider the following examples of odbc.ini configuration parameters.
[TEST_MYSQL] Description = MySQL database 1 Driver = mysql Port = 3306 Server = 127.0.0.1- An example with a connection through an IP and with the use of credentials. A Zabbix database is used by default:
[TEST_MYSQL_FILLED_CRED] Description = MySQL database 2 Driver = mysql User = root Port = 3306 Password = zabbix Database = zabbix Server = 127.0.0.1 - An example with a connection through a socket and with the use of credentials. A Zabbix database is used by default:
[TEST_MYSQL_FILLED_CRED_SOCK] Description = MySQL database 3 Driver = mysql User = root Password = zabbix Socket = /var/run/mysqld/mysqld.sock Database = zabbixAll other possible configuration parameter options can be found in MySQL official documentation web page.
Linux odbc ini mysql
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. This saves a lot of development effort and code maintenance. There is a slight performance cost, because we are adding another application layer between Asterisk and the database, but this can be mitigated with proper design and is well worth it when you need powerful, flexible database capabilities in your Asterisk system.
Before you install the connector in Asterisk, you have to install ODBC into Linux itself. To install the ODBC drivers, use one of the following commands.
$sudo yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel
Note
If you’re using a 64-bit installation, remember to add .x86_64 to the end of your development packages to make sure the i386 packages are not also installed, as stability problems can result if Asterisk links against the wrong libraries.
$sudo apt-get install unixODBC unixODBC-dev
Tip
See Chapter 3, Installing Asterisk for the matrix of packages you should have installed.
You’ll also need to install the unixODBC development package, because Asterisk uses it to build the ODBC modules we will be using throughout this chapter.
Warning
The unixODBC drivers shipped with distributions are often a few versions behind the officially released versions on the http://www.unixodbc.org website. If you have stability issues while using unixODBC , you may need to install from source. Just be sure to remove the unixODBC drivers via your package manager first, and then update the paths in your /etc/odbcinst.ini file.
By default, CentOS will install the drivers for connecting to PostgreSQL databases via ODBC. To install the drivers for MySQL, execute the following command:
$sudo yum install mysql-connector-odbc
To install the PostgreSQL ODBC connector on Ubuntu:
$sudo apt-get install odbc-postgresql
Or to install the MySQL ODBC connector on Ubuntu:
$sudo apt-get install libmyodbc
Configuring ODBC for PostgreSQL
Configuration for the PostgreSQL ODBC driver is done in the /etc/odbcinst.ini file.
On CentOS the default file already contains some data, including that for PostgreSQL, so just verify that the data exists. The file will look like the following:
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/libodbcpsql.so Setup = /usr/lib/libodbcpsqlS.so FileUsage = 1
On Ubuntu, the /etc/odbcinst.ini file will be blank, so you’ll need to add the data to that configuration file. Add the following to the odbcinst.ini file:
[PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib/odbc/psqlodbca.so Setup = /usr/lib/odbc/libodbcpsqlS.so FileUsage = 1
Note
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.
In either case, you can use cat > /etc/odbcinst.ini to write a clean configuration file, as we’ve done in other chapters. Just use Ctrl + D to save the file once you’re done.
Verify that the system is able to see the driver by running the following command. It should return the label name PostgreSQL if all is well:
$odbcinst -q -d[PostgreSQL]
Next, configure the /etc/odbc.ini file, which is used to create an identifier that Asterisk will use to reference this configuration. If at any point in the future you need to change the database to something else, you simply need to reconfigure this file, allowing Asterisk to continue to point to the same place [141] :
[asterisk-connector] Description = PostgreSQL connection to 'asterisk' database Driver = PostgreSQL Database = asterisk Servername = localhost UserName = asterisk Password = welcome Port = 5432 Protocol = 8.1 ReadOnly = No RowVersioning = No ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = Configuring ODBC for MySQL
Configuration for the MySQL ODBC driver is done in the /etc/odbcinst.ini file.
On CentOS the default file already contains some data, including that for MySQL, but it needs to be uncommented and requires a couple of changes. Replace the existing text with the following:
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/libmyodbc3.so Setup = /usr/lib/libodbcmyS.so FileUsage = 1
On Ubuntu, the /etc/odbcinst.ini file will be blank, so you’ll need to add the data to that configuration file. Add the following to the odbcinst.ini file:
[MySQL] Description = ODBC for MySQL Driver = /usr/lib/odbc/libmyodbc.so Setup = /usr/lib/odbc/libodbcmyS.so FileUsage = 1
Note
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.
In either case, you can use cat > /etc/odbcinst.ini to write a clean configuration file, as we’ve done in other chapters. Just use Ctrl + D to save the file once you’re done.
Verify that the system is able to see the driver by running the following command. It should return the label name MySQL if all is well:
#odbcinst -q -d[MySQL]
Next, configure the /etc/odbc.ini file, which is used to create an identifier that Asterisk will use to reference this configuration. If at any point in the future you need to change the database to something else, you simply need to reconfigure this file, allowing Asterisk to continue to point to the same place:
[asterisk-connector] Description = MySQL connection to 'asterisk' database Driver = MySQL Database = asterisk Server = localhost UserName = asterisk Password = welcome Port = 3306 Socket = /var/lib/mysql/mysql.sock Note
On Ubuntu 10.10, the socket location is /var/run/mysqld/mysqld.sock .
Configuring ODBC for Microsoft SQL
Connecting to Microsoft SQL (MS SQL) is similar to connecting to either MySQL or PostgreSQL, as we’ve previously discussed. The configuration of MS SQL is beyond the scope of this book, but the following information will get your Asterisk box configured to connect to your MS SQL database once you’ve enabled the appropriate permissions on your database.
To connect to MS SQL, you need to install the FreeTDS drivers using the package manager (or by compiling via the source files available at http://www.freetds.org).
$sudo yum install freetds
$sudo apt-get install freetds
After installing the drivers, you need to configure the /etc/odbcinst.ini file, which tells the system where the driver files are located.
Insert the following text into the /etc/odbcinst.ini file with your favorite text editor or with the following command:
$sudo cat > /etc/odbcinst.ini[FreeTDS] Description = ODBC for Microsoft SQL Driver = /usr/lib/libtdsodbc.so UsageCount = 1 Threading = 2Ctrl+D
Note
If you compiled via source, the files may be located in /usr/local/lib/ or (if you compiled on a 64-bit system) /usr/local/lib64/ .
Verify that the system is able to see the driver by running the following command. It should return the label name FreeTDS if all is well:
$odbcinst -q -d[FreeTDS]
Once you’ve configured the drivers, you need to modify the /etc/odbc.ini file to control how to connect to the database:
[asterisk-connector] Description = MS SQL connection to 'asterisk' database Driver = FreeTDS Database = asterisk Server = 192.168.100.1 UserName = asterisk Password = welcome Trace = No TDS_Version = 7.0 Port = 1433 In the next section, you will be able to validate your connection to the MS SQL server.
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 asterisk-connector+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> +------------+ | ?column? | +------------+ | 1 | +------------+ SQLRowCount returns 1 1 rows fetched$exit
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 ~/src/asterisk-complete/asterisk/1.8$./configure$make menuselect$make install
Note
Almost everything in this chapter is turned on by default. 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).
Modify the res_odbc.conf file so it looks like the following:
[asterisk] enabled => yes dsn => asterisk-connector username => asterisk password => welcome pooling => no limit => 1 pre-connect => yes
The 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 showODBC DSN Settings ----------------- Name: asterisk DSN: asterisk-connector Last connection attempt: 1969-12-31 19:00:00 Pooled: No Connected: Yes
[141] Yes, this is excessively verbose. The only entries you really need are Driver , Database , and Servername . Even the UserName and Password are specified elsewhere, as you’ll see later (although these are required when testing, as in the section called “Validating the ODBC Connector”).