Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How do I go about dropping Tables from MS SQL Server with Delphi Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Dropping Tables from MS SQL Server with Delphi 01-Dec-02
Category
BDE
Language
Delphi 2.x
Views
136
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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.

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC