Microsoft SQL Server

Connection Settings

Database

Name of the database to connect to.

Username

Username for authentication with the database.

Password

Password for authentication with the database.

Additional JDBC Options

Additional JDBC options to include in the JDBC connection string. This is MSSQL specific. Options are entered exactly as they would be if the JDBC connection string was created manually.

For example, if there are multiple instances running on the same server instanceName can be specified in this field. (eg. instanceName=myInstanceName)

Input Settings

Type

The type of input to use. The options are Query and CDC.

• When using a Query input, users can send a query to the corresponding database.
• When using a CDC input, users can track changes on a specified table

Query Settings

Sends the query to the database and returns the generated result, if there is any.

Query

The full SQL query, which can include any SQL syntax including stored procedures.

Index

This setting is used to index the SQL query and only get new data on each query. When enabled, specifies a Column Name and default Value to reference within the query. This name/value pair is cached across successive product runs and allows the next query to pick up where it left off.

Note the Column Name must be the column name in the table that is being used for indexing. An example query might be SELECT * FROM SimpleMachineX WHERE id > {{index}} By enabling indexing and specifying the Column Name as id and the Value as zero, the first time the query is run {{index}} is set to zero and after the query {{index}} is updated to the latest value from id. The latest value is cached to disk. Successive queries will use the latest value cached and then update the cached value on success.

To reset the index, navigate to the “Index” page and delete the index value. Note that test reads on the input done from the UI will use the current index Value but will not update the index Value.

Column Name

Specifies the column name used to index. To update the index, the column name must be returned in the SQL query.

Value

Specifies the initial value used for the index.

CDC Settings

Generates a standardized value that represents a SQL change event.

{
    "table": {
			"schema": "dbo", // The schema name for the tracked table
			"name": "cdc_table" // The name of the tracked table
	},
	"operation": "Update", // Can be Insert, Update, or Delete
	"primaryKey": {
         // Contains each primary key that belongs to the row along with their corresponding value(s)
    },
    "before": {
        // The state of the row before the operation occurred
        // Is available for Update and Delete operations
    }, 
    "after": {
        // The state of the row after the operation occurred
        // Is available for Update and Insert operations
    }
}

Insert Operation

{
    "table": {
			"schema": "dbo",
			"name": "cdc_table"
	},
	"operation": "Insert",
	"primaryKey": {
        "machineId": 1
    },
    "after": {
        "machineId": 1,
        "speed": 0.532
    }
}

Update Operation

{
    "table": {
			"schema": "dbo",
			"name": "cdc_table"
	},
	"operation": "Update",
	"primaryKey": {
        "machineId": 1
    },
    "before": {
        "machineId": 1,
        "speed": 0.532
    },
    "after": {
        "machineId": 1,
        "speed": 0.567
    }
}

Delete Operation

{
    "table": {
			"schema": "dbo",
			"name": "cdc_table"
	},
	"operation": "Delete",
	"primaryKey": {
        "machineId": 1
    },
    "before": {
        "machineId": 1,
        "speed": 0.567
    }
}

Table Name

Name of the table to track changes for.

Transaction Count

The max number of transaction (LSN) changes that are retrieved per read. Each transaction (LSN) could have multiple changes/rows associated with it.

Output Settings

Table

The name of the SQL table or stored procedure to write to. The table must already exist and have column names that match the model attribute names. Table names containing capital letters must be placed inside quotes (e.g., “MyTable”).

Write Type

Writes to the table can be Inserts, Updates, or Upserts. Inserts add the output data as new rows to the table. Updates update existing rows that have a matching value of the attribute referenced in the Where Column. If there are no matches Update does nothing. Upsert performs an Update if there are matching rows, and if there are no matching rows it performs an Insert.

Where Clause

Specify the body of the where clause used to filter updated records. This setting is used if the Write Type is set to Update or Upsert. As an example, assume the table being to contains a column ‘batchId’. To only update rows that contain a ‘batchId’ greater than 15, the Where Clause would be set to “batchId > 15”. To specify multiple columns and conditions users can use conditional logic such as “AND” and “OR”. This field supports dynamic outputs.

Log as JSON

Specifies whether modeled values should be serialized as a single column JSON blob versus expanding to multiple columns. When enabled, the column name is ‘json’.

Create Table

Controls how the table definition is managed. Options are Off, Create, and Create.

• When Off is used, it’s assumed that the table schema is defined and managed externally. The table must exist. See Table Cache Interval for details on how to respond to table schema changes.
• When Create is used, it’s assumed the connection manages the table and it’s created once. If the table doesn’t exist it’s created using the schema of the payload.
• When Create & Update is used, it’s assumed the connection manages the table and updates the table on schema change. For example, if a new attribute is added to the data, the column is added to the table.

When creating a table the data from the write is used to control the table schema. All tables are created with an auto incrementing _id column used as the primary key. Tables will also include _name, _model, and _timestamp columns unless the output filters these out. Below is a mapping of write data types to SQL data types. Note strings are limited in size to 256 characters. This must be changed by altering the table after creating if larger strings are required.

Table Cache Interval

This setting is useful for scenarios where there have been external modification to the table schema, and those updates should be reflected in the internally cached table schema. This setting is only available when Create Table is set to Off. Disable this refresh by setting the time interval to 0.

MSSQL Windows Authentication

To configure the MSSQL connector to use Windows Authentication perform the following steps.

1. Set integratedSecurity=true in the Connection String

   • Found in the details pane of the MSSQL Connection in HighByte Intelligence Hub

   • Leave User and Password field blank

2. Stop HighByte Runtime

   • For Windows, execute stop-windows.bat file in HB runtime directory

3. Download Microsoft’s JDBC package https://github.com/microsoft/mssql-jdbc/releases/tag/v9.2.1. This is a native Windows dependency required by the MSSQL JDBC driver to authenticate. Ensure you download the correct version for your MSSQL server.

   • Download mssql-jdbc-9.2.1.jre11.jar and mssql-jdbc_auth.zip

4. Extract file mssql-jdbc_auth-9.2.1.x64.dll found in mssql-jdbc_auth.zip at the path \auth\x64\mssql-jdbc_auth-9.2.1.x64.dll

5. Copy mssql-jdbc_auth-9.2.1.x64.dll to HighByte-Intelligence-Hub-x.x.x\runtime directory

6. Delete file mssql-jdbc-8.4.1.jre11.jar found in HighByte-Intelligence-Hub-x.x.x\runtime\lib directory

7. Copy mssql-jdbc-9.2.1.jre11.jar to HighByte-Intelligence-Hub-x.x.x\runtime\lib directory

8. Start HighByte Runtime

   • For Windows, execute start-windows.bat file in HB runtime directory

Considerations

1. Please ensure that the jar version and dll version match.

2. This will fix the This driver is not configured for integrated authentication error.

3. Ensure TCP/IP connections are enabled for the MSSQL Server.

SSL Connection Errors

When connecting to older SQL servers with older encryption, it’s possible to see errors like this.

“The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “The server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]”

To get around this connection issue add useSSL=false to the JDBC Connection string.