Asterisk func func_odbc

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

Return variable: If it is a query which inserts/updates, then the variable ODBC_ROWS will contain an integer specifying the number of rows affected. -1 is reserved for a statement which failed, since it is perfectly possible for an UPDATE to succeed, yet affect 0 rows. For SELECT queries, however, that is a much more difficult question, since it depends upon the particular query. Again, it is perfectly possible for a SELECT query to successfully run, yet return 0 rows. Or it might be that with your dataset, you should never get 0 rows returned.

Tips and tricks

While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else:

[CREATECALL]
dsn=Example
writesql=INSERT INTO x (y) VALUES (z)
readsql=SELECT LAST_INSERT_ID();

The above assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value.
I should also note, make sure you create a 2nd DSN for your specific ODBC commands that will use LAST_INSERT_ID(), otherwise if you are using ODBC CDR or CEL, there is a chance LAST_INSERT_ID() will return the ID of those records.

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




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

Return variable: If it is a query which inserts/updates, then the variable ODBC_ROWS will contain an integer specifying the number of rows affected. -1 is reserved for a statement which failed, since it is perfectly possible for an UPDATE to succeed, yet affect 0 rows. For SELECT queries, however, that is a much more difficult question, since it depends upon the particular query. Again, it is perfectly possible for a SELECT query to successfully run, yet return 0 rows. Or it might be that with your dataset, you should never get 0 rows returned.

Tips and tricks

While, LAST_INSERTID(); is a MySQL-ism, I've been able to use it with func_ODBC. Of cource, my database is MySQL and this function would not work on anything else:

[CREATECALL]
dsn=Example
writesql=INSERT INTO x (y) VALUES (z)
readsql=SELECT LAST_INSERT_ID();

The above assumes you have only one call in existence at a time. If two calls came in and executed the query at about the same time, it's possible for both reads to return the same value.
I should also note, make sure you create a 2nd DSN for your specific ODBC commands that will use LAST_INSERT_ID(), otherwise if you are using ODBC CDR or CEL, there is a chance LAST_INSERT_ID() will return the ID of those records.

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




Created by: jimvanm, Last modification: Mon 04 of Jun, 2012 (07:53 UTC) by admin
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+