Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Jay Olive » Sat, 08 Sep 2001 04:20:20



I'm just curious, is there any difference doing

Recordset.Close
Set Recordset = Nothing

versus doing

set Recordset = Nothing

I've always been under the impression destroying the recordset object
implicitly closed the connection, but I was recently contradicted. So have I
been wrong all along?

Jay Oliver

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Stephen How » Sat, 08 Sep 2001 04:39:41



Quote:> I'm just curious, is there any difference doing

> Recordset.Close
> Set Recordset = Nothing

> versus doing

> set Recordset = Nothing

I thought the same as you using VC++. Now I think I may have to close
recordsets before setting them to nothing, in other words, I was wrong.
I shall check this soon using the de*.

Stephen Howe

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Eric Garz » Sat, 08 Sep 2001 05:31:08


Jay,

It's a matter of good programming practices, you shouldn't rely on the
destructor doing the close.

If you know that you want to close it, do it explicitly.

--
Regards,
Eric Garza, MVP
Project manager
AMI GE


Quote:> I'm just curious, is there any difference doing

> Recordset.Close
> Set Recordset = Nothing

> versus doing

> set Recordset = Nothing

> I've always been under the impression destroying the recordset object
> implicitly closed the connection, but I was recently contradicted. So have
I
> been wrong all along?

> Jay Oliver

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Stephen How » Sat, 08 Sep 2001 07:26:23


Quote:> It's a matter of good programming practices, you shouldn't rely on the
> destructor doing the close.

I don't recognize that as good programming practice for 3 reasons:

1. Consider the ISO C++ file stream objects: ifstream, fstream, ofstream
The destructors are _guaranteed_ to release buffers, close files because
they have been designed to clean up after themselves. That is the normal
behaviour of a well written C++ object. The same could be said basic_string,
stringstream's and all the container classes (vector, list, map). The
programmer does not have to manually clean up and if they did, it would be
error prone.

2. Consider a pending exception. If what you said was true, then you would
have to write messy try-catch clauses to make your code exception proof.
These would be necessary -- just to close the Recordset before the
destructor gets fired because the object is going out of scope.

3. It goes against the grain of "resource acquisition is initialisation"
technique as popularised by Stroustrup in 2nd/3rd edition. The point about
the technique, in that resources are released in the destructor is that it
is _not_ error prone and is _guaranteed_ to release resources whether the
object is terminating normally or because an exception is being thrown.

Ideally the RecordSet SmartPtrs that Microsoft have provided (which I like)
would clean up after themselves. The bare minimum would be that RecordSets
would be closed, Connection pointers would be closed, Com objects would be
Release()'d.

I believe that the smart pointers are well written but it is undocumented as
to how well they behave and what the destructors do.

Cheers

Stephen Howe

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Eric Garz » Sat, 08 Sep 2001 08:43:29


You are right, but that was C++ and I was talking VB.

Experience tells me that it's better if you close them explicitly and I
don't think I'm alone.

Also, I understand that it could be a problem with the implementation of the
destructors, but that is MS business, mine is to deliver robust
applications.

--
Regards,
Eric Garza, MVP
Project manager
AMI GE


Quote:> > It's a matter of good programming practices, you shouldn't rely on the
> > destructor doing the close.

> I don't recognize that as good programming practice for 3 reasons:

> 1. Consider the ISO C++ file stream objects: ifstream, fstream, ofstream
> The destructors are _guaranteed_ to release buffers, close files because
> they have been designed to clean up after themselves. That is the normal
> behaviour of a well written C++ object. The same could be said
basic_string,
> stringstream's and all the container classes (vector, list, map). The
> programmer does not have to manually clean up and if they did, it would be
> error prone.

> 2. Consider a pending exception. If what you said was true, then you would
> have to write messy try-catch clauses to make your code exception proof.
> These would be necessary -- just to close the Recordset before the
> destructor gets fired because the object is going out of scope.

> 3. It goes against the grain of "resource acquisition is initialisation"
> technique as popularised by Stroustrup in 2nd/3rd edition. The point about
> the technique, in that resources are released in the destructor is that it
> is _not_ error prone and is _guaranteed_ to release resources whether the
> object is terminating normally or because an exception is being thrown.

