how my Database can send me a message?

[Shrish]

Hi Everybody,

I have a Database and two hosts- host1 and host2. When host1 makes a
change in Database, I want Database to send a message to host2. I am
wondering how can I make it possible. One way is to have an
application runing in Database and host2 whicuh talk through sockets,
and I assume there would be a way for database to launch that
application. Is there some other way too?

I am using Java, and JDBC for database connectivity. I am using
PostGRESQL database.

Thanks,
Shrish

 

[Chris Smith]

I have a Database and two hosts- host1 and host2. When host1 makes a
change in Database, I want Database to send a message to host2. I am
wondering how can I make it possible. One way is to have an
application runing in Database and host2 whicuh talk through sockets,
and I assume there would be a way for database to launch that
application. Is there some other way too?

I am using Java, and JDBC for database connectivity. I am using
PostGRESQL database.

There is no standard SQL way to deal with this, but PostgreSQL does
provide a non-standard way to solve this problem. It's called LISTEN
and NOTIFY. You can set up a trigger in the data to NOTIFY when data is
changed. You'll need a dedicated thread on the Java side to poll
Connection.getWarnings() for the notification message, since JDBC
doesn't provide any sort of asynchronous callback.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Kieron Briggs]

There is no standard SQL way to deal with this, but PostgreSQL does
provide a non-standard way to solve this problem. It's called LISTEN
and NOTIFY. You can set up a trigger in the data to NOTIFY when data is
changed. You'll need a dedicated thread on the Java side to poll
Connection.getWarnings() for the notification message, since JDBC
doesn't provide any sort of asynchronous callback.

If you're using a polling thread anyway, you can just poll for a row in
a table to indicate that new data is available, and not need to bother
with the non-standard triggers. Just do a select every minute, or second
or hour depending on your needs (simple timeliness vs cpu load tradeoff).


So in answer to the OP's question, you can't have the database send a
message, but you can have host2 ask for one as often as you like...


Kieron Briggs

 

[John Harlow]

Hi Everybody,

I have a Database and two hosts- host1 and host2. When host1 makes a
change in Database, I want Database to send a message to host2. I am
wondering how can I make it possible. One way is to have an
application runing in Database and host2 whicuh talk through sockets,
and I assume there would be a way for database to launch that
application. Is there some other way too?

When you say "host" do you mean java application? It's quite simple for a
java app to create a listening socket which the database can (through an
update trigger, for example) call. I do this all the time to instantly let
services know there is work ready for them. I include a poller loop too "as
a backup". I do this so often I went ahead and included the functionality
in my database connection class.

Another less elegant way is for the database to create a file wherein the
app hooks to the OS to get file system change notifications.

 

[Chris Smith]

If you're using a polling thread anyway, you can just poll for a row in
a table to indicate that new data is available, and not need to bother
with the non-standard triggers. Just do a select every minute, or second
or hour depending on your needs (simple timeliness vs cpu load tradeoff).

That's certainly a possibility. However, depending on the quantity of
data being monitored, PostgreSQL's LISTEN/NOTIFY could make an immense
difference in how feasible the task is, versus polling for actual data
in tables.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation