How to: Reference: |
The standard event processing with row tracking technique enables you to listen to the source table without removing its rows. It requires you to create a single-cell control table that tracks the last new row the Table Listener read from the source table.
The single column of the control table corresponds to a column (or to a group of columns) in the source table that is unique, sortable, and indicates how recently the row was added to the source table relative to the other rows. For example, the first row added to the source table has the lowest value, and the last row added has the highest value. This value is called the event key.
When you create the control table, initialize it to the event key of the row most recently added to the source table. When you specify the listener properties, configure the SQL Post-query property of the listener to automatically update the control table event key.
Each time the listener queries the source table, it looks for rows added since the last query—that is, for rows whose event key is greater than the current value of the field in the control table. It reads each row of this type and returns it to the specific destination using an XML document. To ensure that the row is not read again the next time the listener queries the table, the listener updates the field in the control table to match the value of the row that was just read from the source table.
Note: Event processing may be limited for some non-relational databases due to the functionality of the database and its interaction with the iWay server component. For more information on the iWay server component, see the iWay Data Adapter Administrator User's Guide or consult with your DBA.
The following image illustrates standard event processing with row tracking.
In the previous figure:
The process repeats.
To implement standard event processing with row tracking:
In addition to the required listener properties for standard event processing with row tracking, you also must provide values for the following optional properties:
For detailed instructions about configuring a listener, see Creating a Channel. For information on post query parameters, see The Post-query Parameter Operators.
This example uses an Oracle E-Business Suite (also known as Oracle Applications) table. You can apply the same technique in a similar way to other types of relational databases.
You can follow the steps in this example to create an Oracle E-Business Suite table named TEMP_NEW_YORK_ORDER_ENTITY that has a single field named WIP_ENTITY_ID. You specify this table when you configure the Table Listener, as described in The Post-query Parameter Operators.
When discrete jobs are created through the Oracle E-Business Suite graphical interface, an entry is created in the WIP.WIP_DISCRETE_JOBS table. For this example, you configure an event to detect new entries to this table. You use the standard event processing with row tracking technique. (Oracle E-Business Suite processing cannot delete rows from the table.)
You first create a simple table to track the records processed.
CREATE TABLE WIP.TEMP_NEW_WORK_ORDER_ENTITY_ID ( WIP_ENTITY_ID NUMBER )
This creates a single table with a single field.
Note: Oracle SQL*Plus is part of the Oracle client software. If it is not installed, contact your Oracle Database Administrator.
You must be logged in under the APPS schema or a similar ID with access rights to the Oracle E-Business Suite WIP schema.
You can obtain this ID from the WIP.WIP_DISCRETE_JOBS table.
This sets the value at which to start detecting events as records enter the WIP_DISCETE_JOBS table.
When you configure a Table Listener, you can use two special field operators, ? and ^, with the SQL Post-query parameter. Both of these operators dynamically substitute database values in the SQL post-query statement at run time:
The ? operator is useful in UPDATE statements:
UPDATE table WHERE ?field
For example, the following statement
UPDATE Stock_Prices_Temp WHERE ?RIC
might be evaluated at run time as:
UPDATE Stock_Prices_Temp WHERE RIC = 'PG'
The ^ operator is useful in INSERT statements:
INSERT INTO table VALUES (^field1, ^field2, ^field3, ... )
For example, the following statement:
INSERT INTO Stock_Prices_Temp VALUES (^RIC, ^Price, ^Updated)
might be evaluated at run time as:
INSERT INTO Stock_Prices_Temp VALUES ('PG', 88.62, '2003-03-18 16:24:00.0')
In this example, you listen to the trans_event table using the row tracking technique and use last_trans as the control table that contains the last value of the primary key read from trans_event.
For more information on configuring a listener, see How to Create a File Channel.
last_trans is to contain a single value in a single row and must be set up prior to configuring the Table Listener. The last_trans column must have the same name as the primary key in the trans_event table. This key must be unique and sortable.
The table schemas for this example are:
Sample Schema foSQL> describe trans_event Name Null? Type ---------------------------------------------------------------- EVENT_ID NOT NULL NUMBER(38) LAST_NAME VARCHAR2(50) TRANS_ID CHAR(2) SQL> describe last_trans Name Null? Type --------------------------------------------------------------- EVENT_ID NUMBER
The last_trans single field value must contain the starting value of the primary key.
The listener generates XML response documents for each record found in the trans_event table with a primary key greater than the value found in the last_trans table.
When setting up the port, a specific path is configured for a disposition using the File protocol. A response document with the record data is deposited into the directory after the insert is made.
The following is an example of a response document for the listener deposited into a directory specified when the Port is configured.
<Oracle> <row> <EVENT_ID>1</EVENT_ID> <LAST_NAME>Kaplan</LAST_NAME> <TRANS_ID>03</TRANS_ID> </row> </Oracle>
Parameter | Description |
---|---|
Host | Name or URL of the machine on which the database is installed. |
Port | Port on which the Host database is listening. |
User Name | User name that is registered with the back-end RDBMS. |
Password | Password associated with the user name. |
SQL Query | SELECT * FROM TRANS_EVENT WHERE EVENT_ID>(select EVENT_ID from LAST_TRANS) |
Post Query | UPDATE LAST_TRANS SET ?EVENT_ID |
Polling Interval | Interval in seconds. |
iWay Software |