Is my object really destoyed ?

[TimSki]

Hi,

I'm connecting to a sql server 2005 db using classic asp. typical code would be ...

 function getExample 
 
OpenDataConnection()
set rsID = CreateObject("ADODB.recordset")
searchPhrase = " SELECT blah blah" &_
rsID.Open searchPhrase, strConn 
set getExample = rsID
[rsID.close]
set rsID = nothing 
CloseDataConnection()
 
end function

in the code i have somehting like

 set example = getExample 
Do while example.eof
....
Loop

My question is this...

if include rsID.close then at the point of the Do While it will throw an error saying 'operation not allowed when object is closed'. OK understood. But if i don;t close it isn't the rsID object still hanging around in memory or has the set rsID = nothing destroyrf it so i don't have to worry about the close command.

[jeffstl]

Hi,

I'm connecting to a sql server 2005 db using classic asp. typical code would be ...

function getExample

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchPhrase = " SELECT blah blah" &_
rsID.Open searchPhrase, strConn
set getExample = rsID
[rsID.close]
set rsID = nothing
CloseDataConnec tion()

end function


in the code i have somehting like

set example = getExample
Do while example.eof
....
Loop


My question is this...

if include rsID.close then at the point of the Do While it will throw an error saying 'operation not allowed when object is closed'. OK understood. But if i don;t close it isn't the rsID object still hanging around in memory or has the set rsID = nothing destroyrf it so i don't have to worry about the close command.

You have just stumbled upon a nasty truth of the Microsoft programming structures.

What you did was created nothing but a pointer back to your RS. GetExample does not actually contain your recordset, it only points back to the original RS.

This is because of the Set GetExample = RS, but the problem is that your GetExample is only a variant or function data type, it is not an ADODB Recordset type so the syntax treats it as a pointer instead of the actual RS.

I would recommend doing this instead in your function so that you can still close out your RS and be done with it all in one function:


[code=asp]
option explicit
dim strName
dim strAddress
dim rsID
'declare this outside so you can use it anywhere
set rsID = CreateObject("A DODB.recordset" )
dim searchPhrase

'a suggestion here
'pass this function an SQL string so 'that 'you 'can 'use it over and over again
'with different querys if you wanted
function OpenGetExample( PassedSQLstr)
rsID.Open PassedSQLstr, strConn
end function

function CloseGetExample
[rsID.close]
End Function

'then in your page
OpenDataConnect ion()
OpenGetExample( MySQL)
do while not rsID.EOF
'any code here
wend
CloseGetExample
CloseDataConnec tion()

'I would go ahead and put any settings of objects\records ets to nothing all
'the way down here so that you know your completely done with them
'or have a final function that sets all your rs, objects, etc to nothing last
set rsID = nothing
[/code]

The biggest thing is just to remember to use the CloseGetExample , and CloseDataConnec tion whenever your done looping through your RS. Also remember that you dont actually want to set rsID = Nothing until you are completely and totally done with it on that page. You need to rsID.Close but once you set it to nothing you can no longer use it again on that page. Just FYI

[jeffstl]

By the way I also see now what you were saying in your other post about closing objects properly. I had assumed that the object you were using (Set MyObject = rsID ) was a full class object defined in a .dll class, etc that had all the properties of your recordset, not an empty variant.

So this post above is a continuation of the answer to your other question too. Sorry about that.

[TimSki]

no apology necessary you feedback is very helpful. In fact the 2 posts reflect my developing understanding of what is actually going on here. We will change our code to reflect your suggestions.man y thanks.

Final question (maybe!)

I am therefore right in thinking that where i was calling set rsID = nothing actually does nothing at all. I mean, if it really cleared it from memory then it would throw the same error as if i had called rsID.close would it not. As a result it just hangs around until the garbage collector picks it up ?

[jeffstl]

no apology necessary you feedback is very helpful. In fact the 2 posts reflect my developing understanding of what is actually going on here. We will change our code to reflect your suggestions.man y thanks.

Final question (maybe!)

I am therefore right in thinking that where i was calling set rsID = nothing actually does nothing at all. I mean, if it really cleared it from memory then it would throw the same error as if i had called rsID.close would it not. As a result it just hangs around until the garbage collector picks it up ?

No the set rsID = Nothing really is eliminating the object from the session memory.It does free up your server resources.

The error you got was because you were trying to loop through a recordset that had been closed. You thought you were done with it because you were setting GetExample = rsID, but remember all you were doing was pointing back to rsID, so when you try to loop through it you couldn't because it was closed .

On the flip side, setting rsID = Nothing after it had been closed is not a violation of syntax or execution, so you would not get an error or anything, it would just remove rsID from memory.

The reason I say to put the set x = nothing stuff all the way at the bottom is just a precaution because setting your rsID = nothing totally destroys it from memory and therefore you can not use it again at all.

Like this would not work:

[code=asp]
dim rsID
Set rsID = Nothing
rsID = 1 'this line would error out because rsID no longer exists

[/code]

In other words = nothing is not the same as null or blank. It is a total destruction of the declaration of the variable.

[TimSki]

ah ok. The reason for all these questions is because our site perfomance has dropped off dramtically due to large increas in the number of users. Perhaps nothing unusual but our provider says the problem is due to our poor (ish!) coding. They claim that objects are being left open and so are database connections (hence my other post about viewing the number of open connections).

Now i understand this better i can see why the object would remain open since i'm not closing it (!) just setting it to nothing but do you see any reason why the database connection would fail to close ?

[jeffstl]

ah ok. The reason for all these questions is because our site perfomance has dropped off dramtically due to large increas in the number of users. Perhaps nothing unusual but our provider says the problem is due to our poor (ish!) coding. They claim that objects are being left open and so are database connections (hence my other post about viewing the number of open connections).

Now i understand this better i can see why the object would remain open since i'm not closing it (!) just setting it to nothing but do you see any reason why the database connection would fail to close ?

I would have to see the code for the CloseDataConnec tion() sub.

If you are using that CloseDataConnec tion() and its right after every connection you create you shouldnt have any open connections.

Also, your provider is going to say anything and everything to you to keep them from fully investigating. It takes a pretty thorough analysis to figure out performance problems and thier actual cause considering how many factors play into things.

Bandwidth of the server per hour? How much user volume to you actually have simultaneously? Is there a peak time you can observe? What kind of database is it and how many open connections can it handle at once? Is your site on a dedicated server? Is the server monitored 24\7? etc etc etc

You need to close your recordsets, and you need to close your database connections, and you need to do it in a way that ensures nothing is left hanging yes, but there are alot of other factors that could be effecting performance and I seriously doubt they ruled out all of them.

One thing that should try to be accomplished is monitoring open connections if possible or have the provider try to give you a report proving that your connections are causing the problem.

[TimSki]

i totally agree and half expected as much. They always come back with something. i have asked them to give me the results of sp_who the next time they claim i have connections left open - so far only radio silence...

[jeffstl]

Wanted to add to this

If you are using that CloseDataConnec tion() and its right after every connection you create you shouldnt have any open connections.

That is to say you must be aware of the order of execution so that CloseDataConnec tion line is always hit (can't be behind a conditional argument like an IF statement just in case it never reaches it)

Also be aware that if you page errors out on the server before it reaches that line that will also leave an open connection, atleast temporarily.