Database notification

Thread view:

jwillans@cs.york.ac.uk - 28 Apr 2005 17:26 GMT

Hi All,

We've have a requirement to write an application that shares data using
a centralised repository. The application will be written in Java and
we may potentially use MySQL as the data store using JDBC drivers to
bridge the two. One of the key requirements is that the java
application is notified when the data store is updated or changed such
that it can dynamically update its view to the user. Do any of the
JDBC drivers provide anything that may support this?

James

Reply to this Message

frebe - 28 Apr 2005 19:18 GMT

You need to use trigger for this.

Fredrik Bertilsson
http://butler.sourceforge.net

Reply to this Message

Paul van Rossem - 28 Apr 2005 20:13 GMT

> Hi All,
>
[quoted text clipped - 7 lines]
>
> James

> You need to use trigger for this.
>
> Fredrik Bertilsson
> http://butler.sourceforge.net

No, triggers start stored procedures on the server side, but nothing on
the client side. JDBC doesn't provide this kind of mechanism. You have
to use a timer in each client and check for changes in the database at
certain intervals (for instance every 10 secs or so). It helps if you
add a table with last modification timestamps, for instance one per
table, so that your client needs to perform only simple checks.

Paul.

Reply to this Message

Lee Fesperman - 28 Apr 2005 23:35 GMT

> > Hi All,
> >
[quoted text clipped - 19 lines]
> add a table with last modification timestamps, for instance one per
> table, so that your client needs to perform only simple checks.

JDBC doesn't provide a notification mechanism, but if your JDBC driver supports it, you
can use TYPE_SCROLL_SENSITIVE when creating the statement for the resultset. Then, you
can use rowUpdated()/rowDeleted()/rowInserted() to detect changes ... by scanning each
row in the resultset.

Signature

Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)

Reply to this Message

frebe - 29 Apr 2005 07:43 GMT

> No, triggers start stored procedures on the server side, but nothing on
> the client side.

Yes, that stored procedure can send a message (like JMS
publish/subscribe) to the client. I don't know what kind of languages
MySQL supports for stored procedures. If it doesn't support java, you
might consider switching to a database that support java.

> JDBC doesn't provide this kind of mechanism. You have
> to use a timer in each client and check for changes in the database at
> certain intervals (for instance every 10 secs or so).

Polling is a solution, but a rather primitive one.

> It helps if you
> add a table with last modification timestamps, for instance one per
> table, so that your client needs to perform only simple checks.

You would still need triggers that updates the new timestamp table. If
you use this approach I would recommend a table like this,
log(id, tablename, columnname, timestamp)
and a trigger on every other table in your database that inserts a
record in this one.

Fredrik Bertilsson
http://butler.sourceforge.net

Reply to this Message

Mark Matthews - 29 Apr 2005 15:12 GMT

>>No, triggers start stored procedures on the server side, but nothing
>
[quoted text clipped - 6 lines]
> MySQL supports for stored procedures. If it doesn't support java, you
> might consider switching to a database that support java.

MySQL-5.0 supports triggers, and while it doesn't yet support Java
stored procedures (scheduled for 5.1), you can write UDFs in any
language that can produce a DLL and then have the trigger call your UDF.

One of our guys (before he joined MySQL) wrote a UDF that uses JNI to
let people write their UDFs in Java, so relatively easily you could put
triggers on tables that then use Java-based messaging of one sort or
another to notify the client.

The package (jUDF) is available here:

http://freshmeat.net/projects/judf/?topic_id=66

-Mark

Reply to this Message

Piotr Gaertig /Gertas/ - 02 May 2005 22:35 GMT

On 4/29/2005 4:12 PM, cyber knight Mark Matthews gave sign:

>> Yes, that stored procedure can send a message (like JMS
>> publish/subscribe) to the client. I don't know what kind of languages
[quoted text clipped - 9 lines]
> triggers on tables that then use Java-based messaging of one sort or
> another to notify the client.

But mysql-5 is beta and these workarounds are uncertain (unstable).
Maybe James should use another DBMS:

I think PostgreSQL + PL/Java module (both stable, BDS-license) are an option.
http://gborg.postgresql.org/project/pljava/

Cheers
Piotr Gaertig

Reply to this Message

Volker - 30 Apr 2005 22:17 GMT

Hi James,

A nice feature. Can you make clearer how your client should receive the notification?

Do you want receive the notification for
- all rows of a table?
- all rows of a ResultSet?
- the current row of a ResultSet?

And how should look the notification? A ActionEvent with a RowID?

Volker
SmallSQL - the 100% pure Java Desktop Database with JDBC

> Hi All,
>
[quoted text clipped - 7 lines]
>
> James

Reply to this Message

Database notification

Free Magazines