dbStatement

Processor that executes a statement against a database.

Any legal statement for the target database is supported. Dynamic values can be injected into the statement using named parameters with the syntax :<name>. For example: SELECT * FROM members WHERE age = :age.

Named parameters are looked up from the payload. If the parameter value can not be found on the payload, it will be looked up from the exchange properties. Thus, exchange properties can be used as defaults for values missing from the payload. For example:

  • Payload: {"age": 43, "position": "Admin"}

  • Exchange property: score=5

  • Configured statement:

    SELECT * FROM members
WHERE age = :age AND role = :position AND score = :score

  • Resolved statement:

    SELECT * FROM members
WHERE age = 43 AND role = 'Admin' AND score = 5

If the named parameter resolves to a list, you can use it in an IN clause. For example:

  • Payload: {"ages": [43, 57, 89]}

  • Configured statement: SELECT * FROM members WHERE age IN (:ages)

  • Resolved statement: SELECT * FROM members WHERE age IN (43, 57, 89)

Parameters can also be looked up via expression with the syntax :##<expression>##. For example:

  • Payload: {"age": 43, "position": "Admin"}

  • Configured statement:

    INSERT INTO members (age, role)
VALUES (
    :## envelope.payload['age'] - 10 ##,
    :## envelope.payload['position'].toUpperCase() ##
)

  • Resolved statement: INSERT INTO members (age, role) VALUES (33, 'ADMIN')

It’s possible to build dynamic queries at runtime by inserting expression values verbatim into the statement text with the syntax :@@<expression>@@. For example:

  • Payload: {"age": 43, "position": "Admin"}

  • Exchange property: targetDatabase=admins

  • Configured statement:

    INSERT INTO :@@ exchangePropertiesMap['targetDatabase'] @@ (age, role)
VALUES (:age, :position)

  • Resolved statement: INSERT INTO admins (age, role) VALUES (43, 'Admin')

The processor supports batched processing. If the payload is a list of objects, and the batching processor property is true, a statement will be built for each of the objects in the list. The statements will be executed as one batched transaction. For example:

  • Payload: [{"age": 43, "position": "Admin"}, {"age": 25, "position": "User"}]

  • Configured statement: INSERT INTO members (age, role) VALUES (:age, :position)

  • Resolved statements:

    INSERT INTO members (age, role) VALUES (43, 'Admin');
INSERT INTO members (age, role) VALUES (25, 'User');

Result Payload

The format of the returned results is determined by the statementType property. The following options apply:

  • SelectOne: Returns a JSON compliant map of the selected data with column names as keys. For example: {"age": 43, "position": "Admin"}.

  • SelectList: Returns a JSON compliant list of maps of the selected data with column names as keys. For example: [{"age": 43, "position": "Admin"}, {"age": 25, "position": "User"}].

  • Update: Returns a JSON compliant map with a single noOfUpdates entry indicating how many rows were affected by the insert or update. For example: {"noOfUpdates": 2}.

Inbound Payload Conversion

The processor takes an optional inboundTransformationStrategy that can be used to transform the inbound payload to a format that is suitable for pulling values into your statement. If your input payload is a JSON string, and you set the objectConversionFormat property to MarshallingFormat.JSON, it will be transformed to the standard JSON compliant object format that named parameters are fetched from.

Automatic Outbound Payload Conversion

The processor automatically converts the outbound payload to a JSON compliant format based on the following rules, from JDBC types to JSON compliant types:

  • NULL: null

  • BIT(1), BOOLEAN: Boolean

  • TINYINT, SMALLINT, INTEGER: Int

  • BIGINT: Long

  • REAL: Float

  • FLOAT, DOUBLE: Double

  • BINARY, VARBINARY, LONGVARBINARY, BLOB: Base64 encoded String

  • ARRAY: List of JSON compliant member objects

  • STRUCT: List of JSON compliant attribute value objects

  • REF: REF type is not supported

  • BIT(n) where n > 1, other types: String

Parallelism

Each instance of the processor owns a single database connection and will process messages sequentially. This default behaviour is only suitable for low-traffic scenarios. You will most likely want to add some parallelism via the processingStrategy sub-builder. For example: processingStrategy { concurrencyMaxNo = 15 }.

Properties

Name Summary

connectionString

String containing all the information, except credentials (see authenticationConfigKey), needed to establish the database connection. Typically, a JDBC URL.

authenticationConfigKey

A secret key that the server uses to look up the credentials needed to authenticate the client with the database. Optional, but typically required for most connections.

statement

Required statement to execute on the database. The statement is typically a SQL string with optional named parameters (e.g., SELECT * FROM members WHERE age = :age).

statementType

Required execution type of the statement. Legal values include the following:

  • SelectOne: Single row lookup. Will fail if no data found.

  • SelectList: Regular select. Returns empty list if no data found.

  • Update: Database insert or update.

executionTimeoutSeconds

The maximum number of seconds the statement is allowed to execute without completing. If the threshold is passed, the execution is interrupted, and a transient (retryable) error is raised. However, when executing within a transaction that is set up with an explicit timeout, that timeout will be used instead of the configured execution timeout. The transaction timeout setting is database specific. executionTimeoutSeconds is optional and defaults to 15. There is a maximum value for this property that is checked when the flow is deployed. By default, the maximum value is 60, but this can be overridden in some deployment environments, so check with the system administrator if you are unsure.

maxRows

The maximum number of rows to return for a query. Additional rows will be dropped quietly. Optional and defaults to 1000. There is a maximum value for this property that is checked when the flow is deployed. By default, the maximum value is 10000, but this can be overriden in some deployment environments, so check with the system administrator if you are unsure.

batching

Whether to do batched processing when the payload is a list of objects. Optional and defaults to false.

name

Optional, descriptive name for the processor.

id

Required identifier of the processor, unique across all processors within the flow. Must be between 3 and 30 characters long; contain only lower and uppercase alphabetical characters (a-z and A-Z), numbers, dashes ("-"), and underscores ("_"); and start with an alphabetical character. In other words, it adheres to the regex pattern [a-zA-Z][a-zA-Z0-9_-]{2,29}.

exchangeProperties

Optional set of custom properties in a simple jdk-format, that are added to the message exchange properties before processing the incoming payload. Any existing properties with the same name will be replaced by properties defined here.

retainPayloadOnFailure

Whether the incoming payload is available for error processing on failure. Defaults to false.

Sub-builders

Name Summary

externalSystemDetails

Strategy for describing the external system integration. Optional.

processingStrategy

Strategy for providing message processing hints to the server. Optional.

circuitBreakerStrategy

Strategy for configuring the processor’s circuit breaker. Optional.

messageLoggingStrategy

Strategy for describing how a processor’s message is logged on the server.

payloadArchivingStrategy

Strategy for archiving payloads.

inboundTransformationStrategy

Strategy that customizes the conversion of an incoming payload by a processor (e.g., string to object). Should be used when the processor’s default conversion logic cannot be used.

Details

Authentication

The authenticationConfigKey property supports secrets of type UserNameAndPassword. See the Secret Types documentation for formatting details.