Close Connections - be sure! 2

[RobV]

Hi,

This is a helpful tip I wanted to share with you. Please let me know if you like it.

Sometimes you have a situation where you cannot always be sure that your DB connection is being closed (e.g. in case of a error or simply lazy coding). If you ASP site is running VBScript 5.1 or later (I prefer 5.5) then you have the possibility to finish this problem for once and for all.

The solution to the problem can be found through the use of VBScript classes. By defining a class to track the connection object, you can use the class_terminate() function of the class to release the connection. This even works in a redirect or error condition! (I tested this assumption on VBScript 5.5).

Here's some code. Put this in an include file (e.g. connection.asp) and include it in all your scripts.

-------------------------

class ConnectionWrapper
  private objCon 
		
  public function GetConnection()
   dim strDBPath
   dim strDSN
			
   if objCon = empty then
     set objCon = Server.CreateObject("ADODB.Connection")
     objCon.CursorLocation = adUseServer
     objCon.Mode = adModeReadWrite
     objCon.Open &quot;DSN=<your DSN>&quot;
		
     if err.Number <> 0 or objCon.State <> 1 then
	objCon.Close
	set objCon = nothing
        'error handling here!
	Response.end
     end if
   end if
   set GetConnection = objCon
  end function
		
  private sub class_terminate()
   if not objCon = empty then
      if objCon.State = 1 then 
	objCon.Close					
      end if			
      set objCon = nothing
   end if
  end sub
end class

'create a global connection object.
dim DBConn
set DBConn = new ConnectionWrapper

-------------------------

If you want to use the connection, try the following:

dim oRs
set oRs = server.CreateObject("ADODB.Connection")
set oRs.ActiveConnection = DBConn.GetConnection()

Note that you should be able to use the same principle for creation of COM-objects, Recordsets, XML-objects etc. Another tip on this subject: MAKE SURE THAT THE CODE IN CLASS-TERMINATE IS ABSOLUTELY ERROR-FREE, BECAUSE YOUR SERVER WILL CRASH IF IT ISN'T (this took me some time to find out).

Your ISP will be very happy if you're cleaning up after yourself like this.

Bye,

Rob.

 

[link9]

Nice one, Rob.