Page 307 - Asterisk™: The Future of Telephony

Asterisk Linux
func_odbc.conf res_odbc.conf /etc/odbc/ini database
[INFO]
prefix=hotdesk
dsn=asterisk
readsql=…
[asterisk]
enabled=>yes
dsn=>PostgreSQL-asterisk [PostgreSQL-asterisk]
pre-connect=>yes Description =Aserisk pgsql connection
Servername =localhost

Figure 12-1. Relationships between func_odbc.conf, res_odbc.conf, /etc/odbc.ini (unixODBC), and
the database connection

Be sure that your data is unique enough that you only get a single row
back. If more than one row is returned, Asterisk will see only the first
row returned. With PostgreSQL, you could add a LIMIT 1 to the end
of your SQL statement to limit a single row being returned, but this is
not a good practice to rely on. A little further into this section we’ll see
how we can use the LIMIT and OFFSET PostgreSQL functions to loop
through multiple rows of data!

After the SQL statement is executed, the value returned (if any) is assigned to the
RETURNED_VALUE channel variable.

Using the ARRAY() function

In our example, we are utilizing two separate database calls and assigning those values
to a pair of channel variables, (${E}_STATUS and ${E}_PIN). This was done to simplify
the example:
exten => _110[1-5],n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})
exten => _110[1-5],n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})
As an alternative, we could have returned multiple columns and saved them to separate
variables utilizing the ARRAY() dialplan function. If we had defined our SQL statement
in the func_odbc.conf file like so:
read=SELECT pin,status FROM ast_hotdesk WHERE extension = '${E}'
then we could use the ARRAY() function to save each column of information for the row
to its own variable with a single call to the database:
exten => _110[1-5],n,Set(ARRAY(${E}_PIN,${E}_STATUS)=${HOTDESK_INFO(${E})})

Getting Funky with func_odbc: Hot-Desking | 279