Business object verb processing

This section describes the following aspects of processing a business object's verbs:

Verb determination

A top-level business object and each of its individual child business objects can contain their own verbs. Therefore, a business object request can pass a business object that has different verbs for parent and child business objects to the connector. When this occurs, the connector uses the verb of the top-level parent business object to determine how to process the entire business object. For more information, see Verb processing.

After-images and deltas

An after-image is the state of a business object after all changes have been made to it. A delta is a business object used in an update operation that contains only key values and the data to be changed. Because the connector supports only after-images, when it receives a business object for update, the connector assumes that the business object represents the desired state of the data after update.

Therefore, a connector receives a business object with the Update verb, the connector changes the current representation of the business object in the application so that it exactly matches the source business object. To do this, the connector changes simple attribute values and adds or removes child business objects.

For example, assume the current state of Contract 2345 in the database is as follows:

Assume further that the connector receives the following business object:

To process the update, the connector applies the following changes to the database:

Because the connector assumes that each business object it receives represents an after-image, it is important that developers ensure that each business object sent to such a connector for updating contains valid existing child business objects. Even if none of a child business object's simple attributes have changed, the child business object must be included in the source business object.

There is a way, however, that you can prevent some connectors from deleting missing child business objects during an update operation. You can use the application-specific information for the attribute that represents the child or array of children to instruct the connector to keep child business objects that are not included in the source business object. To do so, set KEEP_RELATIONSHIP to true. For more information, see Specifying an attribute's foreign key.

Verb processing

This section outlines the steps the connector takes when creating, retrieving, updating, or deleting a business object. The connector processes hierarchical business objects recursively; that is, it performs the same steps for each child business object until it has processed all individual business objects.

Note:
A top-level business object that is a wrapper supports the create, retrieve, update and delete verbs. The only difference in processing a wrapper object is that the wrapper object is not processed, only the objects that it contains are processed.

Business object comparison

At various points in the processing outlined below, the connector compares two business objects to see if they are the same. For example, during an update operation, the connector determines whether a particular business object exists in an array of business objects. To perform the check, the connector compares the business object to each business object within the array. For two business objects to be identical, the following two conditions must be satisfied:

Create operations

When creating a business object, the connector returns a status either of VALCHANGE if the operation was successful (regardless of whether the operation caused changes to the business object), or FAIL if the operation failed.

The connector performs the following steps when creating a hierarchical business object:

  1. Recursively inserts each single-cardinality child business object contained with ownership in the application. In other words, the connector creates the child and all child business objects that the child and its children contain.

    If the business object definition specifies that an attribute represents a child business object with single cardinality and that attribute is empty, the connector ignores the attribute. However, if the business object definition requires that attribute to represent a child and it does not, the connector returns an error and stops processing.

  2. Processes each single-cardinality child business object contained without ownership as follows:
    1. Recursively attempts to retrieve the child from the database using the key values passed in by the business object request.
    2. If the retrieve is unsuccessful, indicating that the child does not currently exist in the application, the connector returns an error and stops processing. If the retrieve is successful, the connector recursively updates the child business object.

    Note:
    For this approach to work correctly when the child business object already exists in the application, developers must ensure that primary key attributes in child business objects are cross-referenced correctly on create operations. If the child business object does not already exist in the application database, set the primary key attributes to CxBlank.
  3. Inserts the top-level business object in the database as follows:
    1. Sets each of its foreign-key values to the primary-key values of the corresponding child business object represented with single cardinality. Because values in child business objects can be set by database sequences or counters or by the database itself during the creation of the child, this step ensures that the foreign-key values in the parent are correct before the connector inserts the parent in the database.
    2. Generates a new unique ID value for each attribute that is set automatically by the database. The name of the database sequence or counter is stored in the attribute's application-specific information. If an attribute has an associated database sequence or counter, the value generated by the connector overwrites any value passed in by the request. For more information on specifying a database sequence or counter, see UID=AUTO in Application-specific information for simple attributes.
    3. Copies the value of an attribute to the value of another attribute as specified by the CA (CopyAttribute) parameter of the attribute's application-specific information. For more information on using the CA parameter, see CA=set_attr_name in Application-specific information for simple attributes.
    4. Inserts the top-level business object into the database.

    Note:
    A top-level business object that is a wrapper will not be inserted into the database.
  4. Processes each of its single-cardinality child business objects that stores the parent/child relationship in the child, as follows:
    1. Sets the foreign-key values in the child to reference the value in the corresponding primary-key attributes in the parent. Because the parent's primary-key values may have been generated during the creation of the parent, this ensures that the foreign-key values in each child are correct before the connector inserts the child in the application.
    2. Inserts the child in the application.
  5. Processes each of its multiple-cardinality child business objects, as follows:
    1. Sets the foreign-key values in each child to reference the value in the corresponding primary-key attributes in the parent. Because the parent's primary-key values may have been generated during the creation of the parent, this ensures that the foreign-key values in each child are correct before the connector inserts the child in the application.
    2. Inserts each of its multiple-cardinality child business objects into the database.

