Difference between revisions of "Macros For MySQL Functions"

From Baranoski.ca
Jump to navigation Jump to search
(Created page with "<PRE style="color:white;background-color:black;font-weight:bold;font-size:1.2em;"> ; ==== MYSQL FETCH ============================================================ [macro-MYSQL...")
 
 
(6 intermediate revisions by the same user not shown)
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==
 +
 +
This is used for returning a single value from a query.
 +
 +
 +
===Macro===
 +
<PRE>
 +
[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})
 +
</PRE>
 +
 +
 +
===Calling 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;">
−
; ==== MYSQL FETCH ============================================================
+
exten => s,n,Macro(MYSQLFETCH,DNDVALUE,SELECT value FROM features WHERE name="dnd" AND subname="status")
−
[macro-MYSQLFETCH]
+
exten => s,n,GotoIf($[${DNDVALUE} = 1 ]?LINEISDND,s,1)
 +
</PRE>
 +
 
 +
 
 +
 
 +
==To Perform Other Queries MySQL==
 +
 
 +
This is used for doing inserts, updates, deletes, etc.
  
−
exten => s,1,MYSQL(Connect connid localhost asterisk yourpassword asterisk)
+
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.  For example, if MYMACRO were called with ARG1 as "Smith, John" and ARG2 as "123-456-7890", the result would be:  '''s,n,Macro(MYMACRO,<FONT COLOR=red>"Smith</FONT>,<FONT COLOR=blue>John"</FONT>,<FONT COLOR=GREEN>123-456-7890</FONT>)''' which would be three arguments, not two.
−
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 ${connid})
 
  
  
−
; ==== MYSQL UPDATE ===========================================================
+
===Macro===
 +
<PRE>
 
[macro-MYSQLUPDATE]
 
[macro-MYSQLUPDATE]
−
exten => s,1,MYSQL(Connect connid localhost asterisk yourpassword asterisk)
+
exten => s,1,MYSQL(Connect CONNID localhost databaseuser databasepassword databasename)
−
exten => s,n,MYSQL(Query resultid ${connid} ${URIDECODE(${ARG1})})
+
exten => s,n,MYSQL(Query RESULTID ${CONNID} ${URIDECODE(${ARG1})})
−
exten => s,n,MYSQL(Disconnect ${connid})
+
exten => s,n,MYSQL(Disconnect ${CONNID})
 +
</PRE>
 +
 
 +
 
 +
===Calling 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;">
 +
exten => s,n,Macro(MYSQLUPDATE,${URIENCODE(UPDATE blacklist SET lastcalled=NOW() WHERE number="${CALLERID(number)}")})
 
</PRE>
 
</PRE>

Latest revision as of 14:45, 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. For example, 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)}")})
Retrieved from "https://baranoski.ca/index.php?title=Macros_For_MySQL_Functions&oldid=27"