[DUG] Drop table in Interbase
Patrick Sheehan
paa at xtra.co.nz
Wed Jun 17 11:06:40 NZST 2009
This is from the IB6 manual. Readily available from the IB or Firebird sites
Dropping a table
Use DROP TABLE to remove a table's data, metadata, and indexes from a
database. It also
drops any triggers that are based on the table. A table can be dropped by
its creator, the
SYSDBA user, or any user with operating system root privileges.
You cannot drop a table that is referenced in a computed column, a view,
integrity
constraint, or stored procedure. You cannot drop a table that is being used
by an active
transaction until the table is no longer in use.
DROP TABLE fails and returns an error if:
_ The person who attempts to drop the table is not the owner of the table.
_ The table is in use when the drop is attempted. The drop is postponed
until the table is
no longer in use.
_ If the table has a UNIQUE or PRIMARY KEY defined for it, and the PRIMARY
KEY is referenced
by a FOREIGN KEY in another table. First drop the FOREIGN KEY constraints in
the other
table, then drop the table.
_ If the table is used in a view, trigger, stored procedure, or computed
column. Remove the
other elements before dropping the table.
_ The table is referenced in another table's CHECK constraint.
Note DROP TABLE does not delete external tables; it removes the table
definition from the
database. You must explicitly delete the external file.
DROP TABLE syntax
DROP TABLE name;
The following statement drops the table, COUNTRY:
DROP TABLE COUNTRY;
Patrick Anthony Associates Ltd
PO Box 31-831, Milford
168 Kitchener Rd, Milford
Auckland, New Zealand
Ph: 9 486-5020 Mob: 021 732-107 Fx: 9 486-5020
patrick at compnz.co.nz www.compnz.co.nz <http://www.compnz.co.nz/>
IMPORTANT - The information contained in this email is CONFIDENTIAL. It is
intended for the person to whom it is addressed only. If you are not that
person or his/her authorised agent please be aware that any use,
dissemination, distribution or reproduction of this email is prohibited. If
you have received this in error please notify us immediately by return email
or telephone 64 9 486 5020 and delete your copy of the email and any
attachments. Thank you.
From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Willie Juson
Sent: Wednesday, 17 June 2009 10:35
To: 'NZ Borland Developers Group - Delphi List'
Subject: Re: [DUG] Drop table in Interbase
Here's some quick and dirty code that works (it probably wants some
exception handling added around the ExecSQL), but hopefully you get the gist
of it, in this example the database DM.IBDBKiwitracker is a TIBDatabase
component on our central Datamodule unit for our application. It assumes the
Database has a default transaction assigned - which the TIBQuery gets in the
absence of a specific one being assigned to it.
l_qry := TIBQuery.Create(nil);
try
l_qry.Database := DM.IBDBKiwitracker;
l_qry.Transaction.StartTransaction;
try
l_qry.SQL.Add('select count(*) from rdb$relations where
Upper(RDB$RELATION_NAME) = (''MYTABLE'') ');
l_qry.Open;
if (l_qry.Fields[0].Asinteger > 0) then
begin
l_qry.Close;
l_qry.SQL.Clear;
l_qry.SQL.Add('drop table MyTable');
l_qry.ExecSQL;
end;
finally
if l_qry.Transaction.InTransaction then
l_qry.Transaction.Commit;
end;
finally
FreeAndNil(l_qry);
end;
You might also want to check out a bit how the RDB$ tables work in
interbase...
From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Jeremy Coulter
Sent: Wednesday, 17 June 2009 9:59 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Drop table in Interbase
Thansk for your reply edward. However, I dont normall use Interbase and your
example didnt make a lot of sence. I managed to find a similar example but I
dont follow part of it.
when I run :
select * from rdb$relations WHERE RDB$RELATION_NAME = 'MyTable'
and
DROP TABLE MyTable;
COMMIT;
It tells me DROP is an invalid token. So I assume the "And" is not part of
the SQL statement.
So what I dont follow is, just doing a "SELECT" to see if a record exists
then dropping the table seems like something is missing.
in a programming sense, I would expect something like
IF select * from rdb$relations WHERE RDB$RELATION_NAME = 'MyTable' <> ''
then
DROP TABLE MyTable;
I know thats not real code, but my point is, just because I can do a select,
doesnt tell me if the table exists or not to try to delete it.......I hope
you follow what I am meaning.
Jeremy
On Tue, Jun 16, 2009 at 4:05 PM, John Bird <johnkbird at paradise.net.nz>
wrote:
For a good working example see
http://xkcd.com/327/
(Standard disclaimer - do not try this at home!)
John
----- Original Message -----
From: Jeremy Coulter <mailto:jscoulter at gmail.com>
To: NZ <mailto:delphi at delphi.org.nz> Borland Developers Group - Delphi List
Sent: Tuesday, June 16, 2009 3:35 PM
Subject: Re: [DUG] Drop table in Interbase
ok cool I will try that.
Thanks, Jeremy
On Tue, Jun 16, 2009 at 2:54 PM, Edward Koryagin <ed_iv2001 at yahoo.co.nz>
wrote:
select * from rdb$relations ...
and
DROP TABLE ....
Edward Koryagin
--- On Tue, 16/6/09, Jeremy Coulter <jscoulter at gmail.com> wrote:
> From: Jeremy Coulter <jscoulter at gmail.com>
> Subject: [DUG] Drop table in Interbase
> To: "NZ Borland Developers Group - Delphi List" <delphi at delphi.org.nz>
> Received: Tuesday, 16 June, 2009, 1:31 PM
> Hi all.
> In interbase, how do I drop a table if it already exists?
> I want to check for a table exists and if it does exist,
> drop it.
>
> I have Googled, but I cant seem to find anything. I am
> prob. searching wrong :-)
>
>
> Jeremy
>
>
> -----Inline Attachment Follows-----
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz
> with Subject: unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe
_____
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.374 / Virus Database: 270.12.71/2178 - Release Date: 06/16/09
17:55:00
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20090617/cec348e6/attachment-0001.html
More information about the Delphi
mailing list