Retrieve operations

The connector performs the following steps when retrieving a hierarchical business object:

  1. Removes all child business objects from the top-level business object that it received from the business object request.
  2. Retrieves the top-level business object from the application.

    Notes:

    1. A business object can have attributes that do not map to any database column, such as placeholder attributes. During retrieval, the connector does not change such attributes in the top-level business object; they remain set to the values received from the business object request. In child business objects, the connector sets such attributes to their default values during retrieval.

    2. A top-level business object that is a wrapper must contain any attribute values from the objects at the level immediately below the wrapper object, which would be necessary to retrieve the objects, including keys and placeholder attributes. The wrapper object must have all keys and placeholder attributes populated. Simple attributes in the wrapper object that will be used as foreign keys in the objects one level below the wrapper should be marked as keys in the wrapper object.
  3. Recursively retrieves all multiple-cardinality child business objects.
    Note:
    The connector does not enforce uniqueness when populating an array of business objects. It is the database's responsibility to ensure uniqueness. If the application returns duplicate child business objects, the connector returns duplicate children.
  4. Recursively retrieves each of the single-cardinality children regardless of whether the child business object is contained with or without ownership.

Note:
All single cardinality child business objects are processed based on occurrence in the business object and before the parent business object is processed. Child object ownership and non-ownership do not determine the processing sequence, but do determine the type of processing.

RetrieveByContent operations

A RetrieveByContent verb is applicable only for the top-level business object, because the connector performs a retrieval based on attributes only in the top-level business object.

If a top-level business object uses the RetrieveByContent verb, all of the attributes (including non-key attributes) that are not null are used as retrieval criteria.

If more than one row is returned, the connector uses the first row as the result row and returns MULTIPLE_HITS.

Note:
A RetrieveByContent verb is not applicable for a top-level business object that is a wrapper.

Update operations

When updating a business object, the connector returns a status either of VALCHANGE if the operation was successful (regardless of whether the operation caused changes to the business object), or FAIL if the operation failed.

The connector performs the following steps when updating a hierarchical business object:

  1. Uses the primary-key values of the source business object to retrieve the corresponding entity from the application. The retrieved business object is an accurate representation of the current state of the data in the database.
  2. Recursively updates all single-cardinality children of the top-level business object.

    If the business object definition requires that an attribute represent a child business object, the child must exist in both the source business object and the retrieved business object. If it does not, the update fails, and the connector returns an error.

    The connector handles single-cardinality children contained with ownership in one of the following ways:

    For single-cardinality children contained without ownership, the connector attempts to retrieve every child from the application that is present in the source business object. If it successfully retrieves the child, the connector populates the child business object, but does not update it; single-cardinality child objects that are contained without ownership are never modified by the connector.

  3. For single-cardinality child business objects that store the relationship in the parent, the connector sets each foreign-key value in the parent to the value of the primary key in the corresponding single-cardinality child business object. This step is necessary because single-cardinality children may have been added to the database during previous steps, resulting in the generation of new unique IDs.
  4. Updates all simple attributes of the retrieved business object except those whose corresponding attribute in the source business object contain the value CxIgnore.

    Because the business object being updated must be unique, the connector verifies that only one row is processed as a result. It returns an error if more than one row is returned.

  5. Sets all foreign-key values in each child that stores the parent/child relationship in the child (both multiple-cardinality and single-cardinality) to the primary-key value of its corresponding parent business object. Typically, when InterChange Server is the integration broker, these values have been cross-referenced during data mapping. However, this step is important to ensure that the foreign-key values of new children that store the relationship in the child are correct before the connector updates those children.
  6. Processes each multiple-cardinality child of the retrieved business object in one of the following ways:

