1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Help. ADO VB6 Oracle9i Stored Procedure Call Leaks Memory

Discussion in 'Software Development' started by DRWilcox, Feb 7, 2003.

Thread Status:
Not open for further replies.
  1. DRWilcox

    DRWilcox Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    2
    I've got a VB6 Application that uses ADO 2.7 to create a recordset object from a stored procedure. I'm using the MSDAORA.1 OLEDB Driver. The procedure is run on a timer every few seconds. The program is growing in increments of 4k every 4 or 5 times the recordset is created. I've simplified the code to just running the recordset over and over using a timer to eliminate everything else and it still grows so I believe that I've isolated it to this piece of code. I've tried it against Oracle 9.0.1 and 8.0.5.
    Is ADO Leaking or am I doing something wrong?
    I would appreciate any help anyone could give me on this.


    Here is the code:

    Public Function GET_NEXT_BATCH() As Double
    Dim lBatchID As Double
    Dim parBUID As ADODB.Parameter
    Dim com As ADODB.Command
    Dim RS As ADODB.Recordset
    Dim Connection As ADODB.Connection

    On Error GoTo eh
    Set Connection = GetConnection
    Set RS = New ADODB.Recordset
    Set com = New ADODB.Command

    With com
    Set .ActiveConnection = Connection
    .CommandType = adCmdStoredProc
    .CommandText = "DWILCOX.PKG_ATTRIBUTE.GET_ATTRIB_BY_AID"
    Set parBUID = .CreateParameter("p_BUID", _
    adDouble, adParamInput)
    .Parameters.Append parBUID
    End With

    With RS
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockReadOnly
    End With

    parBUID.Value = 1

    Set RS = com.Execute(, , adAsyncFetch)

    If RS.EOF Then
    lBatchID = 0.1 'SET_NEXT_BATCH()
    Else
    RS.MoveFirst
    lBatchID = RS.Fields(0)
    End If

    GET_NEXT_BATCH = lBatchID

    ' clean house
    RS.Close
    Connection.Close
    Set RS = Nothing
    Set com = Nothing
    Set Connection = Nothing

    Exit Function
    eh:
    RS.Close
    Connection.Close

    Set RS = Nothing
    Set com = Nothing
    Set Connection = Nothing

    MsgBox Err.Number & " " & Err.Description

    End Function

    Regards
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is the stored procedure closing properly or is it opening a new instance of the SP before the prvious one has completly been cleaned up?
     
  3. DRWilcox

    DRWilcox Thread Starter

    Joined:
    Feb 7, 2003
    Messages:
    2
    Every time the timer fires it turns itself off until the SP call is complete, I don't believe there is anyway that it can get called again before it completes.
    I'm going to try to rewrite the SP to return a single value instead of a recordset and see if that works. I've heard that if a SP Call returns an error or just some message such as "No Data Found", ADO does not clean that up and it leaks.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/117594

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice