Database Changes

[MarkusHendersonicus]

Hi, I have a connection to a jdbc database. What I want to do is get notified, somehow, when a new record is added to the database by a different application. How would I get such a notification? I'm thinking I'd have to let derby know what Listener to call but don't know what the code would look like. Please could somebody either point me in the right direction or fling me a bit of sample java? Many Thanks, Mark.

[travishein]

hmm. from a different application, on its own different connection.
the only way i can think of is if the database supports triggers. i would then create a trigger on insert or update on the table, to insert a kind of log message for last updated date, and updated by (because the trigger should know the remote connection that did the update probably). and then my application would likely have to poll this table, where i would select * from update_log_table where last_modified > ? where the ? is the last time i queried the table. this is not ideal because your application has to then poll the database on some interval. but in theory should function.

[NigelRen]

I can't think of any way of automating the link to your application.
If you use unique sequential ID's as the identifier for your data, you can quite simply test the highest value and act if it changes. Made even easier if you use a database sequence or table to hold these high water marks, as you can just check the value on these tables.
This does mean your application has to poll the database, but done at a level which isn't going to affect the database too much.

[MarkusHendersonicus]

Thank you everybody for your replies! This tells me, along with the help from other forums that the 'off the shelf' solution I want is actually not available. So the answer is that I write my own Network Server which can push record updates to other applications as needed.

[Tolls]

Depending on your db, there are messaging solutions you could use, so you'd have a messaging server that then informs other systems (which would register with it) that there is an update (don't push the actual updates out).

JMS essentially.

[MarkusHendersonicus]

Thank you for your reply, Tolls.

The system would have, perhaps, 100 client applications in a real time risk management environment so I would want all clients to have the update as soon as possible. I was thinking that if I was going to send an update message then I might as well send the record that was updated itself.

The system would need to replicate added, updated and deleted records accross all 100 clients as quickly as possible.

[Tolls]

So this isn't a shared database that we're talking about here?
This is a load of copies that you're trying to keep tied together?

Have you looked at your database then? There might be replication type tools in there.

[MarkusHendersonicus]

Any of the 100 clients can add, update or delete records in the centralized database at any time. I need the other clients to be kept up to date on the actually database contents as quickly as possible. It's a full transactional replication situation in a client/server environment. In MS SQL there are threads to set the clients up as slaves and do this type of replication. However, I'm not finding anything in Java SE 6 that directly does the same thing. I think the designers wanted to keep Java relatively simple and not end up with a complex 'bloat-ware' product like Microsoft products.

[Tolls]

So the clients are cacheing data.
So I'd still have the messaging supply only table name and id (or something along those lines).

[MarkusHendersonicus]

So the clients would have to send a request for the changed record itself?

If it was implemented that way, wouldn't it mean that there is an extra set of to and fro messages required? If the server knows the client is going to need the record then why not send it attached to the update notification message?

[Tolls]

I wasn't sure that all your clients are the same.
But that does imply that the db needs to know that the clients need the data.

For me that breaks the encapsulation of various parts of your system. But you know your architecture better than I do.

[MarkusHendersonicus]

You are quite correct! The server will definitely need to know what records need to be sent to which clients. Conceptually though, for purposes of getting things off the ground, all records will go to all clients. Once that is working I'd just implement a filter mechanism in the server.

[Tolls]

But that's why I suggested only sending the basic data (id and table, or whatever). That way you send enough (again, not knowing your setup this could be way out) for the client to decide whether they're interested or not.

Obviously it's a balance based on how much a typical client would ignore. If ignoring an update is a rare event then sending the full update with the message probably makes sense.

Note, that I would hesitate leaving it up to the server to decide. I would consider a mechanism whereby the clients informed the server of types of update they're interested in. In my mind, in these situations, the server shouldn't give a toss.

[MarkusHendersonicus]

Thanks Tolls! I like your idea of the server being dumb and just doing simple things which is where I think you're coming from. Some records will change rarely and will only need to be sent to a single client, others will be changing every couple of seconds and will need to go out to perhaps 75 out of 100 clients! I think your suggestion that the clients telling the server which kinds of records it is interested in is the answer. That way the server can send the records themselves as part of the update notifications. Many thanks Tolls!

[NigelRen]

I think you are setting yourself up with a load of complications.
You have to be very careful with replication of data amongst a group of nodes. Keeping them in line - recovering from failures ( either re-boots or communication problems ).
Unless you have a huge number of records, I'd stick to each update loading all of the data - using block reads gives you much better performance than reading bits as well.

[MarkusHendersonicus]

Many thanks for all your answers, folks. I now know that I shall have to write my own server that manages all the logins and sends update messages. I don't like writing code that does polling as this just seems wrong to me. If the server knows(and it does) exactly what database operations are done and which clients either are or could be logged in then the server is ideally situated to 'generate events' and then send out either an update message or an update itself to any clients that 'need' it. There can't be a more efficient way of doing this in my eyes. Message traffic is kept to a minimum, latency times are minimized as well as there aren't any! Maybe I'm missing something but I can't see a more efficient solution than this. The server deciding which clients need to be sent an update would take a very tiny fraction of a second. As far as sending the updates out, the server could wait until it had, say, 10 updates to send and then send them in a block. For very time sensitive data, the server could send them instantly.

[DarrylBurke]

Abuse reported on user Java Forums - View Profile: Jaione

4 posts, all of which are a copy/paste repeat of another post higher up in the thread.

db

[Nikhilkumr]

the database connectivity so hard for me because I am new for it. But last 1 week I tried for it but I am not perfect for it. so can you help me for my project..
some topics new for me..
please help for my project..