Note:
The developer's code must ensure that business objects contained with multiple cardinality in the source business object are unique (that is, that an array does not contain two or more copies of the same business object). If the connector receives duplicates of a business object in a source array, it processes the business object twice, with possibly unpredictable results.

DeltaUpdate operations

DeltaUpdate verb processing is different from update verb processing as follows:

  1. On a DeltaUpdate no retrieve is done before updating, as is done in update verb processing.
  2. No comparisons are made between the incoming business object and the business object in the database.
  3. All children will be processed based on the verb set in each child object. If a child doesn't have a verb set in it, the connector will return an error.

When delta updating a business object, the connector returns a status of either VALCHANGE if the operation was successful (regardless of whether the operation caused changes to the business object) or FAIL if the operation failed.

The connector performs the following steps when delta updating a hierarchical business object:

  1. Recursively processes all single-cardinality children of the parent object. If a child is marked as IsRequired in the business object specification, it must be present in the inbound object. If not, the delta update will fail and the connector will return an error.
  2. Sets all foreign key values in the parent that reference attributes in single-cardinality children to their corresponding child values. This is necessary because single-cardinality children may have been added to the database during the previous steps, resulting in the generation of new sequence values.
  3. Updates the current object being processed via an SQL UPDATE statement or a stored procedure. All simple attributes of the individual business object are updated, except those attributes set to IsIgnore in the inbound business object. The connector does not compare the inbound object to the current object on an attribute level to determine which attributes need to be added to the update statement; they are all updated. Since the object being updated should be unique, the connector checks to make sure that only one row is processed as a result. An error is returned if more than one row is processed.
  4. Sets all foreign key values in all cardinality N children of the current object that reference parent attributes to the corresponding parent values. Usually these values will already be cross-referenced during data mapping; however, this may not be the case for new children in cardinality N containers. This ensures that the foreign key values in all cardinality N children are correct before those children are updated.
  5. Updates all cardinality N containers of the current object.

When the child objects are processed, each child's verb is taken and the appropriate operation is done. The allowed verbs on a child in DeltaUpdate are Create, Delete and DeltaUpdate.

Delete operations

When deleting a business object, the connector returns a status of SUCCESS if the operation was successful or FAIL if the operation failed. The parent business object is first retrieved and then the adapter recursively deletes all single-cardinality children that have an ownership relationship to the parent, then the parent business object itself, and finally all cardinality N children. Single-cardinality no-ownership children are never deleted. If the business object does not exist, the connector returns a FAIL.

The connector supports logical and physical deletes, depending on the Status Column Name (SCN) value in the object's application-specific information. If the SCN value is defined, the connector performs a logical delete. If the SCN value is not defined, the connector performs a physical delete.

Physical deletes

The connector performs the following steps when physically deleting a hierarchical business object:

  1. Recursively deletes all single-cardinality child business objects contained with ownership.
  2. Deletes the top-level business object.
  3. Recursively deletes all multiple-cardinality child business objects.

Note:
A top-level business object that is a wrapper does not have a corresponding database table, hence it will not be deleted from the database. Any simple attribute values for a wrapper will be ignored.

Logical deletes

When logically deleting a business object, the connector performs the following steps:

  1. Issues an UPDATE that sets the business object's status attribute to the value specified by the business object's application-specific information. The connector ensures that only one database row is updated as a result, and it returns an error if this is not the case.
  2. Recursively logically deletes all single-cardinality children contained with ownership and all multiple-cardinality children. The connector does not delete single-cardinality children contained without ownership.

Using SQL statements

