Troubleshooting ODBC Module in Asterisk

Introduction

This article is to introduce troubleshooting steps for ODBC malfunction for Asterisk.

Description

We are resolving following error for ODBC Connection.

$ echo "select 1" | isql -v asterisk-connector [01000][unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc5.so' : file not found [ISQL]ERROR: Could not SQLConnect

Methodology

Step # 1

Create a separate directory odbc/ in /usr/lib or /usr/lib64/ (as available):

$ mkdir /usr/lib64/odbc/

Step # 2

Download and Install latest MySQL Connector for ODBC with suitable for your OS:

$ wget http://mysql.mirrors.crysys.hit.bme.hu/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.6-linux-el6-x86-64bit.tar.gz $ tar -zxvf mysql-connector-odbc-5.3.4-linux-el6-x86-64bit.tar.gz $ cd mysql-connector-odbc-5.3.4-linux-el6-x86-64bit $ ls lib/ libmyodbc5a.so  libmyodbc5S.so  libmyodbc5w.so $ cp lib/* /usr/lib64/odbc/

Step # 3

Create or Edit file /etc/odbcinst.ini to add following contents:

[MySQL] Description = ODBC Driver for MySQL Driver = /usr/lib64/odbc/libmyodbc5w.so Setup = /usr/lib64/odbc/libmyodbc5S.so FileUsage = 1

Step # 4

Create or Edit file /etc/odbc.ini with following contents:

[asterisk-connector] Description           = MySQL connection to 'asterisk' database Driver                = MySQL Database              = <database> Server                = localhost User                  = <user> Password              = <password> Port                  = 3306 Socket                = /var/lib/mysql/mysql.sock

Step # 5

Add following contents in /etc/asterisk/res_odbc.conf:

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

Step # 6

Add your desired function in /etc/asterisk/func_odbc.conf file:

[FULLNAME] dsn=asterisk readsql=SELECT fullname FROM users WHERE extension=${ARG1}

Step # 7

Reload func_odbc.so module or restart asterisk:

Step # 8

Verifying OBDC Connection:

Terminal

To check odbc Connection in terimal

$ echo "select 1" | isql -v asterisk-connector +---------------------------------------+ | Connected!                            | |                                       | | sql-statement                         | | help [tablename]                      | | quit                                  | |                                       | +---------------------------------------+ SQL> select 1 +---------------------+ | 1                   | +---------------------+ | 1                   | +---------------------+ SQLRowCount returns 1 1 rows fetched

Asterisk CLI

To check ODBC Connection in Asterisk CLI:

CLI> odbc show   ODBC DSN Settings -----------------     Name:   asterisk   DSN:    asterisk-connector     Last connection attempt: 1970-01-01 05:00:00   Pooled: No   Connected: Yes   CLI> odbc read ODBC_FULLNAME "XXXX" exec fullname               ABC Returned 1 row.  Query executed on handle 0 [asterisk]

Share this on

Posted in: Asterisk,CSDL,Elastix