How to execute transactions (or multiple sql queries) in firebird using c#

Question

I have tried several ways, including on SO.

The following MYSQL code does not work in Firebird:

CREATE TABLE publications (
  INT NOT NULL AUTO_INCREMENT , 
  PRIMARY KEY (`id`),
  filename varchar(500) not null unique,
  title varchar(500) DEFAULT NULL,
  authors varchar(1000) DEFAULT NULL,
  uploader int DEFAULT NULL,
  keywords varchar(500) DEFAULT NULL,
  rawtext text,
  lastmodified timestamp default CURRENT_TIMESTAMP
);

So to achieve this in Firebird, I am using:

 CREATE TABLE publications (
   id int NOT NULL PRIMARY KEY,
   filename varchar(500) NOT NULL UNIQUE,
   title varchar(500) DEFAULT NULL,
   authors varchar(1000) DEFAULT NULL,
   uploader int DEFAULT NULL,
   keywords varchar(500) DEFAULT NULL,
   rawtext text,
   file_data BLOB SUB_TYPE 0,
   insertdate timestamp DEFAULT NULL
 );

 CREATE GENERATOR gen_t1_id;
 SET GENERATOR gen_t1_id TO 0;

set term !! ;

 CREATE TRIGGER journalInsertionTrigger FOR publications 
 ACTIVE BEFORE INSERT POSITION 0
 AS
 BEGIN
  if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
 END!!

set term ; !!

And with the above, I get the error:

Batch execution aborted The returned message was: Dynamic SQL Error SQL error code = -104 Token unknown - line 13, char 2 CREATE"

When I uncomment //FbTransaction fbt = Connection.BeginTransaction(); and //fbt.Commit();

Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.

I am using the following C# code:

//FbTransaction fbt = Connection.BeginTransaction(); // 
FbBatchExecution fbe = new FbBatchExecution( Connection );

fbe.SqlStatements = new System.Collections.Specialized.StringCollection();//.Add( queryString ); // Your string here                    
fbe.SqlStatements.Add( queryString ); // Your string here

fbe.Execute();
//fbt.Commit();

NB: Setting set term ; !! at the beginning of the sql code gives the error: The type of the SQL statement could not be determinated

How do I do this?

Answer 1

Firebird can only execute individual SQL statements, and most drivers for Firebird follow that same rule. You cannot execute a script at once like this.

The Firebird.net provider contains a utility class to split scripts into individual statements.

You need to do something like:

using (var connection = new FbConnection(@"User=sysdba;Password=masterkey;Database=D:\data\db\testdatabase.fdb;DataSource=localhost"))
{
    connection.Open();
    FbScript script = new FbScript(dbScript);
    script.Parse();
    FbBatchExecution fbe = new FbBatchExecution(connection);
    fbe.AppendSqlStatements(script);
    fbe.Execute();
}

Note that for your current script to work you also need to replace:

rawtext text,

with

rawtext BLOB SUB_TYPE TEXT CHARACTER SET UTF8

Technically you could leave off the character set clause, but unless you defined a default character set for your database, you should specify the character set otherwise it will be NONE which might lead to problems later.

You cannot start a transaction yourself when you use FbBatchExecution, because the transaction is handled internally in the Execute method. Note that if you also want to insert (or otherwise modify) data in the script, then you should use Execute(true), so that each DDL statement is committed immediately. Firebird doesn't allow DDL changes in a transaction to be used by DML in the same transaction.

The problem with SET TERM is caused by the fact that SET TERM is not part of the Firebird syntax. It is part of the syntax used by tools like ISQL and FlameRobin, and for example FbScript.

If you want to execute these statements individually and have control over the transaction, you'd do something like:

using (var connection = new FbConnection(@"User=sysdba;Password=masterkey;Database=D:\data\db\testdatabase.fdb;DataSource=localhost"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    using (var command = new FbCommand())
    {
        command.Connection = connection;
        command.Transaction = transaction;

        command.CommandText = @"CREATE TABLE publications (
           id int NOT NULL PRIMARY KEY,
           filename varchar(500) NOT NULL UNIQUE,
           title varchar(500) DEFAULT NULL,
           authors varchar(1000) DEFAULT NULL,
           uploader int DEFAULT NULL,
           keywords varchar(500) DEFAULT NULL,
           rawtext BLOB SUB_TYPE TEXT CHARACTER SET UTF8,
           file_data BLOB SUB_TYPE 0,
           insertdate timestamp DEFAULT NULL
         )";
        command.ExecuteNonQuery();

        command.CommandText = "CREATE GENERATOR gen_t1_id";
        command.ExecuteNonQuery();

        command.CommandText = @"CREATE TRIGGER journalInsertionTrigger FOR publications 
         ACTIVE BEFORE INSERT POSITION 0
         AS
         BEGIN
          if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
         END";
        command.ExecuteNonQuery();

        transaction.Commit();
    }
}

Answer 2

As Mark pointed, Firebird can execute only individual statements. If you group them into a block, then the block has its own transaction and you can't start the transaction yourself. I worked around this issue by creating an extension method ExecuteBatch() that you can use instead of ExecuteNonQuery(). Here's the code:

static class FbCommandExtension
{
    public static void ExecuteBatch(this FbCommand cmd)
    {
        var script = new FbScript(cmd.CommandText);
        script.Parse();
        foreach (var line in script.Results)
        {
            using (var inner = new FbCommand(line.Text, cmd.Connection, cmd.Transaction))
            {
                CopyParameters(cmd, inner);
                inner.ExecuteNonQuery();
            }
        }
    }

    private static void CopyParameters(FbCommand source, FbCommand inner)
    {
        foreach (FbParameter parameter in source.Parameters)
        {
            inner.Parameters.AddWithValue(parameter.ParameterName, parameter.Value);
        }
    }
}