This section describes the following aspects of processing business object
verbs:
- Verb determination, which explains how the connector determines the verb to use
for each source business object
- After-images and deltas, which defines the terms and explains how the connector
works with after-images
- Verb processing, which explains the steps the connector takes when creating,
retrieving, updating, or deleting a business object
- SQL statements, which explains how the connector uses simple SQL statements
for selecting, updating, retrieving, or deleting business objects.
- Stored procedures, which explains how the connector uses stored procedures
- Transaction commit and rollback, which briefly explains how the connector uses transaction
blocks
A top-level business object and each of its individual child business
objects can contain its own verbs. Therefore, an integration broker 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.
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 updating, the connector
assumes that the business object represents the desired state of the data
after the update.
Therefore, when an integration broker sends a business object with the
Update verb to the connector, the connector changes the current representation
of the business object in the database 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 shown in the following diagram that has top-level Contract and Address
business objects; attributes of contract_id, address_data, phone_data,
and child_data; and child business objects A through G.:

Assume further that the integration broker passes the following business
object to the connector, which causes the changes described next:

To process the update operation,
the connector applies the following changes to the database:
- It updates the simple attributes in the top-level Contract and Address
business objects
- It creates the Phone business object
- It updates the simple attributes in the child business objects A, B, F,
and G
- It deletes the child business objects C, D, and E
- It creates the child business objects H, I, and J
Because the connector assumes that each business object it receives from
the integration broker represents an after-image, it is important to 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, to prevent some connectors from deleting missing
child business objects during an update operation. The
application-specific information for the attribute that represents the child
or array of children can be used to instruct the connector to keep child
business objects that are not included in the source business object.
This is done by setting KEEP_RELATIONSHIP to
true. For more information, see Specifying an attribute's foreign key.
This section outlines the steps the connector takes when creating,
retrieving, updating, or deleting a business object that it receives from an
integration broker. 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 wrapper business object supports the create, retrieve, update,
and delete verbs. A wrapper object is not processed; only the
objects that it contains are processed.
At various points in the business object verb 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:
- The type of the business objects being compared must be the same.
For example, a Customer business object is never considered identical to a
Contact business object even if all of their attributes are exactly the
same.
- All corresponding key attributes in the two business objects must contain
identical values. If a key attribute is set to CxIgnore in
both business objects, the connector considers them identical. However,
if a key attribute is set to CxIgnore in one business object but
not in the other, the business objects are not identical.
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:
- It recursively inserts each single-cardinality child business object
contained with ownership into the database. 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 athe attribute represent a child and it does not, the
connector returns an error and stops processing.
- It processes each single-cardinality child business object contained
without ownership as follows:
- It recursively attempts to retrieve the child from the database using the
key values passed in by the integration broker.
- If the retrieve operation is unsuccessful, indicating that the child does
not exist in the database, the connector returns an error and stops
processing. If the retrieve operation is successful, the connector
recursively updates the child business object.
- Note:
- For this approach to work correctly when the child business object exists in
the application database, primary key attributes in child business objects
must be cross-referenced correctly on create operations. If the child
business object does not exist in the application database, the primary key
attributes must be set to CxBlank.
- It inserts the top-level business object in the database as follows:
- It 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.
- It 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 integration broker. For more information on specifying a
database sequence or counter, see UID=AUTO in Application-specific information for simple attributes.
- It 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.
- It inserts the top-level business object into the database.
- Note:
- If a top-level business object is a wrapper, it is not inserted into the
database.
- It processes each of its single-cardinality child business objects that
stores the parent-child relationship in the child as follows: It s
- ets 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 into the database.
- It inserts the child into the database.
- It processes each of its multiple-cardinality child business objects as
follows:
- It 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 into the database.
- It inserts each of its multiple-cardinality child business objects into
the database.
The connector performs the following steps when retrieving a hierarchical
business object:
- It removes all child business objects from the top-level business object
that it received from the integration broker.
- It retrieves the top-level business object from the database.
- If the retrieval returns one row, the connector continues
processing.
- If the retrieval returns no rows, indicating that the top-level business
object does not exist in the database, the connector returns
BO_DOES_NOT_EXIST.
- If the retrieval returns more than one row, the connector returns
FAIL.
Notes:
- A business object can have attributes that do not correspond 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 integration
broker. In child business objects, the connector sets such attributes
to their default values during retrieval.
- A top-level wrapper business object must contain any attribute values from
the objects at the level immediately below the wrapper object, which are
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 are
used as foreign keys in the objects one level below the wrapper must be marked
as keys in the wrapper object.
- It 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 database returns duplicate child business objects,
the connector returns duplicate children.
- 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 their
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 they do determine the type of processing.
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 a MULTIPLE_HITS message.
- Note:
- A RetrieveByContent verb is not applicable for a top-level wrapper business
object.
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. When working with an Oracle database, the
connector locks data while retrieving it to ensure data integrity.
The connector performs the following steps when updating a hierarchical
business object:
- It uses the primary-key values of the source business object to retrieve
the corresponding entity from the database. The retrieved business
object is an accurate representation of the current state of the data in the
database.
- If the retrieval fails, indicating that the top-level business object does
not exist in the database, the connector returns BO_DOES_NOT_EXIST
and the update fails.
- Note:
- A top-level wrapper business object does not have to exist in the
database. However, it must contain the attribute values from the
objects at the level immediately below the wrapper object, which are 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 must be marked as keys in the wrapper
object.
- If the retrieval succeeds, the connector compares the retrieved business
object to the source business object to determine which child business objects
require changes in the database. The connector does not, however,
compare values in the source business object's simple attributes to those
in the retrieved business object. The connector updates the values of
all non-key simple attributes.
If all the simple attributes in the top-level business object represent
keys, the connector cannot generate an update query for the top-level business
object. In this case, the connector logs a warning and continues to
step 2.
- It 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 operation
fails, and the connector returns an error.
The connector handles single-cardinality children contained with ownership
in one of the following ways:
- If the child is present in both the source and the retrieved business
objects, instead of updating the existing child in the database, the connector
deletes the existing child and creates the new child.
- If the child is present in the source business object but not in the
retrieved business object, the connector recursively creates it in the
database.
- If the child is present in the retrieved business object but not in the
source business object, the connector recursively deletes it from the
database. The type of delete operation, physical or logical, depends on
the value of its ChildUpdatePhyDelete property.
For single-cardinality children contained without ownership, the connector
attempts to retrieve every child from the database 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, because
single-cardinality children contained without ownership are never modified by
the connector.
- 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.
- It 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.
- It 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.
(When InterChange Server is used as the integration broker, these values are
typically cross-referenced during data mapping.) 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.
- It processes each multiple-cardinality child of the retrieved business
object in one of the following ways:
- If the child exists in both the source and the retrieved business
objects' arrays, the connector recursively updates it in the
database.
- If the child exists in the source array but not in the retrieved business
object's array, the connector recursively creates it in the
database.
- If the child exists in the retrieved business object's array but not
in the source array, the connector recursively deletes it from the database
unless the application-specific information for the attribute that represents
the child in the parent has KEEP_RELATIONSHIP set to
true. In this case, the connector does not delete the child
from the database. For more information, see Specifying an attribute's foreign key. The type of delete operation, physical or logical,
depends on the value of its ChildUpdatePhyDelete property.
- Note:
- The integration broker 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 unpredictable
results.
DeltaUpdate verb processing is different from Update verb processing as
follows:
- In a DeltaUpdate operation, no retrieve operation occurs before updating,
as is done in Update verb processing.
- No comparisons are made between the incoming business object and the
business object in the database.
- All children are processed based on the verb set in each child
object. If a child does not have a verb set in it, the connector
returns an error.
When updating a business object with DeltaUpdate, the connector returns a
status of either VALCHANGE if the operation was successful
(regardless of whether the operation changed the business object) or
FAIL if the operation failed.
The connector performs the following steps when updating a hierarchical
business object with DeltaUpdate:
- It 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 it is not,
the DeltaUpdate operation fails and the connector returns an error.
- It 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.
- It updates the current object being processed using 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. Because the object being updated
must 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.
- It 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 are already cross-referenced during data
mapping; however, this may not be the case for new children in
cardinality N containers. This step ensures that the foreign-key values
in all cardinality N children are correct before those children are
updated.
- It 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 operations are Create, Delete, and DeltaUpdate.
- If a Create verb is found in the child, the child is created in the
database if it is an ownership child. Non-ownership children are
retrieved to validate their existence in the database.
- If a Delete verb is found in the child, that child is deleted.
- If a DeltaUpdate verb is found in the child, the child is updated in the
database.
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 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 FAIL.
The connector supports logical and physical delete operations, 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 operation. If the SCN value is not defined, the
connector performs a physical delete operation.
The connector performs the following steps when physically deleting a
hierarchical business object:
- It recursively deletes all single-cardinality child business objects
contained with ownership.
- It deletes the top-level business object.
- It recursively deletes all multiple-cardinality child business
objects.
- Note:
- A top-level wrapper business object
does not have a corresponding database table, so it is not deleted from the
database. Any simple attribute values for a wrapper are ignored.
When logically deleting a business object, the connector performs the
following steps: It
- issues an Update operation 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.
- It 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.
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.
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.
The connector calls stored procedures in the following circumstances:
- Before processing a business object, to perform preparatory operational
processes
- After processing a business object, to perform post-operational processes
- To perform a set of operations on a business object, instead of using a
simple INSERT, RETRIEVE, UPDATE, or
DELETE statement
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.
This section describes the syntax of stored procedures for business
objects. It also includes examples. It contains the following
sections:
A special kind of attribute must be added 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.
The attribute is named according to the type of stored procedure to be
used. For example, to set up 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. Use the application-specific information or
mapping (only if InterChange Server is used as the integration broker) to
specify values for these attributes before the business object is sent to the
connector. Restart the connector so that it recognizes changes to these
values for subsequent calls on a business object.
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
- Is true if the stored procedure returns a result set or
false if it does not. The default value 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. If RS
is part of the output parameter list, then that particular parameter returns a
result set. Only one result set OUT parameter is
supported. If more than one result set is returned as an OUT
parameter, only the first result set is returned and all others are
ignored. Currently, this feature is supported for Oracle 8i and later,
for stored procedures that use the Oracle JDBC Driver. For the stored
procedure in the database, the corresponding parameter should return a
REFCURSOR type.
- IP
- Input Parameters: The list of business object attributes whose
values the connector uses as input values when executing the stored
procedure.
- OP
- Output Parameters: The list of business object attributes to which
the connector returns values after executing the stored procedure. See
RS for a description of the result set.
- IO
- InputOutput Parameters: The list of business object attributes whose
values the connector uses as input values and to which the connector returns
values after executing the stored procedure.
The property ReturnDummyBOForSP returns output parameters even
when the result set is true but 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 is returned. The default value for this
property is false.
The order of StoredProcedureName, RS, and parameters
is important; however, the order of parameters among themselves is not
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, the values are separated by a colon delimiter; and the
parameter's name for each value is not repeated. Parameters of
different types are separated by a semicolon delimiter. Parameter
values contain no spaces on either side of the equal sign (=).
The following examples use stored procedures named
CustomerAddressRetrieve and
CustomerAddressRetrieveForOracleDB 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.
For Oracle databases, the result set is returned as an Output parameter and
is processed accordingly by the adapter. For other databases, the
result set is a return value from the stored procedure.
- CustomerAddressRetrieve (For databases other than Oracle)
Attribute : RetrieveSP
ASI : SPN=CustomerAddressRetrieve;RS=true;
IP=CustomerName:IP=Customerld;
OP=ErrorStatus;OP=ErrorMsg
- CustomerAddressRetrieveForOracleDB (For Oracle databases)
Attribute : RetrieveSP
ASI : SPN=CustomerAddressRetrieveForOracleDB;
RS=true;IP=CustomerName:IP=Customerld;
OP=RS;OP=ErrorStatus;OP=ErrorMsg
(OP=RS signifies that the first output parameter is a result
set)
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.
- Parameters of the same type are grouped together (IP, IP, OP, OP, OP, OP,
IO):
SPN=CustomerInsert;RS=false;IP=LastName:FirstName;OP=CustomerName:
CustomerID:ErrorStatus:ErrorMessage;IO=VendorID
- Parameters of the same type are interspersed (IP, OP, OP, OP, IP, IO,
OP):
SPN=VendorInsert;RS=false;IP=LastName;OP=CustomerName:
CustomerID:ErrorStatus;IP=FirstName;IO=VendorID;OP=ErrorMessage
The connector supports only the simple data types supported by the JDBC
driver.
There are two ways to specify the stored procedure name and its parameter
values:
- Attribute's AppSpecificInfo property
If the text that specifies the stored procedure is less than or equal to
4000 bytes, the value can be specified in the attribute's
AppSpecificInfo property. This property can be used to
specify the stored procedure regardless of whether the connector has polled
for the business object (that is, the business object represents an
application event) or has received the business object as an integration
broker request.
The following example illustrates specification of the stored procedure in
application-specific information. In this case, the value specified for
the MaxLength property is not important to the stored
procedure.
[Attribute]
Name = BeforeCreateSP
Type = String
MaxLength = 15
IsKey = false
IsRequired = false
AppSpecificInfo = SPN=ContactInsert;IP=LastName:FirstName;OP=CustomerName:
CustomerID:ErrorStatus:ErrorMessage
[End]
- Attribute's value (relevant only if InterChange Server is used as the
integration broker)
If the length of the text that specifies the stored procedure is more than
4000 bytes, then mapping must be used to specify the stored procedure.
Mapping is used to specify the stored procedure only if the business object
represents an integration broker request. In other words, an
attribute's value cannot be used to specify a stored procedure when the
connector is polling for events.
If the text of the stored procedure is longer than 4000 bytes and mapping
is used to specify it, then the value of the MaxLength property
needs to be expanded to accommodate the full text.
- 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 one
business object. When it processes an AfterRetrieve stored
procedure, the connector operates on all business objects returned by the
retrieval.
The following sections explain how the connector processes the stored
procedures and simple SQL statements:
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 the Create stored procedures
(BeforeCreate, Create, AfterCreate): It
c
- hecks whether the business object contains a BeforeCreateSP
attribute. If it does, calls the BeforeCreate stored
procedure.
- If the stored procedure returns values through output parameters, it uses
the values to set the value of simple attributes in the business
object.
- It creates the single-cardinality child business objects.
- It sets each of the top-level business object's foreign key values to
the primary-key value of each single-cardinality child business object.
- It 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.
- If the Create stored procedure returns values through output
parameters, it uses the values to set the value of simple attributes in the
business object.
- It sets the foreign-key value in each multiple-cardinality child to the
value of its parent's primary-key attribute.
- It creates the multiple-cardinality child business objects.
- It checks whether the business object contains an AfterCreateSP
attribute. If it does, calls the AfterCreate stored
procedure.
- If the stored procedure returns values through output parameters, it 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 step 3 or 5.
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 Update stored procedures
(BeforeUpdate, Update, AfterUpdate):
- It checks whether the business object contains a BeforeUpdateSP
attribute. If it does, it calls the BeforeUpdate stored
procedure.
- If the BeforeUpdate stored procedure returns values through
output parameters, it uses the values to set the value of simple attributes in
the business object.
- It updates the single-cardinality child business objects.
- It 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.
- It checks whether the business object contains an UpdateSP
attribute. If it does, it calls the Update stored procedure
to update the top-level business object. If it does not, it builds and
executes an UPDATE statement to update the top-level business
object.
- If the Update stored procedure returns values through output
parameters, it uses the values to set the value of simple attributes in the
business object.
- It sets foreign-key values in the multiple-cardinality children to
reference the value in the corresponding primary-key attributes in the
parent.
- It updates the multiple-cardinality child business objects.
- It checks whether the business object contains an AfterUpdateSP
attribute. If it does, calls the AfterUpdate stored
procedure.
- If the stored procedure returns values through output parameters, it uses
the values to set the value of simple attributes in the business
object.
A Delete stored procedure
does not return values to the connector. The connector performs the
following steps when processing the Delete stored procedures
(BeforeDelete, Delete, AfterDelete):
- It checks whether the business object contains a BeforeDeleteSP
attribute. If it does, it calls the BeforeDelete stored
procedure.
- It deletes the single-cardinality child business objects.
- It deletes the multiple-cardinality child business objects.
- It checks whether the business object contains a DeleteSP
attribute. If it does, it calls the delete stored procedure to delete
the top-level business object. If it does not, it builds and executes a
DELETE statement.
- It checks whether the business object contains an AfterDeleteSP
attribute. If it does, it calls the AfterDelete stored
procedure.
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:
- BeforeRetrieve
- Retrieve
- AfterRetrieve
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:
- {call <spName>(?,?,?)}
- {call <spName>}
- {?= call <spName>(?,?,?)}
where spName is the name of the stored procedure.
The connector supports the first two types. It processes 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.
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 must contain the ResultSet column name to match the attribute to
the column.
For single-cardinality objects, the corresponding result set must 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.
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:
- BeforeRetrieveByContent
- RetrieveByContent
- AfterRetrieveByContent
The connector creates a temporary object to retrieve a single-cardinality
or 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 child
object that is 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.
The following stored procedures
are called on the top-level business object, and they retrieve all child
business objects in the same way as the simple Retrieve
operation.
The order of the procedures is as follows:
- BeforeRetrieveUpdate
- RetrieveUpdate
- AfterRetrieveUpdate
These stored procedures perform the same operations as
BeforeRetrieve and AfterRetrieve. They have
different names to allow for attributes that 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.
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 is successful, or
rolls back the transaction if it encounters an error.
