Configuring Connection Details

For the direction and binder name of this Micro-Integration, see Micro-Integration for Microsoft SQL Server .

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Step 1: Connecting to Your Event Broker .

For information about configuring error handling, see Step 5: Error Handling.

We recommend starting from the application.yml file provided in the samples/config directory of your downloaded archive, and updating it with the connection details described in this section, rather than building a configuration file from scratch. For more information, see Deploying Your Self-Managed Micro-Integration .

Before You Begin

  • The target Microsoft SQL Server table must already exist. This Micro-Integration does not automatically create schemas or tables; it reads the target table's column metadata at startup.

  • The target table must have a primary key that matches the column or columns you configure as the primary key for the Micro-Integration. For composite keys, this is a comma-separated list of column names.

  • The database user specified in the connection configuration must have SELECT privileges (to read column metadata), and INSERT and UPDATE privileges (both are required for the default UPSERT operation, which uses a SQL MERGE statement) on the target table.

  • Message payloads must be JSON. The Micro-Integration does not support XML payloads for Microsoft SQL Server targets.

To share settings across all workflows in a Micro-Integration, configure the binder-specific parameters under default at the binder level instead of under a workflow-specific identifier (for example, output-0) in bindings. This reduces repetition when multiple workflows use the same parameter values. Workflow-specific settings override default settings when both are present.

Note that default is a sibling of bindings; it is not nested under bindings. Use the same hierarchy under default as you use under bindings.<binding-name>. For example, for bindings.output-0.producer.someProperty=someValue the default setting is default.producer.someProperty=someValue.

spring:
  cloud:
    stream:
      <binder-name>:
        default:
          producer:
            <shared parameters>
        bindings:
          output-0:
            producer:
              <workflow-specific parameters>

Microsoft SQL Server Connection Details

Microsoft SQL Server supports SQL authentication (username and password). Configure the connection using the db.* properties in your application.yml file. For example:

db:
  hostname: sqlserver.example.com
  port-number: 1433
  database-name: mydatabase
  user: myuser
  password: mypassword
  encrypt: true
  trust-server-certificate: false

The following table lists the connection configuration options.

Property Type Valid Values Default Value Description

db.hostname

String

None

Required. The hostname of the SQL Server database server.

db.port-number

Integer

None

Required. The port of the SQL Server database server.

db.database-name

String

None

Required. The name of the SQL Server database.

db.user

String

None

Required. The SQL Server login username.

db.password

String

None

Required. The SQL Server login password.

db.encrypt

boolean

true, false

true

Optional. When true, indicates that the connection to SQL Server is encrypted using TLS.

db.trust-server-certificate

boolean

true, false

false

Optional. When true, indicates that the SQL Server certificate is trusted without validation. Use this option to accept self-signed certificates.

db.trust-store

String

None

Optional. The path to the trust store file used to validate the SQL Server certificate.

db.trust-store-password

String

None

Optional. The password for the trust store file.

db.host-name-in-certificate

String

None

Optional. The expected hostname in the server's TLS certificate. Use this option when the certificate hostname differs from the connection hostname.

Microsoft SQL Server Binder Configuration Options

The following properties are available at the Microsoft SQL Server binder level. The primary-key, operation, on-delete, delete-header, delete-value, and delete-key-header properties are prefixed with spring.cloud.stream.sqlserver.bindings.<outputname>.producer.endpoint.query-parameters.. The destination property uses the standard Spring Cloud Stream binding property spring.cloud.stream.bindings.<outputname>.destination.

Microsoft SQL Server Producer Configuration Options

The following configuration options are available for the Microsoft SQL Server producers.

Property Type Valid Values Default Value Description

destination

String

None

Required. The target database table, in the format schema.table. For example, dbo.customers.

primary-key

String

id

Optional. The column name used as the primary key for the MERGE/INSERT operation. For composite keys, separate column names with commas, for example id, id_2.

operation

String

UPSERT, INSERT

UPSERT

Optional. Sets the write behavior for the target table.

UPSERT uses a native SQL Server MERGE INTO statement, keyed on primary-key, to update existing records or insert new ones.

INSERT always inserts a new row and relies on database constraints to reject duplicates.

This property applies only to messages that are not delete-flagged. It does not delete rows, and you cannot set it to DELETE. To delete rows, set on-delete to HARD.

on-delete

String

IGNORE, HARD

IGNORE

