Error Trapping To Close Open Resources

[PaulHInstincticve]

I need to help to implement an error handler in ASP that will be able to identify and close open database resources - can anybody help?

My background is Microsoft Visual FoxPro programming for the Windows desktop, however, I have a small application written in ASP on a website that uses databases also (these are FoxPro but that is irrelevant here). I have hit a problem from time to time where the ISP has been on my back for leaving databases open which has been putting a strain on the shared server. Obviously I know that all resources opened should be closed before the script ends in order to avoid this and I am doing this correctly. What is happening, however, is that from time to time databases are being corrupted by the daily FTP upload process rather than ASP activity and as a result the script crashes when it tries to open a corrupt table and therefore never reaches the end of the script in order to close other open databases.

What I need is an error handler similar to what I write in Visual FoxPro for my desktop apps that would run in ASP on the web to perform the following

- activate a common routine that would be run if an ASP error is detected on ANY script in the application
- report that error on screen to the user including script name and line number responsible
- identify and close all open database resources keeping in mind that the error handler is unlikely to know which databases will have been opened by which scripts
- email a report of the error to myself so that I know there is a problem usually before the client does

I suspect I won't have too much difficulty with most of this except for the 3rd item to identify which database resources are open in order to close them. Can anybody help with this? Thanks

Paul

 

[Tarwn]

VBScript has notoriously poor error handling. While it would be possible to write up a whole routine to handle this, how about looking at it from a differant angle?

If you absolutely do not want to produce the web page to the browser unless the foxpro tables are accessible, then you could create a function that you call first thing that checks to make sure it can query from the foxpro files. If it fails to query, write an error message and Response.End. This way you never even start opening other database connections.

On the other hand, if you want to produce the page even when the Foxpro data is corrupt, you can basically use the limited error capability in VBS to skip your Fox Pro section.

VBScript error stuff:
"On Error Resume Next" - Basically this tells the engine to just keep going, regardless of errors
"On Error Goto 0" - This line tell VBScript to start blowing up on error s the way it normally would
err - the err object holds all of the information about the most recent error that occurred

Also there is a seperate error object in the ADO Connection object that holds errors that aren't always passed up to VBScript.


So basically to do some error catching you would do something like this:

<%
Option Explicit

On Error Resume Next

'-- some code here --

'-- check for error
If err.Number <> 0 Then
   'error occurred, do something
End If

'-- some code here --

'-- check for error
If err.Number <> 0 Then
   'error occurred, do something
End If

'-- some code here --

'-- check for error
If err.Number <> 0 Then
   'error occurred, do something
End If

%>

 

[PaulHInstincticve]

Tarwn,

Thanks for that. I think I am going in the right direction now although I must say I am surprised about how primitive the error handling system is compared to what I have been writing in Visual FoxPro for years. I suspect Asp.net moves it up a notch so I need to look into that soon.

I still have a few problems that are proving tricky, not sure if you or anyone can help further. I have adopted the following approach in my main scripts

<%
on error resume next
%>
<!-- #INCLUDE FILE="include/myfunctions.asp" -->
<%
' --- some code here
checkerror()
' --- some more code
checkerror()
%>
etc

I issue a checkerror() after every database command. I then have a function checkerror() in myfunctions.asp which can be called by all my scripts which looks like the following

If Err.Number <> 0 Then

dim lerrnumber, lerrdescription, lerrsource
lerrnumber = err.number
lerrdescription = err.description
lerrsource = err.source ' put error in variables
' in case more errors
myrecordset1.close
set myrecordset1 = nothing
myrecordset2.close
set myrecordset2 = nothing
myrecordset3.close
set myrecordset3 = nothing
etc
con.close
set con = nothing

on error goto 0 ' stop error handling incase
' of bug in error handler


' --- code here to output the original error to screen
' --- code here to email the error to webmaster

response.end()

end if
%>

One of the main drives for my error handler is in order to close recordsets and release resources should an error occur rather than the script simply stopping as well as to be able to notify me quickly about the problem in order to fix it.

During my tests everything does what I want it to do for ordinary syntax errors where I am not using databases and when I am not trying to close recordsets. The routine is, however, called from all my scripts. Script1 might use for example myrecordset1 and myrecordset2, whereas script2, however, might use myrecordset1 and myrecordset3, some scripts may not even use a recordset or connection. In my code I therefore attempt to close and release all known recordsets whether the script in question has used it or not, I assume that any error generated because it is not open will simply be ignored. What is happening, however, is that as soon as I issue a command to close a recordset that has not been opened, the subroutine simply exits and the calling scripts carries on regardless rather than stopping at the response.end() in the error handler. If the recordset is open it works fine.

Am I doing something really silly here or is there an easier way to identify and close only open recordsets and connections?

Any help really appreciated. Thanks

 

[Tarwn]

There a few additional checks you can make to ensure your connection variables are actually connections and live.
IsEmpty() will check if it is an initialized variable or still uninitialized (ie, never used)
IsObject() checks to make sure it is an object (this one may not be necessary or could be used in place of IsEmpty in this case)

Hmm..
Actually I had another thought. You could use the TypeName() function to determine that it is actually a Connection object (it will return the word "Connection"). Then you could use connObject.State to determine the status (ie, if it is open or not).

