Database Trigger And JMS

Question

Cound I create a trigger to send a record message to JMS? If yes, how can I do? Thanks in advance!

Answer 1

I would summarize your options as follows:

Databases Supporting JMS

Oracle is the only database that I am aware of that supports JMS natively in the form of Oracle Advanced Queueing. If your message receiver is not too keen on that JMS implementation, it is usually possible to find some sort of messaging bridge that will transform and forward messages from one JMS implementation to another. For example:

• Apache Active MQ
• JBoss Messaging

Databases Supporting Java

Some databases such as Oracle and DB2 have a built in Java Virtual Machine and support the loading of third party libraries (Jars) and custom classes that can be invoked by trigger code. Depending on the requirements of your JMS client, this be an issue on account of the version of Java supported (if you need Java 5+ but the DB only supports Java 3). Also keep in mind that threading in some of these embedded JVMs is not what you might expect it to be, but, one might also expect that the sending of JMS messages might be more forgiving of this than the receiving of the same.

Databases Supporting External Invocations (but not in Java)

Several databases support different means of triggering asynchronous events out to connected clients which can in turn forward JMS messages built from the payload of the event:

• Oracle: DBMS_ALERT (synchronous), DBMS_PIPE, DCN
• Postgres: SQLNotify

Some databases (all of the above and including SQLServer) allow you to send SMTP messages from database procedural code (which can be invoked by triggers). While it is not JMS, a mail listener could potentially listen for mail messages (that might conveniently have a JSON or XML message body) and forward this content as a JMS message.

A basic alternate of this is databases packages that allow HTTP posts to call out to external sources where you might have a servlet listening and forwarding the submitted content as a JMS message.

Other databases such as Postgres support non-java languages such as Perl, Python and Tcl where you might employ some clever scripting to send a message to an external message transformer that will forward as JMS. Active MQ (and therefore its message bridge) supports a multi-language JMS client that includes Python and Perl (and many others).

Lowest Common Denominator

Short of all that, your trigger can write an event to a table and an external client can poll the contents of the table, looking for new data and forwarding JMS messages when it finds it. The JMS message can either include the content, or simply indicate that content exists and what the PK is, and the consumer can come and get it.

This is a technique widely supported in Apache Camel which has adapters (technically called Components) specifically for polling databases:

• Hibernate
• JDBC
• SQL
• iBatis

Events read from a database table by Camel can then be transformed and routed to a variety of destinations including a JMS server (in the form of a JMS message). Implementing this Camel is fairly straight forward and well documented, so this is not a bad way to go.

I hope this was helpful.