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.

Answer 1

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";