Query SQL db often

[KennyR]

What is the best method for querying a db in a repeated time frame to check for changes in the tables? Currently I use a loop to query the database but it seems to hang and crash my program often. I just want to periodically check for changes while the program runs. I use a MySQL db on go daddy.com and query the db from a standalone windows program I have built....thanks

Kenny

[Mark]

Hi Kenny,

Create a new table in your database with one record and just a few, or even only one, fields. Add a trigger to your database, which sets the corresponding field in your new table to 1 when particular changes are made. Add another trigger to your database, which sets the field to 0 whenever it is queried. Now you only need to periodically check one field in a very small table. Don't query too often, e.g. only every 10 seconds to avoid server problems; keep in mind that besides checking, you also need to be able to use the database normally.

Kind regards,

Mark

[bangkok]

How many seconds it takes your app to do the "check" query ?

You should perhaps try to optimize your DB and/or your app.

What is the structure and the volume of your table and your data ?

Which query do you use ?

Do you use indexes properly ?

Furthermore, why do you use a loop to perform the "check" query ? In order to check if new records (for instance) were added during a period of time, a single SQL query is enough (using timestamp for instance). So why the loop ?

In any case, you should provide us more details on what you do, why you do it and what you try to achieve.

[KennyR]

Hi gentlemen....Thanks for taking the time to help....TRIGGERS! It is so funny you suggested using a trigger because right before I went to bed last night, I thought about triggers and looked it up online to see what they were....(I'm still learning about SQL obviously). I will give that a shot today and see how it works out. I will report my findings so that others may benefit. Thank you!

bangkok- I should have been more explicit last night when posting, but it was late after work and I got lazy....Essentially what I have is a small widget that sits on a windows platform desktop that connects to a very small SQL database and periodically checks to see if new messages are sitting on the server. (much like what I would expect an email server to do). There are two programs running here....one that sends the message and another that only receives the messages through the widget. The widget that receives the messages does only that and nothing else....So lets say I am at work and I am a supervisor and have a message I need to get out to my employees that each have a laptop. With the widget running and always running on the desktop, there is no need to check email, the message comes in like a chat client. ( I know, not an original idea, but believe it or not, this is a good idea for where I work). I need to simply check periodically if a new message has been sent, and I don't want the employees to have to click a button to refresh the widget....(they would never do that! lol).

The reason I have been using a loop is simply because I have no idea on how to query the DB periodically without one. I am thinking every hour would be sufficient as to not tie up the program or open too much traffic to the server. For testing purposes, I have been setting a loop to check every 10-30 seconds just to see how it is working, and this is where I run into problems. I typically run the loop in the openCard handler and send a "mouseUp" command to the "refresh" button to achieve the results I want. I thought this was the only way to make happen what I wanted, but I thought it was inefficient and there had to be a better way.

As for the structure of my table, it is very simple. One DB with one Table and 3 fields. (Timestamp, Subject, Body). I am not looking at more than a few dozen messages being stored on the server at any given time. It is just that simple. I have no need for indexes since the general public will never use my program...it is strictly a program for my work, so keeping data separated and unique is not a concern.

I hope I have addressed your questions and look forward to your ideas....I am for now going to check in on the trigger idea and see what happens.....Thanks for your help bangkok!

[bangkok]

Indeed, there is an easier way : "send message to object in time"

http://revolution.byu.edu/time/timeDelay.php

[pseudo code]

Code: Select all

on openstack
pollServer
end openstack

on pollServer
---check the DB
send "pollServer" in 30 seconds
end pollServer

With such a structure, you app doesn't "hang" (it will with a loop, unless you use " wait for 0 milliseconds")

What about the crash you were talking about ?

So if i understand : your main app checks the news messages on the DB, and then "broadcasts" those messages to several client apps, right ?

[KennyR]

Yes you are correct...The receiving nodes are checking for new messages over a given time period....much like a push for new messages on IOS....The send in time command is the next thing I am going to check out...I appreciate the link...Im going to check it out now...and thank you for the code...

[Simon]

Hey Kenny,
I think that when the supervisor posts a new message he drops a text file with date/time as well.
Hitting a text file is easy on the system, no need to connect to the db.
The users app would do a comparison to the date/time of the last text file posted (held locally), if different it's a new message, save the current text file.

I was looking at SQL OUTPUT but I couldn't see an easy way to get it to work.

Simon

[KennyR]

Ok I decided to go with the send in time option and all is working as expected. BUT there is a major crash when I attempt to query the DB using my home internet connection. I have no problems querying the DB using another internet connection, but when I connect with my home connection it crashes the IDE and closes LC. I am not sure why this is happening....I am going to attach the error log in the case that someone knows what it means...

errorLog.zip

(64.38 KiB) Downloaded 263 times

[Simon]

errorLog.zip

Cheater!

Simon

[KennyR]

Hey! I just give what I get!

[Simon]

Well I figured out how to open it but I don't know error log stuff.

But back to my posting above...
Do you see how at the same time the supervisor posts a message (because you are a highly skilled programmer) you silently ftp up a text file with "the seconds" in it?
easy-peasy no more hitting your db every hour.

Now about the home network... I'll handle this like my ISP does.
"We can get a tech out say the 28 of next month, will that do?"

OK aside from that I know liveCode server (CPanel) has a remote access filter preventing ip addresses from logging in to mysql. But that shouldn't cause a crash.

Simon

[KennyR]

Simon...so you are saying that instead of checking the DB itself, I would be checking a txt file on the server instead? And then if the time is different in the text file on the server vs the local copy of the txt file held on the users system, THEN it would query the DB with the messages?

Now about the home network... I'll handle this like my ISP does.
"We can get a tech out say the 28 of next month, will that do?"

FUNNY STUFF!

[Simon]

Yeppers!
You got it!
Roger that!
Bingo!

[KennyR]

I'm going to give that a try....I'll let you know how it goes....thanks as always Simon

[Simon]

You do realize that I only suggest this method because I'm a complete coward?
I know that you can hit a text file all day long and not cause a problem, do it every minute if you like it will only be a few bytes. I suppose in reality a db must be able to handle this as well.

But a "put url(http:blah blah) into tNewTime" is less work than connecting and querying a db.

Simon