Define

The Define() step is where specific data definitions are established by your application and/or process. This involves defining columns/fields and creating the tables/files with optional indices.

Below is the code for Define():

procedure TForm1.Define();

begin

    // delete tables ...

    Delete_Tables();

    // ...and re-create them with constraints

    Create_CustomerMaster_Table;

    Create_CustomerOrders_Table;

    Create_OrderItems_Table;

    Create_ItemMaster_Table;

end;

procedure TForm1.Delete_Tables;

begin

    try

        SQLConnection1.StartTransaction(td);

    except on E: Exception do Handle_Exception(E);

    end;

    try

        SQLConnection1.ExecuteDirect('DROP TABLE ordritem');

    except on E: Exception do Handle_Exception(E);

    end;

    try

        SQLConnection1.ExecuteDirect('DROP TABLE custordr');

    except on E: Exception do Handle_Exception(E);

    end;

    try

        SQLConnection1.ExecuteDirect('DROP TABLE custmast');

    except on E: Exception do Handle_Exception(E);

    end;

    try

        SQLConnection1.ExecuteDirect('DROP TABLE itemmast');

    except on E: Exception do Handle_Exception(E);

    end;

    try

        SQLConnection1.Commit(td);

    except on E: Exception do Handle_Exception(E);

    end;

end;

procedure TForm1.Create_CustomerMaster_Table;

begin

    try

        SQLConnection1.StartTransaction(td);

        // create table

        SQLConnection1.ExecuteDirect('CREATE TABLE custmast (' +

            'cm_custnumb CHAR(4) PRIMARY KEY,' +

            '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;

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) PRIMARY KEY,' +

            'co_custnumb CHAR(4),' +

            'FOREIGN KEY (co_custnumb) REFERENCES custmast)');

        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),' +

            'FOREIGN KEY (oi_itemnumb) REFERENCES itemmast,' +

            'FOREIGN KEY (oi_ordrnumb) REFERENCES custordr)');

        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) PRIMARY KEY,' +

            'im_itemdesc VARCHAR(47))');

        SQLConnection1.Commit(td);

    except on E: Exception do Handle_Exception(E);

    end;

end;