Firebird 3 multiple insert-CodePudding

Can I do multiple insert with Firebird 3:

INSERT INTO ECHELONS (N_EMPLOYE, ECHELON, INDICE, "DATE", TYPE_PROMO,NOTE, REMARQUE) VALUES( @P10, @P11, @P12, @P13, @P14, @P15, @P16); 
INSERT INTO ECHELONS (N_EMPLOYE, ECHELON, INDICE, "DATE", TYPE_PROMO,NOTE, REMARQUE) VALUES( @P790, @P791, @P792, @P793, @P794, @P795, @P796);

or that:

INSERT INTO ECHELONS (N_EMPLOYE, ECHELON, INDICE, "DATE", TYPE_PROMO,NOTE, REMARQUE) VALUES( @P10, @P11, @P12, @P13, @P14, @P15, @P16),( @P790, @P791, @P792, @P793, @P794, @P795, @P796);

I am using C# and I get this error:

Error:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 117
INSERT

CodePudding user response：

No, you cannot do this directly.

Your first attempt is not possible, because you can only execute one statement at a time, not two. Unless you use the FbScript component, but then you cannot parameterise.

Your second attempt is not possible, because Firebird unfortunately does not support the table value constructor with more than one row.

Your options are:

Prepare a single statement and use the batch execution facility introduced in the ADO.NET provider 9.0.0.0 for Firebird. I have no experience with this API itself, so I can't demonstrate how to use it. It is also - as far as I understand - a low-level API right now, so it might need more polish for usability.

Use EXECUTE BLOCK with multiple statements, e.g. something like

execute block (p10 integer = ?, p11 varchar(50) = ?, p20 integer = ?, p21 varchar(50) = ?)
as
begin
  insert into sometable(col1, col2) values (:p10, :p11);
  insert into sometable(col1, col2) values (:p20, :p21);
end

This second option comes with a number of limitations, for example the size of all parameters together cannot exceed 64 kilobytes, so if you have very wide rows to insert, this will limit the number of rows you can insert in a single block.

CodePudding user response：

What if I did it like this (performance wise):

 FbTransaction fbTransaction = fb.BeginTransaction() ;
   for (int i = 0; i < length; i  )
    {
          FbCommand fbCommand = new FbCommand();           
          fbCommand.Transaction = fbTransaction;
          fbCommand.Connection = fb;
          fbCommand.CommandText = insertSql;
          fbCommand.ExecuteNonQuery();
   }
   fbTransaction.Commit();

I did not want to do it like this especially in case of multiple accesses.