create GENERATOR error error while creating identity field using firebird and c#

Question

I am using C# & Firebird SQL db in one of my win form project. In on of the form i am creating a table in firebird db having one field "id" as identity/autoincrement field. My code is

private void createOrLoadGIIR(int pid, int tid,int mid)
    {
        string qryStrCL1 = @"EXECUTE BLOCK AS BEGIN
                                if (not exists(select 1 from rdb$relations where rdb$relation_name = 'loc_soningqp')) then
                                execute statement 'CREATE TABLE loc_soningqp (
                                  q_r_id integer NOT NULL,
                                  q_r_seccd varchar(5) NOT NULL,
                                  q_r_subseccd varchar(5) NOT NULL,
                                  q_r_direction blob,
                                  q_r_desc blob NOT NULL,
                                  q_r_caopt1 smallint DEFAULT 0,
                                  q_r_caopt2 smallint DEFAULT 0,
                                  q_r_caopt3 smallint DEFAULT 0,
                                  q_r_caopt4 smallint DEFAULT 0,
                                  q_r_caopt5 smallint DEFAULT 0,
                                  q_r_sol blob,
                                  q_r_difficulty varchar(10) DEFAULT NULL,
                                  id integer NOT NULL,
                                  PRIMARY KEY (id)
                                );';
                                CREATE GENERATOR gen_loc_soningqp_id;

                                CREATE TRIGGER loc_soningqp_bi FOR loc_soningqp
                                ACTIVE BEFORE INSERT POSITION 0
                                AS
                                BEGIN
                                  IF (NEW.id IS NULL) THEN
                                  NEW.id = GEN_ID(gen_loc_soningqp_id,1);
                                END^

                                END";
        try
        {
            using (FbConnection conCL1 = new FbConnection(connectionString))
            {
                conCL1.Open();
                using (FbCommand cmdCL1 = new FbCommand(qryStrCL1, conCL1))
                {
                    cmdCL1.CommandType = CommandType.Text;
                    using (FbDataReader rdrCL1 = cmdCL1.ExecuteReader())
                    {
                        if (rdrCL1 != null)
                        {
                            //some code
                        }
                    }
                }// command disposed here
            } //connection closed and disposed here
        }
        catch (FbException ex)
        {
            //table exists
            MessageBox.Show(ex.Message);
        }
    }

When i run the code then it generates an error near CREATE GENERATOR line saying that 'CREATE is an unknown token'.Please advise what is wrong with my code.I also want to know if its possible to create stored procedures inside EXECUTE BLOCK.

why don't you create the `Sql` properly on the db or in .sql file and launch it from C# here is a link that can show you how to execute the script in 2 lines of code.. next time do a simple google search as well http://stackoverflow.com/questions/9259034/the-type-of-the-sql-statement-could-not-be-determinated — MethodMan, Jan 07 '16 at 20:36
What is the exact verbatim error message please. (including any relevant error numbers etc) — ebyrob, Jan 07 '16 at 21:02

Evgeny · Accepted Answer · 2016-01-07T21:07:11.167

There is an errors in your query. I think it should be like this:

"EXECUTE BLOCK AS BEGIN
  if (not exists(select 1 from rdb$relations where rdb$relation_name = 'loc_soningqp')) then
  begin
    execute statement 'CREATE TABLE loc_soningqp (
      q_r_id integer NOT NULL,
      q_r_seccd varchar(5) NOT NULL,
      q_r_subseccd varchar(5) NOT NULL,
      q_r_direction blob,
      q_r_desc blob NOT NULL,
      q_r_caopt1 smallint DEFAULT 0,
      q_r_caopt2 smallint DEFAULT 0,
      q_r_caopt3 smallint DEFAULT 0,
      q_r_caopt4 smallint DEFAULT 0,
      q_r_caopt5 smallint DEFAULT 0,
      q_r_sol blob,
      q_r_difficulty varchar(10) DEFAULT NULL,
      id integer NOT NULL,
      PRIMARY KEY (id)
     );';

     execute statement 'CREATE GENERATOR gen_loc_soningqp_id;';

     execute statement '        
     CREATE TRIGGER loc_soningqp_bi FOR loc_soningqp
     ACTIVE BEFORE INSERT POSITION 0
     AS
     BEGIN
       IF (NEW.id IS NULL) THEN
       NEW.id = GEN_ID(gen_loc_soningqp_id,1);
     END^
    ';
  end
END";

Hm, try remove last `execute statement` with trigger creation and check if it works — Evgeny, Jan 07 '16 at 21:00
Thanks @Evgeny. I removed the lines set term ^ ; and now its working great :) — gomesh munda, Jan 07 '16 at 21:05

score 3 · Answer 2 · edited May 23 '17 at 10:28

3

The problem is that you are not allowed to execute DDL inside PSQL code (like an EXECUTE BLOCK). As Evgeny already shows in his answer, you also need to wrap the CREATE GENERATOR and CREATE TRIGGER statements in an EXECUTE STATEMENT. Using EXECUTE STATEMENT as a workaround is clever, but sometimes a bit cumbersome.

edited May 23 '17 at 10:28

Community

1
1

answered Jan 07 '16 at 21:48

Mark Rotteveel

100,966
191
140
197

create GENERATOR error error while creating identity field using firebird and c#

2 Answers2