Solved

Close All Open Recordsets?

Posted on 2007-03-29
17
284 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
17 Comments
 
LVL 9

Assisted Solution

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

Expert Comment

by:WMIF
Comment Utility
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
Comment Utility
"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
 
LVL 22

Expert Comment

by:neeraj523
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:sknight
Comment Utility
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
Comment Utility
>>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 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Worked like a charm, I'll tweak it to fit!  Thanks for your help.
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
glad i could find you a solution.
0
 
LVL 22

Expert Comment

by:WMIF
Comment Utility
btw, did you find what you had feared?
0
 
LVL 1

Author Comment

by:sknight
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now