Firebird with .net driver – drop table if exists

I’m new to Firebird and I’m testing a few things to check out the differences between Fb and SQlite (and the .net driver).

I am trying to do a drop table if exists followed by the creation of a table. In Sqlite I am able to do this by:

 command.CommandText = @"DROP TABLE IF EXISTS Persons; CREATE TABLE Persons (
    PersonID int,
    LastName text,
    FirstName text,
    Address text,
    City text); ";
    command.ExecuteNonQuery();

However in Firebird the same query fails. I’ve read that this is not possible to use IFs directly in Firebird SQL, so I’ve tried to use:

command.CommandText = @"
EXECUTE BLOCK AS 
BEGIN IF EXISTS 
(SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = 'Persons') 
THEN DROP TABLE Persons; END CREATE TABLE Persons (
        PersonID int,
        LastName varchar(255),
        FirstName varchar(255),
        Address varchar(255),
        City varchar(255) 
            ); ";
command.ExecuteNonQuery();

But it fails also with the following error:

Dynamic SQL Error SQL error code = -104 Token unknown – line 1, column 27

Can you please help me on this? I’ve tried to find more info on the web that could help me, but did not have any luck.

Answer

Firebird’s SQL syntax doesn’t have a drop table if exists, instead use recreate table. recreate table will try to drop the table if it exists before creating it. The syntax of recreate table is – other than recreate instead of create – the same as create table.

Your attempt to use execute block fails for two reasons:

1. You cannot execute two statements together as a command. If you want to execute a script of multiple statements, you’ll need to execute each statement individually or use the FbScript class which will parse the script and execute the individual statements for you.
2. Even if you execute these statements individually, it will still fail, because PSQL (the stored procedure language used in execute block) does not allow execution of DDL. You can use execute statement to circumvent this limitation, but it is better not to do that. In that way you could also address the previous point by executing both – using execute statement – within the execute block.

Alternatively you could just drop the table unconditionally and catch (and ignore) the resulting exception.