Author: Lou Adler
How do I go about dropping Tables from MS SQL Server with Delphi
Answer:
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...
1 Parameter Descriptions
2
3 //var Ses : TSession; //A valid, open session
4 //DBName : String; //Name of the SQL Server DB
5 //ArTables : array of String; //An array of table names
6 //StatMsg : TStatusMsg); //A status message callback
7 //procedure
8
9 TStatusMsg is a procedural type used as a callback procedure
10
11 type
12 TStatusMsg = procedure(Msg: string);
13
14 procedure DropMSSQLTempTables(var Ses: TSession;
15 DBName: string;
16 ArTables: array of string;
17 StatMsg: TStatusMsg);
18 var
19 N: Integer;
20 qry: TQuery;
21 lst: TStringList;
22 begin
23 lst := TStringList.Create;
24
25 Ses.GetTableNames(DBName, '', False, False, lst);
26
27 try
28 for N := Low(arTables) to High(arTables) do
29 if (lst.IndexOf(ArTables[N]) > 0) then
30 begin
31 StatMsg('Removing ' + arTables[N] +
32 ' from client database');
33 qry := TQuery.Create(nil);
34 with qry do
35 begin
36 Active := False;
37 SessionName := Ses.SessionName;
38 DatabaseName := DBName;
39 SQL.Add('DROP TABLE ' + arTables[N]);
40 try
41 ExecSQL;
42 finally
43 Free;
44 qry := nil;
45 end;
46 end;
47 end;
48 finally
49 lst.Free;
50 end; { try/finally }
51 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.
Title: Dropping Tables from MS SQL Server with Delphi
Author: Lou Adler
Product: Delphi 2.x (or higher)
Post Date: 12/01/2002
Problem/Question/Abstract:
How do I go about dropping Tables from MS SQL Server with Delphi
Answer:
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
52
53 //var Ses : TSession; //A valid, open session
54 //DBName : String; //Name of the SQL Server DB
55 //ArTables : array of String; //An array of table names
56 //StatMsg : TStatusMsg); //A status message callback
57 //procedure
58
59 TStatusMsg is a procedural type used as a callback procedure
60
61 type
62 TStatusMsg = procedure(Msg: string);
63
64 procedure DropMSSQLTempTables(var Ses: TSession;
65 DBName: string;
66 ArTables: array of string;
67 StatMsg: TStatusMsg);
68 var
69 N: Integer;
70 qry: TQuery;
71 lst: TStringList;
72 begin
73 lst := TStringList.Create;
74
75 Ses.GetTableNames(DBName, '', False, False, lst);
76
77 try
78 for N := Low(arTables) to High(arTables) do
79 if (lst.IndexOf(ArTables[N]) > 0) then
80 begin
81 StatMsg('Removing ' + arTables[N] +
82 ' from client database');
83 qry := TQuery.Create(nil);
84 with qry do
85 begin
86 Active := False;
87 SessionName := Ses.SessionName;
88 DatabaseName := DBName;
89 SQL.Add('DROP TABLE ' + arTables[N]);
90 try
91 ExecSQL;
92 finally
93 Free;
94 qry := nil;
95 end;
96 end;
97 end;
98 finally
99 lst.Free;
100 end; { try/finally }
101 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';
102 arPat[1] := 'dbo.Temp1';
103 arPat[2] := 'dbo.Temp2';
104 arPat[3] := 'dbo.Temp3';
105 arPat[4] := 'dbo.Temp4';
106 arPat[5] := 'dbo.Temp5';
107 arPat[6] := 'dbo.PatList';
108 arPat[7] := 'dbo.PatientList';
109 arPat[8] := 'dbo.EpiList';
110 arPat[9] := 'dbo.' + FDisease + 'CrossTbl_' + FQtrYr;
111 arPat[10] := 'dbo.' + FDisease + 'Primary_' + FQtrYr;
and pass it into the procedure.
StatMsg TStatusMsg This is a procedural type of : procedure(Msg : String). You
can’t 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:
112 procedure StatMsg(Msg: string);
113 begin
114 thr.FStatMsg := Msg;
115 thr.Synchronize(thr.UpdateStatus);
116 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.
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.
|
