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

    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.LoadFromFile strFileNameRoot & ""

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

    'read in the trace data
    traceStream.Type = adTypeBinary
    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

    " 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!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

    'timing into
    strAfterDBRecordWriteDateTime = CStr(Now())


    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

    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
    In front of the computer
    Provided Answers: 54
    Smells like connection pooling is enabled to me.


Posting Permissions

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