What's the best practice in this? iirc, Paul McNett does a timer
process in the background where he constantly keeps polling something
like "show tables" in order to keep the connection alive. I was doing
this for getting my handle (and thus the goal was to re-establish
connection if the simple command failed):
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 5
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
liHandle = this.nHandle
ENDIF && tlForce OR this.nHandle = 0
RETURN liHandle
ENDFUNC && GetHandle() as Integer
On 2nd review, I think this fails because once the handle is set, it's a
positive number, so the retry code inside never gets run. DOH! Note to
self: call simple SQL first and treat as tlForce=.T. if it fails!
Or is this something better done at the configuration section in ODBC
rather than via the software?
--
Michael J. Babcock, MCP
MB Software Solutions, LLC
http://mbsoftwaresolutions.com
http://fabmate.com
"Work smarter, not harder, with MBSS custom software solutions!"
_______________________________________________
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.