> Ideally the RecordSet SmartPtrs that Microsoft have provided (which I
like)
> would clean up after themselves. The bare minimum would be that RecordSets
> would be closed, Connection pointers would be closed, Com objects would be
> Release()'d.

> I believe that the smart pointers are well written but it is undocumented
as
> to how well they behave and what the destructors do.

> Cheers

> Stephen Howe

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Robert Simpso » Sun, 09 Sep 2001 03:24:40


I'd also have to disagree, even with VB.

Class A has a global member, m_rs of type ADODB.Recordset, and a property
called Recordset, which returns m_rs to the caller.  Class A initializes
m_rs with some data from a datasource.

Class B calls Class A's property to retrieve the recordset
Class C calls Class A's property to retrieve the recordset

Class B terminates and explicitly closes the recordset
Class C now has a closed recordset, and who's to say whether Class C
*wanted* it closed?

Only the Recordset itself knows when its refcount reaches zero, and in a COM
world where pointers to objects can be handeded out ad-hoc, I personally
think its an incredibly BAD idea to explicitly close a recordset except in
cases where you absolutely positively know that nobody else has a copy of
it.

PS.  Class A's destructor cannot explicitly close m_rs either, since other
classes may still be using the recordset after Class A goes out of scope.

--
Robert Simpson
Programmer at Large
Black Castle Software, LLC
http://www.blackcastlesoft.com


> You are right, but that was C++ and I was talking VB.

> Experience tells me that it's better if you close them explicitly and I
> don't think I'm alone.

> Also, I understand that it could be a problem with the implementation of
the
> destructors, but that is MS business, mine is to deliver robust
> applications.

> --
> Regards,
> Eric Garza, MVP
> Project manager
> AMI GE



> > > It's a matter of good programming practices, you shouldn't rely on the
> > > destructor doing the close.

> > I don't recognize that as good programming practice for 3 reasons:

> > 1. Consider the ISO C++ file stream objects: ifstream, fstream, ofstream
> > The destructors are _guaranteed_ to release buffers, close files because
> > they have been designed to clean up after themselves. That is the normal
> > behaviour of a well written C++ object. The same could be said
> basic_string,
> > stringstream's and all the container classes (vector, list, map). The
> > programmer does not have to manually clean up and if they did, it would
be
> > error prone.

> > 2. Consider a pending exception. If what you said was true, then you
would
> > have to write messy try-catch clauses to make your code exception proof.
> > These would be necessary -- just to close the Recordset before the
> > destructor gets fired because the object is going out of scope.

> > 3. It goes against the grain of "resource acquisition is initialisation"
> > technique as popularised by Stroustrup in 2nd/3rd edition. The point
about
> > the technique, in that resources are released in the destructor is that
it
> > is _not_ error prone and is _guaranteed_ to release resources whether
the
> > object is terminating normally or because an exception is being thrown.

> > Ideally the RecordSet SmartPtrs that Microsoft have provided (which I
> like)
> > would clean up after themselves. The bare minimum would be that
RecordSets
> > would be closed, Connection pointers would be closed, Com objects would
be
> > Release()'d.

> > I believe that the smart pointers are well written but it is
undocumented
> as
> > to how well they behave and what the destructors do.

> > Cheers

> > Stephen Howe

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Robert Simpso » Sun, 09 Sep 2001 03:28:28


Of course, the above is a bad example, but the principle (COM's AddRef() and
Release() having a bearing on whether or not a recordset should be closed)
is the same.

In the real world, Class A would be handing out Clone()'s of the
recordset...


> I'd also have to disagree, even with VB.

> Class A has a global member, m_rs of type ADODB.Recordset, and a property
> called Recordset, which returns m_rs to the caller.  Class A initializes
> m_rs with some data from a datasource.

> Class B calls Class A's property to retrieve the recordset
> Class C calls Class A's property to retrieve the recordset

