Working with SQL Connectors in iPaaS

Overview

Use these instructions to set up one of the three SQL connectors available in iPaaS: Microsoft SQL Server, MySQL, and Oracle. SQL connectors require a valid connection string and credentials.

Configuring SQL Connectors

Learn how to create a SQL connector definition and credential in our knowledge base articles:

• Oracle: Connecting to iPaaS
• MySQL: Connecting to iPaaS
• Microsoft SQL: Connecting to iPaaS
• Snowflake: Connecting to iPaaS

Defining the Connection String

The SQL connection string specifies which server and database to connect to. You will need to enter the connection string later, when you configure the SQL connector in iPaaS.

The format of the connection string changes based on the type of SQL database you are connecting to.

 Data Source=servername:port/servicename

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=servername)(PORT=port))(CONNECT_DATA=(SERVICE_NAME=SID_or_databasename))); 

Data Source=myserver.teamdynamix.com:8080/tdx-customers

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.teamdynamix.com)(PORT=8080))(CONNECT_DATA=(SERVICE_NAME=tdx-customers))); 

server=servername,port;database=databasename

server=myserver.teamdynamix.com,8080;database=tdx-customers

account=accountname;db=databasename;schema=schemaname;

account=myaccount.us-east-2.aws;db=myDatabase;schema=tpch_sf1;

Applying the Connection String

Once the connection string is defined, you can apply it at the connector level or the flow level.

• Connector-level string: when the connector is added to the flow, the connection string box already has the default string in it. You can optionally lock the connector so that it cannot be overridden, effectively creating a SQL connector that can only interact with one database.
• Flow-level string: allows flow builders to specify the connection string when they add the connector to a flow.

To specify the connection string at the connector definition level:

1. On the top navigation bar, click Configuration > Connector Definitions.
2. To the left of the SQL connector, click the Gear Icon.
3. In the Connection String box, enter your connection string.
4. Click Save Changes.

To specify the connection string at the flow step level:

1. On the top navigation bar, click Applications > All Flows > [name of flow].
2. Drag a connector step from the Connectors/Notifications section of the flow toolbox to the flow builder and choose the SQL connector.
3. In the Connection String box, enter your connection string.
4. Click Save Changes.

Testing the SQL Connection

To test that both your credentials are working and your connection string is correct, you can do a basic select statement. If you receive an unauthorized error, you will know that your credentials are incorrect. If you receive another error, you will know that your connection string is incorrect.

To test the SQL connection:

1. Navigate to a flow you will use to test the connection
2. Drag a connector step from the Connectors/Notifications section of the flow toolbox to the flow builder and choose the SQL connector
3. In the Credentials dropdown, choose the credentials to use
4. From the Actions dropdown, choose Select
5. Type a select statement into the box below:
   1. For Microsoft SQL/MySQL: type SELECT 1
   2. For Oracle: type SELECT 1 FROM DUAL
6. Click Save Changes
7. In the top right corner of the flow design interface, click Test
8. To the right of the SQL step, click the play button

Adding Flow Data (Parameters) to SQL Steps

Sometimes, you may want to insert data from elsewhere in the flow into your SQL statement, rather than hand-typing it. You can use a special syntax to create variables or parameters that you can insert into your statements. This is extremely helpful when you want to add a bunch of rows to a table or do other bulk operations.

Dynamic SQL

All iPaaS SQL connectors include actions for each of the common SQL operations (e.g., SELECT, UPDATE, DELETE). These actions have certain limitations; for example, you can't use parameters to specify table names (e.g., SELECT * FROM @tableName).

In the event that the SQL command itself needs to be dynamic, such as passing a table name as a parameter, you can choose the Dynamic action. Any parameters entered will be evaluated in iPaaS before they are passed to the SQL database.

Parameter Syntax

Use a special prefix to indicate parameters in your SQL statement. This prefix varies by connector type.

• MySQL/Microsoft SQL - @parameterName
  • E.g., SELECT * FROM Users WHERE Type = @UserType
• Oracle/Snowflake - :parameterName
  • E.g., SELECT * FROM Users WHERE Type = :UserType

Adding Parameters to a SQL Statement

1. Add a SQL step to your iPaaS flow.
2. In the text area on the step, write your SQL statement, using the parameter syntax as described above.
3. In the bottom right corner of the SQL step, click Parse.
   • If you see a tab with the word Errors, click the tab to view any errors with your SQL statement. Resolve the errors and click Parse again.
4. Click on the Parameters tab. There should be one input bubble for each @parameter or :parameter you entered.
5. Type or use the data picker to fill in the data you would like to add in to your SQL statement.
6. Click Save Changes.

Executing Stored Procedures

1. Add a SQL step to your iPaaS flow.
2. Change the Action to Execute Stored Procedure.
3. In the SQL Command Text area, simply provide the name of the stored procedure.
4. In the bottom right corner of the SQL step, click Parse.
   • If you see a tab with the word Errors, click the tab to view any errors with your SQL statement. Resolve the errors and click Parse again.
5. Click on the Parameters tab. Any input variables in the stored procedure definition will be displayed.
6. Type or use the data picker to fill in the data you would like to add in to your SQL statement.
7. Click Save Changes.

Gotchas/Pitfalls

• When using any of the SQL connectors, you must specify column names in order for them to be selectable in the data picker. Using a wildcard, e.g., SELECT * will work, but the column names will not be selectable in the data picker.
• When using an Oracle connector, returned data will always have the column headers in ALL-CAPS. When selecting values with the data picker, it may show them in lower case. In this case, you will need to use a "custom" value from the data picker and type the column name in ALL-CAPS. 
• If you're trying to dynamically add parameters to a SQL query, you must:
  • In the flow, create a property with a list of the parameters
    • E.g., 'parameter1', 'parameter2', 'parameter3'
    • You can populate this list dynamically later in the flow (this also works on the list data type)
  • Make sure you're using the Dynamic action in the SQL connector
  • Under the Parameters tab, use the data picker to select your property
  • In your query where you want to pass in a dynamic list of parameters, use the {{}} around the name of the property rather than @
    • E.g., SELECT * FROM Users WHERE UserID IN ({{PROPERTY_NAME}})