3 Design-Time and Run-Time Concepts

This chapter describes the design-time and run-time concepts for the Database adapter. It contains the following topics:

Database Adapter Design-Time Concepts
Database Adapter Run-Time Concepts
Starting the Database Adapter
Stopping the Database Adapter

Database Adapter Design-Time Concepts

During design time, the Database adapter maps relationships between application view and common view. The Database adapter can import the following tables and objects for the application view:

Relational
Object
Oracle Object
Advanced Queuing payload

This section contains the following topics:

Importing Database Tables and Objects
Importing Oracle Objects and Advanced Queuing Payloads
Returned In Arguments
Deploying PL/SQL Code

Importing Database Tables and Objects

For a database application, the application and common views resemble the underlying database schema, so iStudio enables the creation of a view by importing tables directly from the database.

The following examples show how importing tables into iStudio modifies their structures:

Example 3-1 Importing Relational Tables

Table 3-1 shows a simple relational database table.

Table 3-1 Customer

Column	Data Type
`NAME`	`VARCHAR2(200)`
`ID`	`NUMBER`
`ADDRESSES`	`LONG`

When imported into iStudio, this table appears as shown in Table 3-2.

Table 3-2 Customer

Attribute	Data Type
`NAME`	`STRING`
`ID`	`INTEGER`
`ADDRESSES`	`STRING`

When importing from a database, iStudio allows any number of columns to be selected.

Example 3-2 Object Table

Table 3-3 shows a simple object table.

Table 3-3 Customer

Column	Data Type
`NAME`	`VARCHAR2(200)`
`ID`	`NUMBER`
`ADDRESSES`	`ADDRESS_ARRAY`

Where ADDRESS_ARRAY is VARRAY of ADDRESS and ADDRESS is an OBJECT TYPE containing the following attributes:

Column	Data Type
`CITY`	`VARCHAR2(200)`
`STATE`	`VARCHAR2(200)`
`ZIP`	`NUMBER`

When imported into iStudio, this table appears as shown in Table 3-4.

Table 3-4 Customer

Attribute	Data Type
`NAME`	`STRING`
`ID`	`INTEGER`
`ADDRESSES`	`ARRAY` (marked as an `ARRAY`)

Where ADDRESS_ARRAY contains the following attributes:

Attribute	Data Type
`CITY`	`STRING`
`STATE`	`STRING`
`ZIP`	`NUMBER`

When dealing with Oracle Object Types, the hierarchical structure is kept intact.

Example 3-3 Foreign Key

For Foreign keys, you must import each of the different tables and manually set up the relationship in iStudio by editing the types of attributes.

Relational Tables related by a Foreign Key

Table 3-5 Customer

Column	Data Type
`NAME`	`VARCHAR2(200)`
`ID`	`NUMBER`
`ADDRESS`	`NUMBER` (Foreign key)

Table 3-6 Address

Column	Data Type
`ID`	`NUMBER` (Primary key)
`CITY`	`VARCHAR2(100)`
`STATE`	`VARCHAR2(50)`
`ZIP`	`NUMBER`

Using iStudio, complete the following to import this structure:

Import the Address table. The table appears as following:

Attribute Data Type

ID NUMBER

CITY STRING

STATE STRING

ZIP NUMBER
Import the Customer table. The table appears as following:

Attribute Data Type

NAME STRING

ID NUMBER

ADDRESS NUMBER
Change the type of Address attribute to Address.

Attribute	Data Type
`ID`	`NUMBER`
`CITY`	`STRING`
`STATE`	`STRING`
`ZIP`	`NUMBER`

Example 3-4 Oracle CLOB

Table 3-7 shows a simple object table with a column Orders of data type CLOB.

Table 3-7 Customer

Column	Data Type
ID	NUMBER
NAME	LONG
ORDERS	CLOB

When imported into iStudio, this table appears as shown in Table 3-8

Table 3-8 Customer

