Thursday, July 22, 2010

Query a database from Asterisk

; Define MySQL ODBC modules
; edit /etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libodbcmyS.so
FileUsage = 1

; edit /etc/odbc.ini
[astodbc]
Description = Retrieve names and extensions
Driver = MySQL
Server = localhost
Port = 3306
USER = astconnector
Password = astpasskey
Database = astodbc
Option = 3

; prepare asterisk for odbc connection
; asterisk will use res_odbc.conf information to connect to MySQL
; edit /etc/asterisk/res_odbc.conf
[sqlconnect]
enabled => yes
dsn => astodbc
username => astconnector
password => astpasskey
pre-connect => yes

; prepare the sql query commands that asterisk will initiate
; edit /etc/asterisk/func_odbc.conf
[GETUSERINFO]
dsn=sqlconnect
read=SELECT id_firstname, id_middlename, id_lastname, age, sex, location FROM client_db WHERE ext_number='${SQL_ESC(${ARG1})}'

; define extensions that connect to MySQL
; Festival needs to be configured prior to this
/etc/asterisk/extensions_custom.conf
[from-internal-custom]
exten => 5000,1,answer
exten => 5000,n,wait(1)
; get user input (ext_number)
exten => 5000,n(getextension),read(keyext,extension,4,,,10)
; say user input
exten => 5000,n,Festival(${keyext})
; retrieve information from odbc query in func_odbc.conf
exten => 5000,n,Set(myinfo=${ODBC_GETUSERINFO(${keyext})})
; say user information
exten => 5000,n,Festival(${myinfo})
exten => 5000,n,Playback(silence/3)
; ask for another input
exten => 5000,n,Goto(getextension)
exten => 5000,n,hangup()

sample sql table
select * from client_db ;
+----------+--------------+---------------+-------------+------------+-----+------+-----------+---------------------+
| uniqueid | id_firstname | id_middlename | id_lastname | ext_number | age | sex | location | stamp |
+----------+--------------+---------------+-------------+------------+-----+------+-----------+---------------------+
| 101 | John | Michael | Price | 4010 | 23 | male | home | 2010-03-12 03:30:42 |
| 103 | Joseph | Edward | Smith | 4020 | 25 | male | office | 2010-03-12 03:30:44 |
+----------+--------------+---------------+-------------+------------+-----+------+-----------+---------------------+

No comments:

Post a Comment