dcsimg
Results 1 to 7 of 7

Thread: Server app detect changes to database

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    19

    Server app detect changes to database

    Hi all,
    I am writing an application with vb.net and firebird database. It is for scheduling jobs. There is a server application, and multiple client applications. Users can use the client application to add/edit jobs to the schedule.

    The server application is used to find the best schedule... it keeps ordering each job in different combinations on each day of the week. If the result is a better combination than previously found, it replaces the schedule with this new schedule. So over time the schedule will get better and better.

    At present the server app is constantly running. As users may have changed the jobs data, it queries the DB for the jobs data then tries a number of combinations and selects the best. It then queries the DB again (in case changes have been made) and tries the combinations again and so on.

    Therefore it is running an SQL query approx every few second! This doesn't seem efficient and probably a big overhead on the DB and LAN etc.

    What is the best way to check if data in the database table has changed? Is there a way to do this without reading all the data in a specific table every few seconds? Is there a way to flag if a change is made to a table? Maybe a trigger on the jobs table, so if a change is done it stores a single record in a separate small table with just the date it changed, or a Boolean flag?

    Any ideas? Thank you!

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

    Re: Server app detect changes to database

    I've never worked with Firebird but it seems it has events that you could push to a listener so that should get you pointed in the right direction.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    19

    Re: Server app detect changes to database

    Hi,
    Thanks for the reply. When you say push to a listener, what do you mean by this? Can a 'signal' be pushed from the db straight to the vb application?

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    19

    Re: Server app detect changes to database

    Hi,
    Thanks for the reply. When you say push to a listener, what do you mean by this? Can a 'signal' be pushed from the db straight to the vb application?

  5. #5
    Fanatic Member
    Join Date
    Dec 2014
    Location
    VB6 dinosaur land
    Posts
    943

    Re: Server app detect changes to database

    You'd create a VB.Net (for example) listener application that would use sockets to listen on a particular port of the computer. When it received such a signal, it would respond as needed (i.e., kicking off your query). Thus, there is no longer a need to poll (query) the database every few seconds. You have a "server" socket application that looks for changes to db and sends out the message when one occurs, which is received by the "client" socket application that is listening for these messages. There is LOTS of info out there on how to do client server winsock applications since it is not something I have done personally.

  6. #6
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,878

    Re: Server app detect changes to database

    What is the best way to check if data in the database table has changed? Is there a way to do this without reading all the data in a specific table every few seconds? Is there a way to flag if a change is made to a table? Maybe a trigger on the jobs table, so if a change is done it stores a single record in a separate small table with just the date it changed, or a Boolean flag?
    You haven't explained what method you use to interact with the database or if the table has a Primary Key. So I'll guess it does have a primary key and your using Ado .Net which means Dataadapter/TableAdapter. When you call the Update method on the DataAdapter, if the row in the database table has changed since you called the Fill method, you will get a Concurrency Violation error. So you will know the data has been modified. Another method people use is to have a field in the table that hold a time stamp. Every time that row is modified the time stamp is updated. This will allow you to compare the time stamp to the data you retrieved, if they're not the same then someone else has modified the data.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jun 2016
    Posts
    19

    Re: Server app detect changes to database

    Topshot that sounds like the right kind of thing. I will look into this.

    Wes4dbt I thought about doing it your way but this would still require to keep querying the db to get the time stamp.

    Thanks.

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