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 > Configure the module for deployment > Configure the module for outbound processing > Selecting and configuring business objects

Selecting and configuring query business objects

Select and configure query business objects for user-defined SELECT statements for use in your module.

To configure query business objects, you must know the structure of the data in your database, including the tables and views. You must know the name and data type of the columns that your module must access. You must also be able to write SQL SELECT statements.

This task is performed through the external service wizard.

You start in the Find Objects in the Enterprise System window and then work in a Specify the Configuration Properties for 'object' window that is specific to the business object you are configuring.


Procedure

  1. In the Discovered objects list of the Find Objects in the Enterprise System window, expand the Query Statements node. This node contains an object template, named Select Statement n, for each query business object you requested in the Specify the Query Properties window.

    For example, if you specified a count of two query business objects in that window, the Discovered objects list contains two object templates, as illustrated in the figure below:

  2. Select one or more object templates and click the > (Add) button to add the objects to the Selected objects list.

  3. In the Name of the business object field, type a name for the business object. The name can contain spaces and national language characters.

  4. In the Select statement field, type the SELECT statement you want to run. Indicate each parameter with a question mark (?). The following sample SELECT statements illustrate the flexibility of the query business object:

    • select * from customer where ccode=?
    • select * from customer where id=? and age=?
    • select * from customer where lname like ?
    • select C.pkey, C.fname, A.city from customer C, address A WHERE (C.pkey = A.custid) AND (C.fname like ?)

    Ensure that the SELECT statement does not include a nested SELECT statement in the FROM clause. As you type each ?, the window expands to display an area where you define the WHERE clause for that parameter. The following figure shows the Specify the Configuration Properties for 'object' window for a query business object that has a single parameter.

  5. In the WHERE clause parameter n area, provide information about each parameter in the SELECT statement.

    1. In the Parameter type field, select the data type of the parameter. For Oracle databases, the adapter does not support the complex types such as array, table, structure, or nested structure as parameters in the batch and query business objects.

    2. In the Sample value field, type a sample value for the parameter.

    For example, for a parameter corresponding to a column containing a customer's last name, you might select string as the data type and provide a sample value of Smith.

  6. Click Validate. The Result area displays the result of the validation.

    If the Result area displays Validation failed, there is a problem in the information you provided. Use the error message from the database server, which follows Validation failed, to correct the definition. Check the syntax of the SELECT statement, the data type of the parameters, and the sample data. If the validation is successful, the Advanced button is displayed.

  7. To specify the data type mapping for each column in the result set returned by the select statement:

    1. Click Advanced.
    2. Expand Result set returned by the Select statement. For each column in the result set, the default data type mapping is displayed.

      For Oracle databases, if the query result contains any complex data type such as an array, structure, nested structure or table, the type name and the child attribute details are also automatically discovered and displayed. The figure below displays the type name and child attribute details in a query result for an Oracle table.

    3. Review the mapping and make changes if required.

  8. Click OK to save the definition of the query business object.


Results

The query business objects you defined are now listed in the Find Objects in the Enterprise System window.


What to do next

In the Find Objects in the Enterprise System window, continue to select and configure other types of business objects.

When you are finished, click Next to set global properties and configure wrapper business objects.

Selecting and configuring business objects


Related concepts:

Business objects

Create operation

Update operation


Related tasks:

Selecting and configuring tables, views, and synonyms or nicknames for outbound processing

Selecting and configuring stored procedures and stored functions

Selecting and configuring batch SQL business objects


Related reference:

Business object attributes

Solutions to common problems

Business object attributes