Unable to access mysql database from asterisk dialplan

[mqacim]

I have updated from Asterisk 1.6 to Asterisk 11. I was using MySQL command in asterisk dialplan to access the data base on Asterisk 1.6, but now this command seems to be not working. After going through some previous posts, I guess that MySQL command is not supported by Asterisk 11. Can someone please tell me what can be the alternative to MySQL and how can i configure such alternative?

Following is what I’m trying to do in dialplan:

exten => _xxxx,n,MYSQL(Connect connid localhost user pswd db) exten => _xxxx,n(noerror),MYSQL(Query resultid ${connid} SELECT system FROM user_to_system WHERE callerID='${CID}') exten => _xxxx,n(fetchrow),MYSQL(Fetch foundRow ${resultid} system) exten => _xxxx,n,MYSQL(Clear ${resultid}) exten => _xxxx,n,MYSQL(Disconnect ${connid})

[ambiorixg12]

Some alternatives AGI, odbc function, System cmd…

[quote][cidlookup]
exten=>_x.,1,Noop( checking the callerid ${CALLERID(num)})
same=>n,Set(__cid=${SHELL(mysql --user=root --password=‘12340’ --skip-column-names dialer -e 'select dnc_phone from dnc where dnc_phone="${CALLERID(num)}" limit 0,1 ')})
same=>n,Noop( result ${cid:0:3})
same => n,GotoIf($["${CALLERID(num)}"="${cid:0:3}"]?allow:disallow)
same=>n(allow),Playback(demo-thanks)
same=>n,Hangup()
same=>n(disallow),Playback(im-sorry)
same=>n,Hangup()[/quote]

[sergani]

It’s worth noting that the mysql command in the shell function will return the result with a line feed at the end. At least this happens on my system.

In which case the admin may want to pipe the mysql command output to tr for example, and so it becomes like this.

same=>n,Set(__cid=${SHELL(mysql --user=root --password=‘12340’ --skip-column-names dialer -e 'select dnc_phone from dnc where dnc_phone=“${CALLERID(num)}” limit 0,1 ’ | tr -d “\n”)})

Just thought to share this in case someone finds their dial plan acting up strangely.

[ambiorixg12]

This solves the problem of the extra line feed ${cid:0:-1}

[ldo]

I dislike this kind of manipulation of command output strings. There are probably cases that you’ve overlooked, which will cause it to break in mysterious ways. Better to write a custom AGI script.