* You are viewing the archive for June, 2011

Asterisk unixODBC configuration files for MySQL and MariaDB

1.0 Asterisk + unixODBC

Having almost all of our Asterisk configuration based on our preferred Database Management System is one of the greatest advantages that we have at the moment to deploy a VoIP solution. Now, having the possibility of building and integrated and unified communication solution in a non-intrusive way for client’s company, while at the same time assuring scalability and flexibility, that’s a mayor thing. That’s precisely what we have at the moment of using Asterisk+unixODBC.

unixODBC “allows the user or the system administrator to easily configure an application to use any ODBC compliant data source. This is perhaps the single biggest advantage of coding an application to the ODBC API and to purchase these applications. Dyamic binding allows the end-user to pick a data source, ie an SQL Server, and use it for all data applications without having to worry about recompiling the application.”

“The unixODBC Project goals are to develop and promote unixODBC to be the definitive standard for ODBC on non MS Windows platforms. This is to include GUI support for both KDE and GNOME.

ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources. Data Sources include SQL Servers and any Data Source with an ODBC Driver.” (unixODBC website)

In this article I’m making the following assumptions:

  • You are using CentOS
  • You are using MariaDB or MySQL (All MySQL connectors -PHP, Perl, Python, Java, MyODBC, Ruby, MySQL C connector etc- work unchanged with MariaDB)

 

2.0 Asterisk ODBC Installation

Follow this document in order to install Asterisk.

You’ll also need to install the Asterisk ODBC package and (optionally) the Asterisk Voicemail ODBC package (if you plan to store your voicemail using ODBC):

$ yum install asterisk16-odbc asterisk16-voicemail-odbcstorage

 

3.0 Install the MySQL-connector

$ yum install mysql-connector-odbc

 

4.0 Configuring the System File for unixODBC: odbcinst.ini

The system file odbcinst.ini contains information about ODBC drivers available to all users. You could have multiple drivers configured, so if you want to use MariaDB, PostgreSQL and ORACLE at the same time, here is where you would be configuring the drivers.

Create the following /etc/odbcinst.ini file

[MySQL]
Description=MySQL ODBC 5.1 Driver
Driver=/usr/lib/libmyodbc5.so
SETUP=/usr/lib/myodbc3S.so
UsageCount=1

 
In the previous example, the ‘[MySQL]‘ part, is an identifier for this driver (you could call it whatever you want, just remember the name later). The ‘driver‘ setting indicates where is located the MySQL Connector driver file. If you are using CentOS 64bit (or other distro), this might have a different location, you might want to try finding it using:

$ locate libmyodbc

 
… or you can use whatever method you feel comfortable with (e.g. find command)

The ‘setup‘ and ‘usercount‘ parts can be ignored, as they are used when you create the file using a GUI (which is not our case right now).

  • For extensive documentation about how to correctly configure unixODBC drivers file and Data Source Name file, visit the exceptional documentation provided by Nick Gorham at unixODBC website.
  • Information about configuring MySQL ODBC Connector and underlying details can be found on MySQL ODBC Connector documentation’s page.

 

5.0 Configuring the DSN (Data Source Name) File for unixODBC: odbc.ini

The DSN file contains information about DSN’s available to all users. These “System DSN’s” are useful for application such as web servers that may not be running as a real user and so will not have a home directory to contain a .odbc.ini file.

Create the following /etc/odbc.ini file:

[asterisk-dsn]
Description=Asterisk Data Source Name
Driver=MySQL
SERVER=localhost
UID=astdbuser
PWD=MYSQL_USER_PASSWD
DATABASE=asteriskdb
PORT=3306
; SOCKET=/var/lib/mysql/mysql.sock

 
In this example ‘[asterisk-dsn]‘ is the DSN identifier, the ‘Driver‘ part indicates what driver to use from your odbcinst.ini file (section 4.0 of this guide). ‘Server‘ indicates where to connect using the driver belonging to this section (in this example, ‘localhost’). ‘UID‘ and ‘PWD‘ are the username and password of a user that has proper privileges on the database defined by ‘DATABASE‘. You might want to indicate a ‘SOCKET’, instead of a ‘PORT’ (only for local connections: localhost). This is the socket where MariaDB/MySQL listen to, in my system:

[root@asteriskfaqs etc]# netstat -n -l | grep -i mysql
unix  2      [ ACC ]     STREAM     LISTENING     14847  /var/lib/mysql/mysql.sock

 

6.0 Testing your unixODBC configuration

If everything went OK, then this command should help you test your configuration:

[root@asteriskfaqs etc]# echo ‘select now()’ | isql asterisk-dsn
+—————————————+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+—————————————+
SQL> +——————–+
| now()              |
+——————–+
| 2011-06-30 17:57:01|
+——————–+
SQLRowCount returns 1
1 rows fetched

 

7.0 Avoiding deadly pitfalls

  • Get sure that the database configured in the DSN file exists! (you could get weird errors if it doesn’t, but none of the meaningful)
  • Get user that the username in the DSN file is valid (it exists) and has privileges over the indicated database
  • Get sure to provide the correct file name in the odbcinst.ini file, when configuring the driver

 
 

I hope this post has been useful to you. I’ll try to keep it updated and keeping improving it over the time. If there’s something you don’t understand, just drop me a line.

Asterisk 1.6.1 Realtime SIP Users

Hello,
I just implement the SIP Peers with MySQL.

In the structure mySQL missing the following fields:

nat = yes
notransfer = yes
dtmfmode = rfc2833
call-limit = 2
canreinvite = no
subscribecontext = blf

subscribecontext (BLF) and call-limit (Protection) are very important …
Can you help me?

Best,
Mickael

patch openr2 asterisk 1.4.42

