Database objects scope

Christiaan Nijdam · Christiaan Nijdam

What will go wrong? I ran the following page for 36 hours. It didn't
have any affect on the memory used. It looks like the ADO-COM objects
get released as they should when going out of scope. It is virtually
impossible to dereference all the ADO objects, since there are
Response.End and Response.Redirect's etc.

Christiaan.

<%
option explicit
dim rs,sql,conn
set conn=Server.CreateObject("ADODB.Connection")
conn.Open "Driver={SQL
Server};Server=.;Uid=SA;Pwd=;Database=northwind"
set rs=conn.Execute("select * from employees")
%>
<html>
<body>
<%
dim i
do until rs.eof
Response.Write rs("employeeid") & " " & rs("Firstname") & " " &
rs("LastName") & "<br>"
i=i+1
if i>5 then exit do
rs.MoveNext
loop
Response.Write now() & "<br>"
%>
<script>
location.reload()
</script>
</body>
</html>

"Kris Eiben" <eibenkthisisforspammers@yahoo.com> wrote in message news:<O#GOfPrUDHA.3312@tk2msftngp13.phx.gbl>...

> OK, so call it a bug in the garbage collection. But your customer won't
> take that as an excuse when your pages stop working. So, yes, there is
> a lot of "use" in closing and destroying all your objects.
> More info here:
> [url]http://www.aspfaq.com/show.asp?id=2435[/url]
> [url]http://www.aspfaq.com/show.asp?id=2227[/url]
>
> "Christiaan Nijdam" <acnijdam_n0spam@yahoo.com> wrote in message
> news:83f4b79a.0307250200.4c899f8d@posting.google.c om...

> > Hi,
> > Is it any use to close and dereference a used recordset and/or
> > connection at the end of an ASP page just before it goes out of scope?
> > <%
> > dim conn,rs
> > set conn=Server.CreateObject("ADODB.Connection")
> > conn.Open "bla"
> > set rs=connection.Execute("select * from tblSomeTable")
> > 'Can the following lines can be left out?
> > rs.Close()
> > Set rs=Nothing
> > conn.Close
> > set conn=nothing
> > %>
> > Some say the recordset and/or connection will exist beyond the page
> > scope and will never be released. Is there any explicit documentation
> > from Microsoft about this subject? If closing and dereferencing is
> > really necessary wouldn't that be a bug in ASP/ADO?

Bob Barrows · Bob Barrows

Christiaan Nijdam wrote:

> What will go wrong? I ran the following page for 36 hours. It didn't
> have any affect on the memory used.

This code may be all right. You are going through the entire set of records
before ending the script. That increases the likelihood that the ADO objects
will be released when they go out of scope. But ... I would not count on it!

Where you can get into trouble is if you fail to retrieve the last record in
a recordset for some reason (this could be deliberate). The recordset may
fail to automatically close in this scenario.

Bottom line: do not depend on your simple test here. When multiple users and
threads intrude, the results may be quite different. There have been many
cases reported in these newsgroups where IIS would die after a day or two,
with the problem going away when ADO objects were explicitly dereferenced.

> It looks like the ADO-COM objects
> get released as they should when going out of scope. It is virtually
> impossible to dereference all the ADO objects, since there are
> Response.End and Response.Redirect's etc.

No it isn't. It is very far from impossible. It may be difficult due to the
number of redirects, etc, but it is certainly not impossible.
Simply dereference the objects before the response.end or response.redirect
statement.

You can use this as a good opportunity to cut down on the number of
redirects and ends ...

Bob Barrows

Christiaan Nijdam · Christiaan Nijdam

Ok, it is clear that it is not so clear what will happen. Knowing the
mentioned reports, better be safe, although it is a lot of work. I
hoped that maybe there is an advise with an explanation from the
builders Microsoft....

Thanks,
Christiaan.

"Bob Barrows" <reb_01501@yahoo.com> wrote in message news:<#8#AVRQVDHA.3220@tk2msftngp13.phx.gbl>...

> Christiaan Nijdam wrote:

> > What will go wrong? I ran the following page for 36 hours. It didn't
> > have any affect on the memory used.

>
> This code may be all right. You are going through the entire set of records
> before ending the script. That increases the likelihood that the ADO objects
> will be released when they go out of scope. But ... I would not count on it!
>
> Where you can get into trouble is if you fail to retrieve the last record in
> a recordset for some reason (this could be deliberate). The recordset may
> fail to automatically close in this scenario.

This is what i tested. Some records are skipped by counting in i...

>
> Bottom line: do not depend on your simple test here. When multiple users and
> threads intrude, the results may be quite different. There have been many
> cases reported in these newsgroups where IIS would die after a day or two,
> with the problem going away when ADO objects were explicitly dereferenced.
>
>

> > It looks like the ADO-COM objects
> > get released as they should when going out of scope. It is virtually
> > impossible to dereference all the ADO objects, since there are
> > Response.End and Response.Redirect's etc.

>
> No it isn't. It is very far from impossible. It may be difficult due to the
> number of redirects, etc, but it is certainly not impossible.
> Simply dereference the objects before the response.end or response.redirect
> statement.
>
> You can use this as a good opportunity to cut down on the number of
> redirects and ends ...
>
> Bob Barrows

Database objects scope

Thread Tools

Display

Re: Database objects scope

Similar Questions and Discussions

#40604 [NEW]: Objects disappear from the global scope

#39719 [NEW]: Lost of scope in objects

Scope of variables defined in text objects

Readonly Database Objects??

Problem with sessions (in global scope vs class scope)

Re: Database objects scope

Re: Database objects scope

Posting Permissions