6

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?

  • 6
    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. – Remus Rusanu Apr 8 '13 at 19:08
  • 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 – Ankit Kapoor Apr 8 '13 at 19:11
  • 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? – Justin Cave Apr 8 '13 at 19:13
  • Database - MySql Java program is external programm resides on a same machine – Ankit Kapoor Apr 8 '13 at 19:14
6

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

  • how can i install the lib_mysqludf_sys utility ?? – Ankit Kapoor Apr 9 '13 at 12:53
  • Take a look into "installation" section here: github.com/mysqludf/lib_mysqludf_sys/blob/master/… ... but in general read the mysql udf manual – Cristian Porta Apr 9 '13 at 19:22
  • 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) – Ankit Kapoor Apr 10 '13 at 13:01
  • do you have a 32bit or 64bit operating system? – Cristian Porta Apr 10 '13 at 13:21
  • its 64 bit ubuntu 0.12... – Ankit Kapoor Apr 10 '13 at 13:25
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.