Cannot run multiple insert record query in firebird via C#

Question

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

score 2 · Answer 1 · answered Apr 11 '13 at 06:34

2

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 ;

answered Apr 11 '13 at 06:34

jachguate

16,976
3
57
98

Thanks for answer. I used ur query now getting exception "{"Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 1, char 62\r\nUNION"}" – D J Apr 11 '13 at 07:04
@DJ Well, it is your query, not mine ;). Try sending three separate inserts. Remember the database engine accepts only one SQL command per request, but you can wrap the three in a Execute Block, if you want to avoid three database round-trips. – jachguate Apr 11 '13 at 14:57
actually I need to insert 1000 records at once. thats why i need somthing same – D J Apr 12 '13 at 02:01
Don't forget in a program, you can just write a loop that repeats 1000 times to insert 1000 records one by one ;) – jachguate Apr 12 '13 at 03:18
which firebird version are you using? – jachguate Apr 12 '13 at 05:33
@jachgute - Actually I tried that but its takes 3 secs to insert 1000 records in db.In future i might need to insert 1 million records also. About firebird version I don't know how to find it out. – D J Apr 12 '13 at 05:49
http://stackoverflow.com/questions/1248079/ways-to-determine-the-version-of-firebird-sql – jachguate Apr 12 '13 at 05:55
BTW, there's no server I know which supports sending such a large SQL statement you have in mind to insert a million rows on a single call! – jachguate Apr 12 '13 at 06:08

Mark Rotteveel · Answer 2 · 2013-04-12T17:04:26.233

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

Thanks for answer. I tried but still getting exception "{"Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 1, char 59\r\nUNION"}". — D J, Apr 11 '13 at 07:08
Possibly the parser doesn't like `UNION` with an `INSERT` you may need to wrap the query in another one (eg `SELECT column1, column2 FROM ()` — Mark Rotteveel, Apr 11 '13 at 07:30
I am really thankful for your responses. But actually your all options are working correctly if I execute it in FlameRobin but when I am executing the statements in C#. Its throwing the error again. Looks like the problem is not with the query. — D J, Apr 11 '13 at 09:51
statement `INSERT INTO COUNTRY1 SELECT '2', 'two ' FROM RDB$DATABASE ` also works fine but the moment I try multiple inserts it starts throwing exception. — D J, Apr 11 '13 at 10:35
@DJ I don't have Firebird available here, I will see if I can check this in more detail tonight. — Mark Rotteveel, Apr 11 '13 at 10:44
@DJ I just tested the query with Firebird 2.5.2 and the Firebird .NET provider 3.0.2 (as on NuGet) and the query simply works — Mark Rotteveel, Apr 12 '13 at 17:01
I am getting the exception "unable to complete the request to host localhost". Just fyi, I dont have fdb server. I am working on a file. — D J, Apr 16 '13 at 03:31

Cannot run multiple insert record query in firebird via C#

2 Answers2