Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

PostgreSQL event notification with payload



  • 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



  • 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.


  • Lifetime Qt Champion

    Hi,

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



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


  • Lifetime Qt Champion

    What source are you talking about ?



  • @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



  • 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.


Log in to reply