Synopsis

 Basic MYSQL Database Functionality

Installation

This is part of the asterisk-addons package available via the Digium Subversion asterisk-addons repository, or as a tarball from the Asterisk website. This is an ADD-ON of asterisk, is not installed by default and must be downloaded and installed with the asterisk-addons package.

Description

MYSQL(): Basic MYSQL Database Functionality

Syntax

(note that the help information returned from 'show application MYSQL' is a little misleading - what's shown below is easier to understand!)

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(Clear ${resultid})

Frees memory and data structures associated with result set.

MYSQL(Disconnect ${connid})

Disconnects from named connection to MySQL.

Examples

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:


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.

Other Things to Consider:

• 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 (\,), tilde (\`), and backslash (\\). (Try Mysqlscape - see below)
• 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".

Example

Notes

• 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

More MYSQL Information

Check this page and Asterisk Queue Callback for background.

• These two sets of statements accomplish the same thing:

1. exten => 1,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ callers\ SET\ uniqueid=${UNIQUEID}\, callback=1\, callbacknum=${CALLERID:-11:11})

1. exten => 2,n,MYSQL(Query resultid ${connid} INSERT\ INTO\ callers\ SET\ uniqueid=${UNIQUEID})
2. 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:

1. exten => 1,n,MYSQL(Query resultid ${connid} DELETE\ FROM\ callers\ WHERE\ callbacknum=${cbn})

1. 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:

1. exten => 2,n,MYSQL(Query resultid ${connid} SELECT\ callbacknum\ FROM\ callers\ WHERE\ callbacknum=${CALLERID:-11:11})
2. exten => 2,n,MYSQL(Fetch fetchid ${resultid} cbn)
3. exten => 2,n,MYSQL(Clear ${resultid})
4. 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:

1. exten => 1,n,MYSQL(Query resultid ${connid} SELECT\ called+0\ FROM\ callers\ WHERE\ callbacknum=${CALLERID(num):-11:11})
2. This returns datetime yyyymmddhhmmss.

Comments

I find it easier to just disconnect and clear in the h extension, no problems.

Alternative: App RealTime

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(<family>|<colmatch>|<value>[|<prefix>])

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.

See also

• MySQL
• Asterisk db
• MySQL Failover in dialplan
• MySQL Data Replication
• Asterisk app_dbquery
• Asterisk PGSQL: Postgres queries
• Mysqlscape - A utility to automatically escape characters in mysql for the Asterisk cmd MYSQL. *(DOES NOT FUNCTION)* -R. Mills
• Restart MYSQL
• Basic MYSQL command line
• Asterisk cmd RealTime: 'Misuse' app RealTime to automatically handle fetch IDs and clear connects
• Example Realtime Macros

---

86614 views strong.

---

Asterisk | Applications | Functions | Variables | Expressions | Asterisk FAQ