?
Solved

How do I close a CreateCOMObject(StringToGUID('ADOX.Catalog'))  connection

Posted on 2006-05-10
6
Medium Priority
?
2,171 Views
Last Modified: 2007-12-19
I have an application that reads fixed field text data, processes it, writes the processed data to an Access database, and writes summary tables to both Excel and Word.  Everything finally works well thanks to many great things I learned from reading this topic area EXCEPT I can't close/release the Access database connection.  Here is a very abbreviated look at my code:
 Catalog := CreateCOMObject(StringToGUID('ADOX.Catalog')) as _Catalog;
 DS := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+mdbName;
 Catalog.Create(DS);
 Table := CreateCOMObject(StringToGUID('ADOX.Table')) as _Table;
 Table.Name := 'some meaningful text string';
 Catalog.Tables.Append(Table);
 Table.Columns.Append('PriKey', adInteger, 0);
   //  and many more
 Catalog := nil; //Close connection
 Table := nil;  //Close connection

I later add records with
 ConnString:='Provider=Microsoft.Jet.OLEDB.4.0;'
      //     + 'Password="";User ID="";'
              + 'Data Source=' + mdbName + ';'
              + 'Persist Security Info=False';
 FLPPQuery := TADOQuery.Create(nil);
 FLPPQuery.ConnectionString := ConnString;

  FLPPQuery.SQL.Text :=
    'INSERT INTO ' + tblName + ' VALUES ('
       +':PriKey,'
     // and many more
       +')';
  FLPPQuery.Parameters.ParamByName('PriKey').Value := Rcount;
     // and many more
  FLPPQuery.ExecSQL;
  FLPPQuery.Close;

After looping through all records, I've added
     FLPPQuery := nil;
just to make sure the the query is also released.

I can create many databases and tables, but in testing I frequently run the same dataset which will create an Access database with the same name.  When I check for the existance of the filename, and try to delete it, I can't delete it. Actually, I can delete it if I've closed the application, but not if I run the same database while the app stays open.

When the app is put into production, the probability that the same dataset will be run twice while the app remains open is close to nil, but I'd rather not chance that.

On a side note, the app processes about 2500 records per second, which is acceptable. Each text line is about 165 characters, and I do quite a bit of processing: converting text strings to datetime values, testing time spans between dates, populating arrays with summary data, etc. I haven't taken the time to separate the processing from the posting time so I dont' know where the bottlenecks are.  Is the query process that I've shown above reasonably efficient, or is there a better way to do it.

Thanks for your help.
0
Comment
Question by:lgcdmb
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:Russell Libby
ID: 16654429

The TADOQuery (FLPPQuery) is not  a COM object (it holds COM refs though) but a Delphi object, which requires a call to .Free to release it. Once the destructor runs, it will release its internal interfaces pointers.

eg:

 FLPPQuery.ExecSQL;
 
 // process records ...

 FLPPQuery.Close;
 FLPPQuery.Free;

--------

Regards,
Russell

 
0
 

Author Comment

by:lgcdmb
ID: 16654719
Using the free method does solve the original problem, but creates another that is worse.
An abstraction of my program structure is something like this:

Procedure ProcessData
   Readln(Input, Line); // Get first line of text file with header info
   CreateMDB;
   While not Eof(Input) do
     begin
      Readln(Input, Line); // Subsequent lines have data
      ProcessData;
      InsertRecord;
     end;
  FLPPQuery.Free;
end;

Procedure ProcessData;
// Parse the text data and do all of the required processing

Procedure CreateMDB;
 From the original question post... Alot is left out
   Catalog.Create(DS);
   Catalog.Tables.Append(Table);
   Table.Columns.Append('PriKey', adInteger, 0);
   Catalog := nil; //Close connection
   Table := nil;  //Close connection
   ConnString:='Provider=Microsoft.Jet.OLEDB.4.0;'
      //     + 'Password="";User ID="";'
              + 'Data Source=' + mdbName + ';'
              + 'Persist Security Info=False';
   FLPPQuery := TADOQuery.Create(nil);
   FLPPQuery.ConnectionString := ConnString;
end;

Procedure InsertRecord;
  //Again, from initial question post
  FLPPQuery.SQL.Text :=
    'INSERT INTO ' + tblName + ' VALUES ('
       +':PriKey,'
     // and many more
       +')';
  FLPPQuery.Parameters.ParamByName('PriKey').Value := Rcount;
     // and many more
  FLPPQuery.ExecSQL;
  FLPPQuery.Close;
end;

I've declared   FLPPQuery: TADOQuery; as a global variable so it should be accessible in all procedures. However, with the FLPPQuery.Free method in the ProcessData procedure, I get the following compiler error:
[Error] Main.pas(1684): E2010 Incompatible types: 'TADOQuery' and 'procedure, untyped pointer or untyped parameter'

The only way that I can eliminate that error is to move the query connection string and Free method to the InsertRecord Procedure. This results in a HUGE performance hit due to establishing a new connection for each record.

Any suggestions?


0
 
LVL 26

Accepted Solution

by:
Russell Libby earned 2000 total points
ID: 16654947
You have a few choices.

1 - Use a TADOConnection to pass to the query, thus allowing you to free the query without a huge performance impact. (You would free the connection at the end of your routine)

2 - Attempt to clear the connection at the end of your routine  (which may not be enough to correct your problem)

   Readln(Input, Line); // Get first line of text file with header info
   CreateMDB;
   While not Eof(Input) do
     begin
      Readln(Input, Line); // Subsequent lines have data
      ProcessData;
      InsertRecord;
     end;
  FLPPQuery.Connection:=nil;

3 - Use the ADO interfaces directly and avoid the Delphi wrappers around them. This would involve creating an instance of a connection object, and then using the .Execute(...) function from it to exec your insert statement, then releasing the interface when the routine is finished.

It all boils down to the same thing, ensuring that the DB/ADO resources are CLOSED when the routine is finished. As it stands now, you have connections left open until the application terminates.

Regards,
Russell
0
 

Author Comment

by:lgcdmb
ID: 16654998
Option 2 compiled, but didn't drop the connection.  I'll try the other two and let you know what happened.

Thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question