Retry connecting until database is available.. 1

[Mandy_crw]

Hi everyone.. may i just ask is it possible to retry and attempt opening the database until database is already available thru network? if possible, could you give me an example for me to base on? thanks and God bless....

 

[Mike Lewis]

Hi Mandy,

I'm assuming that this is a Visual Foxpro database - that is, a DBF and/or DBC - as opposed to a database on a remote database server such as SQL Server or MySQL.

If so, you could do something like this:

DECLARE Sleep IN WIN32API INTEGER iPeriod
  && You only need to do the above command once, at the start of your program

llSuccess = .F.
lnRetryCount = 0
DO WHILE NOT llSuccess AND lnRetryCount < 100
  llSuccess = .T.
  TRY
    USE MyTable  && or any other command to open the DBC / DBF
    lnRetryCount = lnRetryCount + 1
  CATCH
    llSuccess = .F.
  ENDTRY
  Sleep(500)  && pause for half a second
ENDDO

IF llSuccess 
  * Database is now open
ELSE
  * Database cannot be opened after 50 seconds
ENDIF

This will try for up to a hundred times, with a half-second pause between each attempt.

But note that this code is somewhat crude. It doesn't try to determine why the database cannot be opened; it just assumes that it will eventually be possible to open it. If another user has opened it exclusively, it could be many minutes or even hours before it is possible to open it.

Also, it makes no attempt to tell the user what is happening. The user will simply see an hourglass for up to 50 seconds.

So take the above code as a starting point, to give you the general idea of how to go about it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Sorry, Mandy, there is a small bug in my code, above. You need to change this:

 TRY
    USE MyTable  && or any other command to open the DBC / DBF
    lnRetryCount = lnRetryCount + 1
 CATCH
    llSuccess = .F.
 ENDTRY

To this:

 TRY
    USE MyTable  && or any other command to open the DBC / DBF
 CATCH
    lnRetryCount = lnRetryCount + 1
    llSuccess = .F.
 ENDTRY

That's what comes of writing code in a hurry and not testing it.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mandy_crw]

Hi Mike... Thank you so much Mike you are so kind... I will try it.... God bless...

 

[Mandy_crw]

Hi Mike... its working!!! although i havent tried in LAN access, but it worked when i removed the database in the specified path, and connected when the database is present.... Thank you so much Mike.... God bless.... may i just ask, i've noticed that Olaf is no longer answering questions here, I hope he's OK....

 

[Mike Lewis]

Glad to see it is working, Mandy. Keep in mind that I wrote it in a hurry. It could probably be improved.

Whether you run it locally or over a LAN shouldn't make any difference. I tested it by simply running two instances of VFP on the same machine. I opened a DBF exclusively on one of them, then ran the code on the other one. Most of the time, that's all you need to do to test multi-user issues.

Regarding Olaf, you might remember the relevant conversation from November 2020: thread184-1806918. I don't have any more recent news. It's possible that Chris Miller might have more information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gerrit Broekhuis]

Hi Mandy,

I don’t want to spoil the fun you’re having, but instable or lost connections to a database can cause serious corruption of your database and / or tables.

So, even if it’s working for you right now, make sure you have a good backup. Not only now, but ALLWAYS!

Regards, Gerrit

 

[Dan Olsson]

I would add a WAIT WINDOW STR(lnRetryCount) NOWAIT inside the loop to show that something happens.

 

[Mandy_crw]

Thank you always Mike for giving me answers to all my questions....Yes ill try to improve it according to my needs... God bless Mike.... Gerrit thank you for reminding... Thank you Dan... i will try to do that...

 

[Chris Miller]

You might want to check whether you work with SET EXCLUSIVE ON (See in the menu Tools -> Options, in the options dialog activate the Data tab, see first checkbox. That's where you can turn it off and also click "Set As Default" to make this setting permanent default when stating the IDE.

You might not cause the problem in your EXE as exclusive access is off by default there, but when you work with exclusive access in the IDE you can lock out users when you work on the databases the users also use. It's better you limit yourself to shared access by turning "Open exclusive" off and only when you make changes that need exclusive access open something exclusive.

You'll see whether the errors drop and this is the source of the problem, it's good to do anyway.

Chriss

 

[Mandy_crw]

Hi Chris, Yes I will... Thank you and God bless...