This section describes the following aspects of processing a business
object's verbs:
- Verb determination, which explains how the connector determines the verb to use
for each individual, 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 operations
- 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 their 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 update, the connector assumes that the business
object represents the desired state of the data after 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 follows:

Assume further that the integration broker passes the following business
object to the connector:

To process the update, the connector applies the following changes to the
database:
- Updates the simple attributes in the top-level Contract and Address
business objects
- Creates the Phone business object
- Updates the simple attributes in the child business objects A, B, F and G
- Deletes the child business objects C, D and E
- 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, 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.
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 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.
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:
- 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:
- 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 attribute to represent a child and it does not, the
connector returns an error and stops processing.
- Processes each single-cardinality child business object contained without
ownership as follows:
- Recursively attempts to retrieve the child from the database using the key
values passed in by the integration broker.
- If the retrieve is unsuccessful, indicating that the child does not
currently exist in the database, 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 database, 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.
- Inserts the top-level business object in the database as follows:
- 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.
- 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.
- 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.
- 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.
- Processes each of its single-cardinality child business objects that
stores the parent/child relationship in the child, as follows:
- 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 into the database.
- Inserts the child into the database.
- Processes each of its multiple-cardinality child business objects, as
follows:
- 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.
- Inserts each of its multiple-cardinality child business objects into the
database.
The connector performs the following steps when retrieving a hierarchical
business object:
- Removes all child business objects from the top-level business object that
it received from the integration broker.
- 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 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.
- 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
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.
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.
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:
- 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 business object that is a wrapper does not have to exist in the
database. However, it 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.
- 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 value 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.
- 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:
- If the child is present in both the source and the retrieved business
objects, instead of updating the already 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, 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, as
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.
- 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.
- 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 have
typically 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.
- 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, 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 possibly
unpredictable results.
DeltaUpdate verb processing is different from update verb processing as
follows:
- On a DeltaUpdate no retrieve is done 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 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- If a Create verb is found in the child, the child gets 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 gets deleted.
- If a DeltaUpdate verb is found in the child, the child gets 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 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.
The connector performs the following steps when physically deleting a
hierarchical business object:
- Recursively deletes all single-cardinality child business objects
contained with ownership.
- Deletes the top-level business object.
- 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.
When logically deleting a business object, the connector performs the
following steps:
- 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.
- 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 steps you must perform to cause the connector to
use a stored procedure for a business object. It contains the following
sections:
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. 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. The connector must be restarted to recognize 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 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 above,
for stored procedures that use the Oracle JDBC Driver. For the stored
procedure in the database, the corresponding parameter should return a
REFCURSORtype.
- 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. See RS for a description of the result set.
- 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 order of StoredProcedureName, RS, and parameters
is important; 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, 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
(=).
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 length of the text that specifies the stored procedure is less than
or equal to 4000 bytes, you can specify the value in the attribute's
AppSpecificInfo property. You can use this property 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, you must use mapping to specify the stored procedure. You
can use mapping to specify the stored procedure only if the business object
represents an integration broker request. In other words, you cannot
use an attribute's value 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 you use
mapping to specify it, remember to expand the value of the
MaxLength property 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 a single
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:
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):
- Checks 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, uses the
values to set the value of simple attributes in the business object.
- Creates the single-cardinality child business objects.
- Sets each of the top-level business object's foreign key values to the
primary-key value of each single-cardinality child business object.
- 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, uses the values to set the value of simple attributes in the
business object.
- Sets the foreign-key value in each multiple-cardinality child to the value
of its parent's primary-key attribute.
- Creates the multiple-cardinality child business objects.
- 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, 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.
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):
- Checks whether the business object contains a BeforeUpdateSP
attribute. If it does, calls the BeforeUpdate stored
procedure.
- If the BeforeUpdate stored procedure returns values through
output parameters, uses the values to set the value of simple attributes in
the business object.
- Updates the single-cardinality child business objects.
- 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.
- 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.
- If the Update stored procedure returns values through output
parameters, uses the values to set the value of simple attributes in the
business object.
- Sets foreign-key values in the multiple-cardinality children to reference
the value in the corresponding primary-key attributes in the parent.
- Updates the multiple-cardinality child business objects.
- 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, 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):
- Checks whether the business object contains a BeforeDeleteSP
attribute. If it does, calls the BeforeDelete stored procedure.
- Deletes the single-cardinality child business objects.
- Deletes the multiple-cardinality child business objects.
- 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.
- Checks whether the business object contains an AfterDeleteSP
attribute. If it does, calls the AfterDelete stored
procedure.
For simple retrieve operations, stored procedures can be used for top-level
business object, single cardinality children, as well as 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 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.
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.
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
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.
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:
- BeforeRetrieveUpdate
- RetrieveUpdate
- AfterRetrieveUpdate
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.
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.
