How to enable OLEDB resource pooling in a Delphi 7 app

Question

What needs to be done to enable pooling in a Delphi 7 app? My connection string is:

Provider=SQLOLEDB.1;Initial Catalog=%s;Data Source=%s;Password=%s;User ID=%s;OLE Db Services=-1

I can tell that connection pooling is not being achieved by looking at the SQLServer:GeneralStatistics UserConnections performance counter - it fluctuates wildly when my application runs. With connection pooling I'd expect it to achieve a steady state. Also, I see that Logins/sec and Logouts/sec counters are both very high - if connection pooling were used Logouts/sec would be at or near zero.

In searching I found this article on resource pooling:

http://www.ddj.com/database/184416942

It suggests that "If you are working at the OLEDB SDK (or COM) level using ATL, you have to write some more code" (aside from adding OLE Db Services=-1 to the connection string) to get connection pooling:

CDataSource db; CDBPropSet dbinit(DBPROPSET_DBINIT);

dbinit.AddProperty(DBPROP_AUTH_USERID, "MyName); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, "MyServer); dbinit.AddProperty(DBPROP_INIT_CATALOG, "MyDb ); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_OLEDBSERVICES, (long)DBPROPVAL_OS_ENABLEALL); HRESULT hr = db.OpenWithServiceComponents(_T("sqloledb"), &dbinit);

Unfortunately that code is Greek to me and I'm not sure how to translate that to Delphi (or if its even necessary).

I'm also careful not to change the connection string at all. Any suggestions on what else I might need to do to enable resource pooling?

Answer 1

You don't mention it, but are you using Delphi's ADO implementation (dbGo for Delphi 7, IIRC) for your data access? If so, are you connecting everything through the same TADOConnection? If so, it should be doing the pooling for your application (meaning that one running copy of your application is using one connection to the DB server).

Answer 2

You need to keep one instance of the connection open at all times...if it drops to zero, then ADO will re-establish the connection to authenticate the user.