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
User-defined query criteria for operations
IBM WebSphere Adapter for JDBC supports user-defined criteria on table business objects for the RetrieveAll, UpdateAll, DeleteAll, and Exists operations. You can use this user-defined criteria to retrieve, update, delete multiple records in one operation, and check the existence of multiple records or one record in a single operation.
You can set the query criteria on a table business object using the RetrieveAllCriteria, UpdateAllCriteria, DeleteAllCriteria, and ExistsCriteria ASIs in the business object editor, after you run the external service wizard.
Figure 1. Setting user-defined criterion on a table business object using the RetrieveAllCriteria application-specific information
The generated complete SQL statement for an operation constitutes: the basic SQL statement and the criteria SQL statement.
After you set the criterion, during the run time, the adapter generates basic SQL statement as a part of the complete SQL statement based on the operation, and then appends the criterion SQL statement to the generated basic SQL statement, and finally generates the complete SQL statement for the operation.
For example, if you set the RetrieveAllCriteria ASI as WHERE AGE > 18, the complete SQL statement used for the RetrieveAll operation will be like: SELECT ID, FNAME, LNAME, AGE FROM FRIENDS WHERE AGE > 18, where SELECT ID, FNAME, LNAME, AGE FROM FRIENDS is the basic SQL statement based on the RetrieveAll operation, and WHERE AGE > 18 is the criteria SQL statement. These two statements constitute the complete SQL statement for the RetrieveAll operation.
For hierarchical business objects, if you set the criteria on both the top-level business object and child business object, only the criteria specified on the top-level business object is used to generate the final SQL statement. The criterion specified on the child business object is not used to execute the operation on the top-level business object. It is only used when executing operations on the child business object.
Generated basic SQL statement for each operation
When you set the criterion, the adapter generates the following SQL statement as the basic SQL statement for each operation during run time. This basic SQL statement is a part of the complete SQL statement for an operation.
| Operation | Basic SQL statement |
|---|---|
| RetrieveAll | SELECT <COLUMN_1>, <COLUMN_2> ... FROM <TOP_LEVEL_TABLE_NAME> |
| UpdateAll | UPDATE <TOP_LEVEL_TABLE_NAME> SET <COLUMN_1>=?, <COLUMN_2>=? |
| DeleteAll | DELETE FROM <TOP_LEVEL_TABLE_NAME> |
| Exists | SELECT <COLUMN_1>, <COLUMN_2> ... FROM <TOP_LEVEL_TABLE_NAME> |
Generated criteria SQL statement for each operation
The criteria SQL statement is a part of a generated complete SQL statement. The criteria SQL statement can contain:
- Valid SQL operators
>, <, <=, >=, !=, EXISTS, IN, LIKE and other valid SQL operators
- Simple WHERE clause with any valid SQL operators
WHERE (PKEY='XYZ' or FNAME='ABC') and (AGE<10 or AGE>70)
- Complex WHERE clause with nested SQL query
WHERE PKEY NOT IN(SELECT PKEY FROM FRIENDS WHERE AGE !=18)
- Any other valid part of SQL statement
WHERE PKEY NOT IN (SELECT PKEY FROM FRIENDS WHERE AGE != 18) ORDER BY AGE, FNAME
When you use special identifiers inside the criteria, for example, database keywords or an identifier with special characters, you must wrap the identifier using the database-specific wrapper string. By default, the wrapper string is the double quotation marks.
For example, WHERE “COLUMN SPEC 1” = ‘ABC'
If the ASI includes special XML characters and if it is saved into the schema file directly, it causes errors on business object definition. To avoid these errors you must manually encode the XML characters.
For example, "<" must be encoded to "<".
Named parameters in user-defined query criteria
The adapter supports named parameter in the user-define query criteria (Criteria SQL statement). A named parameter is indicated by a colon (:) followed by an identifier. When you use a named parameter, you must set the corresponding value in the input business object when you execute the operation, else the unset field is treated as null.
For example, you can define the criteria SQL statement as, WHERE AGE>:age.
In this example, the WHERE clause contains a named parameter age. The corresponding value of the named parameter is passed into the input business object during run time.
Named parameters are case-sensitive.
To indicate a reference hierarchy in the criteria, use a colon (:) followed by the field name.
Example: WHERE FNAME <> :fname AND EXISTS (SELECT * FROM SALARYINFO WHERE SALARYINFO.EMPNUM=EMPLOYEE.EMPNUM AND BASIC > :salaryinfoobj:basic)
To refer multiple input instances in the parameter, use [n], where the index of the first instance is 0.
The starting index of the instance is 0, and not 1.
Example: WHERE EXISTS (SELECT * FROM SALARYINFO WHERE SALARYINFO.EMPNUM=EMPLOYEE.EMPNUM AND BASIC < :salaryinfoobj[1]:basic), indicates the basic attribute of the second salaryinfoobj instance.
A named parameter can only refer to a field in the business object. Referring to a normal child table business object is invalid. However, referring to a child business object with a Struct type is valid.
Example 1: WHERE STRUCTTYPEOBJ=:structtypeobj is a valid reference.
Example 2: WHERE CCODE=:custinfoobj:ccode is a valid reference.
Example 3: WHERE CUSTINFOOBJ=:custinfoobj is an invalid reference.
To query records for fields that have NULL value, use the IS NULL keyword in the SQL statement.
Example: WHERE <Column Name> is null
If you define a named parameter in the comparison expression of the criterion, during run time, you must enter a not null value for the corresponding field, or the comparison expression always returns FALSE.
Example: WHERE CCODE=:custinfoobj:ccode.
If the CCODE field of CUSTINFOOBJ is set as null, the criterion matches no records.
Related concepts: