Connect DialPlan to Remote Mysql Database

This is a very simple yet effective script that uses the mysql client to connect remotely to a mysql database. The script can be initiated from the DialPlan using System Command

Example of DialPlan Line <extentions.conf>

exten => s,1,System('mmysql.sh "INSERT INTO asteriskcdrdb.calls (phone,act,runtime) VALUES(~CALLERID(Number)~,~Dialed~,~12:00AM~)"')


Example of the .sh Script to be written <mmysql.sh>

hld=`echo ${1}|sed "s/\~/\'/g"`
mysql -h xxx.xxx.xxx.xxx -uasteriskuser -pasteriskpassword -D 'asteriskcdrdb' -e "$hld"


Note that all iterations of quote ['] are shown as tilda [~] The reason for this is because of the way the
System function handles quotes will not allow you to put them into your SQL statement.
So if you notice the first line of the .sh Script has a conversion back to the real ['] from [~]

The xxx.xxx.xxx.xxx can be replaced with the ip address or host of the remote mysql database.
I would also suggest for security that you create a seperate user in your remote mysql user list
that can only connect from the host you are putting this on.

The variable hld that uses the sed command works like this

`echo ${1}|sed "s/\~/\'/g"`

-->`echo ${1} (echo's the first command line parameter to the sed command)
--> | (pipes the command line parameter to the other side of the line)
--> sed "s/\~/\'/g"` ( s means separate files. / is a separator in the command
\~ represents the ~ symbol. / another separator. \' represents the '
/ another separator. g stands for global meaning the whole string
Note: Charaters must be escaped with \ to be swapped correctly)


This can be done for any character with a simple swap in the SQL and an extra line in the .sh statement to do the same thing.

Note: the mmysql.sh script must be put in a place where your system knows the path too such
as /bin or /usr/bin. if you are not sure then type echo $PATH at the command prompt

Thanks
you can email me at schapman1974@gmail.com if you have any questions.

12192

See also

This is a very simple yet effective script that uses the mysql client to connect remotely to a mysql database. The script can be initiated from the DialPlan using System Command

Example of DialPlan Line <extentions.conf>

exten => s,1,System('mmysql.sh "INSERT INTO asteriskcdrdb.calls (phone,act,runtime) VALUES(~CALLERID(Number)~,~Dialed~,~12:00AM~)"')


Example of the .sh Script to be written <mmysql.sh>

hld=`echo ${1}|sed "s/\~/\'/g"`
mysql -h xxx.xxx.xxx.xxx -uasteriskuser -pasteriskpassword -D 'asteriskcdrdb' -e "$hld"


Note that all iterations of quote ['] are shown as tilda [~] The reason for this is because of the way the
System function handles quotes will not allow you to put them into your SQL statement.
So if you notice the first line of the .sh Script has a conversion back to the real ['] from [~]

The xxx.xxx.xxx.xxx can be replaced with the ip address or host of the remote mysql database.
I would also suggest for security that you create a seperate user in your remote mysql user list
that can only connect from the host you are putting this on.

The variable hld that uses the sed command works like this

`echo ${1}|sed "s/\~/\'/g"`

-->`echo ${1} (echo's the first command line parameter to the sed command)
--> | (pipes the command line parameter to the other side of the line)
--> sed "s/\~/\'/g"` ( s means separate files. / is a separator in the command
\~ represents the ~ symbol. / another separator. \' represents the '
/ another separator. g stands for global meaning the whole string
Note: Charaters must be escaped with \ to be swapped correctly)


This can be done for any character with a simple swap in the SQL and an extra line in the .sh statement to do the same thing.

Note: the mmysql.sh script must be put in a place where your system knows the path too such
as /bin or /usr/bin. if you are not sure then type echo $PATH at the command prompt

Thanks
you can email me at schapman1974@gmail.com if you have any questions.

12192

See also

Created by: schapman, Last modification: Fri 01 of Jun, 2012 (04:31 UTC) by admin
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+