Raw SQL Step
  • 22 Mar 2022
  • 3 Minutes to read
  • Dark
    Light

Raw SQL Step

  • Dark
    Light

Article Summary

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:

WARNING!!!
The Raw SQL Step requires an advanced ability to write and understand SQL Queries. It does NOT sanitize inputs which introduces the potential for SQL injection vulnerabilities.
Each use of this step is treated as a separate call and could impact performance on systems with large SQL Query/call volumes.
To learn how to hide this step in the Flow Designer toolbox, visit Toolbox Visibility Rules.

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. 

Additional Information Regarding Transaction Steps 
The following example involves the configuration of Transaction steps. For more information on Transaction steps, see Using Transaction Steps. 

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.
Additional Information on Debug Results 
The changed Company information under the test@decisions.com Account confirms that the Raw SQL step utilized the Transaction info, in addition to the defined SQL Query, to pass the desired changes to the Commit Transaction step. 


For further information on Integrations, visit the Decisions Forum.



Was this article helpful?