PostgreSQL event notification with payload

Ucn_

Hello. I was using ODBC driver for sql server, however after learning that ODBC doesn't support eventNotification I tried to add that feature by building the driver with no success I changed to PSQL driver and used psql by downloading the binary files and adding to the path. It worked. I test the event notification and it works. However I would like to send the notification with the affected row information. I was able to connect with the two signal and slot, but I don't know how to work with the payload. I am new to postgres. This is how I achieved the event notification.

In the postgresql server:

CREATE RULE Eventnotifications AS ON INSERT TO test DO NOTIFY sendToEventNots

In my app:

bool ok = db.open();

        if (ok)
        {
            qDebug() << "Database connected";
            bool eventNot= db.driver()->hasFeature(QSqlDriver::EventNotifications);
            qDebug() << "Has feature eventNotification" << eventNot;
            db.driver()->subscribeToNotification("sendToEventNots");
            qDebug() << "Connected Info" <<  db.databaseName();
 
        }

        connect(db.driver(), SIGNAL(notification(const QString&)),
                this, SLOT(notificationReceived(const QString&)));

        connect(db.driver(), QOverload<const QString &>::of(&QSqlDriver::notification),
            [this](const QString &sendToEventNots){

            qDebug() << "Event received";
        });
}

void MainWindow::notificationReceived(const QString&){

   qDebug() << "Notification received";
}

The above two connect work. I would like to do something like this:
In server:

CREATE RULE Eventnotifications AS ON INSERT TO test DO NOTIFY sendToEventNots, {"userid":the user Id, "value1": value1,"value2":value2,"action":"grok"}

in my app:

connect(sqlDriver, QOverload<const QString &, QSqlDriver::NotificationSource, const QVariant &>::of(&QSqlDriver::notification),
    [=](const QString &name, QSqlDriver::NotificationSource source, const QVariant &payload){ /* ... */ });

How can I achieve this, and be able to receive the values? Is it possible? Thanks

Ucn_

I solved by creating a stored procedure then passing the values to a json object. Created a trigger on Insert then execute the procedure. In my app it detects the source with switch case. I followed some examples and implemented to my needs.

SGaist

Hi,

Don't you have them in the QVariant you receive ?

Ucn_

@SGaist I don't know what the source is.

SGaist

What source are you talking about ?

Ucn_

@SGaist In the last example, I believe I'm not setting the payload the right way on the server. Maybe the way I do, I receive empty Qstring

Ucn_

I solved by creating a stored procedure then passing the values to a json object. Created a trigger on Insert then execute the procedure. In my app it detects the source with switch case. I followed some examples and implemented to my needs.