Detect a change of a database table

[karl_qt]

Hello!

Is there an (elegant) way to detect a change of a certain table in a database using Qt? E.g., I want to observe a table in a database and want to emit a signal, if a row was inserted, updated or deleted.
I could start a thread, which does a database query every 10s, but maybe there is a better way?
It should work with Qt 4.8 and a MySQL database, but I would prefer a database independent solution.

regards,
karl

[lgeyer]

QSqlDriver theoretically supports "notifications":http://qt-project.org/doc/qt-4.8/qsqldriver.html#subscribeToNotification, but the QMYSQL driver doesn't support this feature for the simple fact that MySQL does not support notifications (at least as far as I know).

I don't think there is any other way then polling the database to track external changes (you might be able to use a trigger and sys_exec to emulate changes notifications, but this will introduce a whole bunch of other potential problems).

[Rahul Das]

I think you are looking for something like "Model-View":http://doc.qt.nokia.com/4.7-snapshot/model-view-programming.html.
and "this":http://doc.qt.nokia.com/4.7-snapshot/sql-presenting.html

[tarod.net]

PostgreSQL supports notifications so if you are using it you can get notifications using QSqlDriver::subscribeToNotification.

For example, if we have a database with the following rule:

CREATE RULE notifications AS ON UPDATE TO myTable DO NOTIFY dbupdated;

You could subscribe to the notification dbupdated with the next code and connection:

QSqlDatabase::database().driver()->subscribeToNotification("dbupdated");

connect(QSqlDatabase::database().driver(), SIGNAL(notification(const QString&)),
        this, SLOT(notificationHandler(const QString&)));

[NickV]

I use PostgreSQL notifications. The thing is that when i open the database connection from another thread and subscribe to it, i don't receive any notify. I believe that postgreSQL driver can receive notifications only from main thread. Am i right? Or can receive notifications only in default_connection. Is that true?

[JonB]

@karl_qt said in Detect a change of a database table using Qt:

I could start a thread, which does a database query every 10s, but maybe there is a better way?

But what s your query? I.e. how do you know that some row has been inserted/updated/deleted?