File Handles not being closed

[TheVampire]

I've got an app that runs fine for a while, but eventually will start reporting "Insufficient system resources exist to complete the requested service." errors. Using process explorer shows me that certain file handles are not being closed when I use an OdbcCommand object to connect to a Paradox database.

Try
   Using CMD As New OdbcCommand
      CMD.CommandText = "SELECT MAX (UniqueID) FROM logbase"
      CMD.Connection = LDB
      MyHighestID = CMD.ExecuteScalar() + 1
      CMD.Connection = Nothing
   End Using
Catch
...
End Try

The file handle (which shows in Process explorer as the path to the database) gets created on the ExecuteScalar command, but does not disappear after the end of the using block as I would (apparently incorrectly) expect. Is the file handle controlled by the odbc connection instead? Calling GC.Collect does not help. This code (and the odbc connection object "LDB" it refers to) is in a class which implements IDisposable and the file handles persist even after the class is disposed of. The dispose method of the class has code in it to close the odbc connection, set it to nothing and dispose of it.

Later on in the code I use the same odbc connection to insert a new record in the database, with a odbc command object and a ExecuteNonQuery() command and the same thing happens. Another file handle is added and does not go away after the command object is disposed of.

Any suggestions are appreciated

 

[Sorwen]

I don't use OdbcCommands often and I've not used Paradox tables in years. That said generally you want to close a connection to a database before trying to set it to Nothing.

CMD.Connection.Close

In other instances if you just set the connection to nothing it drops the programs awarness of the connection. It doesn't force Windows to drop any Connections it has made because of your programs requests.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[TheVampire]

Thanks for the suggestion. I had tried using a CMD.Connection.Dispose() command in there and had no luck, and then I tried the CMD.Connection.Close() at your suggestion and this also did not help.

I cut out the code and created a separate project that has just the code to open the database and get the value with the ExecuteScalar command in a button click event, creating the OdbcConnection object and the OdbcCommand object in nested using statements. The same problem happens and the file handles are left open. Every time I click the button 2 more handles are added. (One to the directory that has the database, one to the C:\BDE directory that has the borland database engine). These all stay open until the program is exited.

(In the code sample below, the database path and passwords have been hidden.)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyMachine As String = System.Environment.MachineName
        Dim MyDatabasePath As String = "****************"
        Dim MyHighestID As Long

        Using LDB As New System.Data.Odbc.OdbcConnection("Driver={INTERSOLV Paradox ODBC Driver (*.db)};Server=" & MyMachine & ";Database=" & MyDatabasePath & ";Uid=*******;Pwd=*******")

            LDB.Open()

            Using CMD As New System.Data.Odbc.OdbcCommand
                CMD.CommandText = "SELECT MAX (UniqueID) FROM logbase"
                CMD.Connection = LDB
                MyHighestID = CMD.ExecuteScalar() + 1
                CMD.Connection.Close()
                CMD.Connection.Dispose()
                CMD.Dispose()
            End Using

            LDB.Close()
            LDB.Dispose()
            LDB.ReleaseObjectPool()
        End Using

        System.GC.Collect()

    End Sub

 

[Sorwen]

I had a similar problem with Excel. Try chaning your end coding like this.

            LDB.Close() 'If not then try LDB.Connection.Close() or try Close on the connection and then also on LDB itself.
            NAR(LDB)
            LDB = Nothing
        End Using

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
    End Sub

    Private Shared Sub NAR(ByRef o As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
        Catch
        Finally
            o = Nothing
        End Try
    End Sub

That worked in my situation. The ReleaseComObject for some reason didn't work 100% until I split it to a seperate sub. Hope that helps.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[TheVampire]

Thanks for the effort but unfortunately the problem persists. The Release com fails into catch block with error "The object's type must be __ComObject or derived from __ComObject.

 

[Sorwen]

Yeah my stupid. I didn't think that one through. I forgot we are dealing with a connection and not directly with the object created because of that connection.

Some of the changes are not exactly necessary, but helped me think through what might need to be changed.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyMachine As String = System.Environment.MachineName
        Dim MyDatabasePath As String = "****************"
        Dim MyHighestID As Long
        Dim strSQL As String
        Dim LDB As System.Data.Odbc.OdbcConnection
        Dim CMD As System.Data.Odbc.OdbcCommand

        LDB = New System.Data.Odbc.OdbcConnection("Driver={INTERSOLV Paradox ODBC Driver (*.db)};Server=" & MyMachine & ";Database=" & MyDatabasePath & ";Uid=*******;Pwd=*******")
        LDB.Open()

        strSQL = "SELECT MAX (UniqueID) FROM logbase"

        CMD = New System.Data.Odbc.OdbcCommand(strSQL, LDB)
        MyHighestID = CMD.ExecuteScalar() + 1
        'CMD.Dispose() 'Should dispose on it's own.  If not add back in.

        LDB.Close()
        LDB.Dispose()

        System.GC.Collect()
    End Sub

Try this code instead. Using like try..catch can sometimes hide errors that are actually happening. Also If I remember right part of a Using block is that it is supposed to dispose of resources on its own. So you shouldn't need to .Dispose an object within a Using block.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[TheVampire]

Well, thanks for trying but it still fails to release the handles. It actually creates several file handles when I click the button (it has always done this). Two Named Pipes that go away after about 10 seconds, and two handles to files, one of them being PDOXUSERS.NET which is used for locking a paradox database. Those two disappear after about 20 seconds.

 

[Sorwen]

Well I'm out of ideas. Someone else may come around that has a more current knowledge about working with Paradox. Just on the off chance you could ask over in the Paradox forum if there are any known issues programming with Paradox databases.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[TheVampire]

Frankly I'd be happy if I could find out a way to track down an open file handle based on a string value, and close it. If Process Explorer can do it then it's possible. I can see the total number of handles by using GetCurrentProcess in mmy program and looking at the HandleCount, but that's as far as I can get on that path in pure NET. I'll have to dig into the API.

Thanks for your help.

 

[TheVampire]

Just for a test I tried using a OleDbConnection instead and the Microsoft Jet driver. Then I got 13 (!) file handles to the database folder that didn't go away...

And from searching around after my last post, I read that the people that wrote Process Explorer have an undocumented way to get at and close the file handles and they aren't sharing how they do it.

 

[TheVampire]

Anybody else have any suggestions on why these file handles are not being disposed of? Thanks.

 

[TheVampire]

Never was able to find out why the file handles were not being discarded. I spent some effort in trying to use an external DLL and creating a new appdomain and a class that inherited MarshalByRefobject, but after I got all the code to work to do that, it still would attach the file handles to my current domain after unloading the new appdomain.

I then redid the DLL as an exe with a sub main to do the work of opening the database and getting the value, used process.start with a command line arguement to designate the database path, and redirected the new process standard output with a streamreader back to my original program to obtain the result. This finally gets me the data without having the problem of the open file handles. It's not the most elegant way of doing it but at least it works.

And in-between all of this I had a new son arrive. Yay!