[asterisk-users] mysql phonebook

[Doug Lytle]

I do this.  I connect back to the mysql server via odbc, and as you, I have two databases for this, one called blacklisted and the other called speeddials.

My dialplan code below:


exten => _5XXXXXXXXX,1,Answer()
exten => _5XXXXXXXXX,n,Gosub(check_blacklist,s,1)
exten => _5XXXXXXXXX,n,Gosub(get_callerid,s,1)


[check_blacklist]

exten => s,1,GotoIf($["${CALLERID(number)}" = "" ]?2:3)
exten => s,n,Set(CALLERID(all)=Restricted <0>)
exten => s,n,Set(ARRAY(flag,note)=${ODBC_BLACKLIST(${CALLERID(number)})})
exten => s,n,GotoIf($["${flag}" = "YES"]?blacklisted,s,1)
exten => s,n,NoOP(Caller not blacklisted)
exten => s,n,Return

[blacklisted]

exten => s,1,NoOP(Caller: ${CALLERID(number)} is on the black list)
exten => s,n,NoOP(NOTE: ${note})
exten => s,n,Set(CDR(userfield)=Blacklisted)
exten => s,n,Zapateller(answer)
exten => s,n,Hangup(2)


The ODBC query is:

[BLACKLIST]
dsn=MySQL-blacklisted
readsql=SELECT flag, note FROM [putyourdatabasenamehere] WHERE phone=${SQL_ESC("${ARG1}")}


[get_callerid]

exten => s,1,Set(ARRAY(speed.dial,speed.name)=${ODBC_GET_CALLERID(${CALLERID(num)})})
exten => s,n,Set(CALLERID(name)=${speed.name})
exten => s,n,Return()

The ODBC query is:

[GET_CALLERID]
dsn=MySQL-speeddials
readsql=SELECT phone, name, code FROM [putyourdatabasenamehere] WHERE phone = ${ARG2}

Doug