jdbc: Asking the DB-server for a notification

[Mikhail Teterin]

Hello!

How can I ask the DB-server to notify my JDBC-program any time a table is
modified? Trigger?..

Although the server we are currently dealing with is MS SQL, the ideal
solution will be JDBC-generic...

Thanks!

-mi

 

[Arne Vajhøj]

How can I ask the DB-server to notify my JDBC-program any time a table is
modified? Trigger?..

Although the server we are currently dealing with is MS SQL, the ideal
solution will be JDBC-generic...

No generic JDBC solution exists.

Maybe the JDBC driver has some SQLServer specific classes that
support notification, but I doubt it.

I can see two solutions that will work on SQLServer and be
possible to reimplement with at least some other databases:

--(update)--SQLServer--(trigger)--MSMQ--(JNI)--Java app

--(update)--SQLServer--(CLR trigger)--(socket)--Java app

and:

--(update)--database X--(trigger)--some MQ--(JMS)--Java app

--(update)--database X--(trigger in Java)--(socket)--Java app

Arne

 

[sasuke]

--(update)--database X--(trigger)--some MQ--(JMS)--Java app

--(update)--database X--(trigger in Java)--(socket)--Java app

Arne

Hmm...interesting. A simple google search obviously didn't turn up
anything of interest.

Any links on this would be really appreciated.

Regards,
/sasuke.

 

[Mikhail Teterin]

Hmm...interesting. A simple google search obviously didn't turn up
anything of interest.

Any links on this would be really appreciated.

Well, all servers nowadays allow custom extensions to offer new functions
available in SQL.

One could implement such a function, which will be called from the trigger
and notify the client program somehow -- "out of band".

That's Arne's idea, and it is doable.

However, I was hoping, something more uniform is already available, but that
does not seem to be the case

-mi

 

[TwelveEighty]

One could implement such a function, which will be called from the trigger
and notify the client program somehow -- "out of band".

That's Arne's idea, and it is doable.

However, I was hoping, something more uniform is already available, but that
does not seem to be the case

I would solve this a little further upstream, if possible. What, for
example, causes the table to change? Is there someone or something
that creates a transaction that enters data? Could your program become
the "business logic" layer that sits in between that and the database?

 

[Lew]

I would solve this a little further upstream, if possible. What, for
example, causes the table to change? Is there someone or something
that creates a transaction that enters data? Could your program become
the "business logic" layer that sits in between that and the database?

Isn't this the sort of thing they invented BPEL for?

The insight is that business processes interlock and interact. A process
control system, perhaps with a process control language like BPEL gluing
things together, orchestrates the various business processes that must
coordinate, as in this case, a web application and a database application.

Custom programming can do the same thing. I suspect that thinking of the
custom system as a business-process orchestrator would be useful.

This is a problem space that has been approached for decades, and for which
there are many buzzwords, none of which seem to have emerged as a clear winner.

 

[TwelveEighty]

Isn't this the sort of thing they invented BPEL for?

The insight is that business processes interlock and interact. A process
control system, perhaps with a process control language like BPEL gluing
things together, orchestrates the various business processes that must
coordinate, as in this case, a web application and a database application.

The OP never mentions a web application, just a database, but yes, in
theory BPEL should do the trick. But I find that BPEL today is still
"immature" in a sense that it still feels like that '50s IBM computer:
big, bulky, expensive, hard to use....