Subject Re: [Firebird-Java] Re: firebird stored procedure for calling java program.
Author Roman Rokytskyy
> I want to use this for production. I want to call java program whenever new row inserted to table. Please give me some suggestions.

I think better approach would be to write a small server code in Java
that would be notified about adding rows using Firebird events and that
new Java application would in turn call your Java code.

Your Java code would look something like this:

import org.firebirdsql.event.*;

....

FBEventManager eventManager = new FBEventManager();
eventManager.setHost(DB_HOST);
eventManager.setUser(DB_USER);
eventManager.setPassword(DB_PASSWORD);
eventManager.setDatabase(DB_PATH);

eventManager.connect();

eventManager.addEventListener(
"MY_NAMED_EVENT_AFTER_INSERT",
new EventListener() {
public void eventOccurred(DatabaseEvent event) {
// call my Java code here
}
}
);

Please note that EventManager starts a daemon thread in which it calls
the registered listeners. That means that if your main thread ends, the
dispatcher thread will die too. In other words you have to take measures
to keep your Java application running. For example, you can do the
following:

Object blockObject = new Object();
blockObject.wait();

That will wait until some other code calls blockObject.notify() (and if
you do not have such code, it will wait indefinitely, but will exit if
you use Ctrl-Break).


Your trigger in the database would look like this:

CREATE TRIGGER my_trigger FOR my_table AFTER INSERT AS
BEGIN
POST_EVENT 'MY_NAMED_EVENT_AFTER_INSERT';
END

this would notify about newly inserted records. You can post other
events on updates or deletes (you would need AFTER UPDATE and AFTER
DELETE TRIGGERS).

However, you have to know that it is not possible to pass parameters in
the POST_EVENT call. This means you cannot pass, for example, an ID of
the record that was inserted or updated. You would need to add some flag
to a record (if needed) to identify whether the record is new or not and
then in your event handler code you have to execute a query to identify
the affected records.

The reason for such behavior is that event notification is not
synchronous, and your event handler might be called only once even
multiple POST_EVENT calls were called on the server.

Roman