Difference between revisions of "Macros For MySQL Functions"
Jump to navigation
Jump to search
| Line 11: | Line 11: | ||
To call the macro: | To call the macro: | ||
| + | <PRE> | ||
| + | Macro(MYSQLFETCH,RETURNEDVALUE,QUERY) | ||
| + | </PRE> | ||
| + | RETURNEDVALUE is the value to be returned from the database query. QUERY is the query to be issued to the database. | ||
| + | |||
| + | ====Example==== | ||
<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;"> | ||
exten => s,n,Macro(MYSQLFETCH,DNDVALUE,SELECT value FROM features WHERE name="dnd" AND subname="status") | 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) | exten => s,n,GotoIf($[${DNDVALUE} = 1 ]?LINEISDND,s,1) | ||
</PRE> | </PRE> | ||
| + | |||
| + | |||
==To Perform Other Queries MySQL== | ==To Perform Other Queries MySQL== | ||
| Line 29: | Line 37: | ||
To call the macro: | To call the macro: | ||
| + | <PRE> | ||
| + | Macro(MYSQLUPDATE,${URIENCODE(QUERY)}) | ||
| + | </PRE> | ||
| + | |||
| + | QUERY is the query/action to be issued to the database. | ||
| + | ====Example==== | ||
<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;"> | ||
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> | ||
| − | + | 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. | 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. | ||
Revision as of 14:36, 13 June 2013
To Fetch Data From MySQL
[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})
To call 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.
[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})
To call 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)}")})
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.