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
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?