jdbc: Asking the DB-server for a notification  
Index ‹ java-programmer
Author Message
Mikhail Teterin
PostPosted: 2007-12-27 4:33:00 Top
java-programmer, jdbc: Asking the DB-server for a notification 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鴍
PostPosted: 2007-12-27 4:59:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification Mikhail Teterin wrote:
> 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
 
Mikhail Teterin
PostPosted: 2007-12-27 5:31:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification 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鴍
PostPosted: 2007-12-27 8:11:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification Mikhail Teterin wrote:
> 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...

Already posted once and answered once.

Arne

PS: At least my NNTP server does not allow posts to comp.lang.java, so
followup to that is not so good.
 
 
sasuke
PostPosted: 2007-12-28 2:00:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification On Dec 27, 1:58 am, Arne Vajh鴍 <email***@***.com> wrote:
> --(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
PostPosted: 2007-12-28 2:18:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification Arne Vajh酶j wrote:
> Already posted once and answered once.

The second post was supposed to replace (supersede) the earlier one.
Unfortunately, most NNTP-servers appear to simply ignore the
Supersedes:-header :(

-mi
 
 
Mikhail Teterin
PostPosted: 2007-12-28 2:23:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification sasuke wrote:
> 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
PostPosted: 2007-12-28 5:23:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification On Dec 27, 11:23 am, Mikhail Teterin <usenet+email***@***.com>
wrote:
> 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
PostPosted: 2007-12-28 8:15:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification Mikhail Teterin wrote:
> Arne Vajh酶j wrote:
>> Already posted once and answered once.
>
> The second post was supposed to replace (supersede) the earlier one.
> Unfortunately, most NNTP-servers appear to simply ignore the
> Supersedes:-header :(

Maybe it doesn't work after the earlier post already received an answer?

--
Lew
 
 
Lew
PostPosted: 2007-12-28 8:18:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification TwelveEighty wrote:
> On Dec 27, 11:23 am, Mikhail Teterin <usenet+email***@***.com>
> wrote:
>> 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?

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.

--
Lew
 
 
TwelveEighty
PostPosted: 2007-12-29 9:04:00 Top
java-programmer >> jdbc: Asking the DB-server for a notification On Dec 27, 5:17 pm, Lew <email***@***.com> wrote:

> > 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.

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....

 
 
 
Index ‹ java-programmer