How to execute a block statement in C# for Firebird

Question

I am trying to delete all tables in a database in Firebird using the following command:

string dropAllForeignKeysSql =
  "EXECUTE BLOCK RETURNS (stmt VARCHAR(1000)) AS "+
  "BEGIN "+
  "FOR "+
  "select 'alter table \"'||r.rdb$relation_name ||'\" drop constraint '||r.rdb$constraint_name||';' "+
  "from rdb$relation_constraints r "+
  "where (r.rdb$constraint_type='FOREIGN KEY') "+
  "into :stmt "+
  " DO begin execute statement :stmt; end "+
  "END "+
  ";";

using (var connection = sessionFactory.OpenSession().Connection)
  {
    var command = connection.CreateCommand();
    command.CommandText = dropAllForeignKeysSql;
    command.ExecuteNonQuery();
  }

Unfortunately, the command is not executed. However, when I pause the debugger, copy the string in the CommandText variable and execute the query manually/ not in code, the statement is executed. If I replace the CommandText with a simpler command like DROP TABLE myTable, then this command IS executed in the code, i.e. the table is deleted. No error is thrown when running the code (in contrast to when I intentionally enter a wrong command or open the database in an external tool).

From there, I conclude that the error is not in the SQL statement and not in the command setup and not in having set up a wrong connection. Where else could the error be?

Not 100% sure, but I think you need to remove the semicolon(;) at the end of your dynamically constructed query. Semicolons are not part of Firebird's query language. — Mark Rotteveel
Unfortunately, this was not the reason. Executing query string that is generated works fine (with and without semicolon). — Dani
Have you tried adding newline characters to each concatenated line? Is there any way to trace actual sql query executed by ExecuteNonQuery on database server? — Alexander
The problem might also have to do with the fact that the column rdb$relation_name is a CHAR(31) and therefor padded with spaces. Try to use TRIM. — Mark Rotteveel

jadelgado jadelgado · Accepted Answer · 2013-06-19T08:32:22

You should use FbBatchExecution

FirebirdSql.Data.FirebirdClient.FbTransaction fbt = fbc.BeginTransaction(); // object fbc is your FirebirdSql.Data.FirebirdClient.FbConnection

FirebirdSql.Data.Isql.FbBatchExecution fbe = new FirebirdSql.Data.Isql.FbBatchExecution(fbc);
fbe.SqlStatements.Add(dropAllForeignKeysSql); // Your string here

fbe.Execute(true);

fbt.Commit();

And change 'term' before begin script:

SET term #;

How to execute a block statement in C# for Firebird

1 Answers