On 3/19/2011 10:27 PM, Bill Arnold wrote:
> I have a similar problem with Inmotion provided MySQL databases: it randomly
> drops the server connection.
>
> I believe what's happening is that the shared (cheapo) MySQL servers are
> setup to support webpage access, quick in-and-out transactions, verus long
> running client/server configurations (per a MySQL timeout variable)
>
> As a workaround until I have a better MySQL setup, I've somewhat tamed the
> beast by setting a timer pop in the app that sends a transaction at
> intervals, currently set to 5 seconds. Not perfect, but the connection
> doesn't drop as often. (current MySQL apps are for internal stuff only, I
> wouldn't sell this service to a customer without a better arrangement)
>
> Hopefully you'll find the problem. If it does take time to resolve and you
> need a quick workaround, maybe triggering some activity via a timer pop can
> help?
Bill et al,
I noticed my MySQL remote databases timing out and just re-engineered my
GetHandle routine to verify that it's a good, active connection
(otherwise get a new one):
FUNCTION GetHandle(tlForce as Logical) as Integer
* Returns connection handle to caller.
LOCAL liHandle as Integer, lcDNS as String, loException as
Exception,
lcDSNFile as String, llEncrypt as Logical, liRetryCnt as Integer
IF tlForce OR this.nHandle <= 0 THEN
TRY
=SQLSETPROP(0, 'DispLogin', 3 ) && suppress
login info
=SQLSETPROP(0,"ConnectTimeOut",SQLTIMEOUT) &&
wait N seconds before
timeout (mjb 08-04-06)
IF PEMSTATUS(this,"cDSNFile",5) THEN && mjb
11-09-06 new code for
increased flexibility
lcDSNFile = this.cDSNFile
llEncrypt = this.lEncrypt
ELSE && older standard code
IF FILE("dsn.ini") THEN && mjb 02-26-06
defer to dsn.ini before dns.ini
lcDSNFile = "dsn.ini"
llEncrypt = .F.
ELSE
lcDSNFile = "dns.ini"
llEncrypt = .T.
ENDIF && FILE("dsn.ini")
ENDIF && PEMSTATUS(this,"cDSNFile",5)
IF FILE(lcDSNFile) THEN
lcDSN = FILETOSTR(lcDSNFile)
IF llEncrypt THEN
SET LIBRARY TO cipher50.fll
lcDSN = encrypt(lcDSN,MYKEY)
ENDIF
liHandle = SQLSTRINGCONNECT(lcDSN)
IF SQLEXEC(liHandle,[select @@version
as cVersion],"curVersion") =
1 THEN
this.cDBCVersion =
curVersion.cVersion
USE IN curVersion && done with
temp cursor
ELSE && attempt reconnection
liRetryCnt = 1
DO WHILE liHandle < 0 AND
liRetrCnt <= RETRY_COUNT
WAIT WINDOW TIMEOUT .5
"Re-attempting (" +
ALLTRIM(STR(liRetryCnt)) + ") to connect to SQL database..."
liHandle =
SQLSTRINGCONNECT(lcDSN)
IF
SQLEXEC(liHandle,[select @@version as cVersion],"curVersion")
= 1 THEN
this.cDBCVersion = curVersion.cVersion
USE IN
curVersion && done with temp cursor
ELSE
MESSAGEBOX(this.cSystemName + " is unable to establish
connection to the database.",16,"Unable to connect.")
liHandle = -99
ENDIF
liRetryCnt = liRetryCnt
+ 1
ENDDO
ENDIF
ELSE
liHandle = -1
ENDIF
CATCH TO loException
liHandle = -1
MESSAGEBOX(loException.Message,16,"Problem
getting handle. " +
ADMINMSG)
ENDTRY
this.nHandle = liHandle
ELSE
* mjb 02-04-08 verify that it's good
IF SQLEXEC(this.nHandle,[select @@version as
cVersion],"curVersion2")
= 1 THEN
liHandle = this.nHandle
ELSE && force new handle via recursive call
WAIT WINDOW NOWAIT "Reattempting to connect..."
liHandle = this.GetHandle(.T.)
ENDIF
ENDIF && tlForce OR this.nHandle = 0
RETURN liHandle
ENDFUNC && GetHandle() as Integer
so in the caller, it just knows that the connection will be good when it
comes back if it's positive:
liHandle = this.GetHandle()
if liHandle > 0 then
liResult = SQLEXEC(liHandle,[select iid from table where
iactive=1],"curTemp")
if liResult > 0 then ...
endif
else && problems getting connection
endif
Actually I've got a routine called ExecuteAndFill that does the above
stuff, but you get the idea from that stub code.
--
Mike Babcock, MCP
MB Software Solutions, LLC
President, Chief Software Architect
http://mbsoftwaresolutions.com
http://fabmate.com
http://twitter.com/mbabcock16
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
