DB2 result set filter

A result set filter is invoked when a result set is received from the server. Each row is potentially presented to the filter logic, which can read it, modify it, or remove it from the result set.

The row is available to the JavaScript logic as context.row, and it has the following properties:

• all the column names (case-insensitive) -- see DB2 data types for details on how data types behave in JavaScript

• columnDescriptors: returns a list of SQLDAROW objects, which describe the columns in the result set

• getIndexOfColumn(string): returns the (zero-based) index of the specified column by name, which is case-sensitive. If the result set does not contain a column of that name, -1 is returned. If the result set contains more than one column with the given name, the index to the first one is returned.

• getMetadataForColumn(string): returns a SQLDAROW object for the specified column (case-sensitive). Returns null if no such column is found in the result set.

• getJavaValue(string): returns the Java value of the specified column (case-sensitive). If no such column is found in the result set, an error is thrown. This is used when JavaScript is insufficient to handle certain values.

• remove(): if this is called, then this row will be removed from the result set

Parameters

User pattern

A list of user names and/or regular expressions for user names. If specified, only queries executed by these users will cause execution of the filter.

Example:

jdoe

jdoe,asmith,wanderson

regex:us_.+

Client IPs

A list of IP addresses (IP4 and/or IP6) and/or regular expressions for IP addresses. If specified, only requests from matching IP addresses will cause execution of this filter.

Example: 

12.34.56.78
1234:5678:90ab::01
regex:98\.76\..*
regex:9876:5432:.*

Schema pattern

The name(s) and/or regular expression(s) for the schema(s). If this is specified, then if any column in the result set belongs to one of these schemas, the filter will potentially execute (subject to other parameters).

Table pattern

The name(s) and/or regular expression(s) for the table(s). If this is specified, then if any column in the result set is from one of these tables, the filter will potentially execute (subject to other parameters).

Query pattern

Specifies the query (or queries) for which this filter should execute. Can be literal (in which case the query must be an exact match, including casing) or a regular expression. 

Examples:

select * from cust.customers order by first_name

regex:select.*from\s+cust\.customers.*

Column patterns

Zero or more name=value entries specifying for which values of which columns this filter should get invoked. The column name is case-sensitive.

In addition to the usual regular expression syntax, if a regular expression contains equal signs, they must be escaped with a backslash (e.g. \=). 

For instance:

COUNTRY=regex:(Austria)|(Slovenia)

would restrict this filter to execute only when the result set contains a column named "COUNTRY", and its value is either "Austria" or "Slovenia".

If column names are not available, columns must be accessed by index (starting at 0). Example:

2=9.99

And/or

If you specify more than one name=value column pattern, this specifies whether any one must be satisfied, or all must be satisfied.

Action

If set to "code" (the default), then if all the parameters are satisfied, the code for this filter will be executed.

If set to "hide", then the row will simply be removed from the result set.

Example

You can modify a result set by simply setting column values, e.g.:

let row = context.row;

if (row.COUNTRY === 'Japan') {

   row.WARRANTY = "利用不可";

}

Example

You can hide a row simply by setting parameters and selecting "hide" as the action. 

For more complex cases, you can do this in code, e.g.:

let row = context.row;

if (row.price > 1000 || row.tax > 100) {

    row.remove();

}