SQLServer Query

Use this activity to execute a simple or a complex SQL Query on a Microsoft SQL Server database. The SQLServer Query activity returns information in the form of rows.

Settings

The Settings tab has the following field.

Field Description
Connection Name of the Microsoft SQL Server database connection from where information is retrieved.
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 a query timeout might vary during runtime.
Select Schema

Select the appropriate schema from the Microsoft SQL Server database.

Input Settings

Field Description
Query An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form ?<fieldname> in the query statement. For example: 
select lastname, firstname, title from dbo.employees where lastname='?lastname';

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 into the substitution variable at runtime. Input and Output tabs are populated based on the query.

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

Following are some examples of simple and complex queries:

  • Simple query example: 

    SELECT * FROM dbo.employees;

    For the above query, the output fields are generated from the columns of the table employees. 

    SELECT lastname, firstname 
    FROM dbo.employees 
    WHERE country = ?country and lastname like '%ll%'
    ORDER BY lastname DESC, firstname ASC;

    For the above query, output fields are generated for lastname and firstname and input fields are generated for country. Also, the mapped value for the field country is substituted into the substitution variable ?country at run time.

  • Nested query example: 

  • SELECT categoryID, productName, MAX(unitprice)
    FROM dbo.products A
    WHERE unitprice = (
            SELECT
              MAX(unitprice)
            FROM 
              dbo.products B
            WHERE 
              B.categoryID = A.categoryID)
    GROUP BY categoryID, productName
    HAVING MAX(unitprice) > 100;
  • 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 the Manually Configuring Metadata.
Fields The grid is provided for informational purposes only.

Input

This tab contains the input schema. The fields that are selected on the Input Settings tab are available in the schema. You can either hard code their values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Output

This tab displays the activity output schema in a tree structure format. The output of an activity is displayed for informational purposes only and cannot be modified or altered. The information in this schema varies depending on the fields that you selected on the Input Settings tab.

The properties that are displayed on the Output tab schema correspond to the output of this activity and can be used as input by subsequent activities in the flow.

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.