Close connections to MySQL but connection shows as sleeping

Hi,

I use Adobe Dreamweaver (CS5) to create my ASP pages and often use their standard Recordset for displaying data from a MySQL database (which I connect to using ODBC 3.51 driver).

I’m confident that I am closing my (database) connections correctly (at least I don’t modify the Dreamweaver recordset Close() command) but when I list the “User Connections” in MySQL I can still see all the connections even if I close the page - the connections show as “Sleep” and do disappear eventually but I assumed they’d go as soon as the page was loaded (and it hit the “close” command) and my hosting company has attributed several hosting problems witht he site to too many connections to MySQL.

Here’s a sample Recordset for one of my navigation menus:

<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO" 
' DesigntimeType="ADO"
' HTTP="false"
' Catalog=""
' Schema=""
Dim MM_connDB_STRING
MM_connDB_STRING = "Driver={MySQL ODBC 3.51 Driver}; localhost=localhost; Database=databasename; UID=root; PWD=xxxxxx" 
%>
<%
Dim rsPrimaryMenu
Dim rsPrimaryMenu_cmd
Dim rsPrimaryMenu_numRows

Set rsPrimaryMenu_cmd = Server.CreateObject ("ADODB.Command")
rsPrimaryMenu_cmd.ActiveConnection = MM_connDB_STRING
rsPrimaryMenu_cmd.CommandText = "SELECT pgId, pgMenuTitle, pgLinkName, pgMember, pgLinkOnly, pgActualUrl, pgFixedPage FROM tblpages WHERE pgEnabled = 'Y' AND pgChild = 'N' AND pgWhichMenu = 'P' AND pgShowonMenu = 'Y' AND pgExpires > CurDate() ORDER BY pgMenuOrder ASC" 
rsPrimaryMenu_cmd.Prepared = true

Set rsPrimaryMenu = rsPrimaryMenu_cmd.Execute
rsPrimaryMenu_numRows = 0
%>
<%
Dim RepeatPrim__numRows
Dim RepeatPrim__index
Dim pgPrimaryMenuTitle

RepeatPrim__numRows = -1
RepeatPrim__index = 0
rsPrimaryMenu_numRows = rsPrimaryMenu_numRows + RepeatPrim__numRows
%>
<% If Not rsPrimaryMenu.EOF Or Not rsPrimaryMenu.BOF Then %>      
      <% 
      While ((RepeatPrim__numRows <> 0) AND (NOT rsPrimaryMenu.EOF)) 
      %>
	  
	  this is my repeating database content
	  
      <% 
      RepeatPrim__index=RepeatPrim__index+1
      RepeatPrim__numRows=RepeatPrim__numRows-1
      rsPrimaryMenu.MoveNext()
      Wend
      %> 
<% End If ' end Not rsPrimaryMenu.EOF Or NOT rsPrimaryMenu.BOF %> 
<%
rsPrimaryMenu.Close()
Set rsPrimaryMenu = Nothing
%>

How can I make the MySQL connections actually close rather than “sleep”

Can anybody help.

(BTW I didn’t know whether to post this in ASP or MySQL)

Hi scim1971

As you say you are closing the recordset not the connection. Closing the recordset does not implicitly close the connection. Closing your connections is good programming. You need to close MM_connDB_STRING. Closing is the same as a recordset.

Hi gidday,

Thanks very much for answering my question.

How would I write the code to close the connection? - do I simply write:

MM_connDB_STRING.Close()
Set MM_connDB_STRING = Nothing

or do I need to do something with: rsPrimaryMenu_cmd.ActiveConnection?

Also I often have several Recordsets on the same page (i.e. for a CMS I might have one recorset for the top navigation, one for the side navigation and one for the actual content) - would I (should I) close the connection after each recordset or just one connection close at the bottom of the page?

Many thanks again.

Your closing statements should work. Give it a go. I normally open the connection at the top of the page and close it at the bottom. Open once, close once. Just remember to close the connection.

Hi,

I tried:

MM_connDB_STRING.Close()
Set MM_connDB_STRING = Nothing

(at the bottom of the page)

but it didn’t close the connections (they’re still in “sleep” state)

I also tried adding:

rsPrimaryMenu.ActiveConnection.Close()

to the existing close scripts (in various positions) but the connections still show as “sleeping” in MySQL