--- In firebird-support@yahoogroups.com, Björn Latte <bl@...> wrote:

>
> I'm somewhat stuck on detecting a tables existence and subsequently

dropping it before recreating it or simply creating it if the script
is run for the first time it. The whole operation is intended to be
done by a script run to isql.

>
> The last thing I came up with is:
>
> SET TERM ^ ;
>
> CREATE PROCEDURE TEMP_DROP_TABLE
> AS
> declare relid Integer;
> BEGIN
> SELECT RDB$RELATION_ID FROM RDB$RELATIONS WHERE Name = 'TEST_TAB'

into :relid;

> IF (relid is not NULL) THEN
> BEGIN
> DROP TABLE TEST_TAB;
> END;
> END
> ^
> SET TERM ; ^
>
> EXECUTE PROCEDURE TEMP_DROP_TABLE;
>
> COMMIT WORK;
>
> DROP PROCEDURE TEMP_DROP_TABLE;
>
> COMMIT WORK;
>
> CREATE TABLE TEST_TAB
> (
> TEST_ID INTEGER NOT NULL,
> )
> ;
>
> COMMIT WORK;
>
>
> The DROP statement inside the procedure will throw an SQL error on

running the script as it is obviously not allowed inside the begin/end
block.

The reason that it is not allowed is not because it is inside a
begin/end block, but rather because performing DDL and DML in a single
transaction is not considered safe.

You can of course bypass the safeguards using execute statement, but
that would be ill advised and may lead to problems down the track.
Rather than trying to bypass the check, I would suggest that you try
and understand why such checks are in place.

A bit of creative thinking can solve this problem.

Unconditional drop:

RECREATE TABLE TEST_TAB
(
TEST_ID INTEGER NOT NULL,
);

DROP TABLE TEST_TAB;

For stored procedures and triggers, you have CREATE OR ALTER
(PROCEDURE | TRIGGER).

Adam