Unfortunatly, along with the bad error handling, VBScript evaluates all arguments in an If statement before moving on, rather than shortcutting out when it meets a condition that wuold force the value to false/true. So you would have to have a nested If statement or create another, smaller function to handle each connection.

Something like this might work:

Function IsDisposable(obj,nameOfType)
   If TypeName(obj) = nameOfType Then
      If obj.State <> 0 Then
         obj.Close
      End if
      IsDisposable = True
   Else
      IsDisposable = False
   End If
End Function

'then to close things
If IsDisposable(myRecordset,"Recordset") Then Set MyRecordset = Nothing

You have to set the original variable to nothing rather than the reference you passed to the function, but you can use the reference to close the connection. I don't know how well you understand object referencing, so ignore me if this is familiar already. basically when you set an object to Nothing you are simply telling that varioable to stop referencing the memory holding the object. When you pass the variable to a function what you are actually doing is passing that memory reference, not a copy of the object. Now you have two variables (one outside the function and one inside the function) that are referencing the object. By setting the one inside the function to Nothing you still have one outside the function that is referencing it. The way memory management works is that it keeps track of how many references there are to that memory space. Only when there are no more references is it marked for cleanup. So the purpose of setting your original variable to Nothing is to remove the last/only existing reference to the object explicitly instead of waiting for the script to finish executing and for IIS to get around to cleaning up any existing references. I think the fact that in the case of the function above we are creating a second reference to the memory space is ok, because the scope is local to the function and the function should be releasing it's resources even while the rest of the code continues to run, but that is the way I would do it, so it's possible that by creating another reference in the fnuction you may need to set that one to nothing before the function exits as well. it wouldn't be the first tmie someoen at MS did something silly.

-T

 

[PaulHInstincticve]

Tawrn,

Thanks for your detailed explanations they have been really useful in helping my understanding of how ASP is working, although I am getting serious doubts as to how primitive a language it is now compared to MS Visual FoxPro that I am used to!

Whilst I am sure I can use the code you have suggested in other areas, the way I have finally written the error handler I actually quit the app in a subroutine called after each database call eg a main script would be something like

On Error Resume Next ' Turn On Error Handling
--- some code here ---
Set Con = Server.CreateObject( "ADODB.Connection" )
con.open(....)
checkerror()

Set myrecordset = Server.CreateObject( "ADODB.Recordset" )
myrecordset.ActiveConnection = Con
sqlString = "SELECT * from mytable"
myrecordset.Open sqlString
checkerror()

--- more code and database interactions here

myrecordset.close
set myrecordset = nothing
con.close
set con=nothing

I have lots of scripts like this but the subroutine for error handling is in one file of common subroutines included in each script

In my subroutine

if err.number <> 0
--- display error ----
--- write log ----
--- report by email ----
response.end()
endif

What I wanted to do of course was to within the subroutine to check all open recordsets and connections and close them before response.end. Because the routine is called from different scripts there will be different record sets open on different calls. With your approach, you are focusing on handling the close in the main script rather than the subroutine if I understand correctly and passing the name of the known record sets used in that script to a subroutine that checks they are still available. I suppose what I was looking for was more of a generic bit of code that could detect open record sets, perhaps by traversing through a connection object and do this in a subroutine. In other languages I use I can define variable and objects as public so that they are visble within called subroutines also which would work with your code but what I have failed to recognise is that each object needs passing as a parameter to the subroutine in ASP rather than simply being visible if it was declared in the calling script. I would therefore need to amend my code to

On Error Resume Next ' Turn On Error Handling
--- some code here ---
Set Con = Server.CreateObject( "ADODB.Connection" )
con.open(....)
...

Set myrecordset1 = Server.CreateObject( "ADODB.Recordset" )
myrecordset1.ActiveConnection = Con
sqlString = "SELECT * from mytable"
myrecordset1.Open sqlString
if err.number <> 0
if IsDisposable(myRecordset1,"Recordset") Then Set MyRecordset1 = Nothing
if IsDisposable(myRecordset2,"Recordset") Then Set MyRecordset2 = Nothing
...
checkerror()
endif

This would be a lot of code after every database access repeated throughout all scripts. It might be efficient on resources but the coding would be very untidy.

Following my starting this, we have identified that the reason for the drain on resource on our server which lead to an ISP suspension of the site was actually caused by a malformed 3rd party robot which hit our site for a 2 hour period and blitzed the database with 2000 error calls during that time. Hopefully this is a one off and we are taking measures to prevent this robot visiting again.

Now that I know it was not programming on my part that caused the problem I want to take a step back again and ask 'how important is it really if a script that generates an occasional error does not close all connections and datasets in an ODBC database? Initially I thought it crucial and that I had caused this problem but now people are telling me its not as important as I think and that when scripts close that usually releases any open resources if not immediately then very shortly afterwards when IIS realises that they are no longer in use. Is this myth or a fact and will my reasonable attempts here to notify myself and the customer of an error as soon as it happens, rapid support to repair any such errors, and most forms exiting normally 99% of the time without errors closing all connections and recordsorts just before completion be enough?

Any further thoughts would be really appreciated! Thanks

Paul

 

[Tarwn]

IIS is supposed to release resources shortly after the script exits: True
Microsoft wrote IIS + VBScript: True

I'll leave the level of help you think IIS and VBScript to your own judgement