Andy Kramek has written an excellent connection manager class. You can find
it in the samples source code of the book Megafox (in chapter 13 - if I
remember well)
Vassilis Aggelakos
----- Original Message -----
From: "MB Software Solutions General Account"
<[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, March 05, 2008 6:33 PM
Subject: Re: Cost of open connection handles?
> 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
>
>
>
>
[excessive quoting removed by server]
_______________________________________________
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.
