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
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
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.
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
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
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 =
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
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
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
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 = 1433In the next section, you will be able to validate your connection to the MS SQL server.
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
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.
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.
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”).