System information

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})})

same => 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:

readsql=SELECT pin,status FROM ast_hotdesk WHERE extension = '${E}'

we could have used 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})})

Using ARRAY() is handy any time you might get comma-separated values back and want

to assign the values to separate variables, such as with CURL().

So, in the first two lines of the following block of code, we are passing the value

status and the value contained in the ${E} variable (e.g., 1101) to the HOTDESK_INFO()

function. The two values are then replaced in the SQL statement with ${ARG1} and $

{ARG2}, respectively, and the SQL statement is executed. Finally the value returned is

assigned to the ${E}_STATUS channel variable.

OK, let’s finish writing the pattern-match extension now:

same => n,Set(${E}_STATUS=${HOTDESK_INFO(status,${E})})

same => n,Set(${E}_PIN=${HOTDESK_INFO(pin,${E})})

same => n,GotoIf($[${ODBCROWS} < 0]?invalid_user,1)

; check if ${E}_STATUS is NULL

same => n,GotoIf($[${${E}_STATUS} = 1]?logout,1:login,1)

After assigning the value of the status column to the ${E}_STATUS variable (if the user

dials extension 1101, the variable name will be 1101_STATUS), we check if we’ve received

a value back from the database (error checking) using the ${ODBCROWS} channel variable.

The last row in the block checks the status of the phone and, if the agent is currently

logged in, logs him off. If the agent is not already logged in, it will go to extension

362 | Chapter 16: Relational Database Integration