title: Delphi: how to create Firebird database programmatically
body: <p>I'm using D2K9, Zeos 7Alpha, and Firebird 2.1</p>
  
  <p>I had this working before I added the autoinc field. Although I'm not sure I was doing it 100% correctly. I don' know what order to do the SQL code, with the triggers, Generators, etc.. I've tried several combinations, I'm guessing I'm doing something wrong other than just that for this not to work.  </p>
  
  <p>Current Demo:
    <a href="http://uploading.com/files/bd64d8m9/createdb.zip/" rel="nofollow noreferrer">http://uploading.com/files/bd64d8m9/createdb.zip/</a></p>
  
  <p>Current Error:</p>
  
  <pre><code>It's getting an error here:
  
  SQL Error:  Dynamic SQL Error SQL error code = -104 Token unknown - line 2, column 1 SET.
  Error Code: -104. Invalid token The SQL:
   CREATE GENERATOR GEN_EMAIL_ACCOUNTS_ID;
  </code></pre>
  
  <p>SET GENERATOR GEN_EMAIL_ACCOUNTS_ID TO 1;</p>
  
  <p>SQL File From IBExpert :</p>
  
  <pre><code>/******************************************************************************/
  /*                 Generated by IBExpert 5/4/2010 3:59:48 PM                  */
  /******************************************************************************/
  
  /******************************************************************************/
  /*        Following SET SQL DIALECT is just for the Database Comparer         */
  /******************************************************************************/
  SET SQL DIALECT 3;
  
  
  
  /******************************************************************************/
  /*                                   Tables                                   */
  /******************************************************************************/
  
  
  CREATE GENERATOR GEN_EMAIL_ACCOUNTS_ID;
  
  CREATE TABLE EMAIL_ACCOUNTS (
      ID           INTEGER NOT NULL,
      FNAME        VARCHAR(35),
      LNAME        VARCHAR(35),
      ADDRESS      VARCHAR(100),
      CITY         VARCHAR(35),
      STATE        VARCHAR(35),
      ZIPCODE      VARCHAR(20),
      BDAY         DATE,
      PHONE        VARCHAR(20),
      UNAME        VARCHAR(255),
      PASS         VARCHAR(20),
      EMAIL        VARCHAR(255),
      CREATEDDATE  DATE,
      "ACTIVE"     BOOLEAN DEFAULT 0 NOT NULL /* BOOLEAN = SMALLINT CHECK (value is null or value in (0, 1)) */,
      BANNED       BOOLEAN DEFAULT 0 NOT NULL /* BOOLEAN = SMALLINT CHECK (value is null or value in (0, 1)) */,
      "PUBLIC"     BOOLEAN DEFAULT 0 NOT NULL /* BOOLEAN = SMALLINT CHECK (value is null or value in (0, 1)) */,
      NOTES        BLOB SUB_TYPE 0 SEGMENT SIZE 1024
  );
  
  
  
  
  /******************************************************************************/
  /*                                Primary Keys                                */
  /******************************************************************************/
  
  ALTER TABLE EMAIL_ACCOUNTS ADD PRIMARY KEY (ID);
  
  
  /******************************************************************************/
  /*                                  Triggers                                  */
  /******************************************************************************/
  
  
  SET TERM ^ ;
  
  
  
  /******************************************************************************/
  /*                            Triggers for tables                             */
  /******************************************************************************/
  
  
  
  /* Trigger: EMAIL_ACCOUNTS_BI */
  CREATE OR ALTER TRIGGER EMAIL_ACCOUNTS_BI FOR EMAIL_ACCOUNTS
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
    IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(GEN_EMAIL_ACCOUNTS_ID,1);
  END
  ^
  
  
  SET TERM ; ^
  
  
  
  /******************************************************************************/
  /*                                 Privileges                                 */
  /******************************************************************************/
  </code></pre>
  
  <p>Triggers:</p>
  
  <pre><code>/******************************************************************************/
  /*        Following SET SQL DIALECT is just for the Database Comparer         */
  /******************************************************************************/
  SET SQL DIALECT 3;
  
  CREATE GENERATOR GEN_EMAIL_ACCOUNTS_ID;
  
  SET TERM ^ ;
  
  
  
  CREATE OR ALTER TRIGGER EMAIL_ACCOUNTS_BI FOR EMAIL_ACCOUNTS
  ACTIVE BEFORE INSERT POSITION 0
  AS
  BEGIN
    IF (NEW.ID IS NULL) THEN
      NEW.ID = GEN_ID(GEN_EMAIL_ACCOUNTS_ID,1);
  END
  ^
  
  
  SET TERM ; ^
  </code></pre>
  
  <p>Generators:</p>
  
  <pre><code>CREATE SEQUENCE GEN_EMAIL_ACCOUNTS_ID;
  ALTER SEQUENCE GEN_EMAIL_ACCOUNTS_ID RESTART WITH 2;
  
  /* Old syntax is:
  CREATE GENERATOR GEN_EMAIL_ACCOUNTS_ID;
  SET GENERATOR GEN_EMAIL_ACCOUNTS_ID TO 2;
  */
  </code></pre>
  
  <p>My Code:  Demo in comments below.</p>