Losing connectiion to mySql db after small period of inactivity

Hi all,
I'm losing connection to a mySql database on the WAN after a period of inactivity (only a few minutes).
I get a "Lost connection" error when I do a rst.update (funny thing, if I do it 2 more times, it seems to re-connect by itself and rst.update works, which is good because all the recordset info is intact when it re-connects by itself).
The question is: is there a way or command to make it re-connect without having to to rst.update 2 more times?
Better yet, how can I make it so that it won't loose connection after any period of inactivity?
(btw, in previous solutions cn.connectiontimeout was recommended. this is not correct).
thanks
phil
spoowizAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jaime OlivaresSoftware ArchitectCommented:
>Better yet, how can I make it so that it won't loose connection after any period of inactivity?
A connection timeout parameter could be configured in the mySQL server.
Or you can make a "keep alive" function by making dummy queries every minute using a timer.
0
spoowizAuthor Commented:
tell me more, please, about the "dummy timer", thanks
0
Jaime OlivaresSoftware ArchitectCommented:
You have to create a timer somewhere in your code.

Here is some demo code:

Dim StartTime As Date

Private Sub KeepAliveBtn_Click()
    Timer1.Interval = 60000     ' produces an event every minute
    Timer1.Enabled = True
    StartTime = Now
    Me.Caption = StartTime
End Sub

Private Sub Timer1_Timer()
     ' Put an dummy sql query here. Something like "SELECT 1"
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jaime OlivaresSoftware ArchitectCommented:
Forgot to mention, Timer1 is a timer object you must to insert into your form.
0
spoowizAuthor Commented:
Looks promising. Learning something new...
What would happen when the interval time arrives and the program is doing something? Does it have potential to interrup the going process and cause a problem?
0
Jaime OlivaresSoftware ArchitectCommented:
I don't think so, timer function is made in parallel with your process, mySQL support multiple queries from the same source.
0
spoowizAuthor Commented:
thanks
0
spoowizAuthor Commented:
jaime - where can i find the timer object?
0
Jaime OlivaresSoftware ArchitectCommented:
Timer is a standard ActiveX control which icon is a little clock. You have to drag it to your form.
Have a look to this:
http://sunsite.iisc.ernet.in/virlib/html/platinum/f50-19.gif
0
spoowizAuthor Commented:
I'm using VB6. I don't see it under "Components". (Your screen says "additional controls" at top, mine doesn't). How do I get to your screen?
0
Jaime OlivaresSoftware ArchitectCommented:
Sorry, I have grabbed from the Internet because I haven't installed VB in my office machine.

I have gone to another computer to take a screenshot. Have a look to:
http://www.micrologicaperu.com/vbtimer.html

As you can see, Timer is a standard (fundamental) control you can find in your control toolbar in VB, if not, just have to search and installl in components dialog.
Select it and "draw" it in your form, then double click to generate a Timer event, and fill it with a dummy query.
0
spoowizAuthor Commented:
thanks again. i didn't realize it was a standard one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.