Asterisk: The Future of Telephony, 2nd Edition

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. This saves a lot of development effort and code maintenance. There is a slight performance cost to this because we are adding another application layer between Asterisk and the database. However, this can be mitigated with proper design and is well worth it when you need powerful, flexible database capabilities in your Asterisk system.

Before we install the connector in Asterisk, we have to install ODBC into Linux itself. To install the ODBC drivers, simply run the command:

# yum install -y unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel

Tip

See Chapter 3 for the matrix of packages you should have installed.

We need to install the unixODBC-devel package because it is used by Asterisk to create the ODBC modules we will be using throughout this chapter.

Verify that you have the PostgreSQL ODBC driver configured in the /etc/odbcinst.ini file. It should look something like this:

[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/libodbcpsql.so
Setup           = /usr/lib/libodbcpsqlS.so
FileUsage       = 1

Verify 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.^[135]

[asterisk-connector]
Description         = PostgreSQL connection to 'asterisk' database
Driver              = PostgreSQL
Database            = asterisk
Servername          = localhost
UserName            = asterisk
Password            = welcome
Port                = 5432
Protocol            = 7.4
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =

Let’s verify that we can connect to our database using the isql application. The isql application will not perform the connect as the root user, and must be run as the same owner as the database. Since the owner of the asterisk database under PostgreSQL is the asterisk user, we must create a Linux account with the same name. In Chapter 14, we will take advantage of this user to run Asterisk as non-root.

# su - asterisk
$ 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, we need to recompile Asterisk so that the ODBC modules are created and installed. Change back to your Asterisk sources directory and run the ./configure script so it knows you have installed unixODBC.

# cd /usr/src/asterisk-1.4
# make distclean
# ./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, func_odbc, func_realtime, pbx_realtime, res_config_odbc, res_odbc. For voicemail stored in an ODBC database, be sure to select ODBC_STORAGE from the Voicemail Build Options menu. You can verify the modules exist in the /usr/lib/asterisk/modules/ directory.

Configuring res_odbc for Access to Our Database

ODBC connections are configured in the res_odbc.conf file located in /etc/asterisk. The res_odbc.conf file sets the parameters that the various Asterisk modules will use to connect to the database.^[136]

Modify the res_odbc.conf file:

[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => welcome
pooling => no
limit => 0
pre-connect => yes

The dsn option points at the database connection we 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
Name: asterisk
DSN: asterisk-connector
Pooled: no
Connected: yes

---

^[135] 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 seen later.

^[136] The pooling and limit options are quite useful for MS SQL Server 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).