jump to navigation

Basic func_odbc starting point May 30, 2007

Posted by epower in func_odbc.
trackback

As I’ve already mentioned, I needed to solve some problems in Asterisk and the best method I’ve found was using func_odbc. Getting started with it wasn’t too tricky but it does have it’s quirks and that’s what I’ll be talking about today.

I’ll assume you have a working and tested Asterisk installation with a Asterisk Realtime ODBC connection for one or more configuration files (in my case, sip.conf got the Realtime treatment). My res_odbc.conf looks something like this:

[asterisk]
enabled => yes
dsn => dbserverdsn
username => username
password => password
pre-connect => yes

Next I set up the queries that I want to run from the dial plan

[VOTESFORCANDIDATE]
dsn=asterisk
read=select votes from poll where candidate_id = '${SQL_ESC(${ARG1})}'
write=update poll set votes = votes + 1 where candidate_id = '${SQL_ESC(${ARG1})}'

This is a fairly contrived example where there’s a table called poll with the ids of candidates and and a count of votes for each candidate. One of the key characteristics of func_odbc is that the functions are like property get/set methods from other languages. Setting the value of a variable to the result of the function executes the ‘read’ procedure whereas setting the value of the function executes the ‘write’ procedure. You’ll get a better idea from the example dial plan below.

[default]
; dial a number for the candidate (e.g. 901) to vote for them
exten => _9XX,1,Answer()
exten => _9XX,n,Set(${ODBC_VOTEFORCANDIDATE(${EXTEN})})
; responds with the total votes
exten => _9XX,n,Set(votes=${ODBC_VOTEFORCANDIDATE(${EXTEN})})
exten => _9XX,n,SayNumber(${votes})
exten => _9XX,n,HangUp()

So there you have it…anything from the General Election to ‘Pop Idol’ can run on this now (but not securely).

Comments»

1. David Walsh - June 8, 2007

Excellent Stuff

Reply
2. gaba - December 28, 2007

great! i receive an error:

Dec 28 22:33:06 ERROR[2429]: func_odbc.c:267 acf_odbc_read: No such DSN registered: dinfw (check res_odbc.conf)

so I’m still trying to understand res_odbc.conf

Reply
3. bbarrett - January 5, 2008

@Gaba: you’ll probably want an entry in res_obdc.conf like

[dinfw]
enabled => yes
dsn => …
username => …
password => …
pre-connect => yes

Reply
4. Even - September 26, 2008

Is there ANY variable or ANYTHING else one can check to find the STATUS of the func_odbc command.i.e. if it FAILS or a mySQL ERRO was produced HOW can you tell ?

Reply
5. epower - September 26, 2008

@Even: If a func_odbc read command doesn’t work, it’ll set the returned value to NULL. Use the ISNULL function to check if nothing was returned. For example, if a MySQL error was produced, there should be a log message and a NULL return value.

Reply