Difference between revisions of "Macros For MySQL Functions"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
| + | The Asterisk implementation of MySQL connectivity generally requires a number of lines to execute a single query. This can be simplified using a macro. This is normally a simple task, but submitting values to a macro can be problematic, as seen in the "Other Queries" section below. | ||
| + | |||
| + | |||
==To Fetch Data From MySQL== | ==To Fetch Data From MySQL== | ||
| + | This is used for returning a single value from a query. | ||
| + | |||
| + | |||
| + | ===Macro=== | ||
<PRE style="color:white;background-color:black;font-weight:bold;font-size:1.2em;"> | <PRE style="color:white;background-color:black;font-weight:bold;font-size:1.2em;"> | ||
[macro-MYSQLFETCH] | [macro-MYSQLFETCH] | ||
| Line 10: | Line 17: | ||
</PRE> | </PRE> | ||
| − | + | ||
| + | ===Calling The Macro=== | ||
<PRE> | <PRE> | ||
Macro(MYSQLFETCH,RETURNEDVALUE,QUERY) | Macro(MYSQLFETCH,RETURNEDVALUE,QUERY) | ||
| Line 16: | Line 24: | ||
RETURNEDVALUE is the value to be returned from the database query. QUERY is the query to be issued to the database. | RETURNEDVALUE is the value to be returned from the database query. QUERY is the query to be issued to the database. | ||
| + | |||
====Example==== | ====Example==== | ||
| Line 27: | Line 36: | ||
==To Perform Other Queries MySQL== | ==To Perform Other Queries MySQL== | ||
| − | This is used for doing inserts, updates, deletes, etc. | + | This is used for doing inserts, updates, deletes, etc. |
| + | |||
| + | Note the use of the URIENCODE and URIDECODE functions. This allows the values in the query to contain commas (ie. "Smith, John"). Otherwise, if a macro is called, and an argument contains a comma, it will appears as multiple arguments. | ||
| + | Ie: if MYMACRO were called with ARG1 as "Smith, John" and ARG2 as "123-456-7890", the result would be: '''s,n,Macro(MYMACRO,"Smith,John",123-456-7890)''' which would be three arguments, not two. | ||
| + | |||
| + | |||
| + | ===Macro=== | ||
<PRE style="color:white;background-color:black;font-weight:bold;font-size:1.2em;"> | <PRE style="color:white;background-color:black;font-weight:bold;font-size:1.2em;"> | ||
[macro-MYSQLUPDATE] | [macro-MYSQLUPDATE] | ||
| Line 36: | Line 51: | ||
</PRE> | </PRE> | ||
| − | + | ||
| + | ===Calling The Macro=== | ||
<PRE> | <PRE> | ||
Macro(MYSQLUPDATE,${URIENCODE(QUERY)}) | Macro(MYSQLUPDATE,${URIENCODE(QUERY)}) | ||
| Line 42: | Line 58: | ||
QUERY is the query/action to be issued to the database. | QUERY is the query/action to be issued to the database. | ||
| + | |||
====Example==== | ====Example==== | ||
| Line 47: | Line 64: | ||
exten => s,n,Macro(MYSQLUPDATE,${URIENCODE(UPDATE blacklist SET lastcalled=NOW() WHERE number="${CALLERID(number)}")}) | exten => s,n,Macro(MYSQLUPDATE,${URIENCODE(UPDATE blacklist SET lastcalled=NOW() WHERE number="${CALLERID(number)}")}) | ||
</PRE> | </PRE> | ||
| − | |||
| − | |||
| − | |||
| − | |||
Revision as of 14:42, 13 June 2013
The Asterisk implementation of MySQL connectivity generally requires a number of lines to execute a single query. This can be simplified using a macro. This is normally a simple task, but submitting values to a macro can be problematic, as seen in the "Other Queries" section below.
To Fetch Data From MySQL
This is used for returning a single value from a query.
Macro
[macro-MYSQLFETCH]
exten => s,1,MYSQL(Connect CONNID localhost databaseuser databasepassword databasename)
exten => s,n,MYSQL(Query RESULTID ${CONNID} ${ARG2})
exten => s,n,MYSQL(Fetch FETCHID ${RESULTID} ${ARG1})
exten => s,n,MYSQL(Clear ${RESULTID})
exten => s,n,MYSQL(Disconnect ${RESULTID})
Calling The Macro
Macro(MYSQLFETCH,RETURNEDVALUE,QUERY)
RETURNEDVALUE is the value to be returned from the database query. QUERY is the query to be issued to the database.
Example
exten => s,n,Macro(MYSQLFETCH,DNDVALUE,SELECT value FROM features WHERE name="dnd" AND subname="status")
exten => s,n,GotoIf($[${DNDVALUE} = 1 ]?LINEISDND,s,1)
To Perform Other Queries MySQL
This is used for doing inserts, updates, deletes, etc.
Note the use of the URIENCODE and URIDECODE functions. This allows the values in the query to contain commas (ie. "Smith, John"). Otherwise, if a macro is called, and an argument contains a comma, it will appears as multiple arguments.
Ie: if MYMACRO were called with ARG1 as "Smith, John" and ARG2 as "123-456-7890", the result would be: s,n,Macro(MYMACRO,"Smith,John",123-456-7890) which would be three arguments, not two.
Macro
[macro-MYSQLUPDATE]
exten => s,1,MYSQL(Connect CONNID localhost databaseuser databasepassword databasename)
exten => s,n,MYSQL(Query RESULTID ${CONNID} ${URIDECODE(${ARG1})})
exten => s,n,MYSQL(Disconnect ${CONNID})
Calling The Macro
Macro(MYSQLUPDATE,${URIENCODE(QUERY)})
QUERY is the query/action to be issued to the database.
Example
exten => s,n,Macro(MYSQLUPDATE,${URIENCODE(UPDATE blacklist SET lastcalled=NOW() WHERE number="${CALLERID(number)}")})