Delphi: how to create Firebird database programmatically
- by Brad
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.
SQL File From IB Expert :
/********************************************/
/* 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:
procedure TForm2.New1Click(Sender: TObject);
var
query:string;
begin
if JvOpenDialog1.Execute then
begin
ZConnection1.Disconnect;
ZConnection1.Database:= jvOpenDialog1.FileName;
if not FileExists(ZConnection1.database) then
begin
ZConnection1.Properties.Add('createnewdatabase=create database '''+ZConnection1.Database+''' user ''sysdba'' password ''masterkey'' page_size 4096 default character set iso8859_2;');
try
ZConnection1.Connect;
except
ShowMessage('Error Connection To Database File');
application.Terminate;
end;
end else
begin
ShowMessage('Database File Already Exists.');
exit;
end;
end;
query := 'CREATE DOMAIN BOOLEAN AS SMALLINT CHECK (value is null or value in (0, 1))';
Zconnection1.ExecuteDirect(query);
query:='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,'+
'BANNED BOOLEAN DEFAULT 0 NOT NULL,'+
'"PUBLIC" BOOLEAN DEFAULT 0 NOT NULL,' +
'NOTES BLOB SUB_TYPE 0 SEGMENT SIZE 1024)';
//ZConnection.ExecuteDirect('CREATE TABLE NOTES (noteTitle TEXT PRIMARY KEY,noteDate DATE,noteNote TEXT)');
Zconnection1.ExecuteDirect(query);
{
}
query := 'CREATE SEQUENCE GEN_EMAIL_ACCOUNTS_ID;'+
'ALTER SEQUENCE GEN_EMAIL_ACCOUNTS_ID RESTART WITH 1';
Zconnection1.ExecuteDirect(query);
query := 'ALTER TABLE EMAIL_ACCOUNTS ADD PRIMARY KEY (ID)';
Zconnection1.ExecuteDirect(query);
query := 'SET TERM ^';
Zconnection1.ExecuteDirect(query);
query := '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 ; ^';
Zconnection1.ExecuteDirect(query);
ZTable1.Active:=true;
end;
