System information

A simple example follows. Suppose we have the following func_odbc.conf:
[ALL_AVAIL_EXTENS]
prefix=GET
dsn=asterisk-connector
mode=multirow
readsql=SELECT extension FROM ast_hotdesk WHERE status = '${ARG1}'
and a dialplan in extensions.conf that looks something like this:
[multirow_example]
exten => start,1,Verbose(1,Looping example)
same => n,Set(ODBC_ID=${GET_ALL_AVAIL_EXTENS(1)})
same => n,GotoIf($[${ODBCROWS} < 1]?no_rows,1)
same => n,Set(COUNTER=1)
same => n,While($[${COUNTER} <= ${ODBCROWS}])
same => n,Set(AVAIL_EXTEN_${COUNTER}=${ODBC_FETCH(${ODBC_ID})})
same => n,Set(COUNTER=$[${COUNTER + 1])
same => n,EndWhile()
same => n,ODBCFinish()
exten => no_rows,1,Verbose(1,No rows returned)
same => n,Playback(silence/1&invalid)
same => n,Hangup()
The ODBC_FETCH() function will essentially treat the information as a stack, and each
call to it with the passed ODBC_ID will pop the next row of information off the stack. We
also have the option of using the ODBC_FETCH_STATUS channel variable, which is set once
the ODBC_FETCH() function (which returns SUCCESS if additional rows are available or
FAILURE if no additional rows are available) is called. This permits us to write a dialplan
like the following, which does not use a counter, but still loops through the data. This
may be useful if we’re looking for something specific and don’t need to look at all the
data. Once we’re done, the ODBCFinish() dialplan application should be called to clean
up any remaining data.
Here’s another extensions.conf example:
[multirow_example_2]
exten => start,1,Verbose(1,Looping example with break)
same => n,Set(ODBC_ID=${GET_ALL_AVAIL_EXTENS(1)})
same => n(loop_start),NoOp()
same => n,Set(ROW_RESULT=${ODBC_FETCH(${ODBC_ID})})
same => n,GotoIf($["${ODBC_FETCH_RESULT}" = "FAILURE"]?cleanup,1)
same => n,GotoIf($["${ROW_RESULT}" = "1104"]?good_exten,1)
same => n,Goto(loop_start)
exten => cleanup,1,Verbose(1,Cleaning up after all iterations)
same => n,Verbose(1,We did not find the extension we wanted)
same => n,ODBCFinish(${ODBC_ID})
same => n,Hangup()
exten => good_exten,1,Verbose(1,Extension we want is available)
same => n,ODBCFinish(${ODBC_ID})
same => n,Verbose(1,Perform some action we wanted)
same => n,Hangup()
We’ll be using multirow mode for one of our functions later in this chapter.
Getting Funky with func_odbc: Hot-Desking | 361