dcsimg
Results 1 to 6 of 6

Thread: MySQL - Sending Message back to the Frontend/UI/Client

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,458

    MySQL - Sending Message back to the Frontend/UI/Client

    Hi folks,

    i'm still not into the in's and out's of MySQL :-)

    Question: Is it possible to send a "message" back to the client/UI?
    In the MySQL-manuals i found the SIGNAL-Statement, which DOES look like what i'm looking for, but i'm not really sure where/how to catch that "signal" on the frontend-side...
    I was thinking about using the SIGNAL in an After-Insert/Update/Delete-Trigger

    Setup as follows:
    MySQL-Server running on its own machine.
    Main client feeds a temporary table
    A separate application now wants to watch/display the data in this temporary table.

    Imagine an airport, with a sales-station for tickets. Everywhere in the lounge are screens showing the next few flights and its passengers.
    Passengers can buy a ticket (passenger appears on the load-list --> INSERT)
    Passengers can change a ticket (passenger changes from Flight 1 to Flight 3 --> UPDATE)
    Passenger cancels his flight (Passenger disappears from the Load-List --> DELETE)

    I have no access to the Main-client's source-code, the main-client is even running on its own separate machine.

    Now i know that i could use polling to check for changes, but i admit, i'm loath to use polling, if there is maybe an "event-driven" mechanism.

    Any ideas/experiences on this? Am i on the right path?
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  2. #2
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    900

    Re: MySQL - Sending Message back to the Frontend/UI/Client

    Perhaps some ideas here or here

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: MySQL - Sending Message back to the Frontend/UI/Client

    I don't know MySQL well but that wouldn't be typical. I kinda doubt it's possible out of the box.

    Usually when someone asks this people start suggesting constant polling - i.e. the various clients constantly poll the database to see if anything has changed. I personally think that's a terrible pattern that puts a load of unnecessary burden on the DB.

    A much better approach is to use the "Observer" pattern. You can google this to get a in depth understanding but it basically boils down to putting a service on the front of your DB. Anything that wants to update the DB does so via this service - nothing ever updates it direct. Anything that wants to "Observe" the DB registers itself with the service. The service is responsible for passing updates through to the DB and letting any registered observers know that they need to do a read to update their state. So instead of the DB "pushing" changes to the clients, you have the service tell the clients to do a "pull".
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  4. #4

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,458

    Re: MySQL - Sending Message back to the Frontend/UI/Client

    Topshot,

    thx, but i forgot to mention: It's not a web-app, and JS (and Web-Developement in general) sounds like Chinese in revers to me.

    FD,

    hmmm, yes, i found the references to the Observer-Pattern, too, but as i said: Ich have no access to the main-client (the one which is pushing the rows into that table), so i'm not able to insert a service between it and the mysql-server.
    But you gave me an idea:
    Since the MySQL-Server is running on Linux (This "Watch"-program is going to be written in Lazarus/FreePascal),
    i could code a little program as a daemon (Win-Speak: Service), watching a particular folder on the server.
    Setting up my triggers as mentioned, if activated, they would place an empty textfile into that folder.
    Since Linux has a notification-system, when a file is created, i could try to catch that notification with my daemon, and then send a message via sockets to the watcher
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: MySQL - Sending Message back to the Frontend/UI/Client

    no access to the main-client
    Ah, missed that bit.

    You're solution still relies on constant polling but at least it's only one subscriber rather than several and at least you've moved the target out of the DB and into the file system. I'm not sure but I think that will be considerably more performant.

    I don't like the constant polling but I'm struggling to suggest an alternative.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,458

    Re: MySQL - Sending Message back to the Frontend/UI/Client

    Quote Originally Posted by FunkyDexter View Post
    Ah, missed that bit.

    You're solution still relies on constant polling but at least it's only one subscriber rather than several and at least you've moved the target out of the DB and into the file system. I'm not sure but I think that will be considerably more performant.

    I don't like the constant polling but I'm struggling to suggest an alternative.
    Yeah, additionally to moving out of the DB, i'm staying on the Server with the service "polling" the directory, and since the MySQL-Server is the only thing running there (and my supposed service), with 8GB RAM and a sufficiently modern hardware (maybe 3-4 years old) i don't see any issues performance-wise.

    And i agree: I'm loath to use polling, but in such situations i also struggle to find alternatives.

    Thx for your effort.
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    I say you're crazy not to!
    --------------------------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width