• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Close All Open Recordsets?

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
sknight
Asked:
sknight
2 Solutions
 
fuzzboxerCommented:
I'm not sure you can do this as the ADO does not expose active recordsets.
0
 
WMIFCommented:
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
 
WMIFCommented:
"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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
neeraj523Commented:
why dont you just close the connection object.. automatically all the recordsets associated with it will be close..

neeraj523
0
 
sknightAuthor Commented:
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
 
WMIFCommented:
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
 
sknightAuthor Commented:
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
 
WMIFCommented:
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
 
sknightAuthor Commented:
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
 
WMIFCommented:
>>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
 
WMIFCommented:
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
 
sknightAuthor Commented:
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
 
WMIFCommented:
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
 
sknightAuthor Commented:
Worked like a charm, I'll tweak it to fit!  Thanks for your help.
0
 
WMIFCommented:
glad i could find you a solution.
0
 
WMIFCommented:
btw, did you find what you had feared?
0
 
sknightAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now