Siebel Connector for Oracle Applications > Developer Procedures > Understanding the Database Adapter >

Methods and Arguments

The DB Adapter business service, also known as the EAI SQL Adapter, incorporates methods that are used to perform various tasks (queries, data inserts, updates, and so on). Each method can have one or more arguments that are used to further define the method's action. An argument typically consists of data or an object that the method processes.

Five methods may be used with the DB Adapter:

• Query
• Delete
• Upsert
• Synchronize
• Execute

Query

The DB Adapter uses the Query method to query data from an external database based on a SQL integration object and returns the corresponding integration object instances.

Query takes a QBE (Query By Example) instance as input and returns one or more output objects. If the component field values are set in the input, the values will be used in the where clause of the generated SQL. A blank search specification at the root level will query for all the rows from the table corresponding to the root component.

Parent-child relationships are determined by foreign key definitions in the integration object. The Adapter expects the foreign key of a child component to refer to a target key in the parent component.

Arguments

Query arguments are listed in Table 12.

NOTE:  This table format will be used for all method arguments described in this book.

Delete

The Delete method is used to delete a hierarchy on the external database that is based upon an integration object. Delete takes a QBE instance as input and deletes the entire hierarchy rooted at the specified root component instance. The search specification is allowed only at the root level. If no search specification is provided, Delete removes all rows from the table corresponding to the root component. The CascadeDelete property is specified in the integration object definition at each component level. If this property is set, that component is also deleted when its parent is deleted. Here too, parent-child relationships are determined by the foreign key definition in each component.

Arguments

Delete takes only one argument, defined in Table 13.

Upsert

The Upsert method is used to insert and/or update data into the external database based on the input integration object instances. Upsert performs an UPDATE or an INSERT at each component level, depending upon whether the row already exists or not in the database. The input to the upsert method is the actual integration object instance data.

The DB Adapter uses a combination of two algorithms to upsert data, depending upon the ratio of the number of database rows to the number of component instances in the input instance. This optimization is turned on by default. If the number of database rows is small, it is efficient to query for all rows (of a given parent) and try to match them in memory. If there are a large number of database rows, it is more efficient to query the database for each input component instance, to determine whether the corresponding rowset exists in the database.

Upsert supports multiple user key specification to find the matching row in the database. Each user key is tried in sequence to determine whether or not the rowset exists in the database. If none of the specified user key fields have their values set, an error is returned. A null value for any of the user key fields is valid.

Arguments

Upsert takes a single argument, defined in Table 14.

Synchronize

Synchronize makes the values of an external database match the values of an integration object instance by performing an Update, Insert, or Delete on the external tables. The Synchronize method is similar to Upsert except that deletes are performed on database rows where corresponding component instances are not present in the input integration objects.

Arguments

Synchronize takes a single argument, defined in Table 15.

Execute

The DB Adapter Execute method is used to perform the operations listed below on an integration object. Any operation can be specified at the component level by using the Op Code.

• Delete
• Upsert
• Synchronize

When Execute performs a Delete or Synchronize on a component, all operations below that component are invalid and are ignored.

Arguments

Execute takes only one argument, defined in Table 16.

Op Codes

Operation codes indicate the type of operation to be performed on an integration component. These codes are specified in the component instance; otherwise, they are inherited from the parent's component instance. Processing an integration component, the DB Adapter detects the operation code and performs the action indicated by the code. For example, when the upsert code is detected, the DB Adapter performs an UPSERT operation, starting at that component level in the hierarchy.

• upsert
• delete
• sync
• none

Operation codes are used with the Execute method to specify the operation at the component level.