ADO and resume laptop

[Marc Vanzegbroeck]

Hello,

I have a client that use my program on a network. before it was a version with DBF-files. Now I upgrade his program to ADO with MySQL.
The program is working very nice, but...
The customer don't quit the program at the evening, but all the PC go to 'Sleep-mode'. In the morning he resume the program, but than he get directly an error that the program can't write/access the database.
With the DBF-files he didn't get an error, but now he have to restart the program.
I know it's better to quit the program, but is there a way to fix this problem without restaring the program?

[Rick Lipkin]

Mark

If it were me and your customer does not want to shut down his computer, I would go to the control panel and the power settings and on "Close Lid" .. set the option to "Do Nothing"

That way, your customer and just shut the lid and walk away and the laptop will not go to sleep.

Rick Lipkin

[Marc Vanzegbroeck]

Hi,

I was thinking to do a open again if it fails

like

Code: Select all  Expand view

TRY
   oSQL:execute( vCommando)
CATCH oErr
   TRY
      oSQL:Open()
   CATCH oError
      MsgInfo("Failed to Connect to the Database ")
      quit
   END
END TRY
 

I'm wondering that is would be a good solution?

[Marc Vanzegbroeck]

Hi,

I just found out that there is a ADO-state-propery, so I can check that the connection is still OK.

I will try to use that.

[nageswaragunupudi]

Hi,

I just found out that there is a ADO-state-propery, so I can check that the connection is still OK.

I will try to use that.

oCn:State --> 0 = not open and 1 = open
But when server disconnects you, your oCn:State shows 1 though in real there is no connection.

To resume a connection you need to do:

Code: Select all  Expand view

oCn:Close()
TRY
   oCon:Open()
CATCH
   // handle failure and exit
END
if oCn:State == 0
   // not connected
else
   // connected .. go ahead
endif
 

When connection dies (due to loss of connectivity or server closing the connection), only way we know it when we get a runtime error. Any openration, even a recordset movement causes runtime eror.

So try to have a global error block directing to your own error routine:
1. Check if it is Ado Error ( check oCn:Errors collection )
2. If Ado error is it related to connection
a. If not call standard error routne
b. If yes, Try Resume connection as above and then return RETRY operation

[Marc Vanzegbroeck]

Thank you Rao,

Indeed, I just found out that oCn:State is still open, even when I disconnected the network.

I try to use your suggestion, and put it in my global error-handler.

The only problem I have ir the network-connections was lost, and back OK-again (after resuming the PC) is when writing to the database, because, for reading I allways use a recordset that I open while reading it. If the connections is back OK, than it works.
But for opdating, writing to the database, I use a connection that I open in the beginning of my program, and close while exiting the program.

[nageswaragunupudi]

We are not opening a new connection again. We are closing and reopening the same connection that you opened first. Still do you get error while writing?

Meanwhile please also see
viewtopic.php?f=3&t=26907&start=0&hilit=recordset+local

[Marc Vanzegbroeck]

I know than whe are using the same connection again, but I was refering to recordset that are allways are reopen while calling.

But anyhow, it's working now, like you suggested. By closing the connection, and reopening it, if there was an error.

Thanks,

[nageswaragunupudi]

Glad it is working. Can you post your Code here for the benefit of all of us? Can we standardise this and integrate with fwh adofuncs library?

[Marc Vanzegbroeck]

For now I have put in my functions I have made for myself for updating, deleting and adding records with ADO, because I needed it very fast for that customer.

I will put it in my error-handler, so it will work allways when there is an ADO-error. It will than also write the error in the error-file.
Now the customer just get an error that the connections was lost, and that the program can't reconnect anymore...
I will post the code after I put it in my error handler.

[Marc Vanzegbroeck]

Rao,

What I was thinking to do is add this to errorsys.prg

Code: Select all  Expand view

if ErrorMessage( e ) = 'Error ADODB.Connection/16389  E_FAIL: EXECUTE'
   oSQL:Close()
   oSQL:=CreateObject("ADODB.Connection")
   TRY
      oSQL:Open()
      return .f.
   CATCH
      Msgalert( "Error connecting to server")
   END TRY
endif
 

[Marc Vanzegbroeck]

Hi,

After debugging the program, I finaly found the error that my customer get.
It is : 'MySQL server has gone away'

Now I'm looking a for a way to reconnect. I found something like oSql:ping(), fut there is also a possibility to auto-reconnect according to the MySQL-site.
Has anyone already did something like this?