The connector can use simple SQL statements for select, update, retrieve or delete operations. The column names for SQL statements are derived from an attribute's AppSpecificInfo property. Each query spans one table only, unless posted to a view.

Using stored procedures

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. A stored procedure encapsulates a set of operations or queries for the connector to execute on an object in a database server. An attribute's AppSpecificInfo property is used to pass parameters to stored procedures.

The connector calls stored procedures in the following circumstances:

When it processes a hierarchical business object, the connector can use a stored procedure to process the top-level business object or any of its child business objects. However, each business object or array of business objects must have its own stored procedure.

Specifying a stored procedure

This section describes the steps you must perform to cause the connector to use a stored procedure for a business object. It contains the following sections:

Adding attributes to the business object

You must add a special kind of attribute to the business object for each type of stored procedure that the connector processes. These attributes represent only the stored procedure's type and the application-specific information that defines it. These attributes do not use the application-specific information parameters available for a standard simple attribute.

Name the attribute according to the type of stored procedure to be used. For example, to cause the connector to use AfterUpdate and BeforeRetrieve stored procedures, add the AfterUpdateSP and BeforeRetrieveSP attributes.

The connector recognizes the following business object attribute names:

BeforeCreateSP
AfterCreateSP
CreateSP
BeforeUpdateSP
AfterUpdateSP
UpdateSP
BeforeDeleteSP
AfterDeleteSP
DeleteSP
BeforeRetrieveSP
AfterRetrieveSP
RetrieveSP
BeforeRetrieveByContentSP
AfterRetrieveByContentSP
RetrieveByContentSP
BeforeRetrieveUpdateSP
AfterRetrieveUpdateSP
RetrieveUpdateSP
BeforeDeltaUpdateSP
AfterDeltaUpdateSP
DeltaUpdateSP

Note:
Create an attribute only for those stored procedures that you want the connector to execute. For example, when InterChange Server is the integration broker, use the application-specific information or mapping to specify values for these attributes before the business object is sent to the connector. The connector must be restarted to recognize changes to these values for subsequent calls on a business object.

Syntax of a stored procedure

The syntax for specifying a stored procedure is:

SPN=StoredProcedureName;RS=[true|false]
[;IP=Attribute_Name1[:Attribute_Name2
[:...]]][;OP=Attribute_Name1|RS[:Attribute_Name2|RS
[:...]]][;IO=Attribute_Name1[:Attribute_Name2[:...]]]

where:

StoredProcedureName
The name of the stored procedure.

RS
Result Set: If true, the stored procedure returns a result set, else false if no result set is returned. The default is false. If the value is true, the ColumnName property in an attribute's application-specific information points to the appropriate column in the result set.

IP
Input Parameters: The list of business object attributes whose values the connector should use as input values when executing the stored procedure.

OP
Output Parameters: The list of business object attributes to which the connector should return values after executing the stored procedure.

IO
InputOutput Parameters: The list of business object attributes whose values the connector should use as input values and to which the connector should return values after executing the stored procedure.

The property ReturnDummyBOForSP is used to return output parameters even when the result set is true and empty. In the case of RetrieveSP, a result set is returned. If the result set is empty, no business objects are created and there is no way to retrieve the output parameters returned by the procedure call. If ReturnDummyBOForSP is true, a dummy business object with values from the output and input/output parameters populated in the corresponding attributes will be returned. The default vlaue for this property is false.

The order of the SPN and RS parameters is important; the order of the other parameters is not syntactically important. In other words, it makes no difference to the connector if the stored procedure groups all parameters of each type or intersperses the types of parameters. When multiple parameters of the same type are grouped together, separate the values with a colon delimiter; you need not repeat the parameter's type for each value. Separate parameters of different types with a semicolon delimiter. When specifying parameter values, do not put a blank space on either side of the equal sign (=).

Examples of stored procedures that return result sets

The following example uses a stored procedure named CustomerAddressRetrieve to return a result set that contains multiple addresses and is used to create an n-cardinality child business object.

Note:
Result sets are processed for the attribute RetrieveSP only, and they are used to create an n-cardinality child business object.

