Subj: ODBC pooling Section: Database Issues To: All Friday, August 28, 1998 6:03:08 AM From: Kevin Flanagan, 100141,666 #720019 Anyone help? I posted the question below in the VBDATA list, not getting any response ... Thanks in advance .. ------------------------------ Pardon me digging up a post from some months ago (found in the archives), but this is just the code I've been trying to use in VB for connection pooling. I switch on pooling as shown (returns success), but then whatever combination of RDO environments or connections I use, connections are not pooled. That's to say, if I connect to the datasource using ODBC test, first switching on pooling, then when I disconnect the cxn is left active, a reconnect from ODBC test goes straight to it. But when I disconnect my RDO connection in VB, pooling does not occur - the DM kills the DBMS connection straight away. I can't see anything 'special' that can be done to RDO objects to prevent this - what am I doing wrong? On Mon, 6 Apr 1998 16:36:41 -0700, Jon Fowler wrote: >Cn pooling is an odbc 3.x feature only. Refer to the ODBC 3.0 Programmers >guide (included with MSDN CD) for exact syntax. The following code is an >example of turning it on: >-Jon > >Declare Function SQLSetEnvAttr Lib "odbc32.dll" _ > (ByVal hEnv As Long, _ > ByVal lAttribute As Long, _ > ByVal lValue As Long, _ > ByVal lStringLength As Long) As Integer >Public Const SQL_ATTR_CONNECTION_POOLING = 201 >Public Const SQL_ATTR_CP_MATCH = 202 > >' values for SQL_ATTR_CONNECTION_POOLING >Public Const SQL_CP_ONE_PER_DRIVER = 1 >Public Const SQL_IS_UINTEGER = -5 > >Sub Main() > Dim lRetVal As Long > lRetVal = SQLSetEnvAttr(0, SQL_ATTR_CONNECTION_POOLING, >SQL_CP_ONE_PER_DRIVER, SQL_IS_UINTEGER) >End Sub > ----------------------------------------------------------------------------------------------- Subj: ODBC pooling Section: Database Issues To: Kevin Flanagan, 100141,666 Monday, August 31, 1998 12:24:11 AM From: Calvin G. Smith, 102226,2127 #720109 >>> then when I disconnect the cxn is left active, a reconnect from ODBC test goes straight to it. <<< FWIW, I always close my ODBC API environment handles with the SQLFreeEnv API when I'm done. Perhaps thats the missing link in your test. Calvin Smith - Sr. Visual Basic/MS Access Consultant ******************************************************** http://www.CalvinSmithSoftware.com ******************************************************** ----------------------------------------------------------------------------------------------- Subj: ODBC pooling Section: Database Issues To: Calvin G. Smith, 102226,2127 Monday, August 31, 1998 4:06:13 AM From: Kevin Flanagan, 100141,666 #720113 ??? It's the pooling/reconnect behaviour that I'm trying to *achieve* in VB, not avoid. I want my connection made from VB to remain pooled when I close it, like when I do so in ODBC test. But it is deactivated as soon as I close the connection - no relation to any environment-freeing code. Kevin Flanagan. ----------------------------------------------------------------------------------------------- Subj: ODBC pooling Section: Database Issues To: Kevin Flanagan, 100141,666 Tuesday, September 01, 1998 11:34:02 PM From: Calvin G. Smith, 102226,2127 #720202 >>> I want my connection made from VB to remain pooled when I close it, like when I do so in ODBC test. But it is deactivated as soon as I close the connection - no relation to any environment-freeing code. <<< Sorry Kevin, I mis-understood your needs. I wish that I could be of more help to you; however, I don't have any experience with "pooling" through the ODBC API. I've successfully used quite a few of the ODBC API functions in the odbc32.dll file, some which are listed on my WEB page, but not the one that you're calling. One of my favorite ODBC books though, "The Hitchhiker's Guide To VB And SQL Server", prints that [QUOTE] "By default, RDO and ODBCDirect disable this feature, but you can enable it through API calls" [END QUOTE]. That's on page 145 for your reference. BTW, AFAIK, *most* 32-bit ODBC API in the odbc32.dll file [if not all] return integers, not longs. I learned that the hard way thinking that an API from a 32-bit DLL had to return a long. Not so... Good luck! Calvin Smith - Sr. Visual Basic/MS Access Consultant ******************************************************** http://www.CalvinSmithSoftware.com ******************************************************** ----------------------------------------------------------------------------------------------- Subj: ODBC pooling Section: Database Issues To: Calvin G. Smith, 102226,2127 Thursday, September 03, 1998 5:18:03 AM From: Kevin Flanagan, 100141,666 #720276 Thanks for the reference - shame they don't say more, but it's a start point .... Kevin Flanagan.