Detect when someone uses my TADOConnection after it's been freed?

Question

A colleague came me to today with an error with code that worked on Windows XP but fails on Windows 7:

User failed for login 'SalesOrdersystem'

My phychic debugging told me he was running a query against a database connection that was closed, or that he forgot to open.

Starting with ADO2.6, in Windows Vista, the default value of PersistSecurityInfo in a connection string is False, rather than True.

Prior to Windows Vista a connection string such as:

Data Source=deathstar;User ID=SalesOrderSystem;Password=password1

would keep the password in the connection string after the connection is opened, which makes it the equivalent of:

Data Source=deathstar;User ID=SalesOrderSystem;
      Password=password1;PersistSecurityInfo=true

Starting with Windows Vista the password is, by default, removed from a connection's ConnectionString property:

Data Source=deathstar;User ID=SalesOrderSystem

which is the equivalent of

Data Source=deathstar;User ID=SalesOrderSystem;
      Password=password1;PersistSecurityInfo=false

i knew my colleague was experiencing this behavior where the password is being removed. And then while the connection is closed he's trying to open a query (i.e. ADOQuery.Open) which imiplicitely tries to open the Connection. But without a password saved in the connection string he gets his original error

The question became, "Why are you using a connection without opening it first?"

We traced it back to (multi-threaded code) where he was using a connection that was later being freed:

pseudo-code:

customer := TCustomer.Create(ADOConnection)
ADOConnection.Free;
customer.RefreshFromDatabase;

rather than

customer := TCustomer.Create(DataModule.ADOConnection);
customer.RefreshFromDatabase;

In jest, i suggested he could mask the error, and leave the potential crash, by changing the connection string to include PersistSecurityInfo=True:

connectionString := ...+
    ';PersistSecurityInfo=True';

Which he did.

---

We have some library code that internally makes use of an ADOConnection object. i would love to be able to change my code from:

destructor TAsyncFill.Destroy; 
begin
   ...
   FreeAndNil(FADOConnection)
end;

to

destructor TAsyncFill.Destroy; 
begin
   ...
   FADOConnection.Close;
   FADOConnection.ConnectionString := 'This connection object has been freed. Why are you using it?';
   FreeAndNil(FADOConnection);
end;

But i am sure it will introduce errors, where things used to happen to work.

What i am thinking of is some sort of closure, where i can inject an OnConnect handler to the connection object:

destructor Destroy; 
begin
   ...
   FADOConnection.Close;
   FADOConnection.BeforeConnect := { 
       OutputDebugString('You''re using a connection that''s been freed!'); 
       Windows.Beep(1000, 60000) };
   FreeAndNil(FADOConnection);
end;

But Delphi doesn't have anonymous event handlers.

Can anyone think of a way to be able to alert people when they're using an object after it's been freed?

---

Note: i understand there is no support for what i'm asking. i'm asking for ideas for the best possible hacks - given the limits of reality.

Answer 1

As you are "FreeAndNil"ing your AdoConnection, I am assuming that you are instantiating it yourself as well. In that case what you could do is derive your own TMyAdoConnection and instantiate that. You do not even have to give it another class name if you go for the "interceptor" approach:

type
  TAdoConnection = class(AdoDb.TAdoConnection)
  end;

Then, override the protected DoConnect method. In spite of its name it isn't "just" a method to trigger the OnConnect event. It actually does open a connection. There is a similar DoDisconnect method as well which actually closes the connection.

In these two overridden methods, plus overrides of Create and Destroy, you can write a simple detection mechanism for when the Opens and Closes do not match the Creates and Destroys.

If you have a single AdoConnection instance you could just keep track of things in a couple of global variables. Otherwise you may have to write a small registry where you keep track of stuff for each instance. Gonna be hard though trying to find "Self" in that registry if the instance has been freed and set to nil. So you may have to forgo the FreeAndNil for the time being and recode any if Assigned(FAdoConnection) to detect that the instance still needs to be created, with something else.

Warning: This is based on TAdoConnection in Delphi 6. I don't have the Ado components installed in Delphi 5 at the moment. So you will have to check that the DoConnect and DoDiscoonect are present and virtual in D5 as well.

Answer 2

FastMM with full debug will notify you when your code accesses an object after it has been freed.

Obviously you can't ship with that setting on, but switch it on when you run your test suite and such bugs will be flushed into open sight.

Answer 3

Why the coder is using a TADOConnection object that may have already been freed? Why not have the thread create it's own connection? That would allow the thread to control the lifecycle of the connection.