ADO and a problem which may be memory leak or something

[Chris Cullen]

Hi

I have an application which creates a tray Icon and sits running on the machine all the time.  Periodically (about every 5 seconds) it queries a table on a MS$ SQL 2000 server and scans the table.

Now, after a while (6 hours +) the ADO connection stops working and I get a number of different errors comming back from my connection function.  These errors change and are not always consistent.  For example, I often get a cannot create SSPI message and sometimes I get a OLE-DB could not load type error (I cannot remember the exact text).

Anyway, as this procedure works for a long time before these problems occur, I figured that they are probably to do with how I am creating, using and then closing the ADO objects.

So, here is a psuedo code of what I am doing in the timer procedure:

Code Select Expand


local variable tACB   'UDT with dword's for pointers from FF functions

tACB.lpConnection= FF_AdoCreateObject("ADODB.Connection")
FF_AdoConnection_Open(tACB.lpConnection,tACB.zConnectString)  
-error checking etc.  This is the point of failure!
tACB.lpRecordset = FF_AdoCreateObject("ADODB.Recordset")
-error checking etc
FF_AdoRecordset_Open(tACB.lpRecordset, _
                                  "SQL command", _
                                 tACB.lpConnection, _
                                 %adOpenKeyset, _
                                 %adLockOptimistic, _
                                 %adCmdText)
-error checking etc
tACB.lpFields = FF_AdoRecordset_GetFields(tACB.lpRecordset)
do while not eof
     look at each record in record set
     etc....
loop

' Now we close everything down
IF  tACB.lpFields then FF_AdoRelease tACB.lpFields
If tACB.lpRecordset Then
     FF_AdoRecordset_Close tACB.lpRecordset
     FF_AdoRelease tACB.lpRecordset
end if
If tACB.lpConnection Then
   FF_AdoConnection_Close tACB.lpConnection
   FF_AdoRelease tACB.lpConnection
End If


Now, I've kept the above as clean as possible, and I have looked at where I'm accessing the fields etc.  I am pretty sure that the way I'm releasing the ADO objects (recordset and connection) is where my problem is!

I notice also that I get a LOT of 'Unknown Exception' errors when running this (or ANY FF ADO code) under XP within the powerbasic Debugger.  I do not get these when running under Win98.  I do not have the exception code to hand (its always the same number).

Any ideas would be very much apreciated!
:lol:

[Jose Roca]

Do not create a new connection and recordset each time.

Create the connection and the recordset at the start of your program.

Code Select Expand


Global variable tACB   'UDT with dword's for pointers from FF functions

tACB.lpConnection= FF_AdoCreateObject("ADODB.Connection")
FF_AdoConnection_Open(tACB.lpConnection,tACB.zConnectString)  
-error checking etc.  This is the point of failure!
tACB.lpRecordset = FF_AdoCreateObject("ADODB.Recordset")
-error checking etc


Perform the opening, reading and closing of the recordset

Code Select Expand


FF_AdoRecordset_Open(tACB.lpRecordset, _
                                  "SQL command", _
                                 tACB.lpConnection, _
                                 %adOpenKeyset, _
                                 %adLockOptimistic, _
                                 %adCmdText)
-error checking etc
tACB.lpFields = FF_AdoRecordset_GetFields(tACB.lpRecordset)
do while not eof
     look at each record in record set
     etc....
loop

IF  tACB.lpFields then FF_AdoRelease tACB.lpFields
If tACB.lpRecordset Then
     FF_AdoRecordset_Close tACB.lpRecordset
end if

And when you program is about to finish release the recordset and the connection objects.

Code Select Expand


If tACB.lpRecordset Then
     FF_AdoRelease tACB.lpRecordset
end if
If tACB.lpConnection Then
   FF_AdoConnection_Close tACB.lpConnection
   FF_AdoRelease tACB.lpConnection
End If


[Chris Cullen]

Many thanks for your comments Jose

Is this because the release side of things is not correctly clearing all the memory etc that it uses.

The reason I have taken this approach is that I have many forms which are called into life and then exit.   These forms are to be completely self contained, as I will eventually be using these as a DLL, so I have no control over what is open and what is not at the time the form is created.  If the release is not tidying up after itself, then these forms could theoretically suffer the same problems after a while.

Also,  many of these forms are called by seperate threads, so they really need to keep all their ADO objects local to each instance.  This I achieved using memory allocation and the SetProp GetProp etc.  If the FF release is not clearing the ADO stuff when these forms are destroyed, then I will hit the problem again.  Trying to use some form of Global variables for this kind of development would be a complete nightmare!

Is there no way to 'fully' close down and release the memory/rescources used by these functions?

[Jose Roca]

These functions don't allocate or deallocate any memory by themselves. They are only wrappers to allow you to call ADO methods as if they were standard functions, instead of using OBJECT CALL/GET/LET/SET and variants. But, for performance reasons, ADO doesn't release the memory allocated by recordsets when you release them, but put them in a list. According to information provided by Microsoft, that infortunately I have when unable to find again in a google search (it was an INFO page in the Knowledge Base), it only releases them when the number of cached recordsets is bigger than 258? or when the application finishes.

If you intend to use these forms as DLLs, it is a better approach if you create the connection and the recordset(s) objects in your main code and pass the returned pointers to the DLL as parameters. It is not the same to create an instance of a recordset object than to open, parse and close it. Create a recordset object in your main code and pass this reference to your function, that will open a recordset and close it when finished, but not create a connection and a recordset object each time. This is a waste of time and resources.

BTW the FF_ADO wrapper functions aren't thread safe, since they use a global variable to store the result code. Using PB Automation (OBJECT GET/CALL, etc.) is not thread safe either, since OBJRESULT can be overwritten with the result of another call before you can get his value. If you want to use multiple threads you will need to use critical sections. I once posted thread safe ADO wrappers, but they were a pain to use since you can't have optional parameters and to get a result you need to pass an aditional parameter by reference where the result is returned, since the result of the function is used to return the error code. Besides, it doesn't solve the recordset caching problem.