> Class B terminates and explicitly closes the recordset
> Class C now has a closed recordset, and who's to say whether Class C
> *wanted* it closed?

> Only the Recordset itself knows when its refcount reaches zero, and in a
COM
> world where pointers to objects can be handeded out ad-hoc, I personally
> think its an incredibly BAD idea to explicitly close a recordset except in
> cases where you absolutely positively know that nobody else has a copy of
> it.

> PS.  Class A's destructor cannot explicitly close m_rs either, since other
> classes may still be using the recordset after Class A goes out of scope.

> --
> Robert Simpson
> Programmer at Large
> Black Castle Software, LLC
> http://www.blackcastlesoft.com



> > You are right, but that was C++ and I was talking VB.

> > Experience tells me that it's better if you close them explicitly and I
> > don't think I'm alone.

> > Also, I understand that it could be a problem with the implementation of
> the
> > destructors, but that is MS business, mine is to deliver robust
> > applications.

> > --
> > Regards,
> > Eric Garza, MVP
> > Project manager
> > AMI GE



> > > > It's a matter of good programming practices, you shouldn't rely on
the
> > > > destructor doing the close.

> > > I don't recognize that as good programming practice for 3 reasons:

> > > 1. Consider the ISO C++ file stream objects: ifstream, fstream,
ofstream
> > > The destructors are _guaranteed_ to release buffers, close files
because
> > > they have been designed to clean up after themselves. That is the
normal
> > > behaviour of a well written C++ object. The same could be said
> > basic_string,
> > > stringstream's and all the container classes (vector, list, map). The
> > > programmer does not have to manually clean up and if they did, it
would
> be
> > > error prone.

> > > 2. Consider a pending exception. If what you said was true, then you
> would
> > > have to write messy try-catch clauses to make your code exception
proof.
> > > These would be necessary -- just to close the Recordset before the
> > > destructor gets fired because the object is going out of scope.

> > > 3. It goes against the grain of "resource acquisition is
initialisation"
> > > technique as popularised by Stroustrup in 2nd/3rd edition. The point
> about
> > > the technique, in that resources are released in the destructor is
that
> it
> > > is _not_ error prone and is _guaranteed_ to release resources whether
> the
> > > object is terminating normally or because an exception is being
thrown.

> > > Ideally the RecordSet SmartPtrs that Microsoft have provided (which I
> > like)
> > > would clean up after themselves. The bare minimum would be that
> RecordSets
> > > would be closed, Connection pointers would be closed, Com objects
would
> be
> > > Release()'d.

> > > I believe that the smart pointers are well written but it is
> undocumented
> > as
> > > to how well they behave and what the destructors do.

> > > Cheers

> > > Stephen Howe

 
 
 

Recordset.Close, set Recordset = Nothing vs set Recordset = Nothing

Post by Eric Garz » Sun, 09 Sep 2001 04:46:23


Of course it is a bad example, but that's not the point, the point is if we
should close our Recordset objects before releasing them or not, here are
some related links from MSDN:

Improving MDAC Application Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wp/h...
proveperformancemdacapplication.asp
Coding Techniques and Programming Practices
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs...
l/cfr.asp
25+ ASP Tips to Improve Performance and Style
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnas...
asptips.asp

--
Regards,
Eric Garza, MVP
Project manager
AMI GE

Release Unused ADO Objects
Whenever possible, release ADO objects as soon as you're done with them.
This frees up the database and other resources that might be expensive to
hold for an extended period. Explicitly close all objects rather than
allowing the object to close itself as it is destroyed.

ADO-Specific
  a.. Tune the RecordSet.CacheSize property to what is needed. Using too
small or too large a setting will adversely impact the performance of an
application.
  b.. Bind columns to field objects when looping through recordsets.
  c.. For Command objects, describe the parameters manually instead of using
Parameters.Refresh to obtain parameter information.
  d.. Explicitly close ADO Recordset and Connection objects to insure that
connections are promptly returned to the connection pool for use by other
processes.
  e.. Use adExecuteNoRecords for non-row-returning commands.

