Hi All,

I've a WC application that queries data from SQL Server. I'm interested in how you access SQL Server data...

Currently I create an SQL Server Connection when the WC application starts (upon first hit via the "OnLoad" function) and keep that connection handle open for all subsequent queries.
Keeping the SQL connection open avoids the overhead and delay of opening the connection each time someone hits the server so the response time is faster. The problem is that "IF" that SQL connection were to be broken due to a network interruption or otherwise timed-out by SQL Server (or something), I suspect that my WC App would be effectively hung until restarted so the SQL connection could be re-established. While this has not yet been an issue for me I am anticipating that it could happen at some point.

So my question to all you long time WC application developers out there is simple; When accessing data from SQL Server do you Create a Connection and keep it open or do you Create a Connection-Query-Close Connection on each hit to your WC server application?

Please share your experiences...

Alec

You can use wwSql which handles auto-reconnects on broken connections. This the error method that retries if a command fails with error 1466 (connection invalid):

FUNCTION Error(nError, cMethod, nLine)

DO CASE
*** Invalid Connection Handle
CASE nError = 1466 AND THIS.nerrorno <> 1466
	THIS.nerrorno = 1466
	THIS.cerrormsg = MESSAGE()
	IF THIS.CONNECT(THIS.cconnectstring)
		THIS.lerror = .F.
		THIS.nerrorno = 1466     && So we can trap 'recursive' errors above
		THIS.cerrormsg = ""
		RETRY
	ENDIF
ENDCASE

THIS.linternalerror = .T.

THIS.FillErrors()

ENDFUNC
*   Error

If you're using SQLPassthrough you should be using wwSql anyway as it's more reliable, provides clean error handling and reconnections plus a better way to write your queries using parameter syntax.

Unfortunately that won't help if you're using remote views since those manage connections on their own.

Also depending on which driver you use you can create new connections on every SQL command and get decent performance with connection pooling. It's not going to be quite as quick as an always on connection but much better than a new connection each time. Connection Pooling can be configured in the (32 bit) ODBC applet in Windows which determines how many connections can be kept open.

+++ Rick ---

Hi Rick,

Thanks for the advise. Always appreciated!

Alec