Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database?

I work with a MS SQL Server 2008 (and .NET applications + Entity Framework), but I'd like to get knowing about other types of databases as well.

share|improve this question
    
Microsoft StreamInsight is specifically designed to enable "data event"-based processing. I don't know much about it, but it looks like a complete and independent platform rather than an individual feature of SQL Server. Here's a StreamInsight architecture diagram. –  Nick Chammas May 17 '12 at 3:27

7 Answers 7

up vote 5 down vote accepted

Service Broker for SQL Server 2005+ can do this.

Sorry, I'm not sure of other RDBMS

share|improve this answer
2  
Can you elaborate on how Service Broker does this? @rem is asking about retrieving event-triggered (as opposed to time-triggered) updates from a database. SB is for queuing and asyc processing. –  Nick Chammas Sep 12 '11 at 19:37
    
-1 Service Broker does not seem have anything built into it to let an app know that it has received a message. (I am currently stuck on this issue.) –  Vaccano Aug 30 '13 at 20:43

In Oracle you can use the built in DBMS_ALERT package to facilitate this.

DBMS_ALERT supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.

Suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which performs a signal (SIGNAL) whenever the trigger is fired.

share|improve this answer
    
And for "MS SQL Server 2008" as per OPs question? They asked for other RDBMS but this doesn't actually help. –  gbn Mar 4 '11 at 20:22
9  
@gbn Why doesn't it help? I can't answer the SQL Server part like you can, so I answered the part I could. No one is an expert on all other databases, but if we each contribute our knowledge the OP and future visitors will find useful information. I would expect the OP to accept an SQL Server answer and up-vote other useful answers like ScottCher's (+1). –  Leigh Riffel Mar 4 '11 at 20:58
    
well said. After reading your DBMS_ALERT answer, I learned another way of doing this - more tools for the toolbox! +1 –  ScottCher Mar 4 '11 at 22:08

Certain database vendors also provide integrated message buses that your app can simply subscribe to:

An alternative would be to route the data into the database in the first place via a message bus like Tibco/RV and simply "branch" it, on stream going into the DB and one going to your application, or use a caching layer like Coherence between your app and the DB.

share|improve this answer

Another Oracle solution: We've developed applications using the dotnet framework from Microsoft that take advantage of Database Change Notification feature of Oracle in conjunction with ODP.Net (the Oracle data provider for dotnet). Using this, the database actually notifies the dotnet application when new data has arrived allowing us to avoid constant polling. The link I reference above is an Oracle tutorial for doing just that. Hope this helps you out.

Don't know about any other RDBMS's.

share|improve this answer

LISTEN / NOTIFY for PostgreSQL

http://www.postgresql.org/docs/current/static/sql-notify.html

in the database...

NOTIFY static_channel_name, 'static-message';

or in a function/trigger:

perform pg_notify('dynamic-channel-name', 'dynamic-message');

in the database client:

LISTEN some_channel_name; --note the lack of quotes

The LISTEN client will receive the PostgreSQL process ID, channel name, and message value.

share|improve this answer

For one of our applications (accessed trough Chrome and ONLY Chrome), we're using MySQL with sys_exec UDF. Basically, why Chrome - due to WebSocket support.

Once a critical update / insert / delete occurs, an external program is invoked trough newly added sys_exec functionality embedded in certain triggers. At that point, we've got all we need to relay the message to every connected client without any need for polling or multiple queries as everything happens real-time.

share|improve this answer

We use a combination of Oracle GoldenGate and the Java Persistence API (JPA) to do this with Oracle database, but also with DB2, Sybase, Microsoft SQL Server, MySQL, Teradata, etc. The feature is described here: http://docs.oracle.com/middleware/1212/coherence/COHIG/golden_g.htm

What GoldenGate does is turn the database transaction log into a filterable event stream that can be consumed anywhere across the network. We use it to turn relevant transactions into either cache updates or cache invalidations, both of which can trigger application-level events, e.g. pushing the data all the way out to desktop or HTML5 websocket applications.

(For full disclosure, I work at Oracle on one of the products using GoldenGate.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.