Business object verb processing
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
Verb determination
A top-level business object and each of its individual
child business objects can contain its own verbs. Therefore, the
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.
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 updating,
the connector assumes that the business object represents the desired
state of the data after the update.
Therefore, when the 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.
Verb processing
This section outlines the steps the connector takes when
creating, retrieving, updating, or deleting a business object that
it receives from the 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.
Business object comparison
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.
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:
- 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 the attribute represent
a child and it does not, the connector returns an error and stops
processing.
- It processes each single-cardinality with ownership 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 with ownership 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.
- 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.
Retrieve operations
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 with ownership 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.
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 a MULTIPLE_HITS message.
Note:
A RetrieveByContent verb is not applicable for a
top-level wrapper business object.
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:
- 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 with ownership 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. (With InterChange
Server Express 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 operations
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.
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 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.
Physical delete operations
The connector performs the following steps when physically
deleting a hierarchical business object:
- It recursively deletes all single-cardinality 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.
Logical delete operations
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.
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.
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.
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.
Stored procedure syntax and examples
This section describes the syntax of stored procedures for
business objects. It also includes examples. It contains the following
sections:
Adding attributes to the business object
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 Express 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 business object attribute listed are enabled for use with
bidirectional languages. The format of these attributes will be
transformed based on the attributes set for the BiDi.Metadata standard
configuration property. For information on this property, see Appendix A. Standard configuration properties for connectors.
Syntax of a stored procedure
The syntax for specifying a stored procedure is:
SPN=StoredProcedureName;[RetVal=Attribute_Name|RS:]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.
- RetVal
- The existence of this ASI implies that it is a function call
and not a procedure call, because a value is being returned by the
function. If the returned value is RS, the returned value is a resultset and will be used to create
the N-cardinality container corresponding to this business object.
If the returned value is an attribute, the value will be assigned
to that particular attribute in the business object. If the attribute
is another child business object, it will return an error.
- 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. 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 (=).
Examples of stored procedures that return result sets
The following example uses a stored procedure named CustomerAddressRetrieve, which returns 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 DB2 and SQL Server 2000 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-CustomerId;
OPErrorStatus;OP-ErrorMsg
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.
- 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.
Specifying the stored procedure
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 the 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 Express 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 the 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.
Business object processing with stored procedures or simple
SQL statements
The following sections explain how the connector processes
the stored procedures and simple 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 the Create stored procedures (BeforeCreate, Create, AfterCreate):
It c
- 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,
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.
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 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.
Business object delete operations
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.
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:
- 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.
Result set returned from Retrieve stored procedure (RetrieveSP)
operations
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.
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:
- 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.
Business object Retrieve-for-Update operations
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.
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 is successful, or rolls
back the transaction if it encounters an error.
