Aug
25
2009




How to Query a Database from MySQL with Asterisk 1.6 ODBC

I’ve been administering Asterisk servers for many years and everyday I’m learning something new.  As the size of the projects have gotten bigger, so have the demands of my customers.  One common request is database integration.  In the past I’ve used external AGI scripts to run database queries to return values to Asterisk.  But more recently I’ve been avoiding AGI in favor of direct access SQL.  This is achieved by building ODBC modules into Asterisk at build time.  Let me tell you, it’s worth it!

Asterisk & ODBC provide the ability to easily update and retrieve data by defining SQL statements as special variables that can be called from the dial plan.  For instance, we could bill ‘long distance’, take credit cards and let Asterisk access other types of information.  In my tutorial we will configure Asterisk to dial a variable that represents a SQL statement.  This could also be done by calling an AGI script, but why do the extra work?

This ‘How to’ is specifically geared towards MySQL.  However, if you set the up the ODBC driver correctly you can use any SQL server you like.  Which leads me to my next thought, before you start, make sure ODBC is set up correctly.  Follow one of my previous tutorials:

configure Linux ODBC for MS-SQL

configure Linux ODBC for MYSQL

Once you do this we are ready to continue…

The goals of this post:

– create MySQL table with example data
– configure res_odbc.conf
– configure func_odbc.conf
– configure extensions.conf for query

sample mysql table

We will do this with as much simplicity as possible, once you got the process down you can connect Asterisk to any SQL table you want.  Our example will be a list of extensions.  Each extension has an ID number.  Asterisk will dip the database for a specific extension ID.  Below is the data in our example table.

mysql> select * from extensions;
+————–+——+
| extensionsID | ext  |
+————–+——+
|            1 | 8888 |
|            2 | 8889 |
|            3 | 8890 |
+————–+——+
3 rows in set (0.00 sec)

configure /etc/asterisk/res_odbc.conf

We must define the DSN for Asterisk.  In the ‘/etc/asterisk’ directory locate res_odbc.conf file and replace it with my example below.

[asterisk]
enabled => yes
dsn => asterisk-connector
pre-connect => yes

The DSN in res_odbc.conf points to the DSN in ‘/etc/odbc.ini’, which in turn points to a driver configuration file(odbcinst.ini).  Please see my previous tutorials on odbc!  I have links at the top of the page and I strongly suggest you read them.  You can view DSN’s fron the Asterisk CLI,

*CLI> odbc show


ODBC DSN Settings
—————–


Name:   asterisk
DSN:    asterisk-connector


*CLI>

Configure /etc/asterisk/func_odbc.conf

The func_odbc.conf is the file where we define the SQL statement.  This could be doing a database insert or a simple read.  we can define special variables so that Asterisk can access dynamic data; without haveing to call an external AGI script.  There are so many situations this could be useful, from emergency call routing to credit card processing.

I could for instance, setup an Asterisk PBX at a insurence agency to read Caller ID from incoming calls.  Asterisk can match it to a TAPI compliant CRM.  As soon as the insurence agent picks up the phone he can see the customer file.  My example will be far more limited in scope.

/etc/asterisk/func_odbc.conf:

[TEST]
dsn=asterisk
readsql=SELECT ext from extensions WHERE extensionsID = 1

This contest defines the variable name we will call in the dial plan.  The entry ‘dsn=asterisk’ refers to the res_odbc.conf context ‘asterisk’.  The ‘readsql=’ is the juicy part of this post.  It’s the “rub”.  This section defines the variable we call in asterisk.  When we call this variable it will be named ‘${ODBC_TEST}’.

