Product Documentation

FairCom Java Stored Procedures

Previous Topic

Next Topic

Manage

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;

TOCIndex