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 > Oracle E-Business Suite > Overview of IBM WebSphere Adapter for Oracle E-Business Suite > Technical overview
Query business object overview
Query business objects run a user-defined SELECT statement against the database and return the matching records in business objects.
The external service wizard helps you to build query business objects that run user-defined SELECT statements against the database. Specify the SELECT statement, using ? (the question mark) in place of any parameter that can be substituted in the SELECT statement. The wizard then provides an area where you can specify the data type of each parameter and provide a sample value. The sample value must match data in the database because wizard uses the SELECT statement results to create the query business object.
You must validate the configuration of the query in the wizard before saving it. When you validate, the wizard runs the SELECT statement using the sample values. After obtaining the result set, the wizard analyzes the metadata to obtain the column name and column type of all columns. For each column of the returned result set, the wizard generates one corresponding attribute in the query business object.
In the query business object, the jdbcwhereclause takes precedence over the values specified for the parameter <n>, where 'n' is the parameter index. This means that, if the query business object contains the parameter <n> and the value is specified for the jdbcwhereclause, then the dynamic query is formed with the WHERE clause using the jdbcwhereclause value. This ignores the values in the parameter <n>. However, if you want to use the default WHERE clause then you can specify the WHERE clause parameters by specifying values to parameter <n> and un-setting or setting jdbcwhereclause to null.
For example, assume that you specified the following SELECT statement:
select * from customer where fname=? and age=?The WHERE clause has two parameters, which are indicated by question marks (?). The first parameter has the data type string, to match the data type of the fname column. The second parameter has the data type int, matching the age column. If your database has a customer record where the fname column contains the string Mike and the age column contains the integer 27, you can specify those values as sample values when configuring the query business object. The wizard configures the business object to correspond to the returned result set.
You can also create query business objects to retrieve the interface status of the Oracle open interface tables. The interface status column in the interface table contains the error codes. These codes indicate if the data processed from the Oracle open interface table to the Oracle base table is successful or not. The error code can be used to troubleshoot the issue in case of failure during the data transfer.
Technical overview of IBM WebSphere Adapter for Oracle E-Business Suite
Related tasks: