ExecuteSQL

ExecuteSQL is Clockspring's processor that allows you to perform SQL read queries against a defined database.  

While ExecuteSQL has many different properties available, we will review the most commonly used properties below:

Database Connection Pooling Service:

This property defines which DBCPConnectionPool you would like to connect to.   As described in DBCPConnectionPool, this service allows you to centralize database connections, the associated authentication parameters, timeouts, etc.

SQL Pre-Query: 

Here you include an optional semicolon-delimited list of queries executed before the main SQL query is executed. For example, set session properties before main query. It's possible to include semicolons in the statements themselves by escaping them with a backslash ('\;'). Results/outputs from these queries will be suppressed if there are no errors. 

SQL Select Query:

Add the select statement that you wish to execute against the defined database.  The query can be empty, a constant value, or built from attributes using Expression Language. 

If this property is specified, it will be used regardless of the content of incoming FlowFiles. However, if this property is empty, the content of the incoming FlowFiles is expected to contain a valid SQL select query, to be issued by the processor to the database. Note that Expression Language is not evaluated for FlowFile contents. 

SQL Post-Query: 

Similar to the SQL Pre-Query, include an optional semicolon-delimited list of queries executed after the main SQL query is executed. Example like setting session properties after main query. It's possible to include semicolons in the statements themselves by escaping them with a backslash ('\;'). Results/outputs from these queries will be suppressed if there are no errors. 

Result Format:

This property indicates the format that the output will be delivered in.   Defaults to JSON, but has option for Avro format type if desired.

Max Wait Time:

This indicates the maximum amount of the time the processor will wait for a result from the database.  A value of 0 seconds indicates that no limit is set and will wait indefinitely.

Default Decimal Precision:

When a DECIMAL/NUMBER value is written as a 'decimal' logical type, a specific 'precision' denoting number of available digits is required. Generally, precision is defined by column data type definition or database engines default. However undefined precision (0) can be returned from some database engines. 'Default Decimal Precision' is used when writing those undefined precision numbers. 

Max Rows Per FlowFile:

The number of results that will be included in the output FlowFile.  With a default value of 1, each row will be sent to its own FlowFile.  If set to 0, then all rows will be included in a single FlowFile.

Output Batch Size:

This property allows you to specify the number of output FlowFiles to queue before committing the process session. When this is set to 0, the session will be committed when all result set rows have been processed and the output FlowFiles are ready for transfer to the downstream relationship. 

For large result sets, setting this to 0 can cause a large burst of FlowFiles to be transferred at the end of processor execution. 

If this property is set to any value greater than 0, then when the specified number of FlowFiles are ready for transfer, then the session will be committed, thus releasing the FlowFiles to the downstream relationship. NOTE: The fragment.count attribute will not be set on FlowFiles when this property is set.