4
Sample Use Cases

This chapter describes sample use cases for the Database adapter. This chapter discusses the following topics:

• Database Adapter Sample Use Cases

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: Oracle9iAS InterConnect User Guide

1. Create a business object in iStudio. Enter Customer in the business object name field on the Create Business Object dialog.

2. Create a common data type. On the Create Data Type complete the following:
   1. Enter Address in the Common Data Type Name field.

   2. Add the following attributes:
      • city (STRING)

      • state (STRING)

      • zip (STRING)

3. Create an event in iStudio. On the Create Event dialog, complete the following:
   1. Select Customer for the Business Object.

   2. Enter createCustomer in the Event Name field.

   3. Click Add to add the following attributes:
      • id (NUMBER)

      • address (Address) [ARRAY]

4. Create an application in iStudio. Enter demopub in the Application Name field on the Create Application dialog.

5. Create a Published Event using the Publish Wizard in iStudio:
   1. Select demopub for the Application and Database as the Message Type on the Select an Event page.

   2. Expand the tree in the Select an Event and select createCustomer.

   3. Click Import on the Define Application View page to import attributes from the Common View.

   4. Create the following mapping for the newCustomer procedure on the Define Mapping IN Arguments page:
      • createCustomer [demopub View] -- Object Copy -- createCustomer [Common View]

   5. Click Finish.

6. Create an application in iStudio. Enter demosub in the Application Name field on the Create Application dialog.

7. Create a Subscribed Event using the Subscribe Wizard in iStudio.
   1. Select demosub for the Application and Database as the Message Type on the Select an Event page.

   2. Expand the tree in the Select an Event box and select createCustomer.

   3. Click Import on the Define Application View page and select Common View to import data types from the Common View.

   4. Create the createCustomer [Common View] -- Object Copy -- createCustomer [demosub View] mappings on the Define Mappings page.

   5. 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);

8. Click Finish.

9. Export SQL Code using iStudio. On the Export Application dialog, complete the following:
   1. Select demopub and demosub in the Select the messages or types of message to export box.

   2. 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:

• create_demo_users.sql

• create_demo_table.sql

• demo_publish.sql

  See Also: "Related Files"

To complete the following steps, run the create_demo_users.sql file as the system user.

1. 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.

2. Bring up the demopub and demosub adapters:
   • In a publish SQL prompt, run exec demo_publish(<ANY NUMBER>) in the demopub schema. A new row is created in the Results table in demosub schema every time it receives a message from demopub.

     Note: If a Database adapter has already been installed with the application name of demopub, use the copyAdapter script in the $ORACLE_HOME/oai/9.0.2/bin directory to create the demosub adapter. Usage: copyAdapter demopub demosub.

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: Oracle9iAS InterConnect User Guide

Design Time Steps

1. Create a business object in iStudio. On the Create Business Object dialog, enter Customer in the Business Object Name field.

2. Create a common data type.

3. Create a procedure in iStudio. On the Create Procedure dialog, complete the following:
   1. Select Customer for the business object.

   2. Enter newCustomer in the Procedure Name field.

   3. Click Import and select Database to import attributes.

   4. 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.

4. Create an application in iStudio. Enter demoinv in the Application Name field on the Create Application dialog.

5. Create an invoked procedure using the Invoke Wizard in iStudio:
   1. Select demoinv for the Application and Database as the Message Type on the Select a Procedure page.

   2. Expand the tree in the Select a Procedure box and select newCustomer.

   3. Click Import and select Common View on the Define Application View page to import attributes from the common view.

   4. Change the ID attribute from IN to INOUT.

      See Also: Chapter 5, "Frequently Asked Questions"

   5. Check the box for Synchronous.

   6. Click Returned In Args and enter the following:
      • In Argument: ID

      • Out Argument: ID

6. 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]

7. 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]

8. On the Define Stored Procedure page, do not edit the SQL code; it is correct.

9. Click Finish.

10. Create an application in iStudio. On the Create Application dialog, enter demoimp in the Application Name field.

11. Create an implemented procedure using the Implement Wizard in iStudio:
    1. Select demoimp for the Application and Database as the Message Type.

    2. Expand the tree in the Select a Procedure box and select newCustomer.

    3. Click Import and select Database on the Define Application View page to import attributes from the database.

    4. 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].

12. 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]

13. 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]

14. 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';

15. Click Finish.

16. Export SQL code by selecting Export from the File menu in iStudio. Select demoinv and demoimp from the context menu.

17. 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:

• demo_setup.sql

• create_sync_invoke.sql

  Note: Create copies of the Database adapter using the copyAdapter script named demoinv and demoimp.

  See Also: "Related Files for Synchronous Invoke Implement"

Bring up two SQL prompts:

1. At the first prompt, connect as foo/manager.

2. Run the following SQL scripts:
   • @demo_demoinv_CustomerTYPES, @demo_demoinv_Customer

   • @demo_sync_invoke

3. At the second prompt, connect as bar/manager.

4. Run the following SQL scripts:
   • @demo_demoimp_CustomerTYPES

   • @demo_demoimp_Customer

5. Start the demoinv and demoimp adapters using the start scripts.

6. 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: Oracle9iAS InterConnect User Guide

Design Time Steps

1. Create a business object in iStudio. Enter Customer in the business object name field On the Create Business Object dialog.

2. Create a common data type.

3. Create a procedure in iStudio. On the Create Procedure dialog, complete the following:
   1. Select Customer for the Business Object.

   2. Enter newCustomer in the Procedure Name field.

   3. Click Import and select Database to import attributes from the database.

   4. 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.

4. Create an application in iStudio. Enter demoinv in the Application Name field on the Create Application dialog

5. Create an invoked procedure using the Invoke Wizard in iStudio:
   1. Select demoinv for the Application and Database as the Message Type on the Select a Procedure page.

   2. Expand the tree in the Select a Procedure box and select newCustomer.

   3. Click Import and select Common View on the Define Application View page to import attributes from the common view.

   4. Change the ID attribute from IN to INOUT.

      See Also: Chapter 5, "Frequently Asked Questions"

   5. Uncheck the box for Synchronous.

   6. Click Returned In Args and enter the following:
      • In Argument: ID

      • Out Argument: ID

6. 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]

7. 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]

8. 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;

9. Click Finish.

10. Create an application in iStudio. Enter demoimp in the Application Name field On the Create Application dialog.

11. Create an implemented procedure using the Implement Wizard in iStudio:
    1. Select demoimp for the Application and Database as the Message Type.

    2. Expand the tree in the Select a Procedure box and select newCustomer.

    3. Click Import and select Database on the Define Application View page to import attributes from the database.

    4. 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].

12. 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]

13. 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]

14. 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';

15. Click Finish.

16. Export SQL code by selecting Export from the File menu in iStudio. Select demoinv and demoimp from the context menu.

17. 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:

1. At the first prompt, connect as foo/manager.

2. Run the following SQL scripts:
   • @demo_demoinv_CustomerTYPES

   • @demo_demoinv_Customer

   • @demo_invoke.

3. At the second prompt, connect as bar/manager.

4. Run the following SQL scripts:
   • @demo_demoimp_CustomerTYPES

   • @demo_demoimp_Customer.

5. Start the demoinv and demoimp adapters.

6. 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;
  /