Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Using OraOLEDB.Oracle Provider in VB - connections persisting in Database

    I'm using the OraOLEDB.Oracle provider to connect to an Oracle database via ADODB in VB 6.0. After I've successfully written a record to the DB, I close my recordset object (rst), set recordset object = nothing, close my connection object (con) and then set connection object = nothing. After doing this, I can verify that rst.state and con.state both = 0 (closed). However, both the recordset object and the
    con object are NOT Nothing (they still persist). However, my real problem is that the connection I opened to the database and that VB tells me is closed still persists in the Oracle Database.

    Here is the code. If anyone has any adivce, I'd appreciate any help I can get. TIA!

    Dim con As New adodb.Connection
    Dim rst As New adodb.Recordset

    Dim LogFileName As String
    Dim LogFileNum As Integer

    Dim sql As String ' SELECT statement
    Dim vardata As Variant ' data retrieved from clob data in chunks

    Dim recipeStream As adodb.Stream
    Dim paramStream As adodb.Stream
    Dim traceStream As adodb.Stream

    Dim oFSO As Scripting.FileSystemObject

    Dim strEquipmentName As String

    On Error GoTo ErrorHandler

    'create the ADODB.Stream objects
    Set recipeStream = New adodb.Stream
    Set paramStream = New adodb.Stream
    Set traceStream = New adodb.Stream
    Set con = New adodb.Connection
    Set oFSO = New FileSystemObject

    'read in the recipe data
    recipeStream.Type = adTypeBinary
    recipeStream.Open
    recipeStream.LoadFromFile strFileNameRoot & "_rcp.zip"

    'read in the configuration data
    paramStream.Type = adTypeBinary
    paramStream.Open
    paramStream.LoadFromFile strFileNameRoot & "_prm.zip"

    'read in the trace data
    traceStream.Type = adTypeBinary
    traceStream.Open
    traceStream.LoadFromFile strFileNameRoot & ".bin"

    'timing into
    strAfterStreamReadDateTime = CStr(Now())

    'set equipment name
    strEquipmentName = strMeasurementBenchType & CStr(intMeasurementBenchNumber)

    ' connect as adldemo/adldemo
    con.CursorLocation = adUseClient
    con.Open strConnect 'strConnect =
    ' "Provider=OraOLEDB.Oracle;Password=PWD;User
    ' ID=ID;DataSource=DSN"

    'timing into
    strAfterOpenDBConnDateTime = CStr(Now())

    ' select a recordset - should be an empty record unless
    'this record has already been added for some unknown reason

    sql = "select FWLOTID, EQUIPMENTNAME, MEASUREMENTDATE, PRODUCT, VERSION, RAWRECIPE, RAWBENCHSETUP, RAWDATA" & _
    " from BENCHTRACE where FWLOTID = '" & strLotId & "' and EQUIPMENTNAME = '" & strEquipmentName & _
    "' and MEASUREMENTDATE = " & "To_Date('" & strDateTime & "','MM/DD/yyYY HH24:MIS')"


    rst.Open sql, con, adOpenStatic, adLockOptimistic, adCmdText

    ' add a new record
    rst.AddNew
    rst!FwLotID = strLotId
    rst!EQUIPMENTNAME = strEquipmentName
    rst!MEASUREMENTDATE = strDateTime
    rst!PRODUCT = strMESProduct
    rst!Version = strSWLVersion
    rst.Fields("RAWRECIPE").Value = recipeStream.Read
    rst.Fields("RAWBENCHSETUP").Value = paramStream.Read
    rst.Fields("RAWDATA").Value = traceStream.Read

    'write the record to the database
    rst.Update

    'timing into
    strAfterDBRecordWriteDateTime = CStr(Now())

    rst.Close

    Call LogMessage(0, "RecordSet Close Status = " & CStr(rst.State), "object", "WriteTraceRecord()", "c:\measbench\tracearchive\obj.log", 1000000)

    Set rst = Nothing
    If Not (rst Is Nothing) Then
    Call LogMessage(0, "RecordSet Object Status = exists", "object", "WriteTraceRecord()", "c:\measbench\tracearchive\obj.log", 1000000)
    End If

    con.Close
    Call LogMessage(0, "Connection Close Status = " & CStr(con.State), "object", "WriteTraceRecord()", "c:\measbench\tracearchive\obj.log", 1000000)
    Set con = Nothing

    If Not (con Is Nothing) Then
    Call LogMessage(0, "Connection Object Status = exists", "object", "WriteTraceRecord()", "c:\measbench\tracearchive\obj.log", 1000000)
    End If

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Smells like connection pooling is enabled to me.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •