Asterisk cmd MYSQL
This is an ADD-ON of asterisk, is not installed by default and must be downloaded and installed with the asterisk-addons package.
MYSQL(Connect connid dhhost dbuser dbpass dbname)
Connects to a database. Arguments contain standard MySQL parameters passed to function mysql_real_connect. Connection identifer returned in ${connid}. If the connection wasn’t possible, then ${connid} == “”.
MYSQL(Query resultid ${connid} query-string)
Executes standard MySQL query contained in query-string using established connection identified by ${connid}. Result of query is stored in ${resultid}.
MYSQL(Fetch fetchid ${resultid} var1\ var2\ …\ varN)
If any rows are available to select, ${fetchid} is set to 1 and a single row is fetched from a result set contained in ${resultid}. The return fields are assigned to ${var1}, ${var2} … ${varN} respectively. If no rows are left to select, ${fetchid} is set to 0 and ${var1}, ${var2} … ${varN} remain unchanged.
MYSQL(Nextresult resultid ${connid}) (New in Asterisk 1.8)
If last query returned more than one result set, it stores the next result set in ${resultid}. It’s useful with stored procedures.
MYSQL(Clear ${resultid})
Frees memory and data structures associated with result set.
MYSQL(Disconnect ${connid})
Disconnects from named connection to MySQL.
exten => _X.,1,MYSQL(Connect connid localhost dbuser dbpass dbname)
exten => _X.,2,MYSQL(Query resultid ${connid} SELECT\ scriptname\ from\ mac2pin\ where\ userid=${CALLERIDNAME})
exten => _X.,3,MYSQL(Fetch fetchid ${resultid} AGIScript)
exten => _X.,4,GotoIf($[${AGIScript} = NULL]?5:7)
exten => _X.,5,AGI(${DefaultAGIScript},${EXTEN})
exten => _X.,6,Goto(_X.,8)
exten => _X.,7,AGI(${AGIScript},${EXTEN})
exten => _X.,8,MYSQL(Clear ${resultid})
exten => _X.,9,MYSQL(Disconnect ${connid})
exten => _X.,10,Hangup
In the above example, if the user hangs up at either priority 5 or priority 7, MYSQL(Clear…) and MYSQL(Disconnect….) will never be executed. This will constantly create connections over and over again as calls progressively increase. Eventually, MySQL will not allow anymore connections. (Depending on the limit set in the mysql conf file). Therefore, in this case the following will work: exten => _X.,1,MYSQL(Connect connid localhost asterisk dbpass asterisk)
exten => _X.,2,MYSQL(Query resultid ${connid} SELECT\ scriptname\ from\ mac2pin\ where\ userid=${CALLERIDNAME})
exten => _X.,3,MYSQL(Fetch fetchid ${resultid} AGIScript)
exten => _X.,4,MYSQL(Clear ${resultid})
exten => _X.,5,MYSQL(Disconnect ${connid})
exten => _X.,6,GotoIf($[${fetchid}]?7:9)
exten => _X.,7,AGI(${DefaultAGIScript},${EXTEN})
exten => _X.,8,Hangup
exten => _X.,9,AGI(${AGIScript},${EXTEN})
exten => _X.,10,Hangup
Notice in this case, I am clearing and disconnecting after the Fetch. This ensures that before we even reach the AGI script to do a task, we ensure that Clearing and Disconnecting takes place unless the call is hungup after the connection and before the disconnect. The best way to handle this situation is to clear connections in the ‘h’ or hangup extension. Escape spaces with \ in the query string. If you use quotes they are passed to the application as part of the string Other characters that need to be escaped are quotes (\’ and \”), commas (\,), backtick (\`), and backslash (\\).Note: This is apparently no longer necessary as of Asterisk 1.8. After upgrading two development servers, I discovered that I could define my querystring and return variables without the escape character The fields returned are assigned to the variables in the same order that they are returned by MySQL. It is not recommended to “SELECT *” because you cannot guarantee in which order the fields will be returned. You do not need to give them pretty names “SELECT (long ass query field) as shortfieldname” because the field name is irrelevant to the order. The asterisk online command description states that ${fetchid} is set to TRUE if more rows are available. This is incorrect. It is set to 1 if a row was found in the last fetch and 0 if no row was found. There appears to be a buglet in ‘app_addon_sql_mysql.c’ – change the line “#ifdef EXTRA_LOG” to “#if EXTRA_LOG” (save and ‘make install’) and you’ll stop seeing warning messages like “May 21 15:51:53 WARNING[5309]: app_addon_sql_mysql.c:318 aMYSQL_fetch: ast_MYSQL_fetch: numFields=4″. As of Asterisk 1.6.x.y.z escaping spaces with a backslash (\) generates an SQL error, same is likely true for quotes (\’ & \”), comma (,) & backtick (\`). Asterisk 1.4.xyz does need to have these characters escaped.
exten => 888,1,MYSQL(Connect connid localhost dbuser dbpass dbname)
exten => 888,n,GotoIf($[“${connid}” = “”]?error,1)
exten => 888,n,MYSQL(Query resultid ${connid} SELECT\ `number`\ FROM\ `phones`\ WHERE\ `channel`=\’${chan}\’)
exten => 888,n(fetchrow),MYSQL(Fetch foundRow ${resultid} number) ; fetch row
exten => 888,n,GotoIf($[“${foundRow}” = “1”]?done) ; leave loop if no row found
exten => 888,n,NoOp(${number})
exten => 888,n,Goto(fetchrow) ; continue loop if row found
exten => 888,n(done),MYSQL(Clear ${resultid})
exten => 888,n,MYSQL(Disconnect ${connid})
exten => error,1,NoOp(Connection error – do whatever we have to do to crash nicely!)
exten => error,n,Hangup
Please note the corrections to MYSQL(Fetch). ${fetchid} does not necessarily return 1 if there are more available rows, it returns 1 if there was a row available during that call to MYSQL(Fetch). This appears to have been inaccurately explained since the creation of this page. I have corrected this above and in the example below. – Flobi.
If you do not properly Clear and Disconnect from MySQL, connections will persist and overload MySQL. The only way to undo this is to shutdown Asterisk and restart it, or use mytop and kill the processes that are hung. To avoid this, clean up connections in the ‘h’ extension. – R. Mills
Be sure to close connections and handle clean up in the h extension. This will ensure that on hangup connections do not persist. – R.Mills
Just to clear up the issue. There should be no problem selecting multiple fields. Be sure your syntax is correct. – R.Mills
Using the escape sequences as of 1.6.2X causes syntax errors. Using ` around column names and ‘ around values without escapes works fine. -D.Hansen
In asterisk 1.6.1.11 omitting ` around column names does not cause an error. Use ‘ around string values, not needed for numbers. Eg. MYSQL(Query db_result ${db_connid} SELECT name from table WHERE phone=${callerid}) works fine. -Baji
autoclear: Configuration file for app_addon_sql_mysql If set, autoclear will destroy allocated statement and connection resources when the channel ends. For most usage of the MYSQL app, this is what you want, but it’s conceivable that somebody is sharing MYSQL connections across multiple channels, in which case, this should be set to ‘no’. Defaults to ‘no’, as this was the original behavior.
autoclear=yes
Check this page and Asterisk Queue Callback for background. These two sets of statements accomplish the same thing:
exten => 1,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ callers\ SET\ uniqueid=${UNIQUEID}\, callback=1\, callbacknum=${CALLERID:-11:11})
exten => 2,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ callers\ SET\ uniqueid=${UNIQUEID}) exten => 2,n,MYSQL(Query resultid ${connid} UPDATE\ callers\ SET\ callback=1\, callbacknum=${CALLERID:-11:11}\ WHERE\ uniqueid=${UNIQUEID})
Notice that in extension 2 the resultid value didn’t need to be cleared.
These two sets of statements accomplish the same thing:
exten => 1,n,MYSQL(Query resultid ${connid} DELETE\ FROM\ callers\ WHERE\ callbacknum=${cbn})
exten => 2,n,System(mysql -u acd -h 127.0.0.1 -e “DELETE FROM callers WHERE callbacknum=${cbn}” –password=acdpass acd)
Now, in order to use the MYSQL() delete in stead of the System delete you’ll have to clear the resultid value between queries:
exten => 2,n,MYSQL(Query resultid ${connid} SELECT\ callbacknum\ FROM\ callers\ WHERE\ callbacknum=${CALLERID:-11:11}) exten => 2,n,MYSQL(Fetch fetchid ${resultid} cbn) exten => 2,n,MYSQL(Clear ${resultid}) exten => 2,n,MYSQL(Query resultid ${connid} DELETE\ FROM\ callers\ WHERE\ callbacknum=${cbn})
Moreover, if you are working with a TimeStamp column, you can retrieve values without the delimiters by appending a ‘+0’ to the column name:
exten => 1,n,MYSQL(Query resultid ${connid} SELECT\ called+0\ FROM\ callers\ WHERE\ callbacknum=${CALLERID(num):-11:11}) This returns datetime yyyymmddhhmmss. I find it easier to just disconnect and clear in the h extension, no problems. Q: What is a better way to do it then in terms of performance, security, and flexibility? Using exec and a shell script, or agi or something else?
A: Setup extconfig to have realtime access to the database/table you want to pull info from, then in the dialplan use the app Realtime.
Use the RealTime config handler system to read data into channel variables:
RealTime(||[|])
All unique column names will be set as channel variables with optional prefix to the name. e.g. prefix of ‘var_’ would make the column ‘name’ become the variable ${var_name}
This will not show any auth info in the asterisk cli and automatically clears connect and fetch id’s, works great and decreases the number of priority routines within an extension.
There is alternative solution to easily work with almost all databases in Asterisk using drag-and-drop environment but you will need Visual Dialplan and Apstel Integration Server, both free to download and use. There is some fee to keep it but you do not need to keep it, you can simply download, install and use to develop your dial plan and then uninstall it, like I did. Here is how it looks like.