In fact you must prepend ‘${ODBC_’ to every variable you define.  So for instance we could make a new variable based on the query below,

[A-NEW-TEST]
dsn=asterisk
readsql=SELECT ext from extensions WHERE extensionsID =3

We would call this variable, ‘$ODBC_A-NEW-TEST’ in the dial plan.

configure extensions.conf for query

Add a context for out test like this your extensions.conf file,

[ODBC-test]
exten => s,1,wait(5)
exten => s,n,saydigits(${ODBC_TEST()})
exten => s,n,Dial(SIP/${ODBC_TEST()})
exten => s,n,Voicemail(${ODBC_TEST()})
exten => s,n,hangup

This context will read ${ODBC_TEST()} calls ‘extensionID’ 1 from the database(8888) and use this variable with three separate Asterisk apps.

mysql> select * from extensions;
+————–+——+
| extensionsID | ext  |
+————–+——+
|            1 | 8888 |
|            2 | 8889 |
|            3 | 8890 |
+————–+——+
3 rows in set (0.00 sec)

In this tutorial we demonstrated how to call a variable from a SQL database.  This saves much time from developing AGI and PHP for a function already builtin to Asterisk!  I used this same method to create a calling card application last weekend without a sing external script!  The SQL database doesn’t need to reside on the Asterisk; giving you another option to reduce overhead by relocating your Asterisk SQL database to another server.  I hope thid tutorial helps someone out there!  take care.

If you like this article tell Google!!









Written by mattb in: Asterisk,Asterisk Post,MySQL | Tags: , , , , , , , , , , , , , , , ,

60 Comments »

  • Maxim

    Hello,

    Great How-to, it´s just I was looking for. One question, is there any way to use a variable to pass the extension id? How can I do that?

    Thnks for your job!!

    Comment | October 16, 2009
  • mattb

    Maxim,

    Thank you for reading savelono.com. I may be misreading your question. By extension ID I’m assuming you are referring to my SQL table in the example above.

    Yeah there are a couple ways to do this. The easiest is to change the MySQL call to something like this:

    func_odbc.conf
    ————————————–

    [A-NEW-TEST]
    dsn=asterisk
    readsql=SELECT ext from extensions WHERE extensionsID =3

    to

    [EXID]
    dsn=asterisk
    readsql=SELECT extensionsID from extensions WHERE ext = ‘${ARG1}’

    ———————————–

    define $ARG1 as the number being dialed in the extensions.conf. Did I answer your question?

    Comment | October 17, 2009
  • Maxim

    Yes, that´s answer my question!

    thanks a lot!

    Comment | October 20, 2009
  • Is there any reason you wouldn’t just use MYSQL(Connect _server_ _usernemr_ _password_ _database_) ? Why go through ODBC when Asterisk can talk to MySQL directly…?

    Comment | October 21, 2009
  • mattb

    John,

    I don’t understand your alternative solution. There is no MySQL_connect function built directly into Asterisk that I know of. The Asterisk Addons package has a modules for connecting to populate CDR data and has support for a few other apps.

    /etc/asterisk/func_odbc.conf is special because it lets you predefine variables as a MySQL query or write. Then you can call them in extensions.conf anytime you want. Like dialing from list in a database,

    /etc/asterisk/extensions.conf:

    exten => 3344,1,Dial(SIP/${ODBC_MY_QUERY()},30)
    exten => 3344,n,hangup()

    Comment | October 21, 2009
  • http://www.voip-info.org/wiki/view/Asterisk+cmd+MYSQL

    Yes it’s part of the addons, but isn’t this a much more direct and simplified way to handle connecting to mysql…?

    -John

    Comment | October 21, 2009
  • for your perusal:

    exten => _.,1,Playback(letters/a)
    exten => _.,n,MYSQL(Connect connid ***server*** ***username*** ***password*** test_db)
    exten => _.,n,GotoIf($[“${MYSQL_STATUS}” = “-1”]?lbl_vdp-inbound_0:)
    exten => _.,n,Playback(letters/x)
    exten => _.,n,MYSQL(Query result ${connid} INSERT INTO test_db.test SET number = 60;)
    exten => _.,n,GotoIf($[“${MYSQL_STATUS}” = “-1”]?lbl_vdp-inbound_1:)
    exten => _.,n,Playback(letters/c)
    exten => _.,n(lbl_vdp-inbound_2),MYSQL(Disconnect ${connid})
    exten => _.,n,Hangup()
    exten => _.,n(lbl_vdp-inbound_1),Playback(letters/d)
    exten => _.,n,Goto(lbl_vdp-inbound_2)
    exten => _.,n(lbl_vdp-inbound_0),Playback(letters/e)
    exten => _.,n,Goto(lbl_vdp-inbound_2)

    Comment | October 22, 2009
  • mattb

    That’s pretty cool, I have never connected to MySQL using that command before. Thanks for the tip! I will try it out.

    I think the difference with ODBC is that it is a generic driver that can connect to many different SQL’s. Not just PostGre and MySQL.

    Comment | October 23, 2009
  • oh I’m sure that’s the case Matt…but think about it this way, since Asterisk generally runs on some sort of a LAMP stack, it makes sense to have the MySQL capability built right in. Sure ODBC gives you the flexibility if required, but I’d have to guess the great majority of Asterisk developers lean towards MySQL and they are able to connect in a much easier way without a lot of under the hood tweaking.

    I know this capability is proving to be a godsend for the project I’m currently working on.

    Keep in mind though ODBC doesn’t just limit you to SQL’s either…There are ODBC drivers for M$ Excel, text files, all sorts of stuff that isn’t even remotely SQL based….

    Take Care,

    -John

    Comment | October 23, 2009
  • Also, a quick word of caution…someone might easily find this thread on a search engine and surf away mistakenly thinking that by using these instructions they can connect to any sort of SQL or other data source by simply using these instructions…

    ODBC is a standard way of allowing ***some data*** to be connected to by ***some program*** (Open DataBase Connectivity). In order for a particular application to talk to whatever data you have in mind by using ODBC, you must use the particular ODBC driver for your particular data. For instance, in the case of the above MySQL instructions, libodbcpsql.so is the ODBC driver that will allow Linux to talk to MySQL using ODBC. If for some reason you need to connect to, say, M$ Access, you’d need a Linux ODBC driver for Access.

    This post isn’t for you specifically Matt, it’s more for the other visitors to your site, in order to head off any possible confusion.

    Cheers

    Comment | October 23, 2009
  • mattb

    Actually this post is very much about ODBC/Asterisk! However, I appriciate your clarification. I have two other articles up that are much more general. Thank you so much for reading and posting feedback.

    Comment | October 24, 2009
  • brad

    Perhaps this is somewhat off point, but you mentioned in this post that you’ve “used external AGI scripts to run database queries to return values to Asterisk.”

    Could you clarify how to do this? How do you return a value from an AGI script back to the dialplan?

    Email me if you like.

    Thanks!

    Comment | December 31, 2009
  • mattb

    It’s a little bit long to explain in this post, but essentially asterisk excepts standard in/out via AGI(Asterisk Gateway Interface). The interface provides a list of pre-built commands that can be fed to Asterisk via standard in/out.

    The AGI script may be virtually any language – PERL and PHP being the most common. Although BASH or any Linux compatible scripting language could be used. ODBC is easy because you can define SQL commands as Asterisk Dial plan variables.

    For more information on AGI I suggest ‘Asterisk – the future of telephoney’ by O’Reilly publications.

    Comment | December 31, 2009
  • mattb

    If you need help with custom Asterisk/Database stuff contact VoiceIP Solutions(www.voiceipsolutions.com). good work for a good price, located in Seattle.

    Comment | December 31, 2009
  • Mirza

    Hi,

    Can anyone tell me that this would work with asterisk 1.4.x since i am using asterisk 1.4.26.2 and its giving following error

    [Jan 29 12:55:45] ERROR[14228]: pbx.c:1550 ast_func_read: Function ODBC_TEST not registered
    Although I can connect to MSSQL using your topic “http://www.savelono.com/linux/how-to-configure-linux-odbc-connections-for-ms-sql.html”

    Comment | January 29, 2010
  • mattb

    Yeah this is an easy one. When you compile Asterisk(after you run the ./configure script), type ‘make menuconfig’. A list of modules/apps/codecs should pop up that you can choose from. Check the func_odbc box(under dial plan functions) and it will be built into Asterisk. Unfortunately, I have never been able to compile that option in Asterisk 1.4.X. The option appears for ‘func_odbc’, but it is greyed out. Some dependency error I’ve never been able to solve.

    The best option is to compile Asterisk 1.6 with ODBC support. Remember, you still have to select the build option for ‘func_odbc’ from ‘make menuconfig’ build menu. Good luck!

    Comment | February 5, 2010
  • Mirza

    Hi,

    I compiled 1.6 which works perfectly. I guess there is some problem in 1.4
    Anyways thanks a lot,you are lifesaver 😛

    Comment | February 12, 2010
  • Giridhar

    Dear experts

    I followed all the instruction above to connect my SQL server 2005 with asterisk 1.6.
    I created a SQl statement in func_odbc.conf and called the same from Dial plan.

    during runtime my dial plan is able to play all prompts specified but i dont see any trace or logs where the dial plan is executing the sql.

    Can u please guide me. I dont have the configure option in Asterisk since i did a Yum install from Asterisk.org

    Thanks

    Giridhar

    Comment | March 5, 2010
  • mattb

    Need more information. What is the status of odbc from the Asterisk Console? Is the module being loaded. Does the YUM package have func_odbc built in?

    *CLI> odbc show

    ODBC DSN Settings
    —————–

    Name: callingcard
    DSN: callingcard-connector
    Pooled: No
    Connected: Yes

    Name: asterisk
    DSN: asterisk-connector

    Comment | March 7, 2010
  • mattb

    One other thing. From the console you can see registered functions. The ODBC functions you created should show up in the list. Below you can see that asterisk shows the custom function, ‘ODBC_TEST’. This can be called from the dial plan as ${ODBC_TEST}.

    *CLI> core show functions

    ODBC_TEST ODBC_TEST([…[,]]) output omitted…

    output omitted…

    Comment | March 7, 2010
  • Alberto

    really good post, I would like you to give me the idea about how would you do (general flow) the next scenario.
    someone call, gets in the ivr, query to mysql “there are 5 doctor availables … digit 1 to doctor “x” (so on) , the person calling digits hospital ID number in the mysql database, and then asterisk return a “you have the number “n” to consult with doctor “m” from the database.
    Also the database is in a web server.
    I would like to know also if everything should be realtime.
    Thanks

    Comment | May 18, 2010
  • mattb

    I believe in doing as much work in the extensions.conf file as possible. But for certain things (like handling large amounts of data/users) a database is better, easier, faster! If you need Asterisk consulting look up http://www.voiceipsolutions.com and tell them Lono sent you!

    Comment | May 19, 2010
  • Navnendu

    not getting any proper output on the following commands…..

    media9*CLI> odbc show

    media9*CLI> dnsmgr refresh
    DNS Manager is disabled.

    media9*CLI> core show config mappings
    No config mappings found.

    plz help me how to remove the errors and find the correct output and also help me to enable, the disabled DNS…

    Thanx

    Comment | July 7, 2010
  • andy

    i needed to just update certain tables with specific item on a regular basis. i find this somewhat convoluted, so i’ve resorted to this in the dialplan

    system echo ” | isql -v asterisk-connector

    Comment | July 7, 2010
  • mattb

    Navendu,

    Check to make sure you have the odbc module compiled and loading. Did you install asterisk from source or rpm package?

    -Matt

    Comment | July 12, 2010
  • Hey

    I’ve tried to use this method as a sort of pin code app for Asterisk. Eg, User puts in a 3-4 digit pincode before he’s allowed outgoing calls. Then in the dialplan I do Dial(SIP/${EXTEN:5}) (It logs the prefix, and thats what I need to match up).

    Although, if I use this dial rule, it does not work:
    exten => _${ODBC_TEST()}X.,1,Dial(SIP/${EXTEN:5})

    On my test box, I get this output from asterisk when I try it:
    NOTICE[6886]: chan_sip.c:20039 handle_request_invite: Call from ‘101’ to extension ‘*123*100’ rejected because extension not found

    What am I doing wrong here? Is there another way I can verify if its reading from the database?

    Comment | August 10, 2010
  • I actually stumbled onto “Authenticate” last night http://www.voip-info.org/tiki-index.php?page=Asterisk+cmd+Authenticate and have it half working with your example:
    func_odbc.conf:
    [TEST]
    dsn=asterisk
    readsql=SELECT ext from extensions

    extensions.conf
    exten => _X.,1,Answer
    exten => _X.,n,Authenticate(${ODBC_TEST()},a)
    exten => _X.,n,Dial(SIP/${EXTEN})

    When I try and make a call, it successfully reads the database, but it reads the first entry and nothing more. From what I understand, it should get a newline character from the db, but I guess its not getting it, thus being stuck on the first entry.
    I’m no guru, so I’m figuring it all out slowly. So any help would be appreciated 😉

    Comment | August 11, 2010
  • mattb

    Try the command without the ‘$ODBC_TEST’ part. Instead use the value you would expect ‘$ODBC_TEST’ to have. See if Authenticate performs how you expect. Also be careful what you read on voip-info.org becaue Asterisk 1.6.x has depreceated many past applications. Don’t get me wrong… voip-info.org is awsome! But Asterisk is moving faster than the wiki sometimes!

    Comment | August 11, 2010
  • Joseph

    I have followed your guide exactly and have everything configured. “isql -v asterisk-connector” works just fine!

    however, something is still wrong. when at the asterisk CLI and I type “odbc show” i get the following error: ” No such command ‘odbc show’ ”

    also, when I try to use the ODBC-test sample here in extensions.conf I get the following error output:

    — Executing [122@microsoft:1] Wait(“SIP/5000-00000002”, “5”) in new stack
    [Aug 16 16:26:51] ERROR[878]: pbx.c:1566 ast_func_read: Function ODBC_TEST not registered
    — Executing [122@microsoft:2] SayDigits(“SIP/5000-00000002”, “”) in new stack
    [Aug 16 16:26:51] ERROR[878]: pbx.c:1566 ast_func_read: Function ODBC_TEST not registered
    — Executing [122@microsoft:3] Dial(“SIP/5000-00000002”, “SIP/”) in new stack
    [Aug 16 16:26:51] WARNING[878]: chan_sip.c:3087 create_addr: No such host:
    [Aug 16 16:26:51] WARNING[878]: app_dial.c:1298 dial_exec_full: Unable to create channel of type ‘SIP’ (cause 20 – Unknown)
    == Everyone is busy/congested at this time (1:0/0/1)
    [Aug 16 16:26:51] ERROR[878]: pbx.c:1566 ast_func_read: Function ODBC_TEST not registered
    — Executing [122@microsoft:4] VoiceMail(“SIP/5000-00000002”, “”) in new stack
    — Playing ‘vm-whichbox’ (language ‘en’)
    == Spawn extension (microsoft, 122, 4) exited non-zero on ‘SIP/5000-00000002’

    Comment | August 16, 2010
  • mattb

    Joseph,

    I would likely guess either you are not Pre-loading ODBC modules, in /etc/asterisk/modules.conf or ODBC support is not compiled into Asterisk. The fact that isql works only means that you have ODBC installed correctly.

    Did uoi compile ODBC support into Asterisk with the ‘make menuconfig’ command?

    Comment | August 16, 2010
  • Joseph

    thanks for your quick response. I just ran the “make menuconfig” command and found the following are not installed:

    cdr_odbc, func_odbc, res_config_odbc, res_odbc

    Depends on: unixodbc(E), ltdl(E)

    all of these modules have ‘XXX’ instead of ‘[ ]’ beside their name so I cant build them into the CLI. which surprises me as I thought I have unixodbc compiled to use isql?

    im still new to asterisk, how can you build modules for menuconfig and use the “preload => modules” command in /etc/asterisk/modules.conf so they will run at the CLI?

    Comment | August 17, 2010
  • mattb

    First thing use Asterisk 1.6.X, and get the required devel packages for ODBC. Run “./configure” and “make menu config” again. It may take you a while, but they are there, in your Redhat or Debian OS repository. Thanks for reading!

    Comment | August 17, 2010
  • fernando

    hello friend

    I saw your article about the installation of freetds and unixOBDC

    I have debian lenny 5.0 32 bits on another pc I have windows SQL server 2000 with mixed connection, turn off the firewall.

    install debian

    apt-get install libtool bison autotools-dev g + + build-essential unixodbc-dev tcsh tdsodbc no problem

    freetds-stable.tgz.gz download version 0.82

    . / Configure – prefix = / usr – sysconfdir = / etc – with-unixodbc = / usr – with-tdsver = 8.0

    ODBCINST.INI

    [FreeTDS]
    Description = FreeTDS unixODBC Driver
    Driver = /var/lib/libtdsodbc.so
    Setup = /var/lib/libtdsodbc.so

    when I run

    not connected

    / Etc # TSQL-S 192.168.1.31-p 1433-U guest-P 1234
    locale is “es_VE.UTF-8”
    locale charset is “UTF-8”
    Msg 20009, Level 9, State -1, Server OpenClient, Line -1
    Unable to connect: Adaptive Server is unavailable or does not exist
    There Was a problem connecting to the server

    please i need a help thanks

    Comment | September 17, 2010
  • mattb

    MS SQL adds another set of configuration steps. First of you may need to grant special permissions on your windows machine to allow a remote connection. I encountered this problem at a customer site one time while doing a bid for some CRM integration work with Asterisk.

    I haven’t worked much on that platform since Windows 2000 Server, but I’d open your Microsoft Mangament Console and check the SQL logs. See if a there is even an attempt hitting the SQL server. Also try isql.

    [root@mattop1 ~]# isql -v odbc-test Your-Username Your-Password

    Read my ODBC MS-SQL tutorial as well if you have not.

    How to configure Linux ODBC for MS-SQL

    Comment | September 19, 2010
  • fernando

    thanks for your time MattB

    I have already a month trying to install this I have seen your stuff but nothing to get it to work, I have not found a tutorial or video tutorial to help me with this by chance not have a virtual machine setup where I can see you are doing

    I checked all I have a windows xp with sql server 2000 and a machine with debian leny asterisk, I’ve also tried to make the connection with mysql and I can not figure it out.

    thanks for your support, any other ideas??

    Comment | September 27, 2010
  • mattb

    Fernando,

    If you want help, you need to provide a lot more information. Like logs and ouput from isql. Windows SQL security policy is the most common problem reported. My tutorial does not cover that.

    When you use isql to test the connection, you should check the Windows logs. Both security and application need to be checked.

    -Matt

    Comment | September 27, 2010
  • fernando

    Matt

    again thank you very much for your help

    I could connect to the MSSQL 2000 database

    had many errors in windows and also in linux

    to the end I could take some time and solve the problem

    I have installed an IVR with asterisk 1.6 on debian leny 5 with queries to a database MSSQL 2000 and everything works perfect

    good day

    Ing fernando

    Comment | September 30, 2010
  • Allthe

    Hello,

    I need my Asterisk to communicate with a web service, in other words, Asterisk asks a question to the Web Service and Web Service returns the answer.

    Is it possible?

    Thanks.

    Comment | December 15, 2010
  • mattb

    It’s not only possible it extremly easy with PHP AGI script, but also many other methods. We do that kind of stuff all the time at VoiceIP Solutions. You can also use curl to grab content off a web site and read it back over the phone system. all kinds of awsome stuff!

    To answer you question more specifically what kind of web service are you referring to and what do you want Asterisk to do with it?

    Comment | December 16, 2010
  • Manjunath

    Hi,

    I have PHP sample code which will fetch the data from the MYSQL database through ODBC driver on Linux(CentOS) machine and Apache as server.

    I have created DSN and same able to connect through following command isql -v DSNNAME,But when i use same DSN through PHP code i am getting “No tuples available at this result index” due to which unable to read the data from database through APACHE-PHP-ODBC-MYSQL configuration.

    If anyone provides the solution,It will more helpful for me to proceed further.

    Below are my sample code and other details,Please correct if anything wrong on below configuration details-
    Below is sample PHP code:

    Below is odbc.ini file:

    [DSN]
    Description = MySQL ODBC Database
    DRIVER = MySQL
    TraceFile = /tmp/odbcerr.log
    SERVER = 127.0.0.1
    PORT = 3306
    USER = username
    PASSWORD = password
    DATABASE = database
    OPTION = 3
    SOCKET = /var/lib/mysql/mysql.sock

    Below is odbcinst.ini file:

    [MySQL]
    Description = ODBC for MySQL
    Driver = /usr/lib/libmyodbc5.so
    Setup = /usr/lib/libodbcmyS.so
    FileUsage = 1
    UsageCount = 3

    Please someone help me to sort out this problem…

    Comment | March 17, 2011
  • mattb

    Manjunath,

    Thank you for visiting savelono.com. Do you have to use ODBC as a medium for connecting? There are many functions in PHP for directly connecting to MySQL. ODBC provides a generic API for SQL. If porting to different datbases becomes a problem create a page of custom functions that can serve as an abstraction layer between your site and the database. That way you don’t need to dig through your code later. I hope that makes sense.

    example of built-in MySQL support without ODBC:

    if (!$connect)
    {
    die(‘Could not connect: ‘ . mysql_error()) ;
    }

    $settings_db_found = mysql_select_db($database, $connect) ;

    if ($settings_db_found) {

    $profile_result = mysql_query($sql_select_profile) ;

    }

    else {

    print “Database NOT Found “;

    mysql_close($connect);

    //===================================

    /* Pulling data with ‘mysql_fetch_assoc()’ */

    while ($profile_row = mysql_fetch_assoc($profile_result)) {

    $profile_host = $profile_row[‘host’] ;
    $profile_nat = $profile_row[‘nat’] ;
    $profile_type = $profile_row[‘type’] ;
    $profile_canreinvite = $profile_row[‘canreinvite’] ;
    $profile_context = $profile_row[‘context’] ;

    etc…

    Comment | March 23, 2011
  • Angie

    hello john, please help I need is the following: Enter a phone call to my asterisk central and need to know to which operator belongs, I have a mysql db where are the cell numbers with their respective operator, need to pass that call to another platform with the number of operator code + my code+ cell phone number .. you can help me?

    Comment | September 14, 2011
  • mattb

    I don’t quite understand what you are asking? You need help making a query from an external server before proceeding to the next priority?

    Comment | September 30, 2011
  • deo

    hi,,, i do have a problem regarding my connection with odbc and asterisk 1.4. It seems that i can connect with this command, echo “select 1” | isql -v asterisk-connector but when i try to show the ODBC DSN Settings using odbc show in asterisk cli, an error occured saying ‘No such command found’. How do i solve the problem? Thanks in advance.

    Comment | January 18, 2012
  • mattb

    It sounds like you are connected, have you tried some other SQL commands?

    Comment | January 19, 2012
  • deo

    @mattb

    hhmm i think no, but in my script in php i have a query to display a particular data.. i was able to do that. so it seems i am connected. but why is it that i wasnt able to display the dsn settings in asterisk cli?… still ‘No such command found’. Thanks.

    Comment | January 20, 2012
  • Arham Ali

    Is there any tutorial where i can learn this kind of scrip.

    [ODBC-test]
    exten => s,1,wait(5)
    exten => s,n,saydigits(${ODBC_TEST()})
    exten => s,n,Dial(SIP/${ODBC_TEST()})
    exten => s,n,Voicemail(${ODBC_TEST()})
    exten => s,n,hangup

    I also found

    sipusers => mysql,asterisk,users ; SIP user
    sippeers => mysql,asterisk,users ; SIP peers
    extensions => mysql,asterisk,extensions ; SIP extensions
    voicemail => mysql,asterisk,voicemails ; SIP voicemailboxes
    queues => mysql,asterisk,queues ; SIP queue
    queue_members => mysql,asterisk,queue_members ; SIP queue members

    Is this the alternate for it ?

    Comment | March 3, 2012
  • mattb

    voip-info.org is a great resource to learn extensions.conf dial plan syntax. Also Asterisk has many examples in the default configs generated from ‘make samples’.

    The second set of code you sent is from the extconfig.conf, which determines how Asterisk retrieves configuration. For instance asterisk can retrieve files ‘/etc/asterisk/’ folder or SQL.

    extconfig.conf:

    sipusers => mysql,asterisk,users ; SIP user
    sippeers => mysql,asterisk,users ; SIP peers
    extensions => mysql,asterisk,extensions ; SIP extensions
    voicemail => mysql,asterisk,voicemails ; SIP voicemailboxes
    queues => mysql,asterisk,queues ; SIP queue
    queue_members => mysql,asterisk,queue_members ; SIP queue members

    Comment | March 8, 2012
  • Jason

    MySQL addon is being deprecated in 1.8.11. If you really want to use a generic one you could do:

    [Query]
    dsn= asterisk-connector
    readsql${ARG1}

    and in the dialplan use:
    exten => s,x,Set(${ODBC_Query(SELECT field FROM table WHERE field=match)})

    Thanks for the explanation

    I just went through this in my upgrade to 1.8.11 and found this very helpful.

    Comment | April 3, 2012
  • req.ast

    So, one question is ODBC connect more fast and efficient than app_mysql to access to remote mysql server to make 10 querys and 3 inserts for call??

    is exten = X,n,ODBC_XXX(….)

    more fast and efficient than open mysql query

    exten => _.,n,MYSQL(Connect connid ***server*** ***username*** ***password*** test_db)

    and close later??

    close

    and doing this from AGI like python or php??

    what do you thing is the faster and efficent mode? ODBC, app_mysql,AGI???

    thanks for your experiences guys!

    Comment | June 18, 2012
  • mattb

    Excellent question. I do not believe that ODBC is faster, but it is SQL platform neutral. So you can easily interface with any DB that has a ODBC driver. Like Microsoft SQL server or Oracle. This makes your code portable. Using AGI for pulling single values from a database is a lot of extra work. The exception being is if you have to manipulate that data between the time it passes from Asterisk to the database. Even then completing the transaction with ODBC driver is better.

    I like as much of the code as possible in my dial plan because(I would assume) engineers write in varying scripting languages. I don’t write python; I’m into PHP… but my friend at work does like Python… See the problem? If we keep most of the scripting in the dial plan code everyone can support it more easily. I hope this answers your question. This is just my view on ‘best practices’.

    Comment | June 18, 2012
  • req.ast

    Thanks for your reply, I think you are right, the support with ODBC is mor easily.

    I make the question because I test an enviroment with query/update by app_mysql way:

    exten => _.,n,MYSQL(Connect connid ***server*** ***username*** ***password*** test_db)

    few times during a call and later close connection. And some times asterisk can´t connect remote SQL, and with ODBC I test around 500 sip channels with query ODBC way without problems ( with sipp test tool).

    So I will share the opinion that is more ineffective to asterisk open and close a lot of mysql connections in dialplan vs use ODBC connector always ready.

    Comment | June 19, 2012
  • Hi…thanks to your article i decided to rewrite an old asterisk pbx not mine and substitute phpagi with func_odbc..or so i try..
    i could check in mysql for data passed via phone by {EXTEN} variable
    [IVRUSERLOOKUP]
    dsn=mysql1
    readsql= SELECT * FROM dtLettura WHERE dtCodUtente=’${SQL_ESC(${ARG1})}’ order by dtDal DESC
    but i’m not able to UPDATE data in …i would like to pass two variables to func_odbc read via phone, one for userid and one for water consumption..
    could you explain me a way to do so?

    Comment | August 3, 2012
  • mattb

    I’m sorry, I do not understand your question. Are you asking how to define a variable in Asterisk? Like a user pressing digits ‘1234’ then equating that to ‘${PRESSED_DIGITS}’ ; for the purpose of querying a SQL database?

    Comment | August 27, 2012
  • emilian

    HI!
    CLI=> odbc show
    No such command “odbc show”.
    Help Please I installed Astrerisk from source.

    Comment | August 27, 2012
  • mattb

    Make sure you have the correct build options set. Did you issue the ‘make menuconfig’ command?

    Comment | August 27, 2012
  • emilian

    Yes I issued make menuconfig command but it still doesn’t recognize odbc command.

    Comment | August 27, 2012
  • I have set up the database. I would like to redirect the user based on the call input. Basically I have music on hold that is a looped message “Please enter your … followed by hash” basically extension number and password i.e extpasss so ext 101 and pass 1234 they would dial 1011234.

    With that said I run a php query on he first 3 and last 4 digits and if both return true redirect to the extension else redirect to another extension of a switchboard

    I need then to be able to retrieve the caller key tones. How do i do that?

    Comment | January 9, 2013
  • mattb

    Sorry for late reply. This is a two step process. Create a variable of key presses with the pin() application! It works. I made a Calling card application the same way! good luck.

    Comment | September 9, 2013
  • Levis

    Thanks for your article, I’ve an asterisk version 11, I’m writing the CDR data from calls to a Mysql Table using res_odbc.conf and cdr_adaptive_odbc.so, I’m writing the defaults CDR fields to the Mysql Table, My qustions, Can I change the name fields (for example change “SRC” field name for “source” and write to mysql field call “source”) and add new fields, For example every extension would have a field with a amount of money to spend in calls, Every time a extenions make a call I’ve to call calculate how much that call cost and subtract from amount of the extension and then write to the mysql table. Can I do this with res_odbc.conf and cdr_adaptive_odbc.so or do I have to use AGI?

    Comment | April 7, 2014

RSS feed for comments on this post. TrackBack URL

Leave a comment