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?

Tjader

Possible duplicate of Embedded firebird not accepting create table statement – Orgulous

Note: I retracted my close vote, because I see that is another aspect of the same question. – Orgulous

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();
    }
}

Orgulous

Thanks for the very comprehensive and well elucidated response. However, can you give references to where I can learn more about firebird-c# (for example that SET TERM is not part of the Firebird syntax but rather used by tools like ISQL, FlameRobin, and FbScript). Note that even firebird's FAQ website for SET TERM does not even explain this mush – Tjader

Also, if Firebird can only execute individual SQL statements, how does the concept of rolling back transactions apply to firebird (embedded) in c#? – Tjader

@emmanuel firebirdfaq.org/faq78 says "SET TERM is used by isql, FlameRobin and some other tools to change the statement terminator (TERM is short for Terminator)." If you search for SET TERM in the Interbase 6.0 language reference, you will find at almost each occurrence a note that is only for use in isql. – Orgulous

@emmanuel Executing statements is done per statement. If you want to commit or rollback you start a transaction and execute one or more statements in the transaction block and either commit or rollback at the end of the block. However the FbBatchExecution handles transactions internally, so you have less control with that. – Orgulous

@emmanuel I added an example with executing the statements individually (note the absence of SET TERM). – Orgulous

When I ran the command, an exception was thrown at transaction.Commit(); with the message: unsuccessful metadata update cannot create index RDB$PRIMARY 3 – Tjader

@emmanuel It works for me. Consider posting a new question with the exact code you use and the full exception information. – Orgulous

This must be the reason this test case is set to [Ignore("Not supported")] : github.com/masroore/Firebird-NETProvider/blob/4aceaf3/source/… – Lazulite

@MaxBarraclough That test is probably ignored because a Firebird statement can only produce a single result set at most, and statements need to be executed individually. Also note that test does not exist in the HEAD of the current sources (the code you're linking is some old fork 7 years out of date) – Orgulous

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);
        }
    }
}

Therine