How long do recordsets live? 
Author Message
 How long do recordsets live?

Hi all. It seems to be common practice to manually destroy local recordsets
like this:

    public function blah()

        dim r as recordset

        ***

        r.close
        set r = nothing

    end function

Is this necessary? Or would these recordsets automatically die upon leaving
the subroutine in which they were created? Further, does this go for all
objects?

Just wonderin'. -Adam



Tue, 27 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
hi

Yes indeed, when the function or sub in witch the variable is declared ends,
they get destroyed.

Some say that it is good programming practice to set object variables to
nothing before they get destroyed, just in case.

You can test this behavior by creating a class module, put some code in the
initialize and terminate event of that class (like debug.print "Alive" and
debug.print "Died"), and instantiate it inside a sub or function.

You will see that when the function or sub ends, the class always 'dies' and
so, normally, your recordset objects should to.

bye


Quote:

>Hi all. It seems to be common practice to manually destroy local recordsets
>like this:

>    public function blah()

>        dim r as recordset

>        ***

>        r.close
>        set r = nothing

>    end function

>Is this necessary? Or would these recordsets automatically die upon leaving
>the subroutine in which they were created? Further, does this go for all
>objects?

>Just wonderin'. -Adam



Tue, 27 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
Adam--

Many Objects have methods that are similar to the Close method of a Recordset.
While these methods are supposed to clean up after themselves, history has shown
that 'phantom' references to these objects may remain.

Call it preventative maintenance or an insurance policy; Set every object =
Nothing when your done with it.  It limits the possibility of memory leaks.

--
Robert R. Thele


[Remove NoSpam_ to reply via E-mail]

Quote:

>Hi all. It seems to be common practice to manually destroy local recordsets
>like this:

>    public function blah()

>        dim r as recordset

>        ***

>        r.close
>        set r = nothing

>    end function

>Is this necessary? Or would these recordsets automatically die upon leaving
>the subroutine in which they were created? Further, does this go for all
>objects?

>Just wonderin'. -Adam



Tue, 27 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
It is my belief that they just die when leaving the subroutine, if they are
declared in the subroutine.  This would not be the case for a record set
declared at module level, or one declared as static.

Regards
--
Richard Gillard



Quote:
> Hi all. It seems to be common practice to manually destroy local
recordsets
> like this:

>     public function blah()

>         dim r as recordset

>         ***

>         r.close
>         set r = nothing

>     end function

> Is this necessary? Or would these recordsets automatically die upon
leaving
> the subroutine in which they were created? Further, does this go for all
> objects?

> Just wonderin'. -Adam



Tue, 27 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
What practical purpose can there be in setting a (non-Static) local object
variable to Nothing before exiting the subroutine? Surely exiting will have
precisely that effect. If other cleanup is required beforehand, then of
course you will need to do that anyway.

I appreciate that some may think there's a "don't fall into bad habits"
argument for doing the strictly unnecessary, but presumably they also erase
all their local dynamic arrays, set all their strings to "" etc before
exit...

My attitude is: let the environment do as much cleanup as possible. Minimize
lines of code!

Bertie (in tasteful flame-proof pyjamas)

Quote:

>Adam--

>Many Objects have methods that are similar to the Close method of a
Recordset.
>While these methods are supposed to clean up after themselves, history has
shown
>that 'phantom' references to these objects may remain.

>Call it preventative maintenance or an insurance policy; Set every object =
>Nothing when your done with it.  It limits the possibility of memory leaks.

>--
>Robert R. Thele


>[Remove NoSpam_ to reply via E-mail]

>>Hi all. It seems to be common practice to manually destroy local
recordsets
>>like this:

>>    public function blah()

>>        dim r as recordset

>>        ***

>>        r.close
>>        set r = nothing

>>    end function

>>Is this necessary? Or would these recordsets automatically die upon
leaving
>>the subroutine in which they were created? Further, does this go for all
>>objects?

>>Just wonderin'. -Adam



Wed, 28 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
Bertie--

Quote:
>What practical purpose can there be in setting a (non-Static) local object
>variable to Nothing before exiting the subroutine?

Depends on your definition of local and your definition of object:
    Local?
        Relative to what?:
            A Sub/Function?
            A Class?
            A Form/Module?
            A Project?
    Object?
        Standard data types?
        Classes?
        DLLs?
        ActiveX?

