Firebird BulkInsert with transaction

ServiceStack.OrmLite
1

Hi,

I’m trying to use OrmLite BulkInsert with FirebirdOrmLiteDialectProvider as part of a larger unit of work wrapped in an explicit transaction, but I’m getting the following exception:

A transaction is currently active. Parallel transactions are not supported.

Is BulkInsert designed to always create/manage its own transaction internally (thus conflicting with an already active one), or is this a bug?

2

Firebird and Oracle are only community supported libraries.

Typically you would have had to go to on StackOverflow to try ask for help for them, but as hardly anyone uses StackOverflow anymore your best chance to get help is downloading the repo and using AI assistance over the implementation.

3

As a workaround, I created a custom Firebird dialect provider and overrode BulkInsert:
• if a transaction is already active → execute the generated EXECUTE BLOCK via db.ExecuteSql(…), so it runs inside the existing transaction
• if no transaction is active → keep the original behavior using FbScript / FbBatchExecution

When using ExecuteSql, SET TERM directives must be stripped from the SQL, as they are isql-only.

This approach is slightly slower, but it works correctly within a unit-of-work.

Posting this in case it helps others, and in the hope that someone maintaining the Firebird provider might find it useful.

public class MyFirebirdOrmLiteDialectProvider : FirebirdOrmLiteDialectProvider
{
    public new static readonly MyFirebirdOrmLiteDialectProvider Instance = new();

    public override void BulkInsert<T>(
        IDbConnection db,
        IEnumerable<T> objs,
        BulkInsertConfig? config = null)
    {
        ArgumentNullException.ThrowIfNull(db);
        ArgumentNullException.ThrowIfNull(objs);

        config ??= new BulkInsertConfig();
        
        var requested = config.BatchSize > 0 ? config.BatchSize : 256;
        var batchSize = Math.Min(requested, 256);
        
        var hasTx =
            (db as IHasDbTransaction)?.DbTransaction != null
            || db.GetTransaction() != null; 

        foreach (var batch in objs.BatchesOf(batchSize))
        {
            var sql = ToInsertRowsSql(batch, insertFields: config.InsertFields);

            if (hasTx)
            {
                var executableSql = NormalizeExecuteBlockSql(sql);
                db.ExecuteSql(executableSql);
            }
            else
            {
                var firebirdDb = (FirebirdSql.Data.FirebirdClient.FbConnection)db.ToDbConnection();
                var fbScript = new FirebirdSql.Data.Isql.FbScript(sql);
                fbScript.Parse();
                var fbe = new FirebirdSql.Data.Isql.FbBatchExecution(firebirdDb);
                fbe.AppendSqlStatements(fbScript);
                fbe.Execute();
            }
        }
    }
    
    private static string NormalizeExecuteBlockSql(string sql)
    {
        if (string.IsNullOrWhiteSpace(sql))
            return sql;

        // 1) Remove any SET TERM directives (isql-only)
        // Supports: "set term ^ ;" or "SET TERM ^;"
        sql = Regex.Replace(
            sql,
            @"(?im)^\s*set\s+term\s+.+?;\s*$",
            string.Empty);

        // 2) Replace custom terminator '^' at end of EXECUTE BLOCK with ';'
        // Typical: "END^" or "END ^"
        sql = Regex.Replace(
            sql,
            @"(?im)\bEND\s*\^\s*$",
            "END;");

        // 3) Remove remaining '^' that might be used as statement terminator in isql scripts
        // but keep it conservative: remove only line-ending '^'
        sql = Regex.Replace(
            sql,
            @"(?m)\^\s*$",
            ";");

        // 4) Ensure the whole statement ends with ';' (safe for FbCommand)
        sql = sql.Trim();
        if (!sql.EndsWith(";", StringComparison.Ordinal))
            sql += ";";

        return sql;
    }
}
4

Feel free to send a PR if you want to include it in OrmLite’s version, that’s how the community supported providers are maintained.