[DUG] Drop table in Interbase

[Jeremy Coulter]

actually another/simpler way than your idea above is to do:-

IBDatabase1.GetTableNames(sTableList);
then

if sTableList.indexof('MyTable'); <> -1 then  //drop the table



On Wed, Jun 17, 2009 at 10:44 AM, Jeremy Coulter <jscoulter at gmail.com>wrote:

> yeah I know how to do it in code thats easy. I was just trying to do it in
> SQL.
> I am too used to MSSQL I guess :-)
>
>
> On Wed, Jun 17, 2009 at 10:34 AM, Willie Juson <willie.Juson at satara.co.nz>wrote:
>
>>  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 <jscoulter at gmail.com>
>>
>> *To:* NZ Borland Developers Group - Delphi List <delphi at delphi.org.nz>
>>
>> *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
>>
>>
>>
>> _______________________________________________
>> 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
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20090617/2d9dd4ba/attachment.html