Customizing the SQL Connector

ThingWorx Flow > Out-of-the-Box Services, Utilities, and Devices > Services > SQL Connector > Customizing the SQL Connector

The following table provides the list of data types and their supported operators:

Data Type	Supported Operators
• INT • BIGINT • MEDIUMINT • SMALLINT • TINYINT • NUMBER • FLOAT • REAL • DECIMAL • NUMERIC • DOUBLE • DOUBLE PRECISION • SMALLSERIAL • SERIAL • BIGSERIAL • INTEGER • PRECISION • DEC • MONEY • SMALLMONEY	• GREATER THAN • GREATER THAN OR EQUAL TO • LESS THAN • LESS THAN OR EQUAL TO • EQUAL TO • NOT EQUAL TO • BETWEEN • NOT BETWEEN • IN • NOT IN • IS NULL • IS NOT NULL
• TEXT • TINYTEXT • MEDIUMTEXT • LONGTEXT • NTEXT • CHAR • VARCHAR • VARCHAR2 • NCHAR • NVARCHAR • VARYING • CHARACTER • CHARACTER VARYING	• EQUAL TO • NOT EQUAL TO • LIKE • NOT LIKE • STARTS WITH • ENDS WITH • SUBSTRING • IS NULL • IS NOT NULL
• DATE • DATETIME • DATETIME2 • TIME • TIMESTAMP WITH TIME ZONE • TIMESTAMP • DATETIMEOFFSET • SMALLDATETIME • YEAR • INTERVAL	• BEFORE • AFTER • BETWEEN • NOT BETWEEN • IN • NOT IN • IS NULL • IS NOT NULL
Any other data types	• EQUAL TO • NOT EQUAL TO • IS NULL • IS NOT NULL

You can add an operator for any data type for the following actions:

• Delete Rows

• Get Rows

• Get Rows with Join

• Update Rows

To customize an operator, perform the following steps:

1. Create the data.json file as shown below:

{
"DatatypeOperatorConfig": [{
"dataTypeName":"Any_Data_Type",
"supportedDBs":["Database1","Database2"],
"Operators":[{
"id":"OPERATOR_ID",
"value":"OPERATOR"
}]
}]
}

For example, if you want to add the LIKE operator for Oracle database for unsupported data type, RAW, your data.json file must look like the following:

{
"DatatypeOperatorConfig": [{
"dataTypeName":"RAW",
"supportedDBs":["oracle"],
"Operators":[{
"id":"LIKE",
"value":"LIKE"
}]
}]
}

2. Browse to the path where you have saved the data.json file, and start Command Prompt.

3. Depending on your use case, run one of the following commands:

Scenario	Command
Customize a specific database host name and port	flow-deploy settings file upload -f <Path to data.json file> -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database - s <Database Host><Database Port> For example: flow-deploy settings file upload -f data.json -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database -s localhost32776
Customize all connected instances of the database	flow-deploy settings file upload -f <Path to data.json file> -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database -d

Scenario

Command

Customize a specific database host name and port

flow-deploy settings file upload -f <Path to data.json file> -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database - s <Database Host><Database Port>

For example:

flow-deploy settings file upload -f data.json -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database -s localhost32776

Customize all connected instances of the database

flow-deploy settings file upload -f <Path to data.json file> -t <ThingWorx Flow URL> -u <ThingWorx Administrator User> -p '<ThingWorx Administrator User Password>' -c database -d

4. Refresh the browser.

Now, when you drag one of the supported actions to the Workflow Editor, you should see the operator in the Operators list.

Was this helpful?