|
|
|
Manage() provides data management functionality for your application and/or process.
Below is the code for Manage():
DROP PROCEDURE Add_CustomerMaster_Record;
DROP PROCEDURE Add_CustomerOrders_Record;
DROP PROCEDURE Add_OrderItems_Record;
DROP PROCEDURE Add_ItemMaster_Record;
DROP PROCEDURE Delete_Records;
DROP TRIGGER Validate_CustomerOrders_Record;
DROP TRIGGER Validate_OrderItems_Record;
CREATE PROCEDURE Add_CustomerMaster_Record (
IN cm_custnumb CHAR(4),
IN cm_custzipc CHAR(9),
IN cm_custstat CHAR(2),
IN cm_custrtng CHAR(1),
IN cm_custname VARCHAR(47),
IN cm_custaddr VARCHAR(47),
IN cm_custcity VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custmast VALUES (?,?,?,?,?,?,?)"
);
st.setParam (1, cm_custnumb);
st.setParam (2, cm_custzipc);
st.setParam (3, cm_custstat);
st.setParam (4, cm_custrtng);
st.setParam (5, cm_custname);
st.setParam (6, cm_custaddr);
st.setParam (7, cm_custcity);
st.execute();
END
CREATE PROCEDURE Add_CustomerOrders_Record (
IN co_ordrdate DATE,
IN co_promdate DATE,
IN co_ordrnumb CHAR(6),
IN co_custnumb CHAR(4)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO custordr VALUES (?,?,?,?)"
);
st.setParam (1, co_ordrdate);
st.setParam (2, co_promdate);
st.setParam (3, co_ordrnumb);
st.setParam (4, co_custnumb);
st.execute();
END
CREATE PROCEDURE Add_OrderItems_Record (
IN oi_sequnumb SMALLINT,
IN oi_quantity SMALLINT,
IN oi_ordrnumb CHAR(6),
IN oi_itemnumb CHAR(5)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO ordritem VALUES (?,?,?,?)"
);
st.setParam (1, oi_sequnumb);
st.setParam (2, oi_quantity);
st.setParam (3, oi_ordrnumb);
st.setParam (4, oi_itemnumb);
st.execute();
END
CREATE PROCEDURE Add_ItemMaster_Record (
IN im_itemwght INTEGER,
IN im_itempric MONEY,
IN im_itemnumb CHAR(5),
IN im_itemdesc VARCHAR(47)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"INSERT INTO itemmast VALUES (?,?,?,?)"
);
st.setParam (1, im_itemwght);
st.setParam (2, im_itempric);
st.setParam (3, im_itemnumb);
st.setParam (4, im_itemdesc);
st.execute();
END
CREATE TRIGGER Validate_CustomerOrders_Record
BEFORE INSERT ON custordr
REFERENCING NEWROW
FOR EACH ROW
BEGIN
SQLCursor cur = new SQLCursor ("SELECT cm_custnumb FROM custmast WHERE cm_custnumb = ?");
String custnumb = (String) NEWROW.getValue(4, CHAR);
cur.setParam(1, custnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid customer number"));
throw ex;
}
cur.close();
END
CREATE TRIGGER Validate_OrderItems_Record
BEFORE INSERT ON ordritem
REFERENCING NEWROW
FOR EACH ROW
BEGIN
SQLCursor cur = new SQLCursor ("SELECT co_ordrnumb FROM custordr WHERE co_ordrnumb = ?");
String ordrnumb = (String) NEWROW.getValue(3, CHAR);
cur.setParam(1, ordrnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid order number"));
throw ex;
}
cur.close();
cur = new SQLCursor ("SELECT im_itemnumb FROM itemmast WHERE im_itemnumb = ?");
String itemnumb = (String) NEWROW.getValue(4, CHAR);
cur.setParam(1, itemnumb);
cur.open();
cur.fetch();
if (!cur.found())
{
cur.close();
DhSQLException ex = new DhSQLException (666, new String("Invalid item number"));
throw ex;
}
cur.close();
END
CREATE PROCEDURE Delete_Records (
IN tablename CHAR(256)
)
BEGIN
SQLIStatement st = new SQLIStatement (
"DELETE FROM " + tablename
);
st.execute();
END
-- Manage
ECHO MANAGE;
ECHO Delete records...;
CALL Delete_Records('custmast');
CALL Delete_Records('custordr');
CALL Delete_Records('ordritem');
CALL Delete_Records('itemmast');
ECHO Add records...;
CALL Add_CustomerMaster_Record('1000', '92867', 'CA', '1', 'Bryan Williams', '2999 Regency', 'Orange');
CALL Add_CustomerMaster_Record('1001', '61434', 'CT', '1', 'Michael Jordan', '13 Main', 'Harford');
CALL Add_CustomerMaster_Record('1002', '73677', 'GA', '1', 'Joshua Brown', '4356 Cambridge', 'Atlanta');
CALL Add_CustomerMaster_Record('1003', '10034', 'MO', '1', 'Keyon Dooling', '19771 Park Avenue', 'Columbia');
CALL Add_ItemMaster_Record(10, 19.95, '1', 'Hammer');
CALL Add_ItemMaster_Record(3, 9.99, '2', 'Wrench');
CALL Add_ItemMaster_Record(4, 16.59, '3', 'Saw');
CALL Add_ItemMaster_Record(1, 3.98, '4', 'Pliers');
COMMIT WORK;
CALL Add_CustomerOrders_Record ('09/01/2002', '09/05/2002', '1', '1001');
CALL Add_OrderItems_Record (1, 2, '1', '1');
CALL Add_OrderItems_Record (2, 1, '1', '2');
CALL Add_CustomerOrders_Record ('09/02/2002', '09/06/2002', '2', '9999');
ECHO NOTE: trigger execution failure expected
CALL Add_OrderItems_Record (1, 1, '2', '3');
ECHO NOTE: trigger execution failure expected
CALL Add_OrderItems_Record (2, 3, '2', '4');
ECHO NOTE: trigger execution failure expected
CALL Add_CustomerOrders_Record ('09/22/2002', '09/26/2002', '3', '1003');
CALL Add_OrderItems_Record (1, 2, '3', '3');
CALL Add_OrderItems_Record (2, 2, '3', '99');
ECHO NOTE: trigger execution failure expected
COMMIT WORK;
SELECT co_ordrnumb "Order", co_custnumb "Name" FROM custordr;
SELECT oi_ordrnumb "Order", oi_itemnumb "Item" FROM ordritem;
