On Error Resume Next problem and ADO memory leak 
Author Message
 On Error Resume Next problem and ADO memory leak

Hi,

I have written an app that uses ADO to record logs to a database. As the app
tends to run for long periods, I have to use defensive error-catching
strategies - it doesn't really matter if I lose a couple of records, but it
does matter if the app exits mid-run...

Each of the operations I have performing data access follows a similar
template...

------------------------------------------
Sub SomeSub()

    On Error Goto Err_SomeSub

    ' Open connection

    ' Open recordset(s)

    ' Begin transaction

    ' Update database

    ' Commit transaction

    ' Close recordset(s)

    ' Close connection

    Exit Sub

Err_SomeSub:

    On Error Resume Next

    ' Rollback transaction

    ' Close recordsets

    ' Close connection
End Sub
------------------------------------------

I would have expected this to work under all circumstances, something may
fail, but the error-handler should catch it and attempt to close everything.
If something hasn't been opened (due to the error occurring before that
point), the "On Error Resume Next" should have simply allowed the code to
fall through to the next statement, and attempt the next object - ensuring
eventually that everything is closed correctly (the db objects are all
stored in module-level variables). In practise, I am observing that for long
runs (generally overnight) at some point the error-handling gives up and an
error occurs at one of the lines in that part of the method.

In fact, I would have preferred to simply leave the connection and
recordsets open, and simply use the same approach between Begin and Commit.
In practise I found ADO leaking too much memory on long runs, with the app's
memory use typically rising at a rate of approx. 4Kb per second !!! If the
logging was turned off, this immediately stopped, so the problem was
definitely ADO-related.

--------------------------
Sorry I can't provide more explicit source code, but our company's
non-disclosure policy prevents this.

I am using VB 6.0 with SP 5 installed, and the latest version of ADO (from
MDAC_TYP.EXE 2.7 downloaded from the MS site).

Thanks for any help.

Rob



Sun, 22 Aug 2004 18:44:51 GMT  
 On Error Resume Next problem and ADO memory leak
All,

Just to let you know I found a solution to the error-handling part. Simply
build it on a different machine. To be honest, this is not entirely
satisfactory - so far I'd built it on Win 2K, and Win 98. The successful
build was on a 3rd Win98 machine. I suspect the problems are due to wierd
interactions of installed software versions - One had Office XP on it, one
had Office 2K, the third (successful) no Office at all.

I suppose the m{*filter*}is that VB co-exists badly with other installed software
(almost all on all machines supplied by Microsoft). It just gets too hard to
determine which versions of which components are actually getting used.
We've decided to get a build machine dedicated to building just this one
product - a bit expensive but once the issues are ironed out at least we can
trust that no more software is going to interfere with the process.

Suspect this may be our last ever VB project (although I may evaluate VB.NET
yet) for release - strikes me that supporting stuff when end-users are
liable to install different components from O/S, browser and application
updates is just going to prove to expensive and time-consuming.

Rob



Sun, 22 Aug 2004 21:55:53 GMT  
 On Error Resume Next problem and ADO memory leak
I think the problem is that you cannot setup another error trap from within
an error trap - VB does not allow it. Instead try coding as:
 Sub SomeSub()
     On Error Goto Err_SomeSub

     ' Open connection
     ' Open recordset(s)
     ' Begin transaction
     ' Update database
     ' Commit transaction
     ' Close recordset(s)
     ' Close connection
     Exit Sub

TidyUp:
    On Error Resume Next
     ' Rollback transaction
     ' Close recordsets
     ' Close connection
    Exit Sub

 Err_SomeSub:
     Resume TidyUp
 End Sub


Quote:
> Hi,

> I have written an app that uses ADO to record logs to a database. As the
app
> tends to run for long periods, I have to use defensive error-catching
> strategies - it doesn't really matter if I lose a couple of records, but
it
> does matter if the app exits mid-run...

> Each of the operations I have performing data access follows a similar
> template...

> ------------------------------------------
> Sub SomeSub()

>     On Error Goto Err_SomeSub

>     ' Open connection

>     ' Open recordset(s)

>     ' Begin transaction

>     ' Update database

>     ' Commit transaction

>     ' Close recordset(s)

>     ' Close connection

>     Exit Sub

> Err_SomeSub:

>     On Error Resume Next

>     ' Rollback transaction

>     ' Close recordsets

>     ' Close connection
> End Sub
> ------------------------------------------

