JDBC Driver

Connection Settings

JDBC Connection String

The full JDBC connection string required by the driver. Please consult your driver documentation.

Class Path

Class path of the JDBC driver. Please consult your driver documentation.

Username

Username for authentication.

Password

Password for authentication.

SQL Syntax

Controls the quoting and case sensitivity syntax used by the driver. Options include the syntax used by other common SQL drivers. If not set, SQL Server syntax is used.

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

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.