Java run by MySQL trigger

Question

I am trying to create some MySQL code that will invoke a Java program from a trigger.

Here is what I have so far:

CREATE TRIGGER trig_name after insert ON studentinfo 
FOR EACH ROW
BEGIN

END

The trigger content would then call the Java program. Is this possible?

Answer 1

Though not a standard feature this is very well possible with MySQL. You can use the SELECT .. INTO OUTFILE statement from inside the trigger to write to a named pipe (Windows) or memroy filesystem (Linux). Both of those can easily be monitored from Java code (or any other code for that matter). Using this technique you will avoid polling and because no actual disk access takes place either you will have good performance.

I have written a Java package for this actually so I'm 100% sure it is possible and performs well. Unfortunately I am not allowed to share my efforts here (my previous answer was deleted by a moderator) so you will have to code it yourself, sorry.

Answer 2

A direct answer: no you can't call a java method from a mysql trigger. If you had an oracle database you could, but not mysql.

To do what you want to do with mysql you can

• make the code that updates the database also notify the swing application. Or you can
• make the trigger accumulate data on pending operations in a separate table that you read periodically from the swing app.

Answer 3

Calling a java method from an SQL database isn't a standard feature. The Informix DB can call a shell script from a stored procedure, but I don't know of a feature like this in MySQL (I'm not an expert on mysql).

The closest thing that works with all databases would be to have a thread and periodically poll the database for new records.

SELECT * FROM studentinfo WHERE id > last_seen_id

Or you could use a timestamp:

SELECT * FROM studentinfo WHERE create_date >= last_seen_create_date

In this case you would have to filter duplicated rows which have already loaded from the previous run.