CustomerAddressRetrieve

Attribute : RetrieveSP
ASI : SPN=CustomerAddressRetrieve;RS=true;IP=CustomerName:IP=Customerld;
OP=ErrorStatus;OP=ErrorMsg

The following example uses a SHADOW_ACI stored procedure call to provide direct access to a Natural database. The example also describes the corresponding RetrieveSP application-specific information.

CALL SHADOW_ACI('SEND','ACISP03,SPO3MIN1,SP03MOT1','FIND','JONES')
AppSpecificInfo = SPN=shadow_aci;RS=true;IP=FuncC:SerInpOutC:Param1C:Param2C

FuncC corresponds to attributes of the child and represents the input parameters.

Examples of stored procedures that do not return result sets

The following examples use stored procedures named CustomerInsert and VendorInsert that get values from two input attributes, and return values to four output attributes. The examples illustrate different structures for stored procedures.

The connector supports only the simple data types supported by the JDBC driver.

Specifying the stored procedure

There are two ways to specify the stored procedure name and its parameter values:

Note:
If a stored procedure that handles a create, update, or delete operation is executed on a hierarchical business object containing an array of child business objects, the connector processes each child business object individually. For example, if the connector executes a BeforeCreate stored procedure, it does not process the array as a unit but processes each member in the array. When it processes a BeforeRetrieve stored procedure, the connector operates on a single business object. When it processes an AfterRetrieve stored procedure, the connector operates on all business objects returned by the retrieval.

Processing business objects using stored procedures or simple SQL statements

The following sections explain how the connector processes stored procedures and SQL statements:

Business object create operations

A Create stored procedure usually returns values that the connector uses to populate the simple attributes in the top-level business object. The connector performs the following steps when processing a business object Create operation using SQL statements and stored procedures (BeforeCreate, Create, AfterCreate):

  1. Checks whether the business object contains a BeforeCreateSP attribute. If it does, calls the BeforeCreate stored procedure.
  2. If the stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  3. Creates the single-cardinality child business objects.
  4. Sets each of the top-level business object's foreign key values to the primary-key value of each single-cardinality child business object.
  5. Checks whether the business object contains a CreateSP attribute. If it does, calls the Create stored procedure to create the top-level business object. If it does not, builds and executes an INSERT statement to create the top-level business object.
  6. If the Create stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  7. Sets the foreign-key value in each multiple-cardinality child to the value of its parent's primary-key attribute.
  8. Creates the multiple-cardinality child business objects.
  9. Checks whether the business object contains an AfterCreateSP attribute. If it does, calls the AfterCreate stored procedure.
  10. If the stored procedure returns values through output parameters, uses the values to set the values of simple attributes in the business object.

The connector can use values returned in step 10 to change the values of a business object that it created in steps 3 or 5.

Business object update operations

An Update stored procedure usually returns values that the connector uses to populate the simple attributes in the top-level business object. The connector performs the following steps when processing the business object Update operation using SQL statements and stored procedures (BeforeUpdate, Update, AfterUpdate):

  1. Checks whether the business object contains a BeforeUpdateSP attribute. If it does, calls the BeforeUpdate stored procedure.
  2. If the BeforeUpdate stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  3. Updates the single-cardinality child business objects.
  4. Sets each of the top-level business object's foreign-key values to the primary-key value of each child business object contained with single cardinality.
  5. Checks whether the business object contains an UpdateSP attribute. If it does, calls the Update stored procedure to update the top-level business object. If it does not, builds and executes an UPDATE statement to update the top-level business object.
  6. If the Update stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  7. Sets foreign-key values in the multiple-cardinality children to reference the value in the corresponding primary-key attributes in the parent.
  8. Updates the multiple-cardinality child business objects.
  9. Checks whether the business object contains an AfterUpdateSP attribute. If it does, calls the AfterUpdate stored procedure.
  10. If the stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.

Business object delete operations

