I use MySQL for desktop apps that may be left running for hours. The connection does drop sometimes. Might be a server setting, but when I first discovered this, I figured it could be server issues, network issues, or other things beyond my control. So I decided to make my software smart enough to detect and correct the problem in the background. I do all my database calls (sqlSelect, sqlExecute) in jacket functions that checks the error codes and reconnects if necessary. Users have no idea and never notice any problems or delays with this. I’ve included partial (and untested) sample code below. This uses the old API, release 2019R1 and earlier.
[code]
Public Property db as MySQLCommunityServer
Public Function DBSelect(ByRef rs As RecordSet, sql As String) as Boolean
rs = db.sqlSelect( sql )
If db.Error Then
If db.ErrorCode = 2006 Or db.ErrorCode = 2013 Then ’ server has gone away or connection lost
Call ConnectToData ’ re-open the connection
Call DBSelect(rs, sql) ’ and try again
Else
’ other error, issue error message or take other action
Return False
End
End
Return True
End Function
Public Function ConnectToData() as Boolean
db.Host = “hostname.mydomain.com”
db.Port = 3306
db.Username = “me”
db.Password = “takeaguess”
db.DatabaseName = “mydb”
If db.Connect Then
Return True
Else
’ issue error message: “Unable to connect to database” + db.ErrorMessage
Return False
End
End Function[/code]