2 like 0 dislike
15.2k views
in Education & Reference by | 15.2k views

3 Answers

0 like 0 dislike
Best answer
MySQL is free & robust, When we were developing a program for some voip calling implemenatation and we had a requirement to call some external programm when there is a change in one of our mysql table. And MySQL triggers are provided for the same purpose. Now it is easy.
 
MySQL provides a way to implement your own function called User Defined Function (UDF). How TO is "here" (http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html)
 
Now that we know, how to define your own functions and call them from MySQL events, we need to write our logic in a PHP program by following the interface provided by MySQL and we are done.
 
Wait a minute. That is already done by somebody. They have made a library of UDFs. One of them, LIB_MYSQLUDF_SYS does exactly what we want.
DOWNLOAD LIB_MYSQLUDF_SYS from below URL
OR
Direct Download
 
It allows you to call an external program from MySQL procedure/trigger. Once you download and untar the files, you'll get an install.sh. Just need to run it but, make sure you have gcc and make before that. The install script copies the library shared object file to /usr/lib. In some 64 bit systems it may be /usr/lib64. Also you need to check MySQL plugin_dir property which points to the directory where the .so file should be placed.
 
way to use plugin_dir
in my.cnf under
[mysqld]
plugin_dir=/usr/lib
 
Once the UDF is installed successfully, we can use 2 functions to call an external program -
 
1. sys_exec : Returns the exit code of the external program.
2. sys_eval : Returns the output from the external program.
 
Here is a code snippet which show an example:
 
DELIMITER @@
CREATE TRIGGER Trigger_one
AFTER INSERT ON Table_one
FOR EACH ROW 
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result int(10);
 SET cmd=CONCAT('php /var/www/html/asterisk/passvalue.php');
 SET result = sys_exec(cmd);
END;
@@
DELIMITER ;
 
You ARE DONE  NJOY!!
 
by (800 points)
selected by
0 0
I downloaded the library lib_mysqludf_sys 's source code, compiled,
and installed successfully.
ex:
mysql>  SELECT * FROM    mysql.func;
+-----------------------+-----+---------------------+----------+
| name                  | ret | dl                  | type     |
+-----------------------+-----+---------------------+----------+
| lib_mysqludf_sys_info |   0 | lib_mysqludf_sys.so | function |
| sys_get               |   0 | lib_mysqludf_sys.so | function |
| sys_set               |   2 | lib_mysqludf_sys.so | function |
| sys_exec              |   2 | lib_mysqludf_sys.so | function |
| sys_eval              |   0 | lib_mysqludf_sys.so | function |
+-----------------------+-----+---------------------+----------+
0 0
Please run the below command to install

[root@server lib_mysqludf_sys-master]# ./install.sh
Compiling the MySQL UDF
gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o /usr/lib/lib_mysqludf_sys.so
MySQL UDF compiled successfully

Please provide your MySQL root password
Enter password:
MySQL UDF installed successfully
0 0
I think it runs only in Mysql 5.0 version..right?
0 like 0 dislike
No need to use concat if you want to run simply PHP page..mine is working perfect

DELIMITER $$
    CREATE TRIGGER Feedback
    AFTER INSERT ON Feedback_Data
    FOR EACH ROW
       BEGIN
       DECLARE cmd CHAR(255);
       DECLARE result int(10);
    SET cmd='/usr/bin/php /var/www/html/survey/survey.php';
    SET result = sys_exec(cmd);

 END $$
 DELIMITER ;

But please be sure that you can not do anything on the same table where you are running a trigger..in other words trigger table and update/insert table can not be the same
by (1.4k points)
0 like 0 dislike
I installed the MySQL UDF library successfully but when i call the function sys_exec(), the command i sent as parameter does not run. Can anybody help me?
 
by (110 points)
0 0
But you use the sys_exec() right?
0 0
hey...why are you using two insert queries, please use second query with correct format and yes it should return a value
0 0
With two queries also works. My problem is the sys_exec() not run the external program in php, not the return value.
0 0
it runs the external program but be sure the path of PHP is correct...I used this way
SET cmd='/usr/bin/php /var/www/html/survey/feedback.php';
 SET result = sys_exec(cmd);

And It works for me
0 0
you can test if you have installed correctly

SELECT * FROM    mysql.func;

Related questions

3 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
1 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
1 like 0 dislike
1 answer

Where your donation goes
Technology: We will utilize your donation for development, server maintenance and bandwidth management, etc for our site.

Employee and Projects: We have only 15 employees. They are involved in a wide sort of project works. Your valuable donation will definitely boost their work efficiency.

How can I earn points?
Awarded a Best Answer 10 points
Answer questions 10 points
Asking Question -20 points

1,210 questions
1,356 answers
554 comments
2,099 users