dbStatement

Processor that runs a statement against a database.

Choose the statement type for a query, update, or stored procedure call.

Input Binding

Use named parameters to insert dynamic values into the statement. Write a named parameter as :<name>. For example: SELECT * FROM members WHERE age = :age.

The processor looks up each named parameter in the payload first, then in the exchange properties. Use exchange properties as defaults for values that are missing from the payload.

If an input parameter is missing, the processor fails before it runs the statement. An input parameter whose value is null binds SQL NULL.

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
Types are resolved automatically in named parameter mode. Do not add quotes around string values. The processor quotes each value based on its type in the payload. In the example above, :position resolves to 'Admin' (with quotes) and :age resolves to 43 (without quotes).
The :<name> syntax works only for top-level properties of the payload or the exchange properties. For nested properties, use the :##<expression>## syntax described below.

For statement types SelectOne, SelectList, and Update, you can use a named parameter that resolves to a list 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)

To read nested properties or transform a value before it goes into the statement, use a JSONiq or SpEL expression with the syntax :##<expression>##. Each expression must fit on one line, and it ends at the first closing ##. In the following example, the first expression is SpEL and the second is JSONiq:

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

  • Configured statement:

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

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

Types are also resolved automatically in ## expression mode. The processor quotes expression values based on their resolved type, as it does for :<name>.

For statement types SelectOne, SelectList, and Update, you can insert an expression value into the statement as raw SQL text with the syntax :@@<expression>@@. Each expression must fit on one line, and it ends at the first closing @@.

The :@@<expression>@@ syntax can expose the database to SQL injection. The processor inserts the value as raw SQL, without quoting or escaping. If an attacker can influence the value, they can run any SQL action that the configured database account permits, including reads and writes. Always use database credentials with the least privileges the flow needs. Use :@@<expression>@@ only for special cases, such as setting a table or column name from a value that an attacker cannot control. Never use it for ordinary values; use :<name> or :##<expression>## instead. The following example shows a valid use:

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

  • Set exchange property to targetDatabase=admins

  • Before the dbStatement processor, a map processor checks that targetDatabase matches a list of allowed values

  • Configured statement:

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

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

Statement type Update supports batching. If the payload is a list of objects and the batching processor property is true, the processor builds one statement for each object and runs the statements 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');

Stored Procedure Calls

To call a stored procedure, set statementType = StatementType.Call and write a native CALL statement in statement. Qualify the procedure name as needed.

Call Arguments

In statement, use :name or :in:name for inputs, :out:name for outputs, and :inout:name for input/output arguments. Every OUT/INOUT argument must have a direction tag. A placeholder without a tag is an input.

Use positional arguments in database argument order. Named notation (an argument name followed by an arrow) is not supported. The arguments must match one of the procedure’s signatures. Placeholder names do not need to match the procedure’s argument names.

An input argument can also be a literal or an SQL expression, for example 1 or UPPER(:name). OUT and INOUT placeholders cannot be part of an expression: :out:status works, but NVL(:out:status, 0) does not.

An INOUT argument takes an input value and returns its updated value.

An input name can occur more than once. An OUT or INOUT name must occur only once in the statement.

Direction tags are case-insensitive and must be immediately followed by the parameter name, for example :out:status. :in, :out, and :inout are inputs named in, out, and inout.

Call Output Mapping

A call replaces the payload with a map. Each OUT and INOUT value uses their placeholder names as keys, without the direction tag. Implicitly returned result sets (a result set returned without an OUT argument) use the names configured in callResultSetNames.

Cursor outputs and implicitly returned result sets are lists of row maps. A null output, such as a null cursor, stays in the map with the value null.

The map does not include input-only arguments, update counts, or generated keys. If the call returns no outputs and no implicitly returned result sets, the map is {}.

Scalar OUT and INOUT Values

This call takes accountId as an input, updates balance, and returns status:

dbStatement {
    connectionString = "..."
    statementType = StatementType.Call
    statement = "CALL accounts.adjust(:accountId, :inout:balance, :out:status)"
}

Input payload:

{
  "accountId": 42,
  "balance": 100
}

Result payload:

{
  "balance": 125,
  "status": null
}

The result contains the updated balance (INOUT) and the status (OUT) output under their placeholder names. It does not include the input-only accountId. The status key is included even with the null value.

Cursor OUT Values (Oracle)

In Oracle, a cursor OUT argument has the type SYS_REFCURSOR. Use the same :out:name syntax. This procedure returns three cursors:

dbStatement {
    connectionString = "..."
    statementType = StatementType.Call
    statement = "CALL accounts.list_activity(:accountId, :out:transactions, :out:pending, :out:archived)"
}

Input payload:

{
  "accountId": 42
}

Result payload:

{
  "transactions": [
    {
      "ID": "7",
      "AMOUNT": "25"
    }
  ],
  "pending": [],
  "archived": null
}

Each cursor is a list of rows under its placeholder name, with column names as row keys. Here, transactions has one row, pending is an open cursor with no rows, and archived is a null cursor.

Implicitly Returned Result Sets (Oracle)

An implicitly returned result set is a result set that a call returns without a matching OUT argument. In Oracle, a procedure returns them with DBMS_SQL.RETURN_RESULT. This procedure returns two such sets. Set callResultSetNames to their names, in the order the procedure returns them:

dbStatement {
    connectionString = "..."
    statementType = StatementType.Call
    statement = "CALL accounts.return_activity(:accountId)"
    callResultSetNames = listOf("transactions", "pending")
}

Input payload:

{
  "accountId": 42
}

Result payload:

{
  "transactions": [
    {
      "ID": "7",
      "AMOUNT": "25"
    }
  ],
  "pending": []
}

The first result set becomes transactions and the second becomes pending. The statement has no :out: placeholders.

Implicit Result Set Names

The procedure must return one result set for each name in callResultSetNames, or the call fails. Result sets map to payload fields in the order of the names. If the list is empty or not set, the procedure must return no implicitly returned result sets. A result set with no rows still counts, and becomes []. If an expected result set is missing, the call fails; no empty list or null replaces it.

Names must not be blank, must be unique, and must differ from the OUT and INOUT placeholder names. A non empty list is valid only for statement type Call.

Call Failures and Limitations

A failed call returns no partial results. A failure does not guarantee a rollback: the procedure might already have changed or committed data.

These limitations apply to all calls:

  • Use :##expression## for JSONiq or SpEL input expressions, without direction tags. Raw :@@expression@@ expansion and procedural blocks are not supported.

  • Stored function return syntax, cursor IN or INOUT arguments, collections, and structured arguments are not supported.

  • Call batching is not supported.

Database Support

PostgreSQL

Calls with scalar arguments are supported. Cursor OUT arguments and implicitly returned result sets are not supported. OUT-only arguments require PostgreSQL 14 or later.

Oracle

Calls with scalar arguments, SYS_REFCURSOR OUT arguments, and implicitly returned result sets from DBMS_SQL.RETURN_RESULT are supported. Row values are converted as Automatic Outbound Payload Conversion describes. No cursor handles are passed to later processors.

Oracle calls have these requirements:

  • Supply every argument, including arguments with a DEFAULT value.

  • Match each direction tag to the procedure’s argument directions. A mismatch fails the call, and the error names the argument.

  • For an overloaded procedure, supply a number of arguments that matches only one overload. Overloads with the same argument count are not supported.

  • Name the procedure directly, qualified by schema or package, or through a synonym (an alias for the procedure). Procedures reached through a database link are not supported.

Other Databases

Call support for other listed databases has not been verified.

Result Payload

The format of the returned results is determined by the statementType property:

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

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

  • Update: Returns a JSON-compliant map with a single noOfUpdates entry indicating the number of rows the insert or update affected. For example: {"noOfUpdates": 2}.

  • Call: Returns the output map of the call. See Call Output Mapping.

The result replaces the input payload. To keep input values for later processors, save them with setExchangeProperty before dbStatement.

For row limits, see maxRows.

Inbound Payload Conversion

If the processor cannot read parameter values from the payload as it arrives, convert it with the optional inboundTransformationStrategy. For example, if the payload is JSON text, set inboundTransformationStrategy { fromJsonText().toJsonCompliant() }. The processor then reads named parameters from the JSON-compliant object.

Automatic Outbound Payload Conversion

The processor converts each value in the result from its JDBC type to a JSON-compliant type:

  • NULL: null

  • BIT(1), BOOLEAN: Boolean

  • TINYINT, SMALLINT, INTEGER: Int

  • BIGINT: Long

  • REAL: Float

  • FLOAT, DOUBLE: Double

  • NUMERIC, DECIMAL: String

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

  • ARRAY: List of JSON-compliant member objects

  • STRUCT: List of JSON-compliant attribute value objects

  • REF: Not supported

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

Parallelism

By default, the processor uses one database connection and processes messages one at a time. This default suits only low-traffic flows. To process messages in parallel, configure the processingStrategy sub-builder. For example: processingStrategy { concurrencyMaxNo = 15 }.

Supported Databases

  • Microsoft SQL Server

  • Oracle

  • Redshift

  • PostgreSQL

  • ClickHouse

  • Arrow Flight SQL

Arrow Flight SQL has been tested only with Anybase. See Anybase support.

Anybase Support

Anybase differs from the other databases in these ways:

  • Anybase versions up to 0.20.0-build.691 do not support DATE, TIME, TIMESTAMP, or UBIGINT parameters.

  • Provide DATE, TIME, and TIMESTAMP parameters as numbers: days since the epoch, milliseconds since midnight, and milliseconds since the epoch. Support for ISO string parameters is expected in an upcoming Anybase version.

  • Timestamp parameters might be offset by the local time zone. A fix is expected in an upcoming Anybase version.

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 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).

For statement type Call, use a CALL statement with named argument placeholders. See dbStatement for argument syntax, result naming, and examples.

One and only one statement is supported. If multiple semicolon-separated statements are provided, only the first one will be executed and the rest will be silently ignored. For example, given INSERT INTO logs VALUES (:id); UPDATE counter SET n = n + 1, only the log entry will be inserted. The counter will not be updated.

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.

  • Call: Calls a stored procedure, with or without output arguments.

callResultSetNames

Name each implicitly returned result set, in delivery order. Supply exactly one name per result set. Use an empty list when the procedure returns zero result sets implicitly. Result-set names must not match OUT/INOUT placeholder names. See dbStatement for the definition and output examples.

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 fetched from a result set. Rows are fetched in database delivery order; any remaining rows are silently ignored without failing the statement.

For Call, the limit applies separately to each cursor OUT or implicitly returned result set.

Optional, defaults to 1000. The deployment environment sets an upper limit, which defaults to 10000. Values above that limit are rejected when the flow is deployed.

batching

Whether to do batched processing when the payload is a list of objects. Optional and defaults to false. Supported only for statement type Update.

retainPayloadOnFailure

Whether the incoming payload is available for error processing on failure. 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.

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.

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.

messageLoggingStrategy

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

payloadArchivingStrategy

Strategy for archiving payloads.

Details

Authentication

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