How do I go about dropping Tables from MS SQL Server with Delphi
I've been doing extensive work with Client/Server Delphi and MS SQL Server as my
back-end database. The operational model that I use for my Client/Server is that the
client application acts only as local interface, and that all queries and calculations -
even temporary files - are performed or created on the server. Now this presents a couple
of problems in that garbage cleanup isn't quite as easy as it is when using local tables
as temporary files.
For instance, a lot of my programs create temporary files that I either reference later
in the program or that I use as temporary storage for outer joins. Once I'm done with
them, I need to delete them. With local tables, it's a snap. Just get a list of the
tables, and with a little bit of code that uses some Windows API calls, delete them. Not
so easy with SQL Server tables. The reason why is that you have to go through the BDE to
accomplish the task - something that's not necessarily very intuitive. Luckily, however,
it doesn't involve low-level BDE API calls.
Below is a procedure listing that drops tables from any SQL Server database. After the
listing I'll discuss particulars...
Parameter Descriptions
//var Ses : TSession; //A valid, open session
//DBName : String; //Name of the SQL Server DB
//ArTables : array of String; //An array of table names
//StatMsg : TStatusMsg); //A status message callback
//procedure
TStatusMsg is a procedural type used as a callback procedure
type
TStatusMsg = procedure(Msg : String);
}
procedure DropMSSQLTempTables(var Ses : TSession;
DBName : String;
ArTables : array of String;
StatMsg : TStatusMsg);
var
N : Integer;
qry : TQuery;
lst : TStringList;
begin
lst := TStringList.Create;
Ses.GetTableNames(DBName, '', False, False, lst);
try
for N := Low(arTables) to High(arTables) do
if (lst.IndexOf(ArTables[N]) > 0) then
begin
StatMsg('Removing ' + arTables[N] +
' from client database');
qry := TQuery.Create(nil);
with qry do begin
Active := False;
SessionName := Ses.SessionName;
DatabaseName := DBName;
SQL.Add('DROP TABLE ' + arTables[N]);
try
ExecSQL;
finally
Free;
qry := nil;
end;
end;
end;
finally
lst.Free;
end; { try/finally }
end;
The pseudo-code for this is pretty easy.
- Get a listing of all tables in the SQL Server database passed to the procedure.
- Get a table name from the table name array.
- If a passed table name happens to be in the list of table retrieved from the database,
DROP it.
- Repeat 2. and 3. until all table names have been exhausted.
The reason why I do the comparison in step 3 is because if you issue a DROP query
against a non-existent table, SQL Server will issue an exception. This methodology avoids
that issue entirely.
Below is a detailed description of the parameters.
Parameter Name |
Type |
Description |
Ses |
var TSession |
This is a session instance variable that you pass by
reference into the procedure. Note: It MUST be instantiated prior to use. The procedure
does not create an instance. It assumes it already exists. This is especially necessary
when using this procedure within a thread. But if you're not creating a multi-threaded
application, then you can use the default Session variable. |
DBName |
String |
Name of the MS SQL Server client database |
ArTables |
Array of String |
This is an open array of string that you can pass into the
procedure. This means that you can pass any size array and the procedure will handle it.
For instance, in the Primary table maker program, I define an array as follows:arPat[0] := 'dbo.Temp0';
arPat[1] := 'dbo.Temp1';
arPat[2] := 'dbo.Temp2';
arPat[3] := 'dbo.Temp3';
arPat[4] := 'dbo.Temp4';
arPat[5] := 'dbo.Temp5';
arPat[6] := 'dbo.PatList';
arPat[7] := 'dbo.PatientList';
arPat[8] := 'dbo.EpiList';
arPat[9] := 'dbo.' + FDisease
+ 'CrossTbl_' + FQtrYr;
arPat[10] := 'dbo.' + FDisease +
'Primary_' + FQtrYr;
and pass it into the procedure. |
StatMsg |
TStatusMsg |
This is a procedural type of : procedure(Msg : String).
You cant use a class method for this procedure; instead, you declare a regular
procedure that references a regular procedure. For example, I declare an interface-level
procedure called StatMsg that references a thread instance variable and a method as
follows: procedure StatMsg(Msg : String);
begin
thr.FStatMsg := Msg;
thr.Synchronize(thr.UpdateStatus);
end;
The trick here is that "thr" is the instance variable used to
instantiate my thread class. The instance variable resides in the main form of my
application. This means that it too must be declared as an interface variable. |
I'm usually averse to using global variables and procedures. It's against structured
programming conventions. However, what this procedure buys me is the ability to place it
in a centralized library and utilize it in all my programs.
Before you use this, please make sure you review the table above. You need to declare a
type of TStatusMsg prior to declaring the procedure. If you don't, you'll
get a compilation error.
Copyright © 1997 Brendan V. Delumpa All Rights Reserved
|