How to create a database, tables and triguers

OCTweak writes “Create a new application and add a ZConnection and a ZSQLPROCESSOR to the form
Add a button, name it CreateBtn and add the following click event for it


procedure TForm1.CreateBtnClick(Sender: TObject);
var
HstName, PathName, DBName, Props: string;
begin
HstName := ‘192.168.0.1’; // if using embed, leave blank
PathName := ‘c:fibdatabases’; // path where the database will be created at the server
DBName := ‘TEST’; // database name, do not add an extension
ZConnection.Properties.Clear;
ZConnection.HostName := HstName;
ZConnection.Database := PathName + DBName + ‘.fdb’;
ZConnection.User := ‘sysdba’;
ZConnection.Password := ‘masterkey’;
Props := ‘CreateNewDatabase=CREATE DATABASE ”’;
if ZConnection.HostName ” then // if not firebird embedded
Props := Props + HstName + ‘:’;
Props := Props +
PathName + DBName + ‘.fdb”’ +
‘ USER ”’ + ZConnection.User + ”” +
‘ PASSWORD ”’ + ZConnection.Password + ”” +
‘ PAGE_SIZE 16384 DEFAULT CHARACTER SET ISO8859_1’ +
‘ FILE ”’+ PathName + DBName + ‘2.fdb”’ + // create a 2nd file
‘ LENGTH=50000 STARTING AT PAGE 50001’;

ZConnection.Properties.Add(Props);
ZConnection.Connect;

// Now that the database is created
// create some tables, generators and triguers
ZScript.Script.Clear;
ZScript.Delimiter := ‘!!’; // necessary because of triguers
ZScript.DelimiterType := dtSetTerm; // use ZScriptParser
ZScript.LoadFromFile(ExtractFilePath(ParamStr(0)) + ‘createDB.sql’); // look below for the contents of this file
ZConnection.StartTransaction;
ZScript.Execute;
ZConnection.Commit;

ZConnection.Disconnect; // disconnect from database;

end;

create a text file name it createDB.sql and add the following lines to it
After, save it in the same folder where you create your exe.

SET TERM !! ;
Create Table TBL (
keycode Integer not null,
fchar char(9),
fvarchar varchar( 20 ),
fdate Date,
fblobText Blob sub_type 1,
PRIMARY KEY (keycode))!!
CREATE GENERATOR KEYCODE_GEN!!
Create Table TBLIMG (
keyimgcode integer not null,
fblobJPG Blob sub_type -1,
PRIMARY KEY (keyimgcode))!!
CREATE GENERATOR KEYIMGCODE_GEN!!
CREATE TRIGGER SET_KEYCODE FOR TBL
BEFORE INSERT AS
BEGIN
IF ((NEW.KEYCODE = 0) OR (NEW.KEYCODE IS NULL)) THEN
NEW.KEYCODE = GEN_ID(KEYCODE_GEN, 1);
END !!
CREATE TRIGGER SET_KEYIMGCODE FOR TBLIMG
BEFORE INSERT AS
BEGIN
IF ((NEW.KEYIMGCODE = 0) OR (NEW.KEYIMGCODE IS NULL)) THEN
NEW.KEYIMGCODE = GEN_ID(KEYIMGCODE_GEN, 1);
END !!

This entry was posted in Code Hacks. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *