Page 306 - Asterisk™: The Future of Telephony

our pattern match of _110[1-5]. You could just as easily make this less restrictive by
using _11XX (allowing 1100 through 1199). This extension uses func_odbc to perform
a lookup with the HOTDESK_INFO() dialplan function (which we will be creating shortly).
This custom function (which we define in the func_odbc.conf file) performs an SQL
statement and returns whatever is retrieved from the database.
We would define the new function HOTDESK_INFO() in func_odbc.conf like so:
[INFO]
prefix=HOTDESK
dsn=asterisk
read=SELECT ${ARG1} FROM ast_hotdesk WHERE extension = '${ARG2}'
That’s a lot of stuff in just a few lines. Let’s quickly cover them before we move on.
First of all, the prefix is optional. If you don’t configure the prefix, then Asterisk adds
“ODBC” to the name of the function (in this case INFO), which means this function
would become ODBC_INFO(). This is not very descriptive of what the function is doing,
so it can be helpful to assign a prefix that helps to relate your ODBC functions to the
task they are performing. In this case we chose HOTDESK, which means that this
custom function will be named HOTDESK_INFO.
The dsn attribute tells Asterisk which connection to use from res_odbc.conf. Since sev-
eral database connections could be configured in res_odbc.conf, we specify which one
to use here. In Figure 12-1, we show the relationship between the various file config-
urations and how they reference down the chain to connect to the database.
We then define our SQL statement with the read attribute. Dialplan functions have two
different formats that they can be called with: one for retrieving information, and one
for setting information. The read attribute is used when we call the HOTDESK_INFO()
function with the retrieve format (and we could execute a separate SQL statement with
the write attribute; we’ll discuss the format for the write attribute a little bit later in
this chapter).
Reading values from this function would take the format in the dialplan like so:

exten => s,n,Set(RETURNED_VALUE=${HOTDESK_INFO(status,1101)})
This would return the value located in the database within the status column where
the extension column equals 1101. The status and 1101 we pass to the
HOTDESK_INFO() function are then placed into the SQL statement we assigned to the
read attribute, available as ${ARG1} and ${ARG2}, respectively. If we had passed a third
option, this would have been available as ${ARG3}.

278 | Chapter 12: Relational Database Integration