use sql trigger call java function

Use UDF sys_exec to do this.

You can use this link to use sys_exec function. It says,

sys_exec sys_exec takes one command string argument and executes it. Syntax

sys_exec(arg1) Parameters and Return Values

arg1 : A command string valid for the current operating system or execution environment. returns An (integer) exit code returned by the executed process. Installation

Place the shared library binary in an appropriate location. Log in to mysql as root or as another user with sufficient privileges, and select any database. Then, create the function using the following DDL statement: CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so'; The function will be globally available in all databases. The deinstall the function, run the following statement: DROP FUNCTION sys_exec;

For executing Java program you should fill arg1 as "java <absolute path to precompiled program to run>".  Like,sys_exec('java /home/Desktop/helloWorld') this is passed as an argument. helloWorld here is a class which is being not called by exec function.

Note: path to java should be configured before hand.

Problem: I've got a table which holds certain records. After the insert has been done, I want to call an external program (php script) via MySQL's sys_* UDFs. Now, the issue - the trigger I have passes the ID of the record to the script. When I try to pull the data out via the script, I get 0 rows. During my own testing, I came to a conclusion that the trigger invokes the php script and passes the parameters BEFORE the actual insert occured, thus I get no records for given ID. I've tested this on MySQL 5.0.75 and 5.1.41 (Ubuntu OS). I can confirm that parameters get passed to the script before actual insert happens because I've added sleep(2); to my php script and I've gotten the data correctly. Without sleep(); statement, I'm receiving 0 records for given ID.

My question is - how to fix this problem without having to hardcode some sort of delay within the php script? I don't have the liberty of assuming that 2 seconds (or 10 seconds) will be sufficient delay, so I want everything to flow "naturally", when one command finishes - the other gets executed.

I assumed that if the trigger is of type AFTER INSERT, everything within the body of the trigger will get executed after MySQL actually inserts the data.

Table layout:

CREATETABLE test (
id int notnull auto_increment PRIMARYKEY,
random_data varchar(255)notnull);

Trigger layout:

DELIMITER $$CREATETRIGGER`test_after_insert` AFTER INSERTON`test`FOR EACH ROWBEGINSET@exec_var = sys_exec(CONCAT('php /var/www/xyz/servers/dispatcher.php ', NEW.id));END;$$

DELIMITER ;

Disclaimer: I know the security issues when using sys_exec function, my problem is that the MySQL doesn't insert FIRST and THEN call the script with necessary parameters. If anyone can shed some light on how to fix this or has a different approach that doesn't involve SELECT INTO OUTFILE and using FAM - I'd be very grateful. Thanks in advance.

 

 

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

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

delimiter |CREATETRIGGER testtrigger BEFORE UPDATEON T1
  FOR EACH ROWBEGINDECLARE result int(10);IF NEW.Flag <> OLD.Flag THENSET result = sys_exec('/path/to/javabin -jar your.jar');-- other kind of works and checks...ENDIF;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_execcan do pretty much everything, exposing the function poses a very real security hazard.