CODEWITHSUNDEEP
c#databasefirebird
D J
D J

Reputation: 7028

Cannot run multiple insert record query in firebird via C#

I am generating a multiple insert records query in C# and trying to execute in FireBird.

When I am running it on FlameRobin it works so well. But When I am running it via C# it throws exception.

SET TERM ^ ; INSERT INTO COUNTRY1 SELECT '2', 'two ' FROM RDB$DATABASE UNION ALL SELECT '4', 'four' FROM RDB$DATABASE UNION ALL SELECT '5', 'five' FROM RDB$DATABASE; ^ SET TERM ; ^

Exception in C# code is

{"Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 1, char 5\r\nTERM"}

C# Code is 

string sQuery = "SET TERM ^ ; INSERT INTO COUNTRY1 SELECT '2', 'two ' FROM RDB$DATABASE UNION ALL SELECT '4', 'four' FROM RDB$DATABASE UNION ALL SELECT '5', 'five' FROM RDB$DATABASE; ^ SET TERM ; ^ ";
SqlHelper.ExecuteNonQuery(SQLHelp.Connectionstring, CommandType.Text, sQuery);

Any idea whats wrong I am doing here? Thanks

Upvotes: 0

Views: 1761

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

Your query does not conform to the Firebird syntax. Drivers can only execute a single statement at a time (be aware that an EXECUTE BLOCK is considered to be a single statement). You should not use SET TERM, that is an artifact of the ISQL tool (and some other query tools like FlameRobin) and is not actually part of the Firebird SQL language.

Nor should you include ; in a query. Those are only valid in 1) PSQL (stored procedures, triggers and inside EXECUTE BLOCK) and 2) once again in tools like ISQL to separate (end) statements.

So using the following query on its own should be sufficient:

INSERT INTO COUNTRY1 SELECT '2', 'two ' FROM RDB$DATABASE UNION ALL SELECT '4', 'four' FROM RDB$DATABASE UNION ALL SELECT '5', 'five' FROM RDB$DATABASE

EDIT

As I commented, maybe the parser doesn't understand UNION when combined with an INSERT ... SELECT.

Using a sub-select will probably work:

INSERT INTO COUNTRY1 
  SELECT column1, column2 FROM (
     SELECT '2' AS column1, 'two ' AS column2 FROM RDB$DATABASE 
     UNION ALL SELECT '4', 'four' FROM RDB$DATABASE 
     UNION ALL SELECT '5', 'five' FROM RDB$DATABASE
  )

EDIT 2

I just tested it with the the code below and it works: the expected rows are inserted:

static void Main(string[] args)
{
    var constrBuilder = new FbConnectionStringBuilder();
    constrBuilder.DataSource = "localhost";
    constrBuilder.Database = @"D:\data\db\testdatabase.fdb";
    constrBuilder.UserID = "sysdba";
    constrBuilder.Password = "masterkey";

    string constr = constrBuilder.ToString();

    using (var con = new FbConnection(constr))
    {
        con.Open();
        using (var trans = con.BeginTransaction())
        {
            var cmd = new FbCommand();
            cmd.CommandText = "INSERT INTO COUNTRY1 SELECT '2', 'two ' FROM RDB$DATABASE UNION ALL SELECT '4', 'four' FROM RDB$DATABASE UNION ALL SELECT '5', 'five' FROM RDB$DATABASE";
            cmd.Connection = con;
            cmd.Transaction = trans;

            cmd.ExecuteNonQuery();
            trans.Commit();
        }
    }
}

Upvotes: 1

jachguate
jachguate

Reputation: 17203

The set term statements you're including are not part of Firebird SQL language, but is a kind of interpreter configuration instruction firstly introduced in isql, which is the command line client for Firebird.

Look at your script this way:

SET TERM ^ ; 

INSERT INTO COUNTRY1 
  SELECT '2', 'two ' FROM RDB$DATABASE 
  UNION ALL SELECT '4', 'four' FROM RDB$DATABASE 
  UNION ALL SELECT '5', 'five' FROM RDB$DATABASE; ^ 

SET TERM ; ^

The engine doesn't know and can't process the set term statements.

FlameRobin and other clients add support for the set term statement for compatibility: to allow running SQL scripts written for isql, or because they lacks a fully SQL parser and still needs to properly recognize where a statement ends when a script includes semi-colons. But all this clients, once a statement is delimited inside a script, doesn't send the set term to the engine, just ignores it.

You have to do the same: remove the set term statements and send the unique other statement you have, like this:

INSERT INTO COUNTRY1 
  SELECT '2', 'two ' FROM RDB$DATABASE 
  UNION ALL SELECT '4', 'four' FROM RDB$DATABASE 
  UNION ALL SELECT '5', 'five' FROM RDB$DATABASE

Be aware you must also remove the ;

Upvotes: 2

Related Questions