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
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 build query business objects that run user-defined SELECT statements against the database. You specify the SELECT statement, using ? (the question mark) in place of any substitutable parameters in the SELECT statement. The wizard then provides an area where you specify the data type of each parameter and provide a sample value. The sample value must match data in the database because the wizard uses the results from the SELECT statement to create the query business object.
Before you save the configuration of the query in the wizard, you must validate the query. When you validate the query, 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 for all columns. For each column of the returned result set, the wizard generates a corresponding attribute in the query business object. For each parameter in the WHERE clause, the wizard generates an attribute in the query business object. These attributes are used to filter the result dynamically at run time.
For example, assume that you specify the following SELECT statement:
SELECT * FROM CUSTOMER WHERE FNAME=? and AGE=?For Oracle databases, the adapter supports complex data types such as array, table, structure, or nested structure in the query result of the business object. The adapter does not support these complex types as parameters in batch and query business objects.
This WHERE clause has two parameters. 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 adapter generates two attributes named parameter1 and parameter2 for the query business object. You can dynamically filter the query result by specifying appropriate values for these attributes at run time.
In addition, you can set the WHERE clause attribute dynamically during run time to filter the result.
For example, you can set the WHERE clause as WHERE LNAME LIKE ‘Mike%', to return results that have LNAME starting with Mike.
You cannot use parameters in dynamically set WHERE clause.