Business object verb processing
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 afterimages
- Verb processing, which explains the steps
the connector takes when creating, retrieving, updating, or deleting
a business object
- 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 their own verbs. Therefore, a
business process can pass a business object that has different verbs
for parent and child business objects to the connector. When this occurs,
the connector uses the verb of the top-level parent business object
to determine how to process the entire business object. For more
information, see Verb processing.
After-images and deltas
An after-image is
the state of a business object after all changes have been made
to it. A delta is a business
object used in an update operation that contains only key values and
the data to be changed. Because the connector supports only after-images,
when it receives a business object for update from a business process,
the connector assumes that the business object represents the desired
state of the data after update.
Therefore, when a business process 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 business process passes the following
business object to the connector:
To process the update, the connector applies the following changes
to the database:
- Update the simple attributes in the top-level Contract and Address
business objects
- Create the Phone business object
- Update the simple attributes in the child business objects A,
B, F and G
- Delete the child business objects C, D and E
- Create the child business objects H, I and J
Because the connector assumes that each business object it receives
from a business process represents an after-image, it is important
to ensure that each business object sent to the 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 the connector from
deleting missing child business objects during an update operation.
You can use the application-specific information for the attribute
that represents the child or array of children to instruct the connector
to keep child business objects that are not included in the source
business object. To do so, set KEEP_RELATIONSHIP to true. For more information, see Specifying an Attribute's Foreign Key.
Verb processing
This section outlines the steps the connector takes when
creating, retrieving, updating, or deleting a business object that
it receives from a business process. The connector processes hierarchical
business objects recursively; that is, it performs the same steps
for each child business object until it has processed all individual
business objects.
Note:
A top-level business object that is a wrapper supports
the create, retrieve, update and delete verbs. The only difference
in processing a wrapper object is that the wrapper object is not
processed, only the objects that it contains are processed.
Business object comparison
At various points in the processing outlined below, the
connector compares two business objects to see if they are the same.
For example, during an update operation, the connector determines
whether a particular business object exists in an array of business
objects. To perform the check, the connector compares the business
object to each business object within the array. For two business
objects to be identical, the following two conditions must be satisfied:
- 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:
- 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 business process.
- 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, you
must ensure that primary key attributes in child business objects
are cross-referenced correctly on create operations. If the child
business object does not already exist in the application database,
set the primary key attributes to CxBlank.
- 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 business process. For more information on specifying a
database sequence or counter, see 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 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.
Retrieve operations
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 business process.
- Retrieves the top-level business object from the database.
- If the retrieval returns 1 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.
Note:
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 business process. In child business objects, the connector
sets such attributes to their default values during retrieval.
Note:
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
to the business process.
- Recursively retrieves each of the single-cardinality children
regardless of whether the child business object is contained with
or without ownership.
Note:
All single cardinality child business objects are
processed based on occurrence in the business object and before
the parent business object is processed. Child object ownership
and non-ownership do not determine the processing sequence, but
do determine the type of processing.
RetrieveByContent operations
A RetrieveByContent verb is applicable only for the top-level
business object, because the connector performs a retrieval based
on attributes only in the top-level business object.
If a top-level business object uses the RetrieveByContent verb,
all of the attributes (including non-key attributes) that are not
null are used as retrieval criteria.
If more than one row is returned, the connector uses the first
row as the result row and returns MULTIPLE_HITS.
Note:
A RetrieveByContent verb is not applicable for a
top-level business object that is a wrapper.
Update operations
When updating a business object, the connector returns
a status either of VALCHANGE if the operation was successful (regardless of whether the
operation caused changes to the business object), or FAIL if the operation failed. 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, 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.
(If WebSphere 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 business process must ensure that business objects
contained with multiple cardinality in the source business object
are unique (that is, that an array does not contain two or more
copies of the same business object). If the connector receives duplicates
of a business object in a source array, it processes the business
object twice, with possibly unpredictable results.
DeltaUpdate operations
DeltaUpdate verb processing is different from update verb
processing as follows:
- 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.
Delete operations
When deleting a business object, the connector returns
a status of SUCCESS if the operation was successful or FAIL if the operation failed. The parent business object is first
retrieved and then the adapter recursively deletes all single-cardinality
children that have an ownership relationship to the parent, then
the parent business object itself, and finally all cardinality N
children. Single-cardinality no-ownership children are never deleted.If
the business object does not exist, the connector returns a FAIL.
The connector supports logical and physical deletes, depending
on the Status Column Name (SCN) value in the object's application-specific
information. If the SCN value is defined, the connector performs
a logical delete. If the SCN value is not defined, the connector
performs a physical delete.
Physical deletes
The connector performs the following steps when physically deleting
a hierarchical business object:
- 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.
Logical deletes
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.
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 procedureis
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.
Specifying a stored procedure
This section describes the steps you must perform to cause
the connector to use a stored procedure for a business object. It
contains the following sections:
Adding attributes to the business object
You must add a special kind of attribute to the
business object for each type of stored procedure that the connector
processes. These attributes represent only the stored procedure's
type and the application-specific information that defines it. These
attributes do not use the application-specific information parameters
available for a standard simple attribute.
Name the attribute according to the type of stored procedure
to be used. For example, to cause the connector to use AfterUpdate
and BeforeRetrieve stored procedures, add the AfterUpdateSP and BeforeRetrieveSP attributes.
The connector recognizes the following business object attribute
names:
BeforeCreateSP
AfterCreateSP
CreateSP
BeforeUpdateSP
AfterUpdateSP
UpdateSP
BeforeDeleteSP
AfterDeleteSP
DeleteSP
BeforeRetrieveSP
AfterRetrieveSP
RetrieveSP
BeforeRetrieveByContentSP
AfterRetrieveByContentSP
RetrieveByContentSP
BeforeRetrieveUpdateSP
AfterRetrieveUpdateSP
RetrieveUpdateSP
BeforeDeltaUpdateSP
AfterDeltaUpdateSP
DeltaUpdateSP
Note:
Create an attribute only for those stored procedures
that you want the connector to execute. Use the application-specific
information or mapping (available only when WebSphere 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 business object attributes 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 sreturned 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.
Note:
For Oracle functions, return a resultset as an output
parameter (OP=RS) and not as a return value.
- RS
- Is true if the stored procedure returns a result set or false if it does not. By default, 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 OUTparameter is supported. If there are more than one result
sets returned as OUTparameters, only the first result set is returned and all
others are ignored. Currently, this feature is supported for Oracle
8i and above, 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.
Note:
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 name for each value. Separate parameters
of different types with a semicolon delimiter. When specifying parameter
values, do not put a blank space on either side of the equal sign
(=).
Examples of stored procedures
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, 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 length of the text that specifies the stored procedure
is less than or equal to 4 KiloBytes, 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 when WebSphere InterChange
Server is used as the integration broker)
If the length of the text that specifies the stored procedure
is more than 4 KiloBytes, 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 4 KiloBytes
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.
Processing business objects using stored procedures or simple SQL
statements
The following sections explain how the connector processes
the stored procedures:
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):
- 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.
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):
- 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.
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):
- 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.
Business object retrieve operations
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>(?,?,?)}
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 only.
Processing result set returned from retrieve stored procedure
(RetrieveSP)
ResultSetMetaData is obtained for the result set returned from the stored procedure. Values
of all the columns in the result set are obtained and set on the
corresponding attribute of the business object. The ColumnName property
of an attribute's application-specific information should contain
the ResultSet column name to match the attribute to the column.
For single cardinality objects, the corresponding result set
should consist of only one row. If multiple rows are returned in
the result set, an error is reported.
For multiple cardinality children, multiple rows can be returned
through the result set. For each row returned, a new object is created
and added to the container. The container is then added to the parent
object at the required attribute index.
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 child business object or a multiple cardinality child
business object. For multiple cardinality business objects, the
connector applies the BeforeRetrieveByContent stored procedure to the temporary business object. The AfterRetrieveByContent stored procedure is applied to each of the child objects
retrieved for the container.
The connector executes the AfterRetrieveByContent stored procedure after it executes a RetrieveByContent query generated dynamically from the business object metadata
or stored procedure on the business object. In this case, even though
the retrieval of a hierarchical business object also retrieves its
child business objects, the connector executes the AfterRetrieveByContent stored procedure on every business object present in the
array.
Business object Retrieve-for-Update operations
The following stored procedures are called on the top-level
business object and retrieve all child business objects in the same
way as the simple Retrieve.
The order of the procedures is as follows:
- 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.
Transaction commit and rollback
Whenever the connector receives a business object for
processing, it begins a transaction block. All SQL statements that the connector executes while processing that business
object are encapsulated within the transaction block. When the connector finishes
processing the business object, it commits the transaction block
if the processing was successful, or rolls back the transaction
if it encountered an error.
