drop table if exists

[Lester Caine]

Or rather the recommended work around ;)

----

execute block as

begin
    if (exists(select rdb$relation_name
        from rdb$relations
        where rdb$relation_name = 'wt_site_access_rule'))
    then
        execute statement 'drop table wt_site_access_rule';

end

----

I'm running on Firebird 5 with the laravel driver from xGrz although my fork does support the migrations schema management and is getting very close to being able to install webtrees using a firebird 5 database ;) BUT the above code fails with "Unexpected end of command - line 7, column 27" ... the drop table statement initial ' ... and I've ported it from my other php database abstractions.

[Mark Rotteveel]

That error indicates you're using something that splits statements on
`;`, and so you're executing an incomplete statement: it's missing the
`end`.

Or in other words, what you're executing is only:

```

execute block as
begin
if (exists(select rdb$relation_name
from rdb$relations
where rdb$relation_name = 'wt_site_access_rule'))
then
execute statement 'drop table wt_site_access_rule';

```

If you're using ISQL to execute this, then you must use `set term #;`
before executing the statement, and terminate the statement with `#`
(after `end`).

If you're using something else, then you need to disable splitting
statements on `;`, or configure a different statement terminator, or use
something that consider the whole string a single statement to execute
(i.e. no script parsing or other forms of splitting).

Mark
--
Mark Rotteveel

[Lester Caine]

On 04/02/2026 20:13, 'Mark Rotteveel' via firebird-support wrote:
> If you're using something else, then you need to disable splitting
> statements on `;`, or configure a different statement terminator, or use
> something that consider the whole string a single statement to execute
> (i.e. no script parsing or other forms of splitting).

I'm testing things in Flamerobin, and `set term #;` was my first though,
it still gives the same problem.

----
set term #;

execute block as
begin
if (exists(select rdb$relation_name
from rdb$relations
where rdb$relation_name = 'wt_site_access_rule'))
then

execute statement 'drop table wt_site_access_rule'#
end
set term ;#
----
Unexpected end of command - line 8, column 27

But where I'm actually using it is in the PDO driver wrapped by laravel
and I've just tried adding the set term wrapper there with the same
failure :(

Job for the morning, see if I can split the code, check if the table
exists first and then run a drop table separately. That is how ADOdb
works, but lavarel seems to be building one or more queries, and then
running them. It's all far too bloated really, and it's running through
years of 'updates' just to build the current set of tables :(

--
Lester Caine
------------

[Mark Rotteveel]

On 04/02/2026 22:06, Lester Caine wrote:
> I'm testing things in Flamerobin, and `set term #;` was my first though,
> it still gives the same problem.
>
> ----
> set term #;
> execute block as
> begin
>     if (exists(select rdb$relation_name
>         from rdb$relations
>         where rdb$relation_name = 'wt_site_access_rule'))
>     then
>         execute statement 'drop table wt_site_access_rule'#
> end
> set term ;#
> ----
> Unexpected end of command - line 8, column 27

Yes, that is because *inside* the PSQL of the execute block, you need to
continue using `;`, only *after* the block should you use the
alternative terminator `#`:

```

set term #;
execute block as
begin
if (exists(select rdb$relation_name
from rdb$relations
where rdb$relation_name = 'wt_site_access_rule'))
then

execute statement 'drop table wt_site_access_rule';
end
set term ;#
```

> But where I'm actually using it is in the PDO driver wrapped by laravel
> and I've just tried adding the set term wrapper there with the same
> failure :(

SET TERM is *not* a Firebird statement, it's an ISQL command to control
at what character it decides a statement is complete to send to the
server. Some other Firebird tools follow its example, and support it as
well, but it is not universal, and not server-side.

The driver (or its wrapper) is parsing it as a script (splitting on `;`)
and then executing the elements it split, while this should be executed
as a single statement.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 05/02/2026 09:46, 'Mark Rotteveel' via firebird-support wrote:
> On 04/02/2026 22:06, Lester Caine wrote:
>> I'm testing things in Flamerobin, and `set term #;` was my first
>> though, it still gives the same problem.
>>
>> ----
>> set term #;
>> execute block as
>> begin
>>      if (exists(select rdb$relation_name
>>          from rdb$relations
>>          where rdb$relation_name = 'wt_site_access_rule'))
>>      then
>>          execute statement 'drop table wt_site_access_rule'#
>> end
>> set term ;#
>> ----
>> Unexpected end of command - line 8, column 27
>
>
> Yes, that is because *inside* the PSQL of the execute block, you need to
> continue using `;`, only *after* the block should you use the
> alternative terminator `#`:

Ugh, I actually missed the `#` after block:

```

set term #;
execute block as
begin
if (exists(select rdb$relation_name
from rdb$relations
where rdb$relation_name = 'wt_site_access_rule'))
then

execute statement 'drop table wt_site_access_rule';
end#
set term ;#
```

Mark
--
Mark Rotteveel

[Lester Caine]

Having passed 70 last year the old grey cells do not cooperate as they used to ;) 

Working fine in Flamerobin now even without the # after end ... 
Still having fun with lavarel but replacing the webtree schema dropIfExists with hasTable and drop bypasses the problem for now.

I've also sorted the enum default problem and actually have completed the schema build ... now to sort the missing sequence/generators!
Couple of more questions to follow ;)