Delphi: how to create Firebird database programmatically

Question

I'm using D2K9, Zeos 7Alpha, and Firebird 2.1

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.

Current Demo: http://uploading.com/files/bd64d8m9/createdb.zip/

Current Error:

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;

SET GENERATOR GEN_EMAIL_ACCOUNTS_ID TO 1;

SQL File From IBExpert :

/******************************************************************************/
/*                 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                                 */
/******************************************************************************/

Triggers:

/******************************************************************************/
/*        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 ; ^

Generators:

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;
*/

My Code: Demo in comments below.

Answer 1

Generally you can create Firebird database objects in any order, provided that they don't depend on each other. If they do, then obviously you need to create the depended-on objects before you create the depending objects.

If you have objects with circular references then first create one of them with an empty body, create the other, then fill in the contents of the first by using the ALTER TABLE or corresponding statement. Tools like IBExpert, Database Workbench or FlameRobin analyse dependencies, so following the creation order in scripts written by them should always work.

If your IBExpert created script works, but your own code doing things in the same order doesn't work, then the cause of that may lie in IBExpert committing each DDL statement separately (while your code doesn't). You can do the same in your code, and you should. Your autoinc column involves a trigger, which itself depends on a generator, so make sure that you commit after the table and generator have been created, before you create the trigger.

Edit:

You should make sure that you only execute multiple statements with database components that are able to do that. I don't know Zeos, but from this knowledge base article it seems that both TZQuery and TZUpdateSQL support multiple statements in one execution call. Alternatively you should be able to use TZSQLProcessor to load the complete script as created by IBExpert and execute it.

The method TZConnection.ExecuteDirect() on the other hand may not support multiple statements, and in that case you will get syntax errors after the end of the first statement. This

CREATE GENERATOR GEN_EMAIL_ACCOUNTS_ID;
SET GENERATOR GEN_EMAIL_ACCOUNTS_ID TO 2;

are two statements, and you may need to pass each separately to TZConnection.ExecuteDirect().