IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Overview of IBM WebSphere Adapter for JDBC > Technical overview > Outbound processing > Outbound operations
RetrieveAll operation
The adapter uses the RetrieveAll operation to retrieve an array of business objects from the database. The adapter uses different processes depending on whether the RetrieveAll operation is for database table business objects or for user-specified SQL business objects.
For database table business objectsThe key and non-key attributes populated in the incoming business object determine the selection criteria for the retrieval. The adapter may retrieve multiple rows for the top-level business object from the database, depending on the attributes selected. All values specified in the top-level business object are used. The settings in the child business object are ignored. If no attributes are populated in the incoming business object, all the rows are retrieved from the respective table in the database.
You can use the SQL capabilities to full extent for querying records by using child business object attributes or user-defined query criteria for operations.
The name of a generated business object matches the name of the table in the database.
For example, the Customer table in the database is represented as a business object named "Customer".
To retrieve an array of business objects, the adapter performs the following actions:
- Constructs a container business object for all the retrieved rows. The name of the container business object is the name of the business object with the string "Container" appended to it.
- Constructs a top-level business graph for each of the retrieved rows, if the module was configured to use business graphs, which is optional. The name of the business graph is the same as the business object name with the string "BG" appended to it.
- Retrieves each of the business graphs in the container using the Retrieve operation.
The following figures display the structure of objects returned from a RetrieveAll operation, with and without business graphs.
Figure 1. Structure of the business object returned in a RetrieveAll operation without optional business graphs
Figure 2. Structure of the business object returned in a RetrieveAll operation with optional business graphs
The following errors can result from a RetrieveAll operation:
- RecordNotFoundException– This exception is generated when one or more populated business objects in the input object do not exist in the enterprise information system and if the managed connection factory property for the ErrorOnEmptyResultset property is set to True. To get an empty result set without the RecordNotFoundException, you can either modify the .import file and set the Throw exception when no records are found (ErrorOnEmptyResultSet) property to False, or after you deploy the outbound application on IBM BPM, configure the MCF property Throw exception when no records are found (ErrorOnEmptyResultSet) to False.
- MatchesExceededLimitException – This exception is generated when the number of matching records in the database exceeds the value of the Maximum number of records to be returned property that is defined in the interaction specification. The MatchCount attribute of the fault contains the actual number of matches that the adapter found in the database, so that you can either increase the limit or refine the search.
If the Maximum number of records to be returned property is set to a large number, problems can occur due to the lack of sufficient memory, depending upon the size and number of business objects returned.
- EISSystemException – This exception is generated when one or more unrecoverable errors are reported by the database (the enterprise information system).
For query business objectsBusiness objects created for user-specified SELECT statements (query business objects) also support the RetrieveAll operation. The external service wizard generates the query business object by running the user-specified SQL SELECT statement and creating a hierarchy of query business objects.
If you use optional business graphs, the hierarchy appears as displayed in Figure 3.
Figure 3. User-specified query business objects
If you are not using optional business graphs, the hierarchy appears as displayed in Figure 4. Figure 4. User-specified query business objects
To process the query business object generated by the external service wizard for the user-specified SELECT statement, the adapter performs the following actions:
- Obtains the SELECT SQL statement from the query business object.
- Determines whether a dynamic WHERE clause is specified in the query business object.
- If there is a dynamic WHERE clause, the adapter replaces the default WHERE clause in the SELECT statement with the dynamic one.
- If there is no dynamic WHERE clause, the adapter replaces parameters in the SELECT statement with the corresponding values specified in the query business object.
- Runs the SELECT statement.
- Obtains the result set that is returned and populates the query business object values with the data returned from the database, creating a container business object with the structure displayed in Figure 3.
- Retrieves the entire hierarchy (a deep retrieve) of each top-level query business object in the container, if any child business objects are defined for the query business objects.
A query business object can be a top-level business object only. A query business object cannot have child query business objects.
Retrieving NULL objectsThe adapter can retrieve records from a database table when the column value is NULL.
For example, a Customer business object might have these columns: custid, ccode, fname, and lname, where ccode need not be a primary key. You can query all of the Customer records for which the ccode column is NULL. The adapter generates a select query for the RetrieveAll operation as:
select custid, ccode, fname, lname from customer where custid=? and ccode is NULLFor more information about null data, see Null Data - Frequently Asked Questions.
Related concepts:
User-defined query criteria for operations
Matching records using child business object attributes
Related tasks:
Setting global properties for operations and creating wrapper business objects
Related reference:
Migration considerations for WebSphere Business Integration adapters