Asterisk SQL dialplan examples

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

(ExecIF Examples)

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.

We'll create a database that contains a customer account number and name.
We'll have the IVR ask for their customer#, and if there is a match, send the call to an agent.

STEP 1
Lets create a test database.

From the command line use

    mysql -p

(enter in your database password)

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases

+--------------------+
| Database           |
+--------------------+
| information_schema |
| asterisk           |
| asteriskcdrdb      |
| call_center        |
| endpointconfig     |
| meetme             |
| mysql              |
| performance_schema |
| roundcubedb        |
+--------------------+

You'll see there are some databases.  Lets create a new database called "customerinfo "

CREATE DATABASE customerinfo;

     [response]
     "Query OK, 1 row affected (0.01 sec)"

USE customerinfo;

     [response]
     "Database changed"

STEP 2
Now we'll make a table that contains the customer ID number and NAME

CREATE TABLE account ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );

     [response]
     "Query OK, 0 rows affected (0.03 sec)"

Now we'll put in some data into this system manually
Were going to put in 3 names with corresponding ID#

5587 John Smith
4123 Jane Doe
9788 Maria Jones

INSERT INTO account ( id, name ) VALUES ( 5587, 'John Smith' );

     [response]
     "Query OK, 1 row affected (0.02 sec)"

INSERT INTO account ( id, name ) VALUES ( 4123, 'Jane Doe' );

     [response]
     "Query OK, 1 row affected (0.02 sec)"

INSERT INTO account ( id, name ) VALUES ( 9788, 'Maria Jones' );

     [response]
     "Query OK, 1 row affected (0.02 sec)"

SELECT * FROM account;

     [response]
+------+-------------+
| id   | name        |
+------+-------------+
| 4123 | Jane Doe    |
| 5587 | John Smith  |
| 9788 | Maria Jones |
+------+-------------+
3 rows in set (0.00 sec)

Exit from the database
ctrl+c

     [response]
     MariaDB [customerinfo]> Ctrl-C -- exit!
     Aborted
     [root@issabel ~]#

STEP 3

Now lets create some dialplan code.

First lets create some code to ask the customer for their pin number

open up /etc/asterisk/extensions_custom.conf

Go to the bottom of the file and paste this in:
change SQLPASSWORD to be the password to your MYSQL server

[customerlookup]
exten => s,1,Answer()
exten => s,n,Playback(please-enter-your&pin_number)
exten => s,n,Read(pinnumber,then-press-pound,4,,1,10)
exten => s,n,MYSQL(Connect connid localhost root SQLPASSWORD customerinfo)
exten => s,n,MYSQL(Query resultid ${connid} SELECT name from account where id=${pinnumber})
exten => s,n,MYSQL(Fetch fetchid ${resultid} accountname)
exten => s,n,MYSQL(Disconnect ${connid})
exten => s,n,NoOp(&& the value found is ${accountname} )

;if the account number is not found, the system hangsup.
exten => s,n,ExecIf($["${accountname}"=""]?playback(hangup-try-again))
exten => s,n,ExecIf($["${accountname}"=""]?HANGUP())

;if the account number IS FOUND the system says "THANK YOU"
exten => s,n,PlayBack(auth-thankyou)
exten => s,n,Hangup()

Save those changes

STEP 4

Now in the ISSABEL PBX gui, create a "Custom Destination"

Create a destination called

"customerlookup"

and for "Custom Destination" put in

customerlookup,s,1

Save those changes.

STEP 5

Now, create a route or something to call that customer destination. Can use a ring group to point to it or an IVR prompt or inbound route.

Here I've created a RING group to test this code.  If you dial the ringgroup (600 in my example) the system will dial "1", which is an invalid extension, then will immediately fail to the customerlookup test script.

STEP 6

Call the script
System will say "PLEASE ENTER YOUR PIN NUMBER"

enter in 4123 (or any of the ones you entered)

System should say "THANK YOU"

If you type in the wrong one it will say "hangup and try again"

Here's the output you see in the log

 -- Executing [s@customerlookup:4] MYSQL("SIP/1000-0000000f", "Connect connid localhost root SQLPASSWORD customerinfo") in new stack

    -- Executing [s@customerlookup:5] MYSQL("SIP/1000-0000000f", "Query resultid 23 SELECT name from account where id=4123") in new stack

    -- Executing [s@customerlookup:6] MYSQL("SIP/1000-0000000f", "Fetch fetchid 24 accountname") in new stack

    -- Executing [s@customerlookup:7] MYSQL("SIP/1000-0000000f", "Disconnect 23") in new stack

    -- Executing [s@customerlookup:8] NoOp("SIP/1000-0000000f", "&& the value found is Jane Doe ") in new stack

    -- Executing [s@customerlookup:9] ExecIf("SIP/1000-0000000f", "0?playback(hangup-try-again)") in new stack

    -- Executing [s@customerlookup:10] ExecIf("SIP/1000-0000000f", "0?HANGUP()") in new stack

    -- Executing [s@customerlookup:11] Playback("SIP/1000-0000000f", "auth-thankyou") in new stack

    -- <SIP/1000-0000000f> Playing 'auth-thankyou.gsm' (language 'en')

    -- Executing [s@customerlookup:12] Hangup("SIP/1000-0000000f", "") in new stack

  == Spawn extension (customerlookup, s, 12) exited non-zero on 'SIP/1000-0000000f'