login | register
Tue 02 of Dec, 2008 [11:53 UTC]

voip-info.org

History

Asterisk func func_odbc

Created by: jimvanm,Last modification on Mon 28 of Jul, 2008 [11:31 UTC] by JustRumours

Syntax

ODBC_functionname(<arg1>[...,<argN>])[=val1]

Synopsis

1. Runs the query indicated by the specified function name with the given arguments.
2. Assigns the given value to the specified functionname

Description

Runs the query, as defined in func_odbc.conf, performing substitution of the arguments into the query as specified by ${ARG1}, ${ARG2}, ... ${ARGn} and/or the values it is set to by ${VAL1},${VAL2}, ... ${VALn}.

Note that how you call this function (i.e., its name) is actually something that comes from how you configured it in func_odbc.conf. Note that 'readsql' and 'writesql' are required for Asterisk 1.6.x and may or may not work in earlier versions (if they don't, use 'read' and 'write'!)

ODBC support must be available to the OS (see CentOS 5 and Asterisk 1.4.x installation for an ODBC/MySQL configuration example).

Example 1

func_odbc.conf:
 [PRESENCE]
 dsn=mydb
 readsql=SELECT `location` FROM `locationtable` WHERE `username`='${SQL_ESC(${ARG1})}'
 writesql=UPDATE `locationtable` SET `location`=${SQL_ESC(${VAL1})}` WHERE `username`='${SQL_ESC(${ARG1})}'

extensions.conf:
 exten => 1234,1,NoOp(Set and read location)
 exten => 1234,n,Set(ODBC_PRESENCE(${EXTEN})=office)
 exten => 1234,n,Set(CURLOC=${ODBC_PRESENCE(${EXTEN})})
 exten => 1234,n,NoOp(Current location of user ${EXTEN} is ${CURLOC}.)
 exten => 1234,n,Hangup()

verbose output:
pbx*CLI> console dial 1234
— Executing [1234@default:1] NoOp("Console/dsp", "Set and read location") in new stack
— Executing [1234@default:2] Set("Console/dsp", "ODBC_PRESENCE(1234)=office") in new stack
— Executing [1234@default:3] Set("Console/dsp", "CURLOC=office") in new stack
— Executing [1234@default:4] NoOp("Console/dsp", "Current location of user 1234 is office.") in new stack
— Executing [1234@default:5] Hangup("Console/dsp", "") in new stack
== Spawn extension (default, 1234, 5) exited non-zero on 'Console/dsp'

Example 2 with GotoIf

in func_odbc.conf:
 [ISLOCAL]
 dsn=foo
 read=SELECT COUNT(*) FROM localexchanges WHERE prefix='${ARG1:0:6}'

in extensions.conf:
 exten => _011-1-NXX-NXX-XXXX,1,GotoIf($[${ODBC_ISLOCAL(${EXTEN:4})}]?${EXTEN:4},1:${EXTEN:3},1)

More example lines from func_odbc.conf

[ORGOPEN]
dsn=mydb
readsql=SELECT COUNT(`orgcode`)>0 AS 'open' FROM `opentimes` WHERE ((`orgcode`='${SQL_ESC(${ARG1})}' OR `orgcode`='ALL') AND `day`=DAYOFWEEK(CURDATE()) AND `starttime`<=CURTIME() AND `endtime`>CURTIME()) AND (SELECT COUNT(`orgcode`)=0 FROM `closeddates` WHERE (`orgcode`='${SQL_ESC(${ARG1})}' OR `orgcode`='ALL') AND `closeddate`=CURDATE())=1

GETCIDNAME
dsn=mydb
readsql=SELECT `cidname` FROM `callerid` WHERE `cidnum`='${SQL_ESC(${ARG1})}' LIMIT 1

GETEXCHANGENAME
dsn=mydb
readsql=SELECT `location` FROM `exchangecodes` WHERE '${SQL_ESC(${ARG1})}' REGEXP CONCAT('^',`stdcode`) ORDER BY LENGTH(`stdcode`) DESC LIMIT 1

See also




Comments