What I'm saying is that if you use the SET keyword to create an object, use the
NOTHING keyword you are done with it.

Try this:
1) Throw a datacontrol and flexgrid on a form; Bind the flexgrid to the
datacontrol.
2) Create a valid recordset via DAO
3) Set datacontrol.Recordset = DAO recordset
4) Run Datacontrol.Recordset.Close
5) Display the DAO workspace's recordset count
6) Hmmmm???? (Is this memory reclaimed if I exit the program?)

7) Set Datacontrol.Recordset = Nothing
8) Run DataControl.Refresh
9) Display the DAO workspace's recordset count
10) Hmmmm???? (No memory leaks to worry about)

--
Robert R. Thele


[Remove NoSpam_ to reply via E-mail]

Quote:

>What practical purpose can there be in setting a (non-Static) local object
>variable to Nothing before exiting the subroutine? Surely exiting will have
>precisely that effect. If other cleanup is required beforehand, then of
>course you will need to do that anyway.

>I appreciate that some may think there's a "don't fall into bad habits"
>argument for doing the strictly unnecessary, but presumably they also erase
>all their local dynamic arrays, set all their strings to "" etc before
>exit...

>My attitude is: let the environment do as much cleanup as possible. Minimize
>lines of code!

>Bertie (in tasteful flame-proof pyjamas)


>>Adam--

>>Many Objects have methods that are similar to the Close method of a
>Recordset.
>>While these methods are supposed to clean up after themselves, history has
>shown
>>that 'phantom' references to these objects may remain.

>>Call it preventative maintenance or an insurance policy; Set every object =
>>Nothing when your done with it.  It limits the possibility of memory leaks.

>>--
>>Robert R. Thele


>>[Remove NoSpam_ to reply via E-mail]

>>>Hi all. It seems to be common practice to manually destroy local
>recordsets
>>>like this:

>>>    public function blah()

>>>        dim r as recordset

>>>        ***

>>>        r.close
>>>        set r = nothing

>>>    end function

>>>Is this necessary? Or would these recordsets automatically die upon
>leaving
>>>the subroutine in which they were created? Further, does this go for all
>>>objects?

>>>Just wonderin'. -Adam



Wed, 28 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
Hello Robert...

Quote:

>>What practical purpose can there be in setting a (non-Static) local object
>>variable to Nothing before exiting the subroutine?

>Depends on your definition of local and your definition of object:
>    Local?
>        Relative to what?:
>            A Sub/Function?
>            A Class?
>            A Form/Module?
>            A Project?

Doesn't matter -- when the scope ends the cleanup will happen. Is this
incorrect?

Quote:
>    Object?
>        Standard data types?
>        Classes?
>        DLLs?
>        ActiveX?

I think they will all get cleaned up automatically. But I am very willing to
be proved wrong.

Quote:
>What I'm saying is that if you use the SET keyword to create an object, use
the
>NOTHING keyword you are done with it.

>Try this:
>1) Throw a datacontrol and flexgrid on a form; Bind the flexgrid to the
>datacontrol.
>2) Create a valid recordset via DAO
>3) Set datacontrol.Recordset = DAO recordset
>4) Run Datacontrol.Recordset.Close
>5) Display the DAO workspace's recordset count
>6) Hmmmm???? (Is this memory reclaimed if I exit the program?)

Of course. OS bug if it isn't.

Quote:
>7) Set Datacontrol.Recordset = Nothing
>8) Run DataControl.Refresh
>9) Display the DAO workspace's recordset count
>10) Hmmmm???? (No memory leaks to worry about)

My point is that I would like to know the circumstances when my "rules" are
likely to let me down. But a lot of the counter-arguments seem to be based
on anecdote or assumptions rather than knowledge of the real mechanisms
involved.

Bertie



Fri, 30 Mar 2001 03:00:00 GMT  
 How long do recordsets live?

Quote:


>>>What practical purpose can there be in setting a (non-Static) local
object
>>>variable to Nothing before exiting the subroutine?
<cut>
>My point is that I would like to know the circumstances when my "rules" are
>likely to let me down. But a lot of the counter-arguments seem to be based
>on anecdote or assumptions rather than knowledge of the real mechanisms
>involved.

