Question 2: True or False: Setting a connection to Nothing is important for proper pooling.
False.
Closing the connection is critical to pooling, but destroying the object by setting it to Nothing is only important to resources on your web server. That makes performing this step important, but for a reason separate from pooling. It has no bearing whatsoever on connection pooling. Using the code below we see that leaving out the destruction of the object yields no different results with respect to the open number of connections.
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Option Explicit
Const intConCount = 50
Dim conArray ‘ array of connection objects
Dim i
ReDim conArray(intConCount)
For i = 1 To intConCount
Response.Write “Processing connection #” & i & “<br>”
Set conArray(i) = Server.CreateObject(“ADODB.Connection”)
conArray(i).ConnectionString = strConnectString1
conArray(i).Open
conArray(i).Execute strSQL2
conArray(i).Close
Response.Write “ ” ‘ our simple delay tactic
‘Set conArray(i) = Nothing ‘ un/comment this line to see that there
‘is no difference
Response.Flush
Next ‘i
Response.Write “<p>Done”
</script>
Question 3: True or False: Changing the CursorLocation (adUseClient to adUseServer for example) or Mode (adModeRead, adModeShareDenyRead, etc.) of a pooled connection may cause problems when it is returned to the pool.
False.
Wouldn’t it be appalling if another piece of code set the Connection.CursorLocation (or the Mode or Attributes) to something incompatible, and your code reused the pooled connection with the wrong settings?! Fortunately, this cannot happen. When a connection is plucked from the pool only, the credentials are reused. All properties are reset to the connection’s default values. Well, almost all of them, but that’s getting ahead of ourselves.
Executing the code below, we can see that opening 1000 connections will always result in the CursorLocation defaulting to adUseClient, and the Mode defaulting to adModeUnknown, even though every connection after the initial two is originating from the pool.
All output to the web browser will resemble the following snippet:
…
Connection 1, iteration 42
–cnPubs.CursorLocation=2
–cnPubs.Mode=0
…
This clearly demonstrates that the pool returns a connection with a CursorLocation of adUseServer (2) and a Mode of adModeUnknown, regardless of what it was set to previously.