connect-jdbc-sqlserver-source

JDBC SQL Server source connector

Objective

Quickly test JDBC SQL Server connector.

How to run

Simply run:

$ ./sqlserver-jtds.sh (with [JTDS](http://jtds.sourceforge.net) driver)

$ ./sqlserver-microsoft.sh (with [Microsoft](https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server) driver)

Details of what the script is doing

Load inventory.sql to SQL Server

$ cat inventory.sql | docker exec -i sqlserver bash -c '/opt/mssql-tools/bin/sqlcmd -U sa -P Password!'

JTDS JDBC driver

Creating JDBC SQL Server (with JTDS) source connector

$ curl -X PUT \
     -H "Content-Type: application/json" \
     --data '{
               "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
                    "tasks.max": "1",
                    "connection.url": "jdbc:jtds:sqlserver://sqlserver:1433/testDB",
                    "connection.user": "sa",
                    "connection.password": "Password!",
                    "table.whitelist": "customers",
                    "mode": "incrementing",
                    "incrementing.column.name": "id",
                    "topic.prefix": "sqlserver-jtds-",
                    "validate.non.null":"false",
                    "errors.log.enable": "true",
                    "errors.log.include.messages": "true"
          }' \
     http://localhost:8083/connectors/sqlserver-source/config | jq .

Microsoft JDBC driver

$ curl -X PUT \
     -H "Content-Type: application/json" \
     --data '{
               "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
                    "tasks.max": "1",
                    "connection.url": "jdbc:sqlserver://sqlserver:1433;databaseName=testDB",
                    "connection.user": "sa",
                    "connection.password": "Password!",
                    "table.whitelist": "customers",
                    "mode": "incrementing",
                    "incrementing.column.name": "id",
                    "topic.prefix": "sqlserver-",
                    "validate.non.null":"false",
                    "errors.log.enable": "true",
                    "errors.log.include.messages": "true"
          }' \
     http://localhost:8083/connectors/sqlserver-source/config | jq .

Insert one more row:

$ docker exec -i sqlserver /opt/mssql-tools/bin/sqlcmd -U sa -P Password! << EOF
USE testDB;
INSERT INTO customers(first_name,last_name,email) VALUES ('Pam','Thomas','[email protected]');
GO
EOF

Verifying topic sqlserver-customers

$ docker exec connect kafka-avro-console-consumer -bootstrap-server broker:9092 --property schema.registry.url=http://schema-registry:8081 --topic sqlserver-customers --from-beginning --max-messages 5

Results:

{"id":1001,"first_name":"Sally","last_name":"Thomas","email":"[email protected]"}
{"id":1002,"first_name":"George","last_name":"Bailey","email":"[email protected]"}
{"id":1003,"first_name":"Edward","last_name":"Walker","email":"[email protected]"}
{"id":1004,"first_name":"Anne","last_name":"Kretchmar","email":"[email protected]"}
{"id":1005,"first_name":"Pam","last_name":"Thomas","email":"[email protected]"}

N.B: Control Center is reachable at http://127.0.0.1:9021