StatementExecutorValve

Valve for executing JDBC/SQL statements.

If no items exist in the Current Item Set (CIS), the valve will execute the configured statement and populate the CIS with the result set (if any). If items exist in CIS the valve operates on them and will execute the statement on all Items surviving the common item filtering rules.

When transforming the statement result to items, the first column in the result set is used as id. Make sure your statement returns the columns in the correct order.

Note: This valve does not use prepared statements parameters – make sure that statement input is valid before executing to avoid sql-injection exploits.

Properties

Name Description Default value Mandatory Supports property expansion
connection_ref Id of connection to use. Must be of type 'JDBC' Yes No
statement The statement to execute Yes Yes
attributes List of attributes (columns) to extract from result set. If not specified all columns from result set will be added to the target item (and name from result set will be used). No No
binary_attrs List of attributes that should be treated as binary No No

Example Configuration 

{
        "name": "StatementExecutorValve",
        "config": { 
              "connection_ref":"cff2ddff-9585-47c5-9286-2bee2b731197",
              "statement":"SELECT uid,email FROM Users where uid = '{{request.User-Name}}'",
              "attributes": "uid,email"
        }
}

In the example above, the select statement will query the database for uid and email. These values will then be stored as properties on the current item.

Requirements