These instructions assume that you are using an Oracle database and guide you through the following tasks:
If your install has not created the default database then create a database, following your DBA instructions, before following these instructions.
You can also perform these steps by running pre-defined SQL scripts: Return to sample home and choose one of the import options. After importing you can choose to set up the database from SQL scripts.
Note. When you create and access the database tables, be aware of the following issues:
-- First create the tables needed for Application Data DROP TABLE "ADAPTER_CUSTOMER" ; CREATE TABLE "ADAPTER_CUSTOMER" ( "PKEY" VARCHAR(10) NOT NULL PRIMARY KEY, "FIRSTNAME" VARCHAR(20), "LASTNAME" VARCHAR(20), "ADDRESS" VARCHAR(10), "CCODE" VARCHAR(10) ); ALTER TABLE "ADAPTER_CUSTOMER" ADD CONSTRAINT "CUST_ADDR_FK" FOREIGN KEY ("ADDRESS") REFERENCES "ADAPTER_ADDRESS" ("PKEY") ON DELETE CASCADE; DROP TABLE "ADAPTER_ADDRESS" ; CREATE TABLE "ADAPTER_ADDRESS" ( "PKEY" VARCHAR(10) NOT NULL PRIMARY KEY, "FIRSTLINE" VARCHAR(20), "SECONDLINE" VARCHAR(20), "CODE" VARCHAR(10), "CITY" VARCHAR(20), "COUNTRY" VARCHAR(10)); DROP TABLE "ADAPTER_INVOICE" ; CREATE TABLE "ADAPTER_INVOICE" ( "PKEY" VARCHAR(10) NOT NULL PRIMARY KEY, "ITEM" VARCHAR(10) NOT NULL, "QUANTITY" INTEGER NOT NULL, "CUSTOMER" VARCHAR(10) NOT NULL); ALTER TABLE "ADAPTER_INVOICE" ADD CONSTRAINT "INV_CUST_FK" FOREIGN KEY ("CUSTOMER") REFERENCES "ADAPTER_CUSTOMER" ("PKEY") ON DELETE CASCADE; DROP TABLE "IBM_EVENTS" ; CREATE TABLE "IBM_EVENTS" ( "EVENT_ID" INTEGER PRIMARY KEY, "CONNECTOR_ID" VARCHAR(40), "OBJECT_KEY" VARCHAR(10) NOT NULL, "OBJECT_NAME" VARCHAR(40) NOT NULL, "OBJECT_VERB" VARCHAR(40) NOT NULL, "EVENT_PRIORITY" INTEGER NOT NULL, "EVENT_TIME" TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP, "EVENT_STATUS" INTEGER NOT NULL, "EVENT_COMMENT" VARCHAR(100) ); CREATE SEQUENCE "IBM_SEQUENCE" (start with 1 increment by 1 nomaxvalue); CREATE TRIGGER "IBM_SEQ_TRIG" BEFORE INSERT ON "IBM_EVENTS" FOR EACH ROW BEGIN SELECT DBINPUT_SEQUENCE.nextval INTO :NEW.EVENT_ID FROM dual; END; DROP TRIGGER "ADAP_CUST_EVENT"; CREATE TRIGGER "ADAP_CUST_EVENT" AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_CUSTOMER" REFERENCING NEW AS N OLD AS O FOR EACH ROW BEGIN IF inserting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Create', 1, 0); END IF; IF updating THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Update', 1, 0); END IF; IF deleting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_CUSTOMER', 'Delete', 1, 0); END IF; END; DROP TRIGGER "ADAP_ADD_EVENT"; CREATE TRIGGER "ADAP_ADD_EVENT" AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_ADDRESS" REFERENCING NEW AS N OLD AS O FOR EACH ROW BEGIN IF inserting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Create', 1, 0); END IF; IF updating THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Update', 1, 0); END IF; IF deleting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_ADDRESS', 'Delete', 1, 0); END IF; END; DROP TRIGGER "ADAP_INV_EVENT"; CREATE TRIGGER "ADAP_INV_EVENT" AFTER INSERT OR DELETE OR UPDATE ON "ADAPTER_INVOICE" REFERENCING NEW AS N OLD AS O FOR EACH ROW BEGIN IF inserting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Create', 1, 0); END IF; IF updating THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Update', 1, 0); END IF; IF deleting THEN INSERT INTO IBM_EVENTS(OBJECT_KEY, OBJECT_NAME, OBJECT_VERB, EVENT_PRIORITY, EVENT_STATUS) VALUES(:N.PKEY,'ADAPTER_INVOICE', 'Delete', 1, 0); END IF; END;
sqlplus <uid>/<password> @adapter_create_tables_oracle.sql
Wait for the script to finish running. If you are running the script for the first time, the following message is displayed:
ORA-00942: table or view does not existIgnore this message. The message is displayed because the script attempts to remove a existing tables called DBINPUT_CUSTOMER and DBINPUT_EVENTS before it creates new tables, but if you have not run the script before, the script cannot find the existing table.