• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

How to automatically push data

 
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Just needs some technical advice or suggestion here. I am building an application with a functionality that automatically pushes data from the database to a report display in the front-end. That is, every time a certain database table has been changed, the data should be automatically pushed to the front-end display, which is basically some statistical graphs.

I don't know how to go about this, particularly with the part that automatically pushes the data. Should there be a listener of some sort that checks for a database update? The technologies to use are Spring MVC and Ibatis for the front-end and controller, Jasper Reports for the graphs and report displays, and Oracle 10g for the database.

Feel free to offer up some advice.

Thanks,

Eugene
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What exactly do you mean by "report display in the front-end"? Web pages that are already open at the time of the updates? A running desktop app? Or something else?

Most Databases have a concept of triggers that can be fired automatically if certain events (like a table change) occurs, but I would shy away from using that to reach outside of the DB. That would be a twisted design.

The client could check with the app/web server periodically for updates (say, once per minute), and only if there are updates would the page/report be updated. The call itself could be an AJAX call invisible to the user.

The app/web server wouldn't have to poll the DB every time one of the clients asked for updates - it could itself check in with the DB periodically, and prefetch the required data if there was a change. Thus the DB load wouldn't rise if there were more and more clients.
 
Eugene Abarquez
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ulf,

Thanks for the reply.

Originally posted by Ulf Dittmer:
What exactly do you mean by "report display in the front-end"?



It's just a graph (generated by jasper report) on a web page.

Originally posted by Ulf Dittmer:
The app/web server wouldn't have to poll the DB every time one of the clients asked for updates - it could itself check in with the DB periodically, and prefetch the required data if there was a change. Thus the DB load wouldn't rise if there were more and more clients.



What do you mean by the app/web server checking in with the DB periodically? Does this mean that I would have to create an object that continually queries the database for changes, say every minute? Wouldn't this affect performance, as a database query is done every minute?

Also, I am not sure how to go about the checking for updates in the database. Should I have a counter for the number of rows in the table and prefetch the data if the value of the counter changes? What if the user only updated a row? Does this mean that I would have to check every record against the old ones?

Originally posted by Ulf Dittmer:
Most Databases have a concept of triggers that can be fired automatically if certain events (like a table change) occurs, but I would shy away from using that to reach outside of the DB. That would be a twisted design.



Yes I am considering the use of triggers. I am interested to know though why you think this would be a twisted design.

Thanks,

Eugene
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

What do you mean by the app/web server checking in with the DB periodically? Does this mean that I would have to create an object that continually queries the database for changes, say every minute? Wouldn't this affect performance, as a database query is done every minute?


You can use whatever time period is appropriate. I was assuming that this would be a query that doesn't take a long time to finish.

The alternative would be to do it whenever a client asks for an update, which could potentially be much more often than once a minute (depends on the number of users, obviously).

Also, I am not sure how to go about the checking for updates in the database. Should I have a counter for the number of rows in the table and prefetch the data if the value of the counter changes? What if the user only updated a row? Does this mean that I would have to check every record against the old ones?


Changes in the number of rows could be detected by running a query like "select count(*) from abc where def" instead of "select * from abc where def". That prevents fetching the data when it's not necessary, but doesn't help much if the goal is to reduce DB load (because the query essentially still needs to be executed). It can also be fooled if both inserts and deletes can happen.

How about setting a flag on the app server whenever some user makes a change to the DB that should result in the query result being refreshed? The next time one of the clients polls the server for updates, it would go out to read the data, cache it on the app server, and reset the flag. From that point on the clients would get the data from the app server, without the need to access the DB at all. Only once the next change was made to the DB would it be re-queried.

Yes I am considering the use of triggers. I am interested to know though why you think this would be a twisted design.


Because that's part of the application logic, and that should not live inside of the DB. DBs should be called by other processes for data storage services, not act on their own.
[ November 19, 2008: Message edited by: Ulf Dittmer ]
 
Eugene Abarquez
Ranch Hand
Posts: 211
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Ulf for taking the time to answer this. I will seriously consider all of your inputs. Thanks again.
 
reply
    Bookmark Topic Watch Topic
  • New Topic