You are correct that local variables will be released when the routine ends
and that should free the object.  In the case of databases, recordsets, etc
then the 'Close' should be done by the termination event inside the object.
Your 'rules' are not wrong.

On the other hand, an early rev of DAO had a bug where recordset objects did
not close properly if Close was not called explicitly so not doing it caused
a memory leak.  Also, the order in which objects are released is not
controllable when you let the app do it for you and that may or may not be
an issue depending on the interrelationship between the objects.  I always
explicitly call Close methods and set all objects =Nothing so that (1) I am
sure it has been done, (2) it happens in the order I expect and (3) I think
it helps with maintenance programming.  Is it required? no.  Does it hurt?
no.  Could it help to do it? possibly.

Non-object variables I do not clear because they don't interact with
anything else and there is little chance that a problem can arise no matter
how they are freed.



Fri, 30 Mar 2001 03:00:00 GMT  
 How long do recordsets live?



<snip>

Quote:
> You are correct that local variables will be released when the routine
ends
> and that should free the object.  In the case of databases, recordsets,
etc
> then the 'Close' should be done by the termination event inside the
object.
> Your 'rules' are not wrong.

> On the other hand, an early rev of DAO had a bug where recordset objects
did
> not close properly if Close was not called explicitly so not doing it
caused
> a memory leak.  Also, the order in which objects are released is not
> controllable when you let the app do it for you and that may or may not
be
> an issue depending on the interrelationship between the objects.  I
always
> explicitly call Close methods and set all objects =Nothing so that (1) I
am
> sure it has been done, (2) it happens in the order I expect and (3) I
think
> it helps with maintenance programming.  Is it required? no.  Does it
hurt?
> no.  Could it help to do it? possibly.

> Non-object variables I do not clear because they don't interact with
> anything else and there is little chance that a problem can arise no
matter
> how they are freed.

I agree, and I say thank you for a well reasoned and systematic approach.
I have learned in this game to assume nothing.


Fri, 30 Mar 2001 03:00:00 GMT  
 How long do recordsets live?

Bertie,

Quote:
>>What I'm saying is that if you use the SET keyword to create an object, use
>the
>>NOTHING keyword you are done with it.

>>Try this:
>>1) Throw a datacontrol and flexgrid on a form; Bind the flexgrid to the
>>datacontrol.
>>2) Create a valid recordset via DAO
>>3) Set datacontrol.Recordset = DAO recordset
>>4) Run Datacontrol.Recordset.Close
>>5) Display the DAO workspace's recordset count
>>6) Hmmmm???? (Is this memory reclaimed if I exit the program?)

>Of course. OS bug if it isn't.

If you try the code I supplied above, you will find that there a up to 3 open
recordsets left behind. I contacted MS on this issue and they advised me that it
was "by design" (yeah, right!).

Quote:

>>7) Set Datacontrol.Recordset = Nothing
>>8) Run DataControl.Refresh
>>9) Display the DAO workspace's recordset count
>>10) Hmmmm???? (No memory leaks to worry about)

>My point is that I would like to know the circumstances when my "rules" are
>likely to let me down. But a lot of the counter-arguments seem to be based
>on anecdote or assumptions rather than knowledge of the real mechanisms
>involved.

As I pointed by the facts above, this is not an anecdote nor an assumption.
There are three open recordsets (despite the fact that the Close was used) UNTIL
you set the the datacontrol's recordset property to nothing and Refresh the
datacontrol. This is not an OS bug (because MS says so) and will not be
rectified by MS (because it is "by design"). Therefore, you WILL have (possibly
huge amounts of) data in memory that the average coder will assume has been
destroyed.

I was one who did not subscribe to the "Set everything = Nothing" until this
incident occurred. It proves (to me, at least) that something is to be gained by
cleaning up after myself. Setting every OBJECT to nothing creates an very,very,
very small performance penalty when compared to the possibility of memory leaks;
whether these are caused by accident or "by design".



Fri, 30 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
Thanks, Robert -- definitely something I wasn't aware of.

Spookily, I read an article in the current MSDN News last night, entitled
"Rowset Persistence in MS Data Access Components". Could this be something
to do with it?

Bertie

Quote:

>Bertie,