Attribute	Data Type
ID	DOUBLE
NAME	STRING
ORDERS	STRING

Example 3-5 Oracle Application Data Type

Table 3-9 shows a simple object table.

Table 3-9 Customer

Column	Data Type
CUSTOMER	CUST_TYPE
SOURCE	VARCHAR(50)

Where CUSTOMER column is of data type CUST_TYPE.

Table 3-10 CUST_TYPE

Column	Data Type
ID	NUMBER
NAME	VARCHAR(20)
ADDRESS	ADDR_TYPE

Where ADDRESS column is of data type ADDR_TYPE.

Table 3-11 ADDR_TYPE

Column	Data Type
STREET	VARCHAR(20)
CITY	VARCHAR(20
STATE	VARCHAR(20)

When imported into iStudio, this table appears as shown in Table 3-12.

Table 3-12 Customer

Attribute	Data Type
CUSTOMER	CUST_TYPE
ID	DOUBLE
NAME	STRING
ADDRESS	ADDR_TYPE
STREET	STRING
CITY	STRING
STATE	STRING
SOURCE	STRING

Example 3-6 Complex Data Type that includes Nested Table

Table 3-13 shows a simple object table with a column Orders of data type CLOB

Table 3-13 Business_Contacts

Column	Data Type
INDUSTRY	VARCHAR(20)
CUSTOMER_LIST	CUSTOMERS

where Customer_List column is of Customers data type.

Table 3-14 Customers Table

Column	Data Type
ID	DOUBLE
NAME	STRING
ADDRESS	ADDR_TYPE

where ADDRESS column is of ADDR_TYPE data type.

Table 3-15 ADDR_TYPE

Column	Data Type
STREET	STRING
CITY	STRING
STATE	STRING

When imported into iStudio, the Business_Contacts table appears as shown in Table 3-16.

Table 3-16 Business_Contacts

Column	Data Type
INDUSTRY	VARCHAR(20)
CUSTOMER_LIST	CUSTOMERS (array)
ID	DOUBLE
NAME	STRING
ADDRESS	ADDR_TYPE
STREET	STRING
CITY	STRING
STATE	STRING

Importing Oracle Objects and Advanced Queuing Payloads

Importing an Oracle Object or an Advanced Queuing payload in iStudio is similar to importing database tables. Importing from an Advanced Queuing payload is necessary when working with Advanced Queuing applications.

Note:

When importing an Advanced Queuing payload, it may be necessary to log in as the system user.

Returned In Arguments

The Returned In Args button appears only in the Invoke wizard. Returned In Arguments is used to propagate INOUT attributes contained in the request. If this feature does not exist, then you have to ensure that these attributes exist in both the common view and application view of the implementing application and are INOUT attributes. It would also be necessary to complete all the mappings to copy these attributes on their way out and back in, when receiving the reply. Returned In Args can also be used to correlate the reply with an asynchronous request.

For example, a Customer object looks like the following in the application view:

Customer
  Name
  ID
  Contact
    Address
      City
      State
      Zip
    Phone
      AreaCode
      PhoneNumber

If this is to be sent as part of a CreateCustomer message and ID is to be INOUT in both the request and the reply, then it should be an INOUT parameter. To do this, complete the following steps:

Click Returned In Args on the Invoke wizard.
Select ID in the Please Select In Arguments dialog box and the select Out Arguments option.

Deploying PL/SQL Code

If the Database adapter is used to connect to an application, then iStudio generates PL/SQL stored procedures. These stored procedures enable an application to interface with OracleAS Integration InterConnect through the Oracle database. This code is generated regardless of the integration point used, which is the event for publish/subscribe or procedure for request/reply, and must be deployed in the application schema to be executed at run time. To deploy PL/SQL code, use the Deploy PL/SQL context menu in iStudio.

Database Adapter Run-Time Concepts

The following section describes the run-time concepts pertinent to the Oracle9i Database Server.

How the Database Adapter Works

The following topics describe how the Database adapter works.

Database Sender

The Database adapter comprises of the database bridge and the run-time agent. The bridge is constantly polling the MESSAGEOBJECTTABLE table in the oai schema, specified by the db_bridge_schema1_username parameter. A new row in this table indicates a new outbound OracleAS Integration InterConnect message waiting to be sent by this adapter. The adapter then picks up the message from the interface tables residing in the oai schema, builds the corresponding OracleAS Integration InterConnect message, persists it, transforms it to the common view, and routes it to the hub. From the hub, the message gets routed to the corresponding subscriber based on configuration completed in iStudio, which can be content-based or subscription-based.

The application and the database adapter communicate through the interface tables residing in the oai schema for outbound messages and through iStudio PL/SQL generated procedures for inbound messages. Thus, if the adapter is down while the application is publishing OracleAS Integration InterConnect messages using the iStudio generated PL/SQL procedures, then the messages are held in the interface tables and will be picked up in a FIFO method by the database adapter once it is up and running. If there are messages in the interface tables that no longer need to be published, then the DELETE FROM MESSAGEOBJECTTABLE using SQLPlus can be run in the oai schema.

Database Receiver

On the subscribing or receiving side, the Database adapter receives the message from the hub, transforms it from common view to application view, and passes it to the bridge, which calls the corresponding PL/SQL procedures to inform the application about the newly arrived message. If this adapter were an implementing application, then the OUT arguments from the PL/SQL procedure are put together, and the REPLY, in the form of another OracleAS Integration InterConnect message, is sent back to the INVOKER or REQUESTER.

The receiving adapter is responsible for creating any necessary cross-reference entries. In a publish-subscribe scenario, the subscribing adapter creates the cross-reference entry using the returned arguments, for example OUT, from the subscribe-side procedure.

Note:

The adapter subscribing to an event should be started before any other adapter can publish that event. If you publish an event before starting the subscribing adapter, then the event would not be delivered to the subscribing adapter.

Starting the Database Adapter

Based on the operating system, the process for starting the adapter varies.

To start the Database adapter on UNIX:
1. Change to the directory containing the start script.
```
cd ORACLE_HOME/integration/interconnect/adapters/Application
```
2. Type start and press Enter.
To start the Database adapter from Services on Windows:
1. Access the Services window from the Start menu.
2. Select the OracleHomeOracleASInterConnectAdapter-Application service.
3. Start the service based on the operating system.
Note:
You can also start and stop the Database adapter using the IC Manager. Refer to Oracle Application Server Integration InterConnect User's Guide for more details.

Log File of Database Adapter

You can verify the start up status by viewing the log.xml files. The files are located in the time-stamped subdirectory of the log directory of the Database adapter. Subdirectory names take the following form:

timestamp_in_milliseconds

The following is an example of the information about a Database adapter that started successfully.

The Adapter service is starting.. 
Registering your application (DBAPP).. 
Initializing the Bridge oracle.oai.agent.adapter.database.DBBridge
Starting the Bridge oracle.oai.agent.adapter.database.DBBridge
Service started successfully. 
db_bridge_writer_1 has been started.
db_bridge_reader_1 has been started.
db_bridge_writer_1 has connected to the database successfully.
db_bridge_reader_1 has connected to the database successfully.

Stopping the Database Adapter

Based on the operating system, the process for stopping the adapter varies.

To stop the Database adapter on UNIX:
1. Change to the directory containing the stop script.
```
cd ORACLE_HOME/integration/interconnect/adapters/Application
```
2. Type stop and press Enter.
To stop the Database adapter from Services, on Windows:
1. Access the Services window from the Start menu.
2. Select the OracleHomeOracleASInterConnectAdapter-Application service.
3. Stop the service. Based on the operating system, the method for stopping it varies.
You can verify the stop status of the Database adapter by viewing the log.xml files. These files are located in the time-stamped subdirectory of the log directory of the Database adapter.