|
|
|
Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.
Below is the code for Define():
procedure TForm1.Define();
begin
// open or create the tables
Create_CustomerMaster_Table;
Create_CustomerOrders_Table;
Create_OrderItems_Table;
Create_ItemMaster_Table;
end;
procedure TForm1.Create_CustomerMaster_Table;
begin
try
SQLConnection1.StartTransaction(td);
// create table
SQLConnection1.ExecuteDirect('CREATE TABLE custmast (' +
'cm_custnumb CHAR(4),' +
'cm_custzipc CHAR(9),' +
'cm_custstat CHAR(2),' +
'cm_custrtng CHAR(1),' +
'cm_custname VARCHAR(47),' +
'cm_custaddr VARCHAR(47),' +
'cm_custcity VARCHAR(47))');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
try
SQLConnection1.StartTransaction(td);
SQLConnection1.ExecuteDirect('CREATE UNIQUE INDEX cm_custnumb_idx ON custmast (cm_custnumb)');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Create_CustomerOrders_Table;
begin
try
SQLConnection1.StartTransaction(td);
// create table
SQLConnection1.ExecuteDirect('CREATE TABLE custordr (' +
'co_ordrdate DATE,' +
'co_promdate DATE,' +
'co_ordrnumb CHAR(6),' +
'co_custnumb CHAR(4))');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
try
SQLConnection1.StartTransaction(td);
SQLConnection1.ExecuteDirect('CREATE UNIQUE INDEX co_ordrnumb_idx ON custordr (co_ordrnumb)');
SQLConnection1.ExecuteDirect('CREATE INDEX co_custnumb_idx ON custordr (co_custnumb)');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Create_OrderItems_Table;
begin
try
SQLConnection1.StartTransaction(td);
// create table
SQLConnection1.ExecuteDirect('CREATE TABLE ordritem (' +
'oi_sequnumb SMALLINT,' +
'oi_quantity SMALLINT,' +
'oi_ordrnumb CHAR(6),' +
'oi_itemnumb CHAR(5))');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
try
SQLConnection1.StartTransaction(td);
SQLConnection1.ExecuteDirect('CREATE UNIQUE INDEX oi_ordrnumb_idx ON ordritem (oi_ordrnumb, oi_sequnumb)');
SQLConnection1.ExecuteDirect('CREATE INDEX oi_itemnumb_idx ON ordritem (oi_itemnumb)');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Create_ItemMaster_Table;
begin
try
SQLConnection1.StartTransaction(td);
// create table
SQLConnection1.ExecuteDirect('CREATE TABLE itemmast (' +
'im_itemwght INTEGER,' +
'im_itempric MONEY,' +
'im_itemnumb CHAR(5),' +
'im_itemdesc VARCHAR(47))');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
try
SQLConnection1.StartTransaction(td);
SQLConnection1.ExecuteDirect('CREATE INDEX im_itemnumb_idx ON itemmast (im_itemnumb)');
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
