?
Solved

Close All Open Recordsets?

Posted on 2007-03-29
17
Medium Priority
?
314 Views
Last Modified: 2010-08-05
I'm looking for a Classic ASP solution to iterate through all open recordsets, and all open database connections and close them.  I'd like to use this as an include file, at the bottom of my ASP pages to clean up some old code that may have open recordsets and database connections.  Since there are hundreds of pages, and multiple recordsets, I may not find them all, I thought some coding solution might help to facilitate this...

Any suggestions?
0
Comment
Question by:sknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 9

Assisted Solution

by:fuzzboxer
fuzzboxer earned 100 total points
ID: 18819747
I'm not sure you can do this as the ADO does not expose active recordsets.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18820114
fuzzboxer's comment is correct.  there is no way to do this by running through a collection.  on the other hand, the asp engine is supposed to clear variables out of memory as soon as they go out of scope.  out of scope in asp, is basically when the page is done loading.  i havent fully maxed it out, but testing here and there has kept it pretty clean.  

if you dont want to go through each page manually, just leave it alone and it should clean up just fine.  i would just suggest to get in the habit of setting up the cleanup on each page as you go.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18820126
"VBScript’s garbage collector is completely different.  It runs at the end of every statement and procedure, and does not do a search of all memory.  Rather, it keeps track of everything allocated in the statement or procedure; if anything has gone out of scope, it frees it immediately.  We can see this in action by watching terminators run."

http://blogs.msdn.com/ericlippert/archive/2004/12/22/330276.aspx
0
WordPress Tutorial 3: Plugins, Themes, and Widgets

The three most common changes you will make to your website involve the look (themes), the functionality (plugins), and modular elements (widgets).

In this article we will briefly define each again, and give you directions on how to install them.

 
LVL 22

Expert Comment

by:neeraj523
ID: 18821255
why dont you just close the connection object.. automatically all the recordsets associated with it will be close..

neeraj523
0
 
LVL 1

Author Comment

by:sknight
ID: 18825588
I've recieved a MS error "Too many client connections" which I believe is due in part to too many open collections most likely due to failure on closing those connections on a regular basis, and I thought cleaning up some old code (some of which is 6-7 years old) might lighten the load a bit.  

If I'm declaring the language as VBScript, and have the new 2.0 Framework installed, is there a VB script or ASP.NET solution if the Classic ASP solution doesn't exist?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18826327
what database are you connecting to?  how many users are on your site?  how many connections per page do you use?  is the db connected from anywhere else?
0
 
LVL 1

Author Comment

by:sknight
ID: 18826663
We're running IIS with virtual domains, so we have multiple databases, the error is caused by having too many open database connections with the Jet Engine.  I'm using all ADO connections, and close them immediately after getting my recordset.  But in some cases I think the recordsets aren't all being closed using system resources (sloppy coding).  There is generally only 1 DB connection per page, but in some cases we may be making several DB connections for each recordset on a page (but each DB connection is closed immediately after getting the recordset).

Microsoft doesn't release the actual numbers that the Jet Engine can handle (although they recommend keeping it less than 10 apps using it simultaneously), some say up to 256 concurrent connections.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18826807
would you like to build a script that can run through the directories of the sites to find any recordset connections that have been opened but not closed?  it sounds like most of your apps already close the connections, but we can make a script to confirm.
0
 
LVL 1

Author Comment

by:sknight
ID: 18826887
Hmm.. That would be a different solution to the problem wouldn't it?  :)

Are you thinking about a standalone VB script iterating through all the ASP files searching for open recordsets?  I have written a few (mostly sorting log files, and bloked spam mails and the like), but I'm not sure where I'd begin looking for recordset names that are variables...  Is there an actual "recordset collection"?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18826928
>>Hmm.. That would be a different solution to the problem wouldn't it?  :)

yes, it is an alternate solution since the solution you were hoping to find doesnt exist.


i am talking about a stand alone vbscript to iterate through all files in a directory.  there is no collection to get info from.  my thought is to use regex to find all recordset and connection declarations.  

set testcon = server.createobject("adodb.connection")
set testrs = server.createobject("adodb.recordset")

we can extract the name of the variables used, and do another search for "testrs.close" to see if all objects have been closed.  
0
 
LVL 22

Accepted Solution

by:
WMIF earned 400 total points
ID: 18827098
the script below will run through the files in a single directory.  do you have subfolders?  that will take a bit more code to loop through all the subfolders as well.  i found that i was lazy in an app. :)



dim fso, fol, fil, ts, tsc, wrfil, openfolder, regex, mcol

openfolder = "X:\dep\admin\"

Set regex = new regexp
regex.Global = true
regex.IgnoreCase = true

set fso = createobject("scripting.filesystemobject")
set fol = fso.getfolder(openfolder)
set wrfil = fso.createtextfile("findado_result.txt")
wrfil.writeline("Results for " & openfolder & vbcrlf)

for each fil in fol.files
      wrfil.writeline(fil.Name)
      wscript.echo(fil.Name)
      set ts = fil.openastextstream(1)
      tsc = ts.readall
      regex.pattern = "set ([^\s]*)\s?=\s?(server\.)?createobject\(""adodb.(connection|recordset)""\)"
      set mcol = regex.execute(tsc)
      if mcol.count > 0 then
            for each rm in mcol
                  if instr(tsc, rm.submatches(0) & ".close") > 0 then
                        wrfil.writeline(" OK " & rm.submatches(0) & " was opened and closed.")
                        wscript.echo(" OK " & rm.submatches(0) & " was opened and closed.")
                  else
                        wrfil.writeline(" !!!! " & rm.submatches(0) & " was opened and NOT closed.")
                        wscript.echo(" !!!! " & rm.submatches(0) & " was opened and NOT closed.")
                  end if
            next
      end if
next
wrfil.close

set regex = nothing
set mcol = nothing
set wrfil = nothing
set ts = nothing
set fol = nothing
set fso = nothing






C:\scripts\test>cscript findado.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

appemail.asp
 OK conn was opened and closed.
 !!!! RShead was opened and NOT closed.
 !!!! adConn was opened and NOT closed.
 !!!! RS was opened and NOT closed.
deals.asp
 !!!! conn was opened and NOT closed.
 OK conn400 was opened and closed.
 !!!! RSdeals was opened and NOT closed.
 !!!! RSuser was opened and NOT closed.
deal_approve.asp
 !!!! conn was opened and NOT closed.
 !!!! RSdeal was opened and NOT closed.
 !!!! RStemp was opened and NOT closed.
deal_main.asp
deal_main_old.asp
 !!!! conn was opened and NOT closed.
 OK RShead was opened and closed.
 !!!! RSuser was opened and NOT closed.
default.asp
 !!!! conn400 was opened and NOT closed.
 !!!! connsql was opened and NOT closed.
 !!!! RSreps was opened and NOT closed.
 !!!! RSuser was opened and NOT closed.
enh_products.asp
 !!!! conn was opened and NOT closed.
 !!!! conn400 was opened and NOT closed.
 !!!! RSdeals was opened and NOT closed.
 !!!! RSuser was opened and NOT closed.
enh_templates.asp
 !!!! conn was opened and NOT closed.
 !!!! conn400 was opened and NOT closed.
 !!!! RSdeals was opened and NOT closed.
 OK rscust was opened and closed.
template_head.asp
 OK conn was opened and closed.
 !!!! RStemp was opened and NOT closed.
 OK RScust was opened and closed.
unsubmitted.asp
 !!!! conn was opened and NOT closed.
 !!!! conn400 was opened and NOT closed.
 !!!! RSdeals was opened and NOT closed.
 !!!! RSuser was opened and NOT closed.
users.asp
 !!!! conn400 was opened and NOT closed.
 !!!! connsql was opened and NOT closed.
 OK RSreps was opened and closed.
 OK RSusers was opened and closed.
0
 
LVL 1

Author Comment

by:sknight
ID: 18827114
That's a beautiful looking script.  :)

I'll have to do some research into regex for myself, is the .NET Framework 1.0 all I need for this to function?  Or do I need any specific Service Packs or the like?

Thanks for your help and suggestions by the way.  :)
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18827134
this is not .net at all.  most computers have the engine needed to run the script.  just run it like i pasted above using "cscript" in front of the script name.
0
 
LVL 1

Author Comment

by:sknight
ID: 18827258
Worked like a charm, I'll tweak it to fit!  Thanks for your help.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18827348
glad i could find you a solution.
0
 
LVL 22

Expert Comment

by:WMIF
ID: 18827353
btw, did you find what you had feared?
0
 
LVL 1

Author Comment

by:sknight
ID: 18827386
More so even.  One particular script we have been using over and over again in particular, opening 2 recordsets, and not closing them.  Seems I'll have my work cut out for me cleaning up years worth of work.

It's been said a million times before, but just for anyone else that stumbles accross this thread:  If you open it.. close it.  Just like mom said!  :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question