System information

A Gentle Introduction to func_odbc
The very first use of func_odbc, which occurred while its author was still writing it, is
also a good introduction to its use. A customer of one of the module’s authors noted
that some people calling into his switch had figured out a way to make free calls with
his system. While his eventual intent was to change his dialplan to avoid those prob-
lems, he needed to blacklist certain caller IDs in the meantime, and the database he
wanted to use for this was a Microsoft SQL Server database. With a few exceptions,
this is the actual dialplan
‖
:
[span3pri]
exten => _50054XX,1,NoOp()
same => n,Set(CDR(accountcode)=pricall)
same => n,GotoIf($[${ODBC_ANIBLOCK(${CALLERID(number)})}]?busy)
same => n(dial),Dial(DAHDI/G1/${EXTEN})
same => n(busy),Busy(10)
same => n,Hangup
This dialplan, in a nutshell, passes through all calls to another system for routing pur-
poses, except those calls whose caller IDs are in a blacklist. The calls coming into this
system used a block of 100 7-digit DIDs. There is a mystery function in this dialplan,
though: ODBC_ANIBLOCK(). This function is defined in another configuration file,
func_odbc.conf, at runtime:
[ANIBLOCK]
dsn=telesys
readsql=SELECT IF(COUNT(1)>0, 1, 0) FROM Aniblock WHERE NUMBER='${ARG1}'
So, your ODBC_ANIBLOCK()
#
connects to a listing in res_odbc.conf named telesys and
selects a count of records that have the NUMBER specified by the argument, which is,
referring to our dialplan above, the caller ID. Nominally, this function should return
either a 1 (indicating the caller ID exists in the Aniblock table) or a 0 (if it does not).
This value also evaluates directly to true or false, which means we don’t need to use an
expression in our dialplan to complicate the logic.
Getting Funky with func_odbc: Hot-Desking
The func_odbc dialplan function is arguably the coolest and most powerful dialplan
function in Asterisk. It allows you to create and use fairly simple dialplan functions that
retrieve and use information from databases directly in the dialplan. There are all kinds
‖ This system is unfortunately no longer in service. Thus, any changes have been made for the sake of simplicity,
not to conceal the business for which it was designed.
#We’re using the IF() SQL function to make sure we return a value of 0 or 1. This works on MySQL 5.1 or
later. If it does not work on your SQL installation, you could also check the returned result in the dialplan
using the IF() function there.
354 | Chapter 16: Relational Database Integration