lgcdmb
asked on
How do I close a CreateCOMObject(StringToGUID('ADOX.Catalog')) connection
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(StringToGU
DS := 'Provider=Microsoft.Jet.OL
Catalog.Create(DS);
Table := CreateCOMObject(StringToGU
Table.Name := 'some meaningful text string';
Catalog.Tables.Append(Tabl
Table.Columns.Append('PriK
// and many more
Catalog := nil; //Close connection
Table := nil; //Close connection
I later add records with
ConnString:='Provider=Micr
// + 'Password="";User ID="";'
+ 'Data Source=' + mdbName + ';'
+ 'Persist Security Info=False';
FLPPQuery := TADOQuery.Create(nil);
FLPPQuery.ConnectionString
FLPPQuery.SQL.Text :=
'INSERT INTO ' + tblName + ' VALUES ('
+':PriKey,'
// and many more
+')';
FLPPQuery.Parameters.Param
// 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.
Catalog := CreateCOMObject(StringToGU
DS := 'Provider=Microsoft.Jet.OL
Catalog.Create(DS);
Table := CreateCOMObject(StringToGU
Table.Name := 'some meaningful text string';
Catalog.Tables.Append(Tabl
Table.Columns.Append('PriK
// and many more
Catalog := nil; //Close connection
Table := nil; //Close connection
I later add records with
ConnString:='Provider=Micr
// + 'Password="";User ID="";'
+ 'Data Source=' + mdbName + ';'
+ 'Persist Security Info=False';
FLPPQuery := TADOQuery.Create(nil);
FLPPQuery.ConnectionString
FLPPQuery.SQL.Text :=
'INSERT INTO ' + tblName + ' VALUES ('
+':PriKey,'
// and many more
+')';
FLPPQuery.Parameters.Param
// 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.
ASKER
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(Tabl
Table.Columns.Append('PriK
Catalog := nil; //Close connection
Table := nil; //Close connection
ConnString:='Provider=Micr
// + 'Password="";User ID="";'
+ 'Data Source=' + mdbName + ';'
+ 'Persist Security Info=False';
FLPPQuery := TADOQuery.Create(nil);
FLPPQuery.ConnectionString
end;
Procedure InsertRecord;
//Again, from initial question post
FLPPQuery.SQL.Text :=
'INSERT INTO ' + tblName + ' VALUES ('
+':PriKey,'
// and many more
+')';
FLPPQuery.Parameters.Param
// 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?
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(Tabl
Table.Columns.Append('PriK
Catalog := nil; //Close connection
Table := nil; //Close connection
ConnString:='Provider=Micr
// + 'Password="";User ID="";'
+ 'Data Source=' + mdbName + ';'
+ 'Persist Security Info=False';
FLPPQuery := TADOQuery.Create(nil);
FLPPQuery.ConnectionString
end;
Procedure InsertRecord;
//Again, from initial question post
FLPPQuery.SQL.Text :=
'INSERT INTO ' + tblName + ' VALUES ('
+':PriKey,'
// and many more
+')';
FLPPQuery.Parameters.Param
// 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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Option 2 compiled, but didn't drop the connection. I'll try the other two and let you know what happened.
Thanks.
Thanks.
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