Blogs on System Administration, Scripting, System Monitoring, Cloud Computing & Virtualizaton, VoIP Systems, SMS Systems and Web Applications Development.

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:



$ tar -zxvf mysql-connector-odbc*.tar.gz
$ cd mysql-connector-odbc-*
$ 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]

0 comments:

Post a Comment

Please Enter your Comments Here.