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 a SQL Query made against the Account database onto a CSV Editor within a Form. 

1. From a Designer Folder, select CREATE FLOW from the Global Action Bar. Then, CREATE a new Flow.
2. From the Flow Designer, attach a Raw SQL Step from Steps >Integration > Database > Advanced, to the Start step. 
3. From the Properties tab of the Raw SQL Step, under Connection, select the desired Connection type (for this example External Database), then 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)*

   

4. Under Output Settings, determine if the query will Return Data. 
5. In the case of this example, check the Return Data box. 
6. Then under Return Data, select Data Rows.
   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)

   

7. Under Inputs, set the Mapping Type for SQL Command to Merge Plain Text, then click Show Editor. 

   

8. From the Merge Text Editor, define a SQL Query, then click SAVE. 
9. Connect the Done and Failed paths to the End step.
10. Select Debug from the Top Action Bar, and run the Debugger in Full.

    

11. When the Debugger runs, click on the Raw SQL Step, and select Execution 1 > View Input/Output Data.
12. The resulting window will show a user their top 10 listed Account and Group IDs.

    

Use Transaction Data 

As a way to further the ability to integrate with Database Tables, Decisions installations v.6.19+ (or 7.5+ in 7x) offer the ability to use Transaction Data from Transaction steps as the Connection Settings for the 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 Steps > Integration > [NAMEOFDATBASE} (this example utilizes Steps > Integration > LOCAL CONNECTION (Database), to the Start step. 
2. Select the Start Transaction step to navigate to its Properties and configure its Inputs; set Commit Unresolved Transactions to False, set Isolation Level to ReadCommitted, and provide a Transaction Name. 
3. From Steps > Integration > Database > Advanced, attach a Raw SQL Step to the Start Transaction step. 
4. From the Properties of the Raw SQL Step, under Connection Settings, check the Use Transaction Data box. 
5. Under Output Settings, check Return Data. Under Output Type, select the desired Output; for the sake of this example select Account. 
6. Under Inputs > SQL Command, Constant map the desired message. Then, still under the Inputs section, Select From Flow map Transaction Data to 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'.
   
   

   

7. From the same category as the Start Transaction step, attach a Commit Transaction step to the Done path of the Raw SQL Step. 
8. From the newly added step's Properties tab, map Transaction Data to Inputs > Transaction. 
9. Connect the Done path of the Commit Transaction and the Failed path of the Raw SQL step, to the End step. Save the Flow.
10. From the Flow Designer, select the Debug link from the top Action bar. Then, click FULL. 
11. 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. 
12. 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.