How to recompile asterisk to get ODBC support for FreePBX 2.8.1.5

Distro Discussion & Help
1

Hello All,

I have additionally installed the following ODBC software on my freepbx 2.8.1.5 distro that is using Asterisk (Ver. 1.6.2.20).

The following ODBC packages appear on this system currently.

1. mysql-connector-odbc 3.51.26r1127-2.el5
2. php-odbc 5.1.6-39.el5_8
3. unixODBC 2.2.11-10.el5
4. unixODBC-devel 2.2.11-10.el5
5. unixODBC-libs 2.2.11-10.el5

When I putty into the freepbx 2.8.1.5 machine and run the command
#odbcinst -q -d
i do show:
[MySQL]
[PostgreSQL]
When I run the command:
#odbcinst -j
it gives me this back:
unixODBC 2.2.11
DRIVERS…: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES…: /root/.odbc.ini

However on the freepbx 2.8.1.5 GUI in the “SmartRoutes” link under the inbound Call Control, the notes show the following;
ODBC support cannot be confirmed in your Asterisk install. If needed, please confirm installation. Instructions for compiling Asterisk with ODBC are here.

Instructions for configuring ODBC in linux and Asterisk are here.

Aftering following the instructions on both links I am stuck at the part that states that “…recompile Asterisk so that the ODBC modules are created and installed.”.

It instructs me at the end of the "Installing and Configuring ODBC (the second link) to “…recompile Asterisk so that the ODBC modules are created and installed.” by doing the following from the command prompt:
# cd /usr/src/asterisk-1.4
# make distclean
# ./configure
# make menuselect
# make install
I have confirmed that there is no /usr/src/asterisk-1.4 only /usr/src/freepbx-2.7.0 which is what I started out with a few years ago.

Does anyone know how I get my freepbx distro to recompile asterisk to get this “SmartRoutes” to recognize support for ODBC? Please provide a command by command instruction if you can please.

My System

FreePBX 2.8.1.5 (CentOS Linux 5.8)
Asterisk (Ver. 1.6.2.20)
SmartRoutes Module v1.3

2

I’m in the same situation. I’m using PIAF and i’m struggling to make smartroutes works.

Did it worked for you?

Thanks

Alex

3

Yes, smart routes works great. The issue is the distro’s. You have to build asterisk and freepbx for example by hand. I used this How to Build FreePBX 2.8 with asterisk 1.8 guide to accomplish this. It’s very easy and painless with this guide if you ask me. The key is that you build the module with ODBC, and this can’t be done with the older Distros. I have not tried this with freepbx 2.9 distros or higher.

1st I used the backup module to create a backup of everything. This will still work if you have installed and setup smartroutes already. I included all the bold directories from this website for good measures.

2nd. On the new system I still used CentOS Linux 5.8 to start off with. Try to match the OS as close as possible. If you don’t care about most of the stuff on the current system just start from scratch using this website How to Build FreePBX 2.8 with asterisk 1.8. Building the PBX by hand gives you the opportunity to change all the default passwords from the start avoiding that amp11, freepbx user name and passwr0d default passwords that were such a pain to remove on freepbx 2.8x. Now restore the new system with the backup from the old system. For good measure recompile asterisk from the /usr/src/asterisk-1.4 directory a last time.

When your all done, make sure that your odbc.ini and odbcinst.ini files are in the /etc folder.

I set my odbc.ini file up like this.

[asterisk-connector]
Description=PostgreSQL connection to 'asterisk' database
Driver=MySQL
Server=IP address where the database is located
Database=name of the database
UID=user name to the database
PWD=my password to the database
Port=3306
SOCKET=/var/lib/mysql/mysql.sock

Then place your res_odbc.conf in the /etc/asterisk folder. Then use this in your res_odbc.conf file

[asterisk-2-crm or whatever you want]
enabled => yes
dsn => asterisk-connector (use what you placed between the brackets in your odbc.ini file)

username => database user name
password => database password
pooling => no
;limit => 0 (comment this out.  It will not allow the "show odbc" to work in the asterisk CLi)

pre-connect => yes

This is the only way to use odbc as a database type. I was not able to reverse the negative effects of using the mysql connection so don’t use it or even try it. The MySQL setting causes the asterisk server to reset at random times for no reason.

Here is a little side note. I guess the that you can connect to multiple databases my creating multiple odbc.ini and res_odbc.conf connections in these files if you need to connect to different databases. Let me know if this works if you need this functions from freepbx.

4

If your having a problem with SmartRoutes not passing your caller to the correct destination, but is sending the caller to the Default Destination: that you chose. Please look at your CID (caller ID)that your system is displaying.
A common problem is that the caller ID that is passed from the trunk provider might be sending extra digits that are not in your database that your connecting to. In my case I am connecting to an external database for our CRM in a different location and the trunk provider is sending the +1 with the CID.