Today, i needed to update the servers for asterisk 1.4.42, and how we use
openr2 on most of the servers, it was necessary to update the patch to this
version, as the last version of the patch on the site was
openr2-asterisk-4.1.39-p1.patch, i had to make my on patch updated. I paste
the code on pastebin.

http://pastebin.com/JpPmpam2

cisco sip

Hi
I have a working asterisk 1.8 system with 10 7961 phones. Everything works perfect except for the line ringing. I have 4 lines configured on each phone (IE 7002, 7022, 7042, and 7062). When I dial 7002 it rings line 1 like it should. But when I dial any of the other numbers it still rings only line 1 and not the actual line that it is assigned to. I checked the cli and it is dialing the correct number (7022, 7042 or 7062), but the phone still rings on 7002. Below is my .cnf.xml file for one of the phones.

Thanks

Kelly


SIP
cisco
cisco



M/D/Ya
Eastern Standard/Daylight Time


172.16.0.10
Unicast





2000
5060
5061

172.16.0.10











true


true
x–serviceuri-cfwdall
x-cisco-serviceuri-pickup
x-cisco-serviceuri-opickup
x-cisco-serviceuri-gpickup
x-cisco-serviceuri-meetme
x-cisco-serviceuri-abbrdial
false
2
true
true
2
2
1
true


6
10
180
3600
5
120
120
5
500
4000
70
true
None

1
false
true

false false

g711ulaw

101

3

avt false
false
3

false

Kelly’s 1
true
10
false
16384
32766

9
7002

172.16.0.10

5060 7002
7002

2

3
7002
pass7002
false
1
*97
4
5
100

true
true
false
true
9
Gateway

172.16.0.10

5060 7022
7022

2

3
7022
pass7022
false
1
*97
4
5
100

true
true
false
true
9
Wakefield

172.16.0.10

5060 7042
7042

2

3
7042
pass7042
false
1
*97
4
5
100

true
true
false
true
9
DID

172.16.0.10

5060 7062
7062

2

3
7062
pass7062
false
1
*97
4
5
100

true
true
false
true

5060
184
0
dialplan.xml
softkey.cnf.xml

true
1

SIP41.8-5-4S


false
false

1 1
0
0
0
0
1
1
1

1143565489-a3cbf294-7526-4c29-8791-c4fce4ce4c37
US

US
5.0(2)

1
http://172.16.0.10/cisco/services/authentication.php
http://172.16.0.10/xmlservices/PhoneDirectory.php
http://172.16.0.10/xmlservices/index.php

proxy:3128 http://172.16.0.10/xmlservices/index.php
96
0
96

4 0

3804



false

not setting the fax header

Hello,

after I solved my problem with the fax processing after receiving,
I got another problem while sending a fax: the header is not set properly.

I use a PHP_Script to upload a PDF file and to generate a call file.

A bash script is looking for existent call files in the web directory
and moves
them the asterisk’s outgoing directory.

Ok, my call file looks like this:

======
$cf_commands = “Channel: local/$ext@commonn”
.”MaxRetries: 5n”
.”RetryTime: 60n”
.”WaitTime: 60n”
.”Context: faxn”
.”Extension: 100n”
.”Set: FAXFILE=$file_dstn”
.”Set: FAXOPT(localstationid)=$_POST[mynumber]n”
.”Set: FAXOPT(headerinfo)=$_POST[mynumber]
$_POST[myname] $_POST[myemail]n”
.””;
======

As you can see, I set the LOCALHEADERINFO to string I got from a web form.

My fax extension for sending fax looks like that:

======
[fax]
exten => 100,1,System(/usr/local/bin/pdf2fax.sh ${FAXFILE})
exten => 100,n,Wait(1)
exten => 100,n,Verbose(${BOUNDARY} ${FAXOPT(localstationid)}
${FAXOPT(headerinfo)})
exten => 100,n,Verbose(${BOUNDARY} ${FAXFILE})
exten => 100,n,SendFAX(${FAXFILE}converted)
exten => 100,n,Verbose(1,### FAXSTATUS: ${FAXSTATUS})
exten => 100,n,Verbose(1,### FAXERROR: ${FAXERROR})
exten => 100,n,Verbose(1,### FAXMODE: ${FAXMODE})
exten => 100,n,Verbose(1,### FAXPAGES: ${FAXPAGES})
exten => 100,n,Verbose(1,### FAXBITRATE: ${FAXBITRATE})
exten => 100,n,Verbose(1,### FAXRESOLUTION: ${FAXRESOLUTION})
exten => 100,n,Verbose(1,### REMOTESTATIONID: ${REMOTESTATIONID})
exten => 100,n,HangUp()
======

The Verbose() application shows to me that ${FAXOPT(headerinfo)} is
correctly set,
but the headerinfo is not appearing on the fax itself…
It’s not important if I set FAXOPT(headerinfo) or LOCALHEADERINFO, the
result is always the same: no header on the fax received.

I even tried to set FAXOPT(headerinfo) directly in the fax extension:
same result: no header on the fax…

Console output:
======

asterisk recording problem

Hi,

I’m trying to record the incoming calls with Asterisk 1.6.2.15 and Dahdi
2.4.0. Without the line Record, everything works fine. If I add the line
Record, I can hear only a beep sound, the caller and the callee cannot
hear each other and there is no recording file.

The extension in the extensions.conf:

SZERVERSZOBA=DAHDI/19,,rtT

exten => 57,1,Record(${TIMESTAMP}${CALLERID(num)}-${EXTEN}.wav)
exten => 57,n,Dial(${SZERVERSZOBA})
exten => 57,n,Playback(vm-nobodyavail)
exten => 57,n,Hangup()

In the log: