Vince Teachout wrote:
> I have an app that I'm modifying. At start up, the app gets a
> connection handle to the main database and stores it in a public var,
> let's call it gonHandle, which is then used throughout the app. No
> problems there.
>
> I need to tie in a second database, which I've done. This database will
> be used for the occasional report. Most days, it won't be used at all.
> My question is this: Do I open and save a public handle at startup, or
> just get a handle when the actual report is needed. Others who maintain
> the program would probably expect the same behavior as the current
> reports (ie, a global handle is setup and ready when/if needed)
>
> Is there a high cost in performance, etc, with creating the handle and
> leaving it available through the program, or should I call as needed?
>
I talked about this recently, along with Paul McNett, iirc. I store the
handle in my global utility object and then just check it to make sure
it's live and return or recreate as needed:
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
* 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
_______________________________________________
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.
