Raw SQL Step

Overview

The Raw SQL Step executes arbitrary SQL and should only be used when other methods of integrating with a database are not viable, including:

• Table Integration
• Query Integration

Example

The following example utilizes the Raw SQL Step to pass an SQL Query made against the Account table for a Decisions database. Users may use their own default Decisions database or they may integrate with another database. For more information on integrating a database, see Integrating an External Database with a Flow.

1. From a  Designer Project, select CREATE FLOW and CREATE a new Flow.
2. From the Flow Designer, attach a Raw SQL Step from Toolbox > INTEGRATION > DATABASE > ADVANCED, to the Start step.
3. From the Properties tab of the Raw SQL Step, under Connection, select the desired connection type (External Database).
4. PICK the desired Database (LOCAL CONNECTION). 
   Additional Connection Options

   If desired/required, users may select one the following options instead of the External Database option:
   
   

   • Use Current Connection (to query Decisions tables)
   • Use Custom Connection *(to define a connection to the database)*

   

5. Under OUTPUT SETTINGS, determine if the query will Return Data.
6. Check the Return Data box. 
7. Then select Data Rows as the Return Data option. 
   Additional Return Data Options 

   The following represents a list of all the available Return Data Options on the Raw SQL Step:

   • Select Type (if have a defined type that matches query output)
   • Data Rows (output will be represented as a collection of Rows with generic Columns)
   • CSV File (data represented in FileData format CSV File)
   • All Report Data (outputs Report Data type)
   • Excel File (data represented in FileData format Excel File)
   • PDF File (data represented in FileData format PDF File)

   

8. Under INPUTS, set SQL Command to Merge Plain Text, then click SHOW EDITOR. In the Merge Text Editor, define a SQL Query, then click SAVE. 

Debug

1. From the Flow Designer, select the Debug link, then click START DEBUGGING. 
2. When the Debugger runs, click on the Raw SQL Step, and select Execution 1 > View Input/Output Data.
3. The resulting window will show a user their top 10 listed Accounts.

   

Use Transaction Data

As a way to further integrate with Database Tables, users have the ability to use Transaction Data from Transaction steps as the CONNECTION SETTINGS for a Raw SQL Step. By doing so, users may update or add a value to a Database. 

To utilize this feature to update an entry in the Address Database Table: 

1. From the Flow Designer, attach a Start Transaction step from Toolbox > INTEGRATION > [NAMEOFDATABASE} (this example utilizes Toolbox > INTEGRATION > LOCAL CONNECTION (DATABASE), to the Start step. 
2. Select the Start Transaction step to navigate to its Properties and configure its INPUTS.
3. Set Commit Unresolved Transactions to False, set Isolation Level to ReadCommitted, and provide a Transaction Name.
4. From the Toolbox > INTEGRATION > DATABASE > ADVANCED category, attach a RAW SQL Step to the Start Transaction step. 
5. From the Properties of the RAW SQL Step, under CONNECTION SETTINGS, check the Use Transaction Data box. 
6. Under OUTPUT SETTINGS, check Return Data. 
7. Under Output Type, select Account.
8. Under SQL Command, Constant map the desired message. Select From Flow map Transaction Data to INPUTS > Transaction.
   Additional Information on SQL Command 

   The SQL Command should be a line of text in SQL Query format. This example, uses update entity_account set company='Decisions123' Where email_address='test@decisions.com'.

   

9. From the same category as the Start Transaction step, attach a Commit Transaction step to the Done path of the Raw SQL Step. From the newly added step's Properties tab, map Transaction Data to INPUTS > Transaction. 
10. Connect the Done path of the Commit Transaction and the Failed path of the Raw SQL step, to the End step. Save the Flow.
11. From the Flow Designer, select the Debug link from the top Action bar. Then, click START DEBUGGING. 
12. After the Flow runs, verify the data running in and out of the RAW SQL step by selecting RAW SQL Step > Execution 1 > View Input/Output Data. Then close out of the Debugger. 
13. From the Decisions Studio, navigate to Security > Accounts, and right-click test@decisions.com and select Edit Account. Confirm that the Company is defined as Decisions123.