SQLServer Update

Use this activity to execute an update statement on one or more rows of a Microsoft SQL Server table.

Settings

The Settings tab has the following field.

Field Description
Connection Name of the Microsoft SQL Server database connection from where information is retrieved. You can select a connection from the Connection dropdown list.
Query Timeout

Wait time in seconds for executing the query. The default value is 0, which means there is no timeout.

Note: Due to network and Database latency, the time to display error messages in case of query timeout might vary during runtime.
Select Schema

Select the appropriate schema from the Microsoft SQL Server database.

Input Settings

The Input Settings tab has the following field.

Field Description
Update Statement

An SQL statement used to update one or more rows in a table. You can construct prepared SQL queries by using substitution parameters of the form ?<fieldname> in the query statement.

As you begin typing a query, the auto-suggest dropdown menu displays relevant keywords, table names, and column names that you can select from. To choose a column name from the dropdown menu, first type the table name followed by a period (.), which shows the list of available column names for that table.
If you are unfamiliar with the tables present in the database schema, press the Ctrl + space key in the query editor to obtain auto-suggestion of all the tables and keywords present in the database schema.

Each substitution variable identifies an input parameter whose mapped value is substituted at run time. If the query contains parameters then only parameter metadata is populated.

End the query with semicolon (;) to fetch the metadata in the table at the bottom of the query.

The following example represents a typical update query: 

UPDATE dbo.pet SET Name=?Name , Color = ?Color WHERE Species = 'cat';

For the above query, all rows in the 'pet' table have their Name and Color columns set to the values provided on the Input tab where the species is cat. As usual all rows satisfying the where clause are updated. If the where clause is omitted then ALL rows are updated. The limit clause can also be used to control this behavior.

It is also possible to use a subquery to derive the values to be used in the update. The subquery format follows the general form: 

UPDATE schema_name.table_name
SET column1 = ?column1, column2 = ?column2...., columnN = ?columnN
WHERE [condition];
In this case the parameters supplied are used in the select query that provides replacement values in the outer update statement. The where clause could also be parameterized.

  • Call Procedure and Call Functions Syntax:
    • With no parameters
      Exec Procedure_Name;

Select Function_Name( );
    • With prepared parameters
      Exec  Procedure_Name @procedure_variable = ?param1;

Select Function_Name(?Param1, ?Param2);
Manually Configure Metadata Set the Manually Configure Metadata field to True to fetch table metadata manually. For more information, see Manually Configuring Metadata.
Fields The grid is provided for informational purpose only and documents the fields that are made available as parameters on the Input tab.

Input

This tab displays the input schema of the activity in a tree structure format. The information in the schema varies based on the update query provided. The fields that are selected on the Input Settings tab are available in the schema. You can either hard code the values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Substitution parameters from the query statement are provided in the Parameters node.

Output

The Update activity returns the number of rows affected by the query.

Loop

If you want this activity to iterate multiple times within the flow, enter an expression that evaluates the iteration details. Select a type of iteration from the Type menu. The default type is None, which means the activity does not iterate.

Retry on Error

For more information, see the Using the Retry on Error Feature in an Activity section in the Flogo® app documentation.