BDP, be carefull out there!

When you need database connection in a ASP.NET website you will use, of course, Borland DataProvider, because it gives you a database independent connection.
Use of the BDP components is very straight forwarded, and very much alike the 'standard' ADO.NET components.

However some things of BDP are a bit different, in the detail sence of the word, that it can make you pull your hair from your head sometimes. BDP also covers a lot that is not in ADO.NET so there is plenty to learn also.

Today such an event occured to me (again). A database-driven website that I'm building uses an Microsoft Access database to hold the content(Yes I know, not the best choice) . I use BDP to get the content from the database and on a local machine a Delphi Win32 app is used to maintain the content. So if the content changes the database must be uploaded to the website.
I noticed during developing that the Access .lck file (LockFile) stayed open, and that of course could be a connection which was not closed properly.
But I learned from the Delphi 8 times to close everything you use to get data from the database.
My way of doing this is somewhat like this:

MyConnection := BdpConnection.Create;
MyCommand := BdpCommand.Create(AQuery);
MyCommand.Connection := MyConnection;
try
  MyConnection.Open;
  MyDataReader := MyCommand.ExecuteDataReader;
  while MyDataReader.Read do begin
    Response.Write(MyDataReader.Item[0].ToString);
  end;
finally
  MyDataReader.Close;
  MyCommand.Close;
  MyConnection.Close;
end;

So believe me I don't leave database connections open lately. Anyway the lock file did not give trouble during the developement.
But now that the website has gone live my Win32 app is not able to upload a changed database because it is used by another process. And that of course is the ASP Worker process, which I can not kill at the ISP's webserver. (Oh Oh)

So after checking all my connections to close(as I told you before I do close them) I found out that it had to do with the Connection Pooling. Of course, that is default set to true and it keeps the connection 'open'. So I turned of the Connection Pooling and everything went fine even the .lck file disappeared immediately after the first refresh.
Now I only need to find a way to free the connection just-before uploading the database. I think that can easily be done following this tutorial at BDN.

So here is my small(and growing?) list of BDP tips:

1. When using Memo fields don't forget that BDP returns by default 1024 characters as set in the property BlobSize of the connection. So if you have more you won't see it until you increase the blobsize.
2. Don't get yourself fooled by the Connection Pooling! Connection pooling makes it look like you did not close your connection properly.
3. Close all that can be closed and you will be fine.

More info on BDP can be found in this BDN article Borland Data Provider 2.5 features.

Bottom line Borland DataProvider rocks, but be carefull out there...