Execution of a Java program by a trigger.

Question

I want to make an update trigger execute a Java program.

I have a table T1 with a column named Flag. Whenever Flag changes, I want to run a trigger that results in an execution of a Java program.

Is this possible?

• Remus Rusanu: What if the transaction rollback later, will the execution be undone? Call back the missile? You do not solve this through triggers, you use queues and fire the program after commit.
• simplifiedDB: Can you please explain a bit , what im doing is i have only two values in Flag i.e True and False , every time value toggles , java program should run
• Justin Cave: What database are we talking about? Is the Java program running inside the database (i.e. a Java stored procedure in Oracle)? Or are you trying to execute an external application? Does that application reside on the database server or on a different machine?
• simplifiedDB: Database - MySql Java program is external programm resides on a same machine

Answer 1

Yes, using the MySQL User-Defined Function (see MySQL 5.5 FAQ: Triggers) and installing the lib_mysqludf_sys

Then, for example, you can write your own trigger calling the sys_exec like this:

delimiter |
 CREATE TRIGGER testtrigger BEFORE UPDATE ON T1
 FOR EACH ROW BEGIN
 DECLARE result int(10);
 IF NEW.Flag <> OLD.Flag THEN 
 SET result = sys_exec('/path/to/javabin -jar your.jar');
 -- other kind of works and checks...
 END IF;
 END;
|

The result contains the exit code of the external program

There are other useful functions in this library:

• sys_eval : executes an arbitrary command, and returns it's output.
• sys_get : gets the value of an environment variable
• sys_set : create an environment variable, or update the value of an existing environment variable
• sys_exec : executes an arbitrary command, and returns it's exit code

More info here

Try it on a dev env and...

Be very careful in deciding whether you need this function. UDFs are available to all database users - you cannot grant EXECUTE privileges for them. As the commandstring passed to sys_exec can do pretty much everything, exposing the function poses a very real security hazard.

But anyway, I agree with the proposal of Remus Rusanu

• simplifiedDB: how can i install the lib_mysqludf_sys utility ??
• Cristian Porta: Take a look into "installation" section here: github.com/mysqludf/lib_mysqludf_sys/blob/master/… ... but in general read the mysql udf manual
• simplifiedDB: getting this error -----CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so'; ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_sys.so' (errno: 0 /usr/lib/mysql/plugin/lib_mysqludf_sys.so: wrong ELF class: ELFCLASS32)
• Cristian Porta: do you have a 32bit or 64bit operating system?
• simplifiedDB: its 64 bit ubuntu 0.12...

Answer 2

I know this post is fairly old and sorry for picking it up but I recently had the same requirement, so I created a MySQL UDF that uses JNI to call Java code from within a MySQL trigger. I'm posting this as a reference for similar future requests. I've checked in the code in bitbucket. You can download it from here:
MySQLUDFJavaLauncher and here is also a link to the Instructions.
Maybe this will help others.