Dial Plan Connect to External MSSQL Database

Not sure if this is the right place to ask, but here goes.

I have successfully connected FreePBX to an external MSSQL database that use to track our customers on our own internal systems.

I need to write a custom dial plan that goes a bit like this:-

Customer calls > Customers number comes in > Checks MSSQL Database to see if they have called before > If the query is greater the 2 put the call to one queue, if not place them in another queue.

I know many years back there was a decent Visual Call Plan editor, but can not find it now.

Any help would be much appreciated.

I’ve done this with a separate MySQL database on the FreePBX box, but the logic should be the same to connect to MS SQL.

Add the ODBC connection information to /etc/odbc.ini

[sb-custom]
Description=MySQL connection for Custom database information
driver=MySQL
server=localhost
database=sbcustom
Port=3306
Socket=/var/lib/mysql/mysql.sock
option=3
Charset=utf8

Add it to /etc/asterisk/res_odbc_custom.conf. Obviously, you will not use the AMPDB info…

;Custom Connection for Unique Database Queries
;https://github.com/asterisk/asterisk/blob/master/configs/samples/res_odbc.conf.sample
[sb-custom]
;this has to match what is in /etc/odbc.ini
dsn => sb-custom
;Use the AMPDBUSER & AMPDBPASS values from /etc/amportal.conf since it is the same instance
username => freepbxuser
password => XXXXXXXXXXXXX
;The database to use
database=>sbcustom
;This setting is in the default res_odbc_additional.conf
max_connections=>5
enabled => yes
pre-connect => yes

Write your queries and put them in /etc/asterisk/func_odbc.conf, these are examples from mine.

; Return the CID to use based on the AUTCODE dialed.
; If the AUTCODE was valid, but there is no cid_num, populate with a defualt value.
[AUTCODECID]
prefix=GET
dsn=sb-custom
readsql=SELECT cid_num FROM autcodes WHERE code = '${ARG1}'

; Return the ID of the autcode if it exists.
; This is used to validate the call can be placed.
[VALIDATEAUTCODE]
prefix=GET
dsn=sb-custom
readsql=SELECT id FROM autcodes WHERE code = '${ARG1}'

Then you use then in dialplan like this.

; Check if the AUTCODE has a CID associated with it. 
exten => s,n,Set(AUTCODECIDNUM=${GET_AUTCODECID(${AUTCODE})})
; Check the database for the autcode. SQL query returns the `id` of the row if found
exten => s,n,Set(AUTCODEID=${GET_VALIDATEAUTCODE(${AUTCODE})})

For reference, ${AUTCODE}, is simply part of the number the users dial.

exten => s,n,Set(AUTCODE=${OUTNUM:-5})
1 Like

Hi, thank you for your swift reply!

Where does it pick this file up from as I can not see it linked any where?

Thanks

Just create it. All .conf files in the /etc/asterisk directory are read when starting Asterisk.

I believe the Asteirsk git I pulled the original example from used that file name and I just stuck with it because.

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.