|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
procedure TForm1.Manage();
var
str : string;
begin
// populate the tables with data
Add_CustomerMaster_Records;
Add_CustomerOrders_Records;
Add_OrderItems_Records;
Add_ItemMaster_Records;
// perform a query:
// list customer name and total amount per order
// name total
// @@@@@@@@@@@@@ $xx.xx
// for each order in the CustomerOrders table
// fetch order number
// fetch customer number
// fetch name from CustomerMaster table based on customer number
// for each order item in OrderItems table
// fetch item quantity
// fetch item number
// fetch item price from ItemMaster table based on item number
// next
// next
try
SQLQuery1.SQL.Clear;
str := 'SELECT cm_custname, SUM(im_itempric * oi_quantity) ' +
'FROM custmast, custordr, ordritem, itemmast ' +
'WHERE co_custnumb = cm_custnumb AND co_ordrnumb = oi_ordrnumb AND oi_itemnumb = im_itemnumb ' +
'GROUP BY cm_custnumb, cm_custname';
SQLQuery1.SQL.Add(str);
SQLQuery1.Prepared := true;
SQLQuery1.Open;
ClientDataSet1.Active := true;
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Add_CustomerMaster_Records;
const
data : array [1..4] of string = (
'(''1000'', ''92867'', ''CA'', ''1'', ''Bryan Williams'', ''2999 Regency'', ''Orange'')',
'(''1001'', ''61434'', ''CT'', ''1'', ''Michael Jordan'', ''13 Main'', ''Harford'')',
'(''1002'', ''73677'', ''GA'', ''1'', ''Joshua Brown'', ''4356 Cambridge'', ''Atlanta'')',
'(''1003'', ''10034'', ''MO'', ''1'', ''Keyon Dooling'', ''19771 Park Avenue'', ''Columbia'')'
);
nRecords : integer = 4;
var
i : integer;
str : string;
begin
try
SQLConnection1.StartTransaction(td);
Delete_Records('custmast');
for i := 1 to nRecords do
begin
// add one record at time to table
str := 'INSERT INTO custmast VALUES ' + data[i];
SQLConnection1.ExecuteDirect(str);
end;
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Add_CustomerOrders_Records;
const
data : array [1..2] of string = (
'(''09/01/2002'', ''09/05/2002'', ''1'', ''1001'')',
'(''09/02/2002'', ''09/06/2002'', ''2'', ''1002'')'
);
nRecords : integer = 2;
var
i : integer;
str : string;
begin
try
SQLConnection1.StartTransaction(td);
Delete_Records('custordr');
for i := 1 to nRecords do
begin
// add one record at time to table
str := 'INSERT INTO custordr VALUES ' + data[i];
SQLConnection1.ExecuteDirect(str);
end;
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Add_OrderItems_Records;
const
data : array [1..4] of string = (
'(''1'', ''2'', ''1'', ''1'')',
'(''2'', ''1'', ''1'', ''2'')',
'(''3'', ''1'', ''1'', ''3'')',
'(''1'', ''3'', ''2'', ''3'')'
);
nRecords : integer = 4;
var
i : integer;
str : string;
begin
try
SQLConnection1.StartTransaction(td);
Delete_Records('ordritem');
for i := 1 to nRecords do
begin
// add one record at time to table
str := 'INSERT INTO ordritem VALUES ' + data[i];
SQLConnection1.ExecuteDirect(str);
end;
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Add_ItemMaster_Records;
const
data : array [1..4] of string = (
'(''10'', ''19.95'', ''1'', ''Hammer'')',
'( ''3'', ''9.99'', ''2'', ''Wrench'')',
'( ''4'', ''16.59'',''3'', ''Saw'')',
'( ''1'', ''3.98'', ''4'', ''Pliers'')'
);
nRecords : integer = 4;
var
i : integer;
str : string;
begin
try
SQLConnection1.StartTransaction(td);
Delete_Records('itemmast');
for i := 1 to nRecords do
begin
// add one record at time to table
str := 'INSERT INTO itemmast VALUES ' + data[i];
SQLConnection1.ExecuteDirect(str);
end;
SQLConnection1.Commit(td);
except on E: Exception do Handle_Exception(E);
end;
end;
procedure TForm1.Delete_Records(Table : string);
var
str : string;
begin
try
str := 'DELETE FROM ' + Table;
SQLConnection1.ExecuteDirect(str);
except on E: Exception do Handle_Exception(E);
end;
end;
