Asterisk SQL dialplan examples

Want to do some SQL look ups to MYSQL from your asterisk dialplan? Here’s how!

Well here’s -HOW- you do it.

This example I’ll show you how to do the sql lookup and everything all through dialplan. Don’t usually need to install anything, most modern FreePBX distro’s have this included in the modules compiled.

Connect to a MYSQL/MariaDB database
Query with an SQL statement
Check the data
Disconnect from database

1> This dialplan example below connects to the MySQL database server and uses the database “call_center”

2> Then it looks for the value of “id” in the table “contact” where the value of cedula_ruc=9989

3> Then it shows you the value of the return result in the variable “NUMBER”

4> Then disconnects from the sql database

5> Then shows you the return value in variable ${number}

6> Hangup

BOLD portions indicate items that are adjustable by you

[pincollection] exten => s,1,MYSQL(Connect connid localhost root Password50 call_center)
exten => s,2,MYSQL(Query resultid ${connid} SELECT id from contact where cedula_ruc=9989)
exten => s,3,MYSQL(Fetch fetchid ${resultid} number)
exten => s,4,MYSQL(Disconnect ${connid})

exten => s,5,NoOp(&& the value found is ${number} )

exten => s,6,Hangup()

In the above example, the select statement will return the value of column “ID” if it finds something in column “CEDULA_RUC”

Here’s an example pulling 2 variables from a select

Here we connect to the host, user, password, database name
exten => s,n,MYSQL(Connect connid 10.1.100.217 asterisk password01 vtiger710) ;open connection to database

Then we do a select of two columns, lasstname and firstname from any row with a contactnumber that equals the variable tech id
exten => s,n,MYSQL(Query resultid ${connid} SELECT lastname, firstname FROM vtiger_contactdetails WHERE contactnumber=${techid})

Now that we have a select (above) done, now we apply those two results into these variables
exten => s,n,MYSQL(Fetch fetchid ${resultid} crmlast crmfirst)

then we do a check. if crmlast= “” nothing, then hangup, else continue (means we found a match)
exten => s,n,ExecIf($[“${crmlast}” = “”]?hangup()) ;if number not found (“”), hangup call

disconnect the SQL connection
exten => s,n,MYSQL(Disconnect ${connid})

echo the data to the live log for reassurance
exten => s,n,NoOp(&&&&&&&&&&&&&&& Success! Firstname ${crmfirst} Lastname ${crmlast} was found in CRM database&&&&&&&&&&&&&&&&&&&&&&&&)

Asterisk SQL dialplan examples

ทิ้งคำตอบไว้ ยกเลิกการตอบ