Supported SQL Connector Types
Add an SQL Connector connector type to authorize ThingWorx Flow to execute SQL Connector actions. ThingWorx Flow supports the following connector types for SQL Connector:
|
Connector Type
|
Supported Connections
|
|---|---|
|
• Basic
• One-way SSL
• Two-way SSL
|
|
|
• Basic
• One-way SSL
|
|
|
• Basic
• One-way SSL
• Two-way SSL
|
|
|
• Basic
• One-way SSL
• Two-way SSL
|
Before creating a one-way SSL or two-way SSL connection for any connector, ensure that you set up one-way SSL and two-way SSL connections for SQL Connector.
Adding a PostgreSQL connector
1. Drag any action under SQL Connector to the canvas, place the pointer on the action, and then click 
or double-click the action.
or double-click the action.2. For Connector Type, select PostgreSQL.
3. In the PostgreSQL list, select Add New.
4. Edit the Connector Label field, if you want. Labels are case-sensitive. Do not use spaces, special characters, and leading numbers.
5. In the Host field, enter the database host.
6. In the Port field, enter the database port.
7. In the Database Name field, enter the name of the database.
8. In the Schema Name field, enter the schema name. By default, this is set to public.
9. Enter the correct User Name and Password to authorize the database connection.
10. In the SSL Mode list, select one of the following options:
▪ Disabled—Select to configure a basic connection. If you select Disabled, skip to Step 13 of this procedure.
▪ Verify CA—Select to configure a one-way SSL or two-way SSL connection. Validate the server certificate against the Key for CA Certificate value.
▪ Verify Full—Select to configure a one-way SSL or two-way SSL connection. Validate the server certificate against the Key for CA Certificate value and validate that the certificate CN (common name) matches with the value of the Host field.
11. In the Validate Server Certificate list, select true if you want the server certificate to be validated.
Select false if you do not want the server certificate to be validated. This is not recommended for a production environment.
If you set Validate Server Certificate to true and the server certificate is signed by a certificate authority that cannot be validated, the connection fails.
12. In the following fields, specify the key names that are used to load the server certificates into the ThingWorx Flow TrustStore.
▪ Key for CA Certificate—Key name used to load the server certificates into the ThingWorx Flow TrustStore. This field is required when the database server certificate is self-signed or signed by a non-standard certificate authority. This field is not required if you set Validate Server Certificate to false.
▪ Key For Client Certificate Public Key—Key name used to load the client certificate public key. This is not required if you are setting up a one-way SSL connection.
▪ Key For Client Certificate Private Key—Key name used to load the client certificate private key. This is not required if you are setting up a one-way SSL connection.
You can obtain these values while setting up one-way SSL and two-way SSL connection for the SQL Connector.
13. Click ADD to add the PostgreSQL connector.
Adding an SQL Server connector
1. Drag any action under SQL Connector to the canvas, place the pointer on the action, and then click or double-click the action.
or double-click the action.2. For Connector Type, select SQL Server.
3. In the SQL Server list, select Add New.
4. Edit the Connector Label field, if you want. Labels are case-sensitive. Do not use spaces, special characters, and leading numbers.
5. In the Host field, enter the database host.
6. In the Port field, enter the database port.
7. In the Database Name field, enter the name of the database.
8. In the Schema Name field, enter the schema name. By default, this is set to dbo.
9. In the User Name and Password fields, enter the correct user name and password to authorize the database connection.
10. In the SSL Mode list, select one of the following options:
▪ Disabled—Select to configure a basic connection. If you select Disabled, skip to Step 13 of this procedure.
▪ Verify Full—Select to configure a one-way SSL or two-way SSL connection. Validate the server certificate against the Key for CA Certificate value and validate that the certificate CN (common name) matches with the value of the Host field.
11. In the Validate Server Certificate list, select true if you want the server certificate to be validated.
Select false if you do not want the server certificate to be validated. This is not recommended for a production environment.
If you set Validate Server Certificate to true and the server certificate is signed by a certificate authority that cannot be validated, the connection fails.
12. In the Key for CA Certificate field, specify the key name used to load the server certificates into the ThingWorx Flow TrustStore. This field is required when the database server certificate is self-signed or signed by a non-standard certificate authority. This field is not required if you set Validate Server Certificate to false.
13. Click ADD to add the SQL Server connector.
Adding a MySQL connector
1. Drag any action under SQL Connector to the canvas, place the pointer on the action, and then click or double-click the action.
or double-click the action.2. For Connector Type, select MySQL.
3. In the MySQL list, select Add New.
4. Edit the Connector Label field, if you want. Labels are case-sensitive. Do not use spaces, special characters, and leading numbers.
5. In the Host field, enter the database host.
6. In the Port field, enter the database port.
7. In the Database Name field, enter the name of the database.
8. In the User Name and Password fields, enter the correct user name and password to authorize the database connection.
9. In the SSL Mode list, select one of the following options:
▪ Disabled—Select to configure a basic connection. If you select Disabled, skip to Step 12 of this procedure.
▪ Verify CA—Select to configure a one-way SSL or two-way SSL connection. Validate the server certificate against the Key for CA Certificate value.
10. In the Validate Server Certificate list, select true if you want the server certificate to be validated.
Select false if you do not want the server certificate to be validated. This is not recommended for a production environment.
If you set Validate Server Certificate to true and the server certificate is signed by a certificate authority that cannot be validated, the connection fails.
11. In the following fields, specify the key names that are used to load the server certificates into the ThingWorx Flow TrustStore.
▪ Key for CA Certificate—Key name used to load the server certificates into the ThingWorx Flow TrustStore. This field is required when the database server certificate is self-signed or signed by a non-standard certificate authority. This field is not required if you set Validate Server Certificate to false.
▪ Key For Client Certificate Public Key—Key name used to load the client certificate public key. This is not required if you are setting up a one-way SSL connection.
▪ Key For Client Certificate Private Key—Key name used to load the client certificate private key. This is not required if you are setting up a one-way SSL connection.
You can obtain these values while setting up one-way SSL and two-way SSL connection for the SQL Connector.
12. Click ADD to add the MySQL connector.
Adding an Oracle connector
1. Drag any action under SQL Connector to the canvas, place the pointer on the action, and then click or double-click the action.
or double-click the action.2. For Connector Type, select Oracle.
3. In the Oracle list, select Add New.
4. Edit the Connector Label field, if you want. Labels are case-sensitive. Do not use spaces, special characters, and leading numbers.
5. In the SSL Mode list, select one of the following options, and do the following:
▪ Select Disabled to configure a basic connection, and do the following:
1. In the Host field, enter the database host.
2. In the Port field, enter the database port.
3. In the Database Name field, enter the name of the database. This is the service name of the database.
4. In the Schema Name field, enter the schema name. By default, this is set to the user name. If you want to connect to the database of another user, then enter the user name of that user.
5. In the User Name and Password fields, enter the correct user name and password to authorize the database connection.
▪ Select Enabled to configure a one-way SSL or two-way SSL connection, and do the following:
a. In the Service Name field, enter the service name. You can retrieve the service name from the tnsnames.ora file that you created in Setting up One-Way SSL and Two-Way SSL connection for Oracle database.
b. In the Schema Name field, enter the schema name. By default, this is set to the user name. If you want to connect to the database of another user, then enter the user name of that user.
c. In the User Name and Password fields, enter the correct user name and password to authorize the database connection.
6. Click ADD to add the Oracle connector.
 | For an Oracle two-way SSL connection, if you remove the client certificate from the Oracle wallet, the connection to the database continues to work until you restart the ThingWorx Flow server. |