> I would have expected this to work under all circumstances, something may
> fail, but the error-handler should catch it and attempt to close
everything.
> If something hasn't been opened (due to the error occurring before that
> point), the "On Error Resume Next" should have simply allowed the code to
> fall through to the next statement, and attempt the next object - ensuring
> eventually that everything is closed correctly (the db objects are all
> stored in module-level variables). In practise, I am observing that for
long
> runs (generally overnight) at some point the error-handling gives up and
an
> error occurs at one of the lines in that part of the method.

> In fact, I would have preferred to simply leave the connection and
> recordsets open, and simply use the same approach between Begin and
Commit.
> In practise I found ADO leaking too much memory on long runs, with the
app's
> memory use typically rising at a rate of approx. 4Kb per second !!! If the
> logging was turned off, this immediately stopped, so the problem was
> definitely ADO-related.

> --------------------------
> Sorry I can't provide more explicit source code, but our company's
> non-disclosure policy prevents this.

> I am using VB 6.0 with SP 5 installed, and the latest version of ADO (from
> MDAC_TYP.EXE 2.7 downloaded from the MS site).

> Thanks for any help.

> Rob



Sun, 29 Aug 2004 08:10:18 GMT  
 On Error Resume Next problem and ADO memory leak
Phil,

Thanks, I had to take another tack on this one - close examination reveals
that your code exhibits the same problem, but at least you pointed me in the
right direction. The problem is now consigned to oblivion.

Rob


Quote:
> I think the problem is that you cannot setup another error trap from
within
> an error trap - VB does not allow it. Instead try coding as:
>  Sub SomeSub()
>      On Error Goto Err_SomeSub

>      ' Open connection
>      ' Open recordset(s)
>      ' Begin transaction
>      ' Update database
>      ' Commit transaction
>      ' Close recordset(s)
>      ' Close connection
>      Exit Sub

> TidyUp:
>     On Error Resume Next
>      ' Rollback transaction
>      ' Close recordsets
>      ' Close connection
>     Exit Sub

>  Err_SomeSub:
>      Resume TidyUp
>  End Sub



> > Hi,

> > I have written an app that uses ADO to record logs to a database. As the
> app
> > tends to run for long periods, I have to use defensive error-catching
> > strategies - it doesn't really matter if I lose a couple of records, but
> it
> > does matter if the app exits mid-run...

> > Each of the operations I have performing data access follows a similar
> > template...

> > ------------------------------------------
> > Sub SomeSub()

> >     On Error Goto Err_SomeSub

> >     ' Open connection

> >     ' Open recordset(s)

> >     ' Begin transaction

> >     ' Update database

> >     ' Commit transaction

> >     ' Close recordset(s)

> >     ' Close connection

> >     Exit Sub

> > Err_SomeSub:

> >     On Error Resume Next

> >     ' Rollback transaction

> >     ' Close recordsets

> >     ' Close connection
> > End Sub
> > ------------------------------------------

> > I would have expected this to work under all circumstances, something
may
> > fail, but the error-handler should catch it and attempt to close
> everything.
> > If something hasn't been opened (due to the error occurring before that
> > point), the "On Error Resume Next" should have simply allowed the code
to
> > fall through to the next statement, and attempt the next object -
ensuring
> > eventually that everything is closed correctly (the db objects are all
> > stored in module-level variables). In practise, I am observing that for
> long
> > runs (generally overnight) at some point the error-handling gives up and
> an
> > error occurs at one of the lines in that part of the method.

> > In fact, I would have preferred to simply leave the connection and
> > recordsets open, and simply use the same approach between Begin and
> Commit.
> > In practise I found ADO leaking too much memory on long runs, with the
> app's
> > memory use typically rising at a rate of approx. 4Kb per second !!! If
the
> > logging was turned off, this immediately stopped, so the problem was
> > definitely ADO-related.

> > --------------------------
> > Sorry I can't provide more explicit source code, but our company's
> > non-disclosure policy prevents this.

> > I am using VB 6.0 with SP 5 installed, and the latest version of ADO
(from
> > MDAC_TYP.EXE 2.7 downloaded from the MS site).

> > Thanks for any help.

> > Rob



Tue, 31 Aug 2004 00:35:36 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. On local error resume next Vs on error resuem next

2. Problem with On Error Resume Next in a converted database

3. On Error Resume Next (but Only with Specific Error, like Err.Number = 70)

4. cmd.Errors.Count Not Working (On Error Resume Next)

5. Runtime error(3012): On error resume next

6. Wish List Vote: Resume and Resume Next in Try blocks

7. ADO Memory Leak results in out of memory

8. ADO duplicate trap/resume next

9. On Error Resume Next being ignored

10. On Error Resume Next has no effect

11. Resume Next doesn't trap error

12. ON ERROR RESUME NEXT Failure?

 

 
Powered by phpBB® Forum Software