A Delete stored procedure does not return values to the connector. The connector performs the following steps when processing the business object Delete operations using simple SQL statements and stored procedures (BeforeDelete, Delete, AfterDelete):

  1. Checks whether the business object contains a BeforeDeleteSP attribute. If it does, calls the BeforeDelete stored procedure.
  2. Deletes the single-cardinality child business objects.
  3. Deletes the multiple-cardinality child business objects.
  4. Checks whether the business object contains a DeleteSP attribute. If it does, calls the Delete stored procedure to delete the top-level business object. If it does not, builds and executes a DELETE statement.
  5. Checks whether the business object contains an AfterDeleteSP attribute. If it does, calls the AfterDelete stored procedure.

Business object retrieve operations

For simple Retrieve operations, stored procedures can be used for the top-level business object, single cardinality children, and multiple cardinality children. The order of the procedures is as follows:

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. The connector applies the BeforeRetrieve stored procedure to the temporary business object. The AfterRetrieve stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieve stored procedure after it executes a Retrieve query generated dynamically from the business object metadata or stored procedure on the business object.

According to the JDBC specification there are three types of StoredProcedure calls as follows:

The connector supports the first two types. It will process the ResultSet that is returned from StoredProcedure.

In the stored procedure syntax, if RS=true, the result set from the stored procedure is processed. If RS=false, the result set is not processed. By default the value of RS is false. After the result set values are processed, the stored procedure output variables are processed. If RS=true, multiple cardinality children cannot specify the output variables in the related stored procedure.

Note:
Result set processing is supported only for Retrieve verb operations and for RetrieveSP .

Processing result set returned from retrieve stored procedure (RetrieveSP):

ResultSetMetaData is obtained for the result set returned from the stored procedure. Values of all the columns in the result set are obtained and set on the corresponding attribute of the business object. The ColumnName property of an attribute's application-specific information should contain the ResultSet column name to match the attribute to the column.

For single cardinality objects, the corresponding result set should consist of only one row. If multiple rows are returned in the result set, an error is reported.

For multiple cardinality children, multiple rows can be returned through the result set. For each row returned, a new object is created and added to the container. The container is then added to the parent object at the required attribute index.

The n-cardinality child of a wrapper business object has stored procedure attributes, and the attributes represent the input parameters and the result set columns. WRAPPER=true is set at the business object application-specific information level. The child business object application-specific information will have TN=dummy.

Business object RetrieveByContent operations

For simple RetrieveByContent operations, stored procedures can be used only for the top-level business object and its single-cardinality children; that is, they cannot be used to return a result set or multiple rows. The order of the procedures is as follows:

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. For multiple cardinality business objects, the connector applies the BeforeRetrieveByContent stored procedure to the temporary business object. The AfterRetrieveByContent stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieveByContent stored procedure after it executes a RetrieveByContent query generated dynamically from the business object metadata or stored procedure on the business object. In this case, even though the retrieval of a hierarchical business object also retrieves its child business objects, the connector executes the AfterRetrieveByContent stored procedure on every business object present in the array.

Business object Retrieve-for-Update operations

The following stored procedures are called on the top-level business object and retrieve all child business objects in the same way as the simple Retrieve.

The order of the procedures is as follows:

These stored procedures perform the same operations as BeforeRetrieve and AfterRetrieve. They have distinguishing names so that you can create separate attributes to cause the connector to perform both BeforeRetrieve and BeforeRetrieveUpdate operations, as well as AfterRetrieve and AfterRetrieveUpdate operations.

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. For multiple cardinality business objects, the connector applies the BeforeRetrieveUpdate stored procedure to the temporary business object. The AfterRetrieveUpdate stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieveUpdate stored procedure after it executes a RETRIEVE query generated dynamically from the business object metadata or stored procedure on the business object. In this case, even though the retrieval of a hierarchical business object also retrieves its child business objects, the connector executes the AfterRetrieveUpdate stored procedure on every business object present in the array.

Transaction commit and rollback

Whenever the connector receives a business object for processing, it begins a transaction block. All SQL statements that the connector executes while processing that business object are encapsulated within the transaction block. When the connector finishes processing the business object, it commits the transaction block if the processing was successful, or rolls back the transaction if it encountered an error.

Copyright IBM Corp. 1997, 2004