Chapter 56. Microsoft SQL Server Sink
Send data to a Microsoft SQL Server Database.
This Kamelet expects a JSON as body. The mapping between the JSON fields and parameters is done by key, so if you have the following query:
'INSERT INTO accounts (username,city) VALUES (:#username,:#city)'
The Kamelet needs to receive as input something like:
'{ "username":"oscerd", "city":"Rome"}'
56.1. Configuration Options
The following table summarizes the configuration options available for the sqlserver-sink Kamelet:
| Property | Name | Description | Type | Default | Example |
|---|---|---|---|---|---|
| databaseName * | Database Name | The Database Name we are pointing | string | ||
| password * | Password | The password to use for accessing a secured SQL Server Database | string | ||
| query * | Query | The Query to execute against the SQL Server Database | string |
| |
| serverName * | Server Name | Server Name for the data source | string |
| |
| username * | Username | The username to use for accessing a secured SQL Server Database | string | ||
| serverPort | Server Port | Server Port for the data source | string |
|
Fields marked with an asterisk (*) are mandatory.
56.2. Dependencies
At runtime, the sqlserver-sink Kamelet relies upon the presence of the following dependencies:
- camel:jackson
- camel:kamelet
- camel:sql
- mvn:org.apache.commons:commons-dbcp2:2.7.0.redhat-00001
- mvn:com.microsoft.sqlserver:mssql-jdbc:9.2.1.jre11
56.3. Usage
This section describes how you can use the sqlserver-sink.
56.3.1. Knative Sink
You can use the sqlserver-sink Kamelet as a Knative sink by binding it to a Knative object.
sqlserver-sink-binding.yaml
apiVersion: camel.apache.org/v1alpha1
kind: KameletBinding
metadata:
name: sqlserver-sink-binding
spec:
source:
ref:
kind: Channel
apiVersion: messaging.knative.dev/v1
name: mychannel
sink:
ref:
kind: Kamelet
apiVersion: camel.apache.org/v1alpha1
name: sqlserver-sink
properties:
databaseName: "The Database Name"
password: "The Password"
query: "INSERT INTO accounts (username,city) VALUES (:#username,:#city)"
serverName: "localhost"
username: "The Username"
56.3.1.1. Prerequisite
Make sure you have "Red Hat Integration - Camel K" installed into the OpenShift cluster you’re connected to.
56.3.1.2. Procedure for using the cluster CLI
-
Save the
sqlserver-sink-binding.yamlfile to your local drive, and then edit it as needed for your configuration. Run the sink by using the following command:
oc apply -f sqlserver-sink-binding.yaml
56.3.1.3. Procedure for using the Kamel CLI
Configure and run the sink by using the following command:
kamel bind channel:mychannel sqlserver-sink -p "sink.databaseName=The Database Name" -p "sink.password=The Password" -p "sink.query=INSERT INTO accounts (username,city) VALUES (:#username,:#city)" -p "sink.serverName=localhost" -p "sink.username=The Username"
This command creates the KameletBinding in the current namespace on the cluster.
56.3.2. Kafka Sink
You can use the sqlserver-sink Kamelet as a Kafka sink by binding it to a Kafka topic.
sqlserver-sink-binding.yaml
apiVersion: camel.apache.org/v1alpha1
kind: KameletBinding
metadata:
name: sqlserver-sink-binding
spec:
source:
ref:
kind: KafkaTopic
apiVersion: kafka.strimzi.io/v1beta1
name: my-topic
sink:
ref:
kind: Kamelet
apiVersion: camel.apache.org/v1alpha1
name: sqlserver-sink
properties:
databaseName: "The Database Name"
password: "The Password"
query: "INSERT INTO accounts (username,city) VALUES (:#username,:#city)"
serverName: "localhost"
username: "The Username"
56.3.2.1. Prerequisites
Ensure that you’ve installed the AMQ Streams operator in your OpenShift cluster and created a topic named my-topic in the current namespace. Make also sure you have "Red Hat Integration - Camel K" installed into the OpenShift cluster you’re connected to.
56.3.2.2. Procedure for using the cluster CLI
-
Save the
sqlserver-sink-binding.yamlfile to your local drive, and then edit it as needed for your configuration. Run the sink by using the following command:
oc apply -f sqlserver-sink-binding.yaml
56.3.2.3. Procedure for using the Kamel CLI
Configure and run the sink by using the following command:
kamel bind kafka.strimzi.io/v1beta1:KafkaTopic:my-topic sqlserver-sink -p "sink.databaseName=The Database Name" -p "sink.password=The Password" -p "sink.query=INSERT INTO accounts (username,city) VALUES (:#username,:#city)" -p "sink.serverName=localhost" -p "sink.username=The Username"
This command creates the KameletBinding in the current namespace on the cluster.