To resolve this, simply edit a few files (2) and two(2) places in your “Trunks”. This is currently the case with SIP Station and is working for SIP Station users. “SIP Station” currently sends the CID as +1 and the 10 digits. As far as I know, this will not strip the UN from UNKNOWN Caller ID or any other non numerical digits from the CID.

  1. Navigate to the /etc/asterisk/extensions_custom.conf on your hand built asterisk system. SartRoutes will not work on the 2.8 and older Distro’s as you cannot enable ODBC database types. My default location was /etc/asterisk/. If your file is empty just paste this code: [from-ptsn-custom]
    exten => _X!,1,GotoIf($["${CALLERID(num):0:2}" != “+1”]?noplusatstart)
    exten => _X.,n,NoOp(Changing Caller ID number from ${CALLERID(num)} to ${CALLERID(num):2})
    exten => _X.,n,Set(CALLERID(num)=${CALLERID(num):2})
    exten => _X.,n,Set(CALLERID(ANI)=${CALLERID(num)})
    exten => _X!,n(noplusatstart),Goto(from-trunk,${EXTEN},1)
    type = friend     at the top of your file and save and close the file.

    Now navigate to the /etc/asterisk/sip_custom.conf file and do the same as before and paste that same code in this sip_custom.conf file. Now again Place the code at the top of your file and save and close the file.

Now the last thing that you need to do is go into the GUI and navigate to the “Trunks” and down to “PEER Details”. Now just and -custom to the end of the context=from-ptsn line. It should now look like “context=from-ptsn-custom”

Do this for all the trunks that you wish to only strip only the +1 if it exists from the incoming CID.

Still in the Trunks, the last part to take care of is to edit the Incoming Settings of all the “Trunks” that you wish to strip the +1 from.
In the USER Context: box, simply place your newly added “from-ptsn-custom” into this box and " Submit Changes button and reload.

The routes CID should now match the database(s) phone number(s).

Note:
The SmartRoutes module does not allow you to do the following things that I am currently working on to fix.

  • Your SmartRoute needs to be before anything else for your inbound route.
    You will get the "Due to technical difficulties.." message if you do this.
  • You cannot have a time condition as a destination in your SmartRoute
     If you do this, the call will just hang up.
  • Only use Database Type ODBC*recommended.
    The consequences are that your system will continue to reboot at will and the effect cannot be reversed, even if you change the Database Type back to ODBC*recommended.
    • If your comfortable making changes to your files and to avoid this devastating problem, navigate to the following area in the modules. /var/www/html/admin/modules/smartroutes Backup and edit the page smartroutes.php file.
    At or around line 372 comment out the line that says "
    Selecting the option for MySQL will cause your asterisk server to reboot at random times without you knowing and drop calls. There is no way to reverse the effect even if you choose ODBC after the fact.
    If anyone has a way to use this with "Time Conditions" this would be great for me. I currently have to have all users log out of their phones and have the Queue(S)go to the closed IVR when no users are logged in.

    Thanks for reading my post.

    5

    The_buck - you need to edit your post, it’s very confusing.

    Specifically, over and over you say you have FreePBX Distro, yet you don’t have the Distro. That’s a FreePBX version number not a Distro. You either installed from scratch or used another distro.

    6

    Installing everything from scratch is or was the only way to get the ODBC connection to work on asterisk 1.8 and and freepbx 2.8. I wish that I could edit the post. I just wanted everyone that read this to know the new update on what happened to my system, and what not to do or how to prevent any irreversible mistakes like Trying to connect to a database using the MySql connection. As you can see that I suggest removing the option all together on using the MySql connection in the smartroutes module. It’s easy to over look when your doing a lot of smartroutes to forget to change that Database connection to ODBC.

    Should you need some help, let me know. I started out asking for help myself, and then moved to reporting on how I resolved the smartroutes integration process.

    I will ask the guys here if I could create an official “how too” after I work out all the details. This is sort of a working log of sorts for now. I apologize for any confusing.

    7

    Mr. Holtzman. Take a look at Tue, 10/23/2012 - 23:59. I state that “Yes, smart routes works great. The issue is the distro’s. You have to build asterisk and freepbx for example by hand. I used this How to Build FreePBX 2.8 with asterisk 1.8 guide to accomplish this.” Specifically during the “# make menuselect” you would select the ODBC option during the “Asterisk” process of whatever version you choose up to 1.8 as far I know. I gave the link to installation guide that I followed and referenced. The first post is what I started out with. Once again, If you need some help with the process I will be more then happy to answer what I can for your or anyone else.