4
Sample Use Cases
This chapter describes sample use cases for the Database adapter.
Database Adapter Sample Use Cases
For all of the scripts and steps for the use cases provided in this chapter, replace the following strings with the correct values.
repo_owner
--The repository owner.
version
--The version of the appropriate metadata in iStudio. This is usually V1
unless the metadata versioning features was used in iStudio.
Case One: Publish and Subscribe
This case illustrates a simple Publish-Subscribe scenario using a Database adapter at each end. In this case a Customer
message containing the ID
attribute and an array of Addresses is published using a PL/SQL procedure. This message is picked up by the publishing adapter, published, and routed to the appropriate subscribing adapter through the hub. The message becomes a new row in a table in the destination schema. These adapters can be located anywhere and can talk to any database. The scripts described here create the publish and subscribe side schemas on the same database. These scripts can be modified to fit any custom scenario.
Design Time Steps
The following section describes metadata creation using iStudio.
See Also:
Oracle Application Server InterConnect User's Guide
|
- Create a business object in iStudio. Enter Customer in the business object name field on the Create Business Object dialog.
- Create a common data type. On the Create Data Type complete the following:
- Enter Address in the Common Data Type Name field.
- Add the following attributes:
- city (STRING)
- state (STRING)
- zip (STRING)
- Create an event in iStudio. On the Create Event dialog, complete the following:
- Select Customer for the Business Object.
- Enter createCustomer in the Event Name field.
- Click Add to add the following attributes:
- id (NUMBER)
- address (Address) [ARRAY]
- Create an application in iStudio. Enter demopub in the Application Name field on the Create Application dialog.
- Create a Published Event using the Publish Wizard in iStudio:
- Select demopub for the Application and Database as the Message Type on the Select an Event page.
- Expand the tree in the Select an Event and select createCustomer.
- Click Import on the Define Application View page to import attributes from the Common View.
- Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
createCustomer [demopub View] -- Object Copy -- createCustomer [Common View]
- Click Finish.
- Create an application in iStudio. Enter demosub in the Application Name field on the Create Application dialog.
- Create a Subscribed Event using the Subscribe Wizard in iStudio.
- Select demosub for the Application and Database as the Message Type on the Select an Event page.
- Expand the tree in the Select an Event box and select createCustomer.
- Click Import on the Define Application View page and select Common View to import data types from the Common View.
- Create the
createCustomer [Common View] -- Object Copy -- createCustomer [demosub View] mappings
on the Define Mappings page.
- Enter the following SQL code on the Define Stored Procedure page:
- For
sub_createCustomer_<repo_owner>_<version>
:
- Following the line
dummy:= 0;
, Enter insert into results values (id, address);
- Click Finish.
- Export SQL Code using iStudio. On the Export Application dialog, complete the following:
- Select demopub and demosub in the Select the messages or types of message to export box.
- Enter demo for the File Prefix.
The following files are created and stored in the ORACLE_HOME
/oai/4.1/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
Runtime Steps
The following steps are based on the following files:
To complete the following steps, run the create_demo_users.sql
file as the system
user.
- Bring up two SQL prompts:
- Connect as the demopub/manager and run
@demo_demopub_CustomerTYPES, @demo_demopub_Customer, @demo_publish
.
- Connect as dempsub/manager and run
@demo_demosub_CustomerTYPES, @create_demo_table, @demo_demosub_Customer
.
- Bring up the demopub and demosub adapters:
Related Files
The following files are related to the runtime steps in case one.
- File:
create_demo_users.sql
CREATE USER demopub identified by manager;
GRANT connect, resource to demopub;
CREATE USER demosub identified by manager;
GRANT connect, resource to demosub;
- File:
create_demo_table.sql
CREATE TABLE results (id NUMBER, address demosub_Address_<repo_owner>_
<version>_Arr);
- File:
demo_publish.sql
CREATE OR REPLACE PROCEDURE Demo_Publish(id NUMBER)
AS
moid NUMBER;
aoid NUMBER;
addrid NUMBER;
BEGIN
Customer.crMsg_createCustomer_<repo_owner>_<version>(moid, aoid, id);
addrid := Customer.cr_Address_address('SFO', 'CA', '94040', moid, aoid);
addrid := Customer.cr_Address_address('Reno', 'NV', '93949', moid, aoid);
addrid := Customer.cr_Address_address('SJC', 'CA', '95117', moid, aoid);
Customer.pub_createCustomer_<repo_owner>_<version>(moid, 'demopub');
COMMIT;
END;
/
Case Two: Invoke and Implement
This use case illustrates a simple invoke and implement scenario using a Database adapter at each end. Both synchronous and asynchronous modes of invocation are illustrated. A Customer
message containing the ID
attribute, and an array of Addresses
is sent using a PL/SQL procedure. This message is picked up by the invoking adapter and routed to the appropriate implementing adapter through the hub. On the implementing end, a new row is created in a table in destination schema and a response is sent back indicating that it has received this message. Subsequently on receiving the response, the invoking adapter updates the status for the corresponding customer.
These adapters can be located anywhere and can talk to any database. The scripts provided create the sender and receiver side schemas on the same database. These schemas can be modified to adapt to any custom scenario.
Synchronous Invoke Implement
Run the demo_setup.sql
file to create necessary schemas in the database on the application or spoke database. It may be necessary to connect as the system
user.
See Also:
Oracle Application Server InterConnect User's Guide
|
Design Time Steps
- Create a business object in iStudio. On the Create Business Object dialog, enter Customer in the Business Object Name field.
- Create a common data type.
- Create a procedure in iStudio. On the Create Procedure dialog, complete the following:
- Select Customer for the business object.
- Enter newCustomer in the Procedure Name field.
- Click Import and select Database to import attributes.
- Log in to the Database as the FOO user.
- Expand the FOO schema, Tables/Views and select
FOO.CUSTOMERS
.
- On the right hand side of the dialog, select the ID, ADDRESS, and STATUS columns using the control key.
- Click Done to return to the Publish Wizard.
- Import arguments as IN arguments in the Publish Wizard. Change the last column (IN/OUT/INOUT) for Status to Out and click Save.
- Create an application in iStudio. Enter demoinv in the Application Name field on the Create Application dialog.
- Create an invoked procedure using the Invoke Wizard in iStudio:
- Select demoinv for the Application and Database as the Message Type on the Select a Procedure page.
- Expand the tree in the Select a Procedure box and select newCustomer.
- Click Import and select Common View on the Define Application View page to import attributes from the common view.
- Change the
ID
attribute from IN
to INOUT
.
- Check the box for Synchronous.
- Click Returned In Args and enter the following:
- In Argument: ID
- Out Argument: ID
- Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
newCustomer:IN [demoinv View] -- Object Copy -- newCustomer:IN [Common View]
- Create the following mapping for the newCustomer procedure on the Define Mapping OUT Arguments page:
newCustomer:OUT.STATUS [Common View] -- Copy Fields -- newCustomer:OUT.STATUS [demoinv View]
- On the Define Stored Procedure page, do not edit the SQL code; it is correct.
- Click Finish.
- Create an application in iStudio. On the Create Application dialog, enter demoimp in the Application Name field.
- Create an implemented procedure using the Implement Wizard in iStudio:
- Select demoimp for the Application and Database as the Message Type.
- Expand the tree in the Select a Procedure box and select newCustomer.
- Click Import and select Database on the Define Application View page to import attributes from the database.
- Enter the correct information on the Database Login dialog for the BAR schema.
- Expand BAR, Tables/Views and select
BAR.RESULTS
.
- On the right hand side of the dialog, select the ID, ADDRESS, and STATUS columns using the control key.
- Click Done.
- Import arguments as IN arguments. Add an attribute called STATUS [String, OUT].
- Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
newCustomer:IN [Common View] -- Object Copy -- newCustomer:IN [demoimp View]
- Create the following mapping for the newCustomer procedure on the Define Mapping OUT Arguments page:
newCustomer:OUT [dempimp View] -- Object Copy -- newCustomer:OUT [Common View]
- Edit the SQL code on the Define Stored Procedure page as follows:
- For
imp_newCustomer_<repo_owner>_<version>
, following the line dummy:= 0;
, enter insert into results values(i_id, i_address);o_status := 'SUCCESS';
- Click Finish.
- Export SQL code by selecting Export from the File menu in iStudio. Select demoinv and demoimp from the context menu.
- Enter demo for the File Prefix.
The following files are created and stored in the ORACLE_HOME
/oai/4.1/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
Runtime Steps
The following steps are based on the following files:
Bring up two SQL prompts:
- At the first prompt, connect as foo/manager.
- Run the following SQL scripts:
@demo_demoinv_CustomerTYPES
, @demo_demoinv_Customer
@demo_sync_invoke
- At the second prompt, connect as bar/manager.
- Run the following SQL scripts:
@demo_demoimp_CustomerTYPES
@demo_demoimp_Customer
- Start the demoinv and demoimp adapters using the start scripts.
- In invoke side SQL prompt, run
exec newCustomer_sync(id, city, state, zip, timeout)
.
A new row in the customers
table in foo
schema is created. This new row has Status
initially set to None
but changes to Success
when the invoking adapter receives a response from the implementing adapter.
A new row is also created in the results
table in bar
schema. If the invoking adapter does not receive a response within the time specified in seconds in the timeout
parameter, then the Status
column is not updated in foo.customers
; instead, a new row is created in the correlation table cus_newcustomer_<repo_owner>_<version>
. This table is created by the iStudio exported PL/SQL code. If necessary, foo.customers
have a trigger to update automatically when a new row is created in the correlation table.
Asynchronous Invoke Implement
Run the demo_setup.sql
file to create necessary schemas in the database on the application or spoke database. It may be necessary to connect as the system
user.
See Also:
Oracle Application Server InterConnect User's Guide
|
Design Time Steps
- Create a business object in iStudio. Enter Customer in the business object name field On the Create Business Object dialog.
- Create a common data type.
- Create a procedure in iStudio. On the Create Procedure dialog, complete the following:
- Select Customer for the Business Object.
- Enter newCustomer in the Procedure Name field.
- Click Import and select Database to import attributes from the database.
- Log in to the Database using the correct information.
- Expand the FOO schema, Tables/Views and select
FOO.CUSTOMERS
.
- On the right hand side of the dialog, select the ID, ADDRESS, and STATUS columns using the control key.
- Click Done.
- Import arguments as IN arguments. Change the last column (IN/OUT/INOUT) for Status to Out and click Save.
- Create an application in iStudio. Enter demoinv in the Application Name field on the Create Application dialog
- Create an invoked procedure using the Invoke Wizard in iStudio:
- Select demoinv for the Application and Database as the Message Type on the Select a Procedure page.
- Expand the tree in the Select a Procedure box and select newCustomer.
- Click Import and select Common View on the Define Application View page to import attributes from the common view.
- Change the
ID
attribute from IN
to INOUT
.
- Uncheck the box for Synchronous.
- Click Returned In Args and enter the following:
- In Argument: ID
- Out Argument: ID
- Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
newCustomer:IN [demoinv View] -- Object Copy -- newCustomer:IN [Common View]
- Create the following mapping for the newCustomer procedure on the Define Mapping OUT Arguments page:
newCustomer:OUT.STATUS [Common View] -- Copy Fields -- newCustomer:OUT.STATUS [demoinv View]
- Edit the SQL code on the Define Stored Procedure page as follows:
- For
sub_newCustomer_<repo_owner>_<version>
, following the line dummy:= 0;
, enter update customers set status=sub_newCustomer_<repo_owner>_<version>.status where id=sub_newCustomer_<repo_owner>_<version>.id;
- Click Finish.
- Create an application in iStudio. Enter demoimp in the Application Name field On the Create Application dialog.
- Create an implemented procedure using the Implement Wizard in iStudio:
- Select demoimp for the Application and Database as the Message Type.
- Expand the tree in the Select a Procedure box and select newCustomer.
- Click Import and select Database on the Define Application View page to import attributes from the database.
- Enter the correct information on the Database Login dialog.
- Expand BAR, Tables/Views and select
BAR.RESULTS
.
- On the right hand side of the dialog, select the ID, ADDRESS, and STATUS columns using the control key.
- Click Done.
- Import arguments as IN arguments. Add an attribute called STATUS [String, OUT].
- Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
newCustomer:IN [Common View] -- Object Copy -- newCustomer:IN [demoimp View]
- Create the following mapping for the newCustomer procedure on the Define Mapping OUT Arguments page:
newCustomer:OUT [dempimp View] -- Object Copy -- newCustomer:OUT [Common View]
- Edit the SQL code on the Define Stored Procedure page as follows:
- For
imp_newCustomer_<repo_owner>_<version>
, following the line dummy:= 0;
, enter insert into results values(i_id, i_address);o_status:= 'SUCCESS';
- Click Finish.
- Export SQL code by selecting Export from the File menu in iStudio. Select demoinv and demoimp from the context menu.
- Enter demo for the File Prefix.
The following files are created and stored in the ORACLE_HOME
/oai/4.1/iStudio
directory:
demo_demopub_Customer.sql
demo_demopub_CustomerTYPES.sql
demo_demosub_Customer.sql
demo_demosub_CustomerTYPES.sql
Runtime Steps
Bring up two SQL prompts:
- At the first prompt, connect as foo/manager.
- Run the following SQL scripts:
@demo_demoinv_CustomerTYPES
@demo_demoinv_Customer
@demo_invoke
.
- At the second prompt, connect as bar/manager.
- Run the following SQL scripts:
@demo_demoimp_CustomerTYPES
@demo_demoimp_Customer
.
- Start the demoinv and demoimp adapters.
- In invoke side SQL prompt, run
exec newCustomer_async(id, city, state, zip, timeout)
.
A new row is created in the customers
table in the demoinv
schema. This new row has STATUS
initially set to none
but changes to success
if the invoking adapter receives a response from the implementing adapter. A new row is created in the Results
table in the bar
schema.
Related Files for Synchronous Invoke Implement
The following scripts are related to the runtime steps described in both cases in case two.
demo_setup.sql
CREATE USER foo identified by manager;
GRANT connect, resource to foo;
CREATE USER bar identified by manager;
GRANT connect, resource to bar;
CREATE OR REPLACE TYPE foo.Address IS OBJECT (
city VARCHAR2(1000),
state VARCHAR2(1000),
zip VARCHAR2(1000)
);
/
CREATE OR REPLACE TYPE foo.Address_Array IS VARRAY(1000) OF foo.Address;
/
CREATE TABLE foo.customers (id NUMBER, address foo.Address_Array, status
VARCHAR2(20));
CREATE OR REPLACE TYPE bar.Address IS OBJECT (
city VARCHAR2(1000),
state VARCHAR2(1000),
zip VARCHAR2(1000)
);
/
CREATE OR REPLACE TYPE bar.Address_Array IS VARRAY(1000) OF bar.Address;
/
CREATE TABLE bar.results (id NUMBER, address bar.Address_Array);
Related Files for Asynchronous Invoke Implement
demo_sync_invoke.sql
CREATE OR REPLACE PROCEDURE newCustomer_sync(
ID NUMBER,
CITY LONG,
STATE LONG,
ZIP LONG,
timeout NUMBER)
AS
moid NUMBER;
aoid NUMBER;
addrid NUMBER;
corrid NUMBER;
ret_id NUMBER;
ret_status LONG;
BEGIN
insert into customers values (id, Address_Array(Address(city, state,
zip)),
'NONE');
Customer.crMsg_newCustomer_<repo_owner>_<version>(moid, aoid, id);
addrid := Customer.cr_ADDRESS_ARRAY_ADDRESS(city, state, zip, moid, aoid);
corrid := Customer.inv_newCustomer_<repo_owner>_<version>(moid, 'demoinv',
timeout,
ret_id, ret_status);
update customers set status=ret_status where id=ret_id;
COMMIT;
END;
/
demo_setup.sql
CREATE USER foo identified by manager;
GRANT connect, resource to foo;
CREATE USER bar identified by manager;
GRANT connect, resource to bar;
CREATE OR REPLACE TYPE foo.Address IS OBJECT (
city VARCHAR2(1000),
state VARCHAR2(1000),
zip VARCHAR2(1000)
);
/
CREATE OR REPLACE TYPE foo.Address_Array IS VARRAY(1000) OF foo.Address;
/
CREATE TABLE foo.customers (id NUMBER, address foo.Address_Array, status
VARCHAR2(20));
CREATE OR REPLACE TYPE bar.Address IS OBJECT (
city VARCHAR2(1000),
state VARCHAR2(1000),
zip VARCHAR2(1000)
);
/
CREATE OR REPLACE TYPE bar.Address_Array IS VARRAY(1000) OF bar.Address;
/
CREATE TABLE bar.results (id NUMBER, address bar.Address_Array);
demo_async_invoke.sql
CREATE OR REPLACE PROCEDURE newCustomer_async(
ID NUMBER,
CITY LONG,
STATE LONG,
ZIP LONG)
AS
moid NUMBER;
aoid NUMBER;
addrid NUMBER;
BEGIN
insert into customers values (id, Address_Array(Address(city, state,
zip)),
'NONE');
Customer.crMsg_newCustomer_<repo_owner>_<version>(moid, aoid, id);
addrid := Customer.cr_ADDRESS_ARRAY_ADDRESS(city, state, zip, moid, aoid);
Customer.inv_newCustomer_<repo_owner>_<version>(moid, 'demoinv');
COMMIT;
END;
/