Executes a SQL statement

Last published at: May 2nd, 2023

Description:

Executes an SQL statement against the selected database, if an select statement, returns an XML record set.

Inputs

  • singleResult – Returns a single result
  • connectionString – connection string to the database
  • changeDatabase - Connect to a different Database
  • sqlStatement – SQL statement to execute
  • variableName – variable/global to store the value from the SQL statement
  • resultFormat – Result Format
  • Connect to a different database - Connect to different database on the same connection string path
  • Return a single result - Select YES for 1 row & 1 column expected result
  • CommandTimeOut – Command timeout value (default 30 secs)

Returns

  • True – SQL statement executed successfully
  • False – SQL statement failed to execute

Usage:

Example:

Let’s build and execute the executeSQLDef example.                             

  • Create a new definition called “executeSQLDef”
  • Select the definition and click the “design” button
  • Drag an executeSQL step to the canvas
  • Connect the dots between the start and executeSQL step

  • Click on the “executeSQL” step to configure its properties
  • Configure the following values for the properties as shown on the below graphic

  • Save the workflow definition, create a workflow instance and execute. Step should execute the SQL query in selected database server and returns result in JSON or XML based on the result format.
  • Configure the following values to utilize the advanced features as shown on the below graphic
  • Configure the SQL Parameters as shown on the below graphic

  • The SQL Statement input can also accept a valid string with parameters (?) as shown above. The parameters are mentioned separately in a different UI. Take note to represent the parameters name and order of appearance as per the SQL string. 
  • Connect to a different database - mention the name of the other database if the SQL string requires to connect to (on the same connection string path)
  • Variable/Global to store the value - mention the name of the variable/global to store the result 
  • Return a single result - Enable "Yes" option if the result expected is 1 row & 1 colum and this value is directly stored (as is) in above variable/global. By default "No" option, stores the result (i.e. multiple rows/columns) in JSON / XML format as configured above. 
  • Command timeout value - Default 30 secs.  Gets or sets the wait time (in seconds) before terminating the attempt to execute a command and generating an error.   
  • Save the workflow definition, create a workflow instance and execute. Step should execute the SQL query in selected database server and returns result in JSON or XML based on the result format.