[DUG] Drop table in Interbase

Wed Jun 17 11:44:24 NZST 2009

Sorry I misunderstood your original post, and yeah the
TIBDatabase.GetTablenames is a much tidier way of doing it. 

MSSQL is definitely a lot more flexible with dynamic SQL.

From: delphi-bounces at delphi.org.nz [mailto:delphi-bounces at delphi.org.nz] On
Behalf Of Jeremy Coulter
Sent: Wednesday, 17 June 2009 10:51 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] Drop table in Interbase

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 <mailto:jscoulter at gmail.com>  Coulter 

To: NZ Borland Developers Group - Delphi List <mailto: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/27f689a6/attachment-0001.html 