Developing business objects using dynamic retrieve

The Dynamic Retrieve function module is a mapping tool and dynamic SQL statement generator. This function module uses the metadata stored in the YXR_DISPLY table to generate SQL select statements at runtime. Dynamic Retrieve takes the resulting fields of these SQL statements and fills the attributes of the WebSphere business object. When the Dynamic Retrieve function module is called, the following steps are performed:

  1. All entries are retrieved from YXR_DISPLY, where,

    object name = objectName

  2. For each new table specified in YXR_DISPLY, a SQL where clause is generated based on the fields marked as key. The corresponding Field Name in business object attributes is used to populate the value in the where clause. If a default value is specified in YXR_DISPLY, this default is used.
  3. The SQL select statement is executed. The resulting fields are copied into the corresponding Field Name in business object attributes.

Note:
Before you can generate a business object definition, you must create a WebSphere business object using the IBM WebSphere InterChange Server Connector Tools Window (YXR1).

Tips

Table 44 shows table entries for the Dynamic Retrieve table.

Table 44. Table Entries for Dynamic Retrieve

Field Name Description When Used Technical Name
Object Name WebSphere business object name Always OBJ_NAME
Counter Counter Always POSNR
Table Table to read Always TABNAME
Field Name Field name in table Always NAME_FELD
Key Specifies a key field of a table First field of each table. Used to build the where clause of the select statement. KEYFLAG
Optional Free text description of screen, field or command Used for non-key fields. If all non-key fields are marked as optional and if the select statement fails, the results will be a warning, not an error. OPTIONAL
Rel Operand (relation) Used to determine relationships in where clause. By default, the operands in the where clause are 'equal' but can be changed by entering a value in this field. YXR_OPERND
Field Name in business object Attribute in the WebSphere business object to supply the input value
  • Key fields: to build the where clause.
  • Non-key fields: to return database fields to attributes in the business object

SOURCEFLD
Default Value A static default value to use if no entry is provided in the WebSphere business object Key fields DEFLT_VAL
SY Field A dynamic system field to be used as a default value (for example: DATUM) Key fields SYFIELD
Length Character length from the 0 or offset position of the attribute value that should be used when building the where clause. Key fields. Only relevant when using an attribute that contains a composite value. LENGTH
Offset Character offset from the 0 position of the attribute value that should be used when building the where clause. Key fields. Only relevant when using an attribute that contains a composite value. YXR_OFFSET

To access adapter's table-driven connector table for Display:

  1. Go to the IBM WebSphere InterChange Server Connector Tools window (transaction YXR1).
  2. From the Customizing menu, click Dynamic Read, and then click Modify Retrieve.

Figure 73 shows the Dynamic Retrieve table with a nested SQL select statement. Results from a previous select statement are used to build the key on subsequent select statements.

Figure 73. Dynamic retrieve with a nested SQL select statement


Using the Counter column as a line number for discussion, you can step through the SAP_FuncLocation example of a functional location object in the Dynamic Retrieve table.

100
Table IFLOT has only one key field. The value in the CustomerId attributes in the where clause. If the value in CustomerId is 4711, then the where clause is:

where TPLNR = '4711'

310
This is the first non-key field. At this point, the actual select statement is executed. The select statement is:

Select * from IFLOT where TPLNR = '4711'

The resulting value of ILOAN is then copied into the ObjectLocation attribute, which is '5678' for this example.

320
Since this is a new table and a key field, the where clause is built again. Once again the where clause is:

where TPLNR = '4711'

330
Another key for table TFLOTX. The where clause is extended with:

and SPRAS = 'E'

If the value of Language2 is CxIgnore, then the E is taken from the Default Value field.

340
This is the first non-key field for table IFLOTX. The select statement is executed. The statement is:

Select * from IFLOTX where TPLNR = '4711' and SPRAS = 'E'

The resulting value of PLTXT is copied into the CustomerName attribute. If the select statement fails, a warning is issued, because all the non-key fields for table IFLOTX are marked as optional.

350
The value of KZLTX from the previous select statement is copied into the TextIndicator attribute.

360
Since this is a new table and key field, the where clause is built again. The value of the where clause is taken from the attribute ObjectLocation, which was filled by an earlier select statement. If '5678' is the value in the attribute ObjectLocation, the where clause is:

where ILOAN = '5678'

370
This is the first non-key field. The select statement is executed. The statement is:

Select * from ILOA where ILOAN = '5678'

The resulting value of ADRNR will be copied into the AddressId attribute.

This completes the building of the SAP_FuncLocation function module for Dynamic Retrieve support.

Copyright IBM Corp. 1997, 2004