>>>What I'm saying is that if you use the SET keyword to create an object,
use
>>the
>>>NOTHING keyword you are done with it.

>>>Try this:
>>>1) Throw a datacontrol and flexgrid on a form; Bind the flexgrid to the
>>>datacontrol.
>>>2) Create a valid recordset via DAO
>>>3) Set datacontrol.Recordset = DAO recordset
>>>4) Run Datacontrol.Recordset.Close
>>>5) Display the DAO workspace's recordset count
>>>6) Hmmmm???? (Is this memory reclaimed if I exit the program?)

>>Of course. OS bug if it isn't.

>If you try the code I supplied above, you will find that there a up to 3
open
>recordsets left behind. I contacted MS on this issue and they advised me
that it
>was "by design" (yeah, right!).

>>>7) Set Datacontrol.Recordset = Nothing
>>>8) Run DataControl.Refresh
>>>9) Display the DAO workspace's recordset count
>>>10) Hmmmm???? (No memory leaks to worry about)

>>My point is that I would like to know the circumstances when my "rules"
are
>>likely to let me down. But a lot of the counter-arguments seem to be based
>>on anecdote or assumptions rather than knowledge of the real mechanisms
>>involved.

>As I pointed by the facts above, this is not an anecdote nor an assumption.
>There are three open recordsets (despite the fact that the Close was used)
UNTIL
>you set the the datacontrol's recordset property to nothing and Refresh the
>datacontrol. This is not an OS bug (because MS says so) and will not be
>rectified by MS (because it is "by design"). Therefore, you WILL have
(possibly
>huge amounts of) data in memory that the average coder will assume has been
>destroyed.

>I was one who did not subscribe to the "Set everything = Nothing" until
this
>incident occurred. It proves (to me, at least) that something is to be
gained by
>cleaning up after myself. Setting every OBJECT to nothing creates an
very,very,
>very small performance penalty when compared to the possibility of memory
leaks;
>whether these are caused by accident or "by design".



Sat, 31 Mar 2001 03:00:00 GMT  
 How long do recordsets live?
Thanks for that, all grist to the mill. But with whom am I conversing? Is
this Bob Butler, or Bilbo Butler perhaps? I know how I can tell -- how's
about a FAQ for this newsgroup, Bbo? <vbg>

Bertram

Quote:



>>>>What practical purpose can there be in setting a (non-Static) local
>object
>>>>variable to Nothing before exiting the subroutine?
><cut>
>>My point is that I would like to know the circumstances when my "rules"
are
>>likely to let me down. But a lot of the counter-arguments seem to be based
>>on anecdote or assumptions rather than knowledge of the real mechanisms
>>involved.

>You are correct that local variables will be released when the routine ends
>and that should free the object.  In the case of databases, recordsets, etc
>then the 'Close' should be done by the termination event inside the object.
>Your 'rules' are not wrong.

>On the other hand, an early rev of DAO had a bug where recordset objects
did
>not close properly if Close was not called explicitly so not doing it
caused
>a memory leak.  Also, the order in which objects are released is not
>controllable when you let the app do it for you and that may or may not be
>an issue depending on the interrelationship between the objects.  I always
>explicitly call Close methods and set all objects =Nothing so that (1) I am
>sure it has been done, (2) it happens in the order I expect and (3) I think
>it helps with maintenance programming.  Is it required? no.  Does it hurt?
>no.  Could it help to do it? possibly.

>Non-object variables I do not clear because they don't interact with
>anything else and there is little chance that a problem can arise no matter
>how they are freed.



Fri, 06 Apr 2001 03:00:00 GMT  
 
 [ 12 post ] 

 Relevant Pages 

1. Detecting live ADO recordsets hanging off connection objects

2. Recordset: What am I doing wrong?

3. find the top 8 values in recordset, done in code

4. Recordset object, what am i doing wrong?

5. Cannot access fields using recordset property when doing a self-join

6. ADO Update the Database with the modify done on a disconnected recordset in background mode

7. My recordset is no longer deleting!?! (Newbie)

8. A long question about ADO recordsets and DCOM problems

9. A long question about ADO recordsets and DCOM problems

10. Recordset persisting using shape + adPersistADTG (long)

 

 
Powered by phpBB® Forum Software