Tip 8: Acquire Resources Late, Release Early
Here's a short tip for you. In general, it's best to acquire resources late
and release them early. This goes for COM objects as well as file handles
and other resources.

ADO Connections and recordsets are the prime candidates for this
optimization. When you are done using a recordset, say after painting a
table with its data, release it immediately, rather than waiting until the
end of the page. Setting your VBScript variable to Nothing is a best
practice. Don't let the recordset simply fall out of scope. Also, release
any related Command or Connection objects. (Don't forget to call Close() on
recordsets or Connections before setting them = Nothing.) This shortens the
time span in which the database must juggle resources for you, and releases
the database connection to the connection pool as quickly as possible."


> Of course, the above is a bad example, but the principle (COM's AddRef()
and
> Release() having a bearing on whether or not a recordset should be closed)
> is the same.

> In the real world, Class A would be handing out Clone()'s of the
> recordset...



> > I'd also have to disagree, even with VB.

> > Class A has a global member, m_rs of type ADODB.Recordset, and a
property
> > called Recordset, which returns m_rs to the caller.  Class A initializes
> > m_rs with some data from a datasource.

> > Class B calls Class A's property to retrieve the recordset
> > Class C calls Class A's property to retrieve the recordset

> > Class B terminates and explicitly closes the recordset
> > Class C now has a closed recordset, and who's to say whether Class C
> > *wanted* it closed?

> > Only the Recordset itself knows when its refcount reaches zero, and in a
> COM
> > world where pointers to objects can be handeded out ad-hoc, I personally
> > think its an incredibly BAD idea to explicitly close a recordset except
in
> > cases where you absolutely positively know that nobody else has a copy
of
> > it.

> > PS.  Class A's destructor cannot explicitly close m_rs either, since
other
> > classes may still be using the recordset after Class A goes out of
scope.

> > --
> > Robert Simpson
> > Programmer at Large
> > Black Castle Software, LLC
> > http://www.blackcastlesoft.com



> > > You are right, but that was C++ and I was talking VB.

> > > Experience tells me that it's better if you close them explicitly and
I
> > > don't think I'm alone.

> > > Also, I understand that it could be a problem with the implementation
of
> > the
> > > destructors, but that is MS business, mine is to deliver robust
> > > applications.

> > > --
> > > Regards,
> > > Eric Garza, MVP
> > > Project manager
> > > AMI GE



> > > > > It's a matter of good programming practices, you shouldn't rely on
> the
> > > > > destructor doing the close.

> > > > I don't recognize that as good programming practice for 3 reasons:

> > > > 1. Consider the ISO C++ file stream objects: ifstream, fstream,
> ofstream
> > > > The destructors are _guaranteed_ to release buffers, close files
> because
> > > > they have been designed to clean up after themselves. That is the
> normal
> > > > behaviour of a well written C++ object. The same could be said
> > > basic_string,
> > > > stringstream's and all the container classes (vector, list, map).
The
> > > > programmer does not have to manually clean up and if they did, it
> would
> > be
> > > > error prone.

> > > > 2. Consider a pending exception. If what you said was true, then you
> > would
> > > > have to write messy try-catch clauses to make your code exception
> proof.
> > > > These would be necessary -- just to close the Recordset before the
> > > > destructor gets fired because the object is going out of scope.

> > > > 3. It goes against the grain of "resource acquisition is
> initialisation"
> > > > technique as popularised by Stroustrup in 2nd/3rd edition. The point
> > about
> > > > the technique, in that resources are released in the destructor is
> that
> > it
> > > > is _not_ error prone and is _guaranteed_ to release resources
whether
> > the
> > > > object is terminating normally or because an exception is being
> thrown.

> > > > Ideally the RecordSet SmartPtrs that Microsoft have provided (which
I
> > > like)
> > > > would clean up after themselves. The bare minimum would be that
> > RecordSets
> > > > would be closed, Connection pointers would be closed, Com objects
> would
> > be
> > > > Release()'d.

> > > > I believe that the smart pointers are well written but it is
> > undocumented
> > > as
> > > > to how well they behave and what the destructors do.

> > > > Cheers

> > > > Stephen Howe