Optional. Controls how delete-flagged messages are handled. The default value, IGNORE, skips delete-flagged messages without modifying the target table. Set to HARD to delete the matching row from the target table. An unrecognized value logs a warning and is treated as IGNORE. This property applies only to delete-flagged messages; the operation property controls all other messages.

delete-header

String

cdc_operation

Optional. The name of the message header that identifies a delete-flagged message.

delete-value

String

d

Optional. The value of delete-header that identifies a message as a delete.

delete-key-header

String

cdc_key

Optional. A message header containing a JSON object with the primary key column values to delete, used when the message payload does not include all primary key columns. If the message payload contains all primary key columns, those values take precedence over this header. The delete key must resolve every primary key column; a partial composite key is rejected and the row is not deleted.

You can override the configured destination and primary-key at runtime by setting the scst_targetDestination header on the message. This header always requires all three parts together, in the format schema.table.primaryKey (comma-separated for composite keys)—you cannot override just the destination or just the primary key on their own. For more information, see Dynamic Producer Destinations.

Null or absent fields in the incoming payload are omitted from the generated INSERT/MERGE statement rather than written as SQL NULL.

Insert, Upsert, and Delete Message Handling

The operation and on-delete properties work independently, and each applies to a different kind of message:

  • A message that is not delete-flagged is written using the operation property: UPSERT (the default) or INSERT.

  • A message is delete-flagged when the header named by delete-header has the value of delete-value. A delete-flagged message is handled using the on-delete property: it is skipped when set to IGNORE (the default), or it deletes the matching row when set to HARD.

The operation property cannot be set to DELETE. A single batch can contain both kinds of messages, and the batch is committed as one transaction. If any write in the batch fails, the entire batch is rolled back.

The following examples use the same binding. The only difference is the on-delete property.

Example: Inserting or Updating a Row

With the following configuration, a message that is not delete-flagged is written to dbo.customers using a MERGE INTO statement keyed on id.

spring:
  cloud:
    stream:
      bindings:
        output-0:
          destination: dbo.customers
      sqlserver:
        bindings:
          output-0:
            producer:
              endpoint:
                query-parameters:
                  primary-key: id
                  operation: UPSERT

Given a message that is not delete-flagged and has the payload {"id": 42, "name": "Ada"}, the row with id 42 is updated if it exists, or inserted if it does not.

Example: Deleting a Row

To also delete rows, add on-delete: HARD to the same binding. The operation property is still used for messages that are not delete-flagged.

spring:
  cloud:
    stream:
      bindings:
        output-0:
          destination: dbo.customers
      sqlserver:
        bindings:
          output-0:
            producer:
              endpoint:
                query-parameters:
                  primary-key: id
                  operation: UPSERT
                  on-delete: HARD

Given a delete-flagged message with the payload {"id": 42}, the row with id 42 is deleted from dbo.customers. If the payload does not contain the primary key columns, set the header named by delete-key-header to a JSON object with the key values, for example {"id": 42}.

If you do not set on-delete, the same delete-flagged message is skipped and the row is not changed.

Connecting to Multiple Systems

To connect to multiple systems of the same type, use the multiple binder syntax.

For example:

spring:
  cloud:
    stream:
      binders:

        # 1st solace binder in this example
        solace1:
          type: solace
          environment:
            solace:
              java:
                host: tcp://localhost:55555

        # 2nd solace binder in this example
        solace2:
          type: solace
          environment:
            solace:
              java:
                host: tcp://other-host:55555

        # The only sqlserver binder
        sqlserver1:
          type: sqlserver
          # Add `environment` property map here if you need to customize this binder.
          # But for this example, we'll assume that defaults are used.

        # Required for internal use
        undefined:
          type: undefined
      bindings:
        input-0:
          destination: <input-destination>
          binder: solace1 # Reference 1st solace binder
        output-0:
          destination: <output-destination>
          binder: sqlserver1
        input-1:
          destination: <input-destination>
          binder: solace2 # Reference 2nd solace binder
        output-1:
          destination: <output-destination>
          binder: sqlserver1

The preceding configuration defines two binders of type solace and one binder of type sqlserver, which are then referenced within the bindings.

Each preceding binder is configured independently under spring.cloud.stream.binders.<bindername>.environment..

  • If you connect to multiple systems, all binder configuration must be specified using the multiple binder syntax for all binders. For example, under the spring.cloud.stream.binders.<binder-name>.environment.

  • Do not use single-binder configuration (for example, solace.java.* at the root of your application.yml